• Post Reply Bookmark Topic Watch Topic
  • New Topic

My first java application - Connecting to MySQL  RSS feed

 
Andrew Great
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I'm new to Java, I've been learning / working on it for about 3 days now and have come up with an application which I would like help & feedback on.

To be honest a lot of this has been on guess work and trial & error.





 
Carey Brown
Saloon Keeper
Posts: 3328
46
Eclipse IDE Firefox Browser Java MySQL Database VI Editor Windows
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you introduce confusion with the two names: "JSoup" and "Jsoup". I think you could find a better name for your class.

Your MySQL.connect() method should return a connection, otherwise if something goes wrong, throw an exception (with a meaningful message). This would make the method cleaner as well.
 
Dave Tolls
Ranch Foreman
Posts: 3068
37
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


You can skip this bit entirely.
MySQL's driver is a Type 4 so it can be automatically discovered by the DriverManager...no need for this boiler plate.

You have an awful lot of static there.
 
Andrew Great
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Following your advice Dave, I've removed the unnecessary code, and cleaned up the messages a little.

Carey, I'm not sure what you mean about returning a connection because I do have a "return connection" in there. Can you please clarify.

 
Dave Tolls
Ranch Foreman
Posts: 3068
37
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

This bit.
Just throw the exception.
The way you are using this method well mean a failure results in a NullPointerException, which won't actually tell you the problem (which would be a failure to create a connection).
SO you may as well simply leave the SQLException to propagate up.

This does show up something else as well:

The connection cannot be null at this point.
Either getConnection() worked and you have a connection, or it threw an exception, so this code will never be accessed.
So you can skip that whole if/else check.

Throwing code away is good!
 
Andrew Great
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So if I understood that correctly, if there is a problem connecting, the connect() will catch the SQLException and re-throw it back up to wind() ?

Something like this?





 
Dave Tolls
Ranch Foreman
Posts: 3068
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
At which point you may as well get rid of the try/catch:



And you're down to a nice clean two lines of (non-debug) code.
 
Dave Tolls
Ranch Foreman
Posts: 3068
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Which now highlights another issue.

You never close the connection.
So every time you call the updateWind() method it opens a new connection.
You'll eventually flood your database.

You need to be closing your connection (inside a try-with-resources) in updateWind().
 
Andrew Great
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is something I wasn't too sure about.
So lets say I have 10 classes as part of my application, all of which require a database connection.
Do I open a connection and close the connection every time a method is called, or open once and close it within that loop?
 
Dave Tolls
Ranch Foreman
Posts: 3068
37
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It depends.
If everything done in that loop should be a single transaction then it has to be a single connection, then commit at the end.

If not then you need to decide what work classes as a transaction.
 
Andrew Great
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm used to coding in PHP where I open general connection at the very start of the code, then close the connection at the very end.
Since I will have a lot of classes which use MySQL, it makes sense to open one connection, run the various methods, then close the connection, all within that delayed loop.

Will this work?





 
Andrew Great
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What's that autobot?


Server Error in '/' Application.

The resource cannot be found.
Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable.  Please review the following URL and make sure that it is spelled correctly.

Requested URL: /java/total-component.aspx

Version Information: Microsoft .NET Framework Version:2.0.50727.6421; ASP.NET Version:2.0.50727.6427
 
Andrew Great
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm really struggling with this

I've tried about 15 different tutorials now but they code in a procedural style withing classes.

How do I open a connection, run several class using a prepared statement, then close the connection?
 
Andrew Great
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This seems to work perfectly but it's constantly opening and closing the connection every time I run a query.

Maybe this is the right way, I don't know?



 
Knute Snortum
Sheriff
Posts: 4281
127
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here is a way to have only one DB connection.  (Others may improve on it.)

  • Make your static variable conn private.
  • In connect(), test whether conn is null.  It will be the first time.
  • If conn is null, get the connection.
  • Regardless (outside the if statement), return conn.


  • Now when you want a connection to the DB, call connect(); don't access conn directly.  connect() will create the connection the first time and just return that connection subsequent times.
     
    Carey Brown
    Saloon Keeper
    Posts: 3328
    46
    Eclipse IDE Firefox Browser Java MySQL Database VI Editor Windows
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    If you are going to close the connection on line 27 then you should reset the conn variable back to null. This tends to lead one to create a close() method to handle this small bit of logic.
     
    Andrew Great
    Greenhorn
    Posts: 20
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Knute Snortum wrote:Here is a way to have only one DB connection.  (Others may improve on it.)

  • Make your static variable conn private.
  • In connect(), test whether conn is null.  It will be the first time.
  • If conn is null, get the connection.
  • Regardless (outside the if statement), return conn.


  • Now when you want a connection to the DB, call connect(); don't access conn directly.  connect() will create the connection the first time and just return that connection subsequent times.


    Perfect, thanks for the advice !!
     
    Andrew Great
    Greenhorn
    Posts: 20
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    The application is coming along nicely now thanks to the advice given above.

    I've noticed there are two ways to call a method in another class, while both work, which is the "standard" way?



    or

     
    Andrew Great
    Greenhorn
    Posts: 20
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    I actually meant...




    or

     
    Campbell Ritchie
    Marshal
    Posts: 56578
    172
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    The latter, of course, because you are using an object. The former way would require the method be static, and things static are always the exception to the rule, never standard. You can use a third method which is not for static methods, but works if you never need to use that particular object reference again:-
    new MySQL(...).connect(...);
     
    Andrew Great
    Greenhorn
    Posts: 20
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    The application was coming along nicely but I've hit a problem...

    Exception in thread "main" java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x95' for column 'info' at row 1

    I guess this is because I'm trying to insert a UTF8 character into the database, but after changing the database I'm still getting the problem and I don't know how to fix it.

    I've already got my table set to utf8mb4, as set below...
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
     
    Campbell Ritchie
    Marshal
    Posts: 56578
    172
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    What do those characters mean and what is the text supposed to be? Have you tried it from the command line?
     
    Andrew Great
    Greenhorn
    Posts: 20
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    I've tried installing it through the MySQL workbench and it fails with a similar error...

    Error Code: 1366. Incorrect string value: '\xF0\x9D\x8C\x86' for column 'info' at row 1 0.000 sec

    I suppose that makes it a MySQL issue. Just wondering if anyone know the cause?
     
    Andrew Great
    Greenhorn
    Posts: 20
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Re-installing MySQL and forced the "utf8mb4" settings into the config file, seems to have fixed that issue

     
    Campbell Ritchie
    Marshal
    Posts: 56578
    172
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Seems a bit drastic, but well done sorting it out
     
    Andrew Great
    Greenhorn
    Posts: 20
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Next educational question...

    I have a method, lets say it's called getCustomerData().
    This method will get the customer name, customer age, and country of the customer.
    My thinking was to create an array, and return that.
    Then in the receiving method, do something like:



    This works, but I'm just wondering if there is an easier option?
     
    Andrew Great
    Greenhorn
    Posts: 20
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    That was a complete fail

    Try again...

     
    Dave Tolls
    Ranch Foreman
    Posts: 3068
    37
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    You should have a Customer class that represents the data, and the DAO (your mysql class) should create one and return that.
     
    • Post Reply Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!