This week's book giveaway is in the Artificial Intelligence and Machine Learning forum.
We're giving away four copies of Machine Learning with R: Expert techniques for predictive modeling and have Brett Lantz on-line!
See this thread for details.
Win a copy of Machine Learning with R: Expert techniques for predictive modeling this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Ganesh Patekar

Inserting into DB: how to handle exceptions and close Connection/Statement

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Basically I wanted to write a method that inserts new record into the database (MySQL, if that matters). Any logic such as: does this record already exist in the database? is excluded, for simplicity.

I have came up with this:


Here is another "permutation" of the above code, with PreparedStatement defined outside Try block and closing it in a Finally block:



This code just looks so ugly to me. Note: I think I can outsource lines 11 ~ lines 15 to another private method, but that's not the point of this thread.

And the third version also sets Connection and PreparedStatement instances to null:



My questions come down to:

1. What's wrong with my exception handling? I am sure there's room for improvement. Especially closing the PreparedStatement or Connection.

2. What should be the scope of instances of Connection or PreparedStatement? I can define both Connection and PreparedStatement inside a try block, or both outside a try block, or for example Connection outside and PreparedStatement inside.

I suppose the difference where I define it comes down to Exception handling. If I define PreparedStatement inside try block, then I cannot close it in finally block.

3. Because I've used PreparedStatement instead of Statement, is this sql injection safe? Is it that simple?

4. Basically, is this how it's done in pure Java? I have just started learning so I don't want to jump straight forward into Hibernate/Spring.

Thanks.
 
Saloon Keeper
Posts: 6246
58
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Likes 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use try-with-resources, it is much cleaner and more robust.
Don't catch exceptions. Let them bubble up to the caller.
 
Nani Ksowy
Greenhorn
Posts: 6
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And then I would need to do something like this? Whenever calling  registerNewUser method:

 
Carey Brown
Saloon Keeper
Posts: 6246
58
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Catch the SQLException and do some recovery.  Do NOT catch ClassNotFoundException. You can't recover from it. Let it bubble up.
 
Carey Brown
Saloon Keeper
Posts: 6246
58
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Alternatives:
  • Seeing as how a ClassNotFoundException is not recoverable you may want to implement some sort of exit() method that can shut down your application in a safe fashion; closing any open resources and preventing any further SQL calls.
  • You may want to report an SQLException and then re-throw it.

  •  
    Sheriff
    Posts: 6269
    167
    Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
    • Likes 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Is the Class.forName(String) needed, or is it just a way to test if the JDBC driver can be loaded?
     
    Carey Brown
    Saloon Keeper
    Posts: 6246
    58
    Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
     
    Marshal
    Posts: 65821
    250
    • Likes 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    I am very “rusty” about this. In the good old days, you had connector classes with static initialisers which set up the connection. So Class#forName(...) causes the class to be loaded and that runs the static initialiser. At least I think that is what happened. More recently, they introduced something different, but I can't remember what. There should be something in the Java™ Tutorials. Yes, there is.
     
    Master Rancher
    Posts: 260
    12
    IntelliJ IDE Spring Java
    • Likes 2
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    As of JDBC 4 it is no longer necessary, I think. Because as of then it is mandatory to have a META-INF/Service folder in the .jar file of the driver. That is how the DriverManager knows that a JAR file is a driver.
     
    Rancher
    Posts: 4275
    47
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    If it's a class 4 driver (which most of them are, including MySQL) then you don't need to do the class loading stuff.
    The DriverManager handles all that.

    Carey Brown wrote:Alternatives:

  • Seeing as how a ClassNotFoundException is not recoverable you may want to implement some sort of exit() method that can shut down your application in a safe fashion; closing any open resources and preventing any further SQL calls.



  • I wouldn't expect to see clean up code for the application to be at such a low level.
    You'd end up with calls to it scattered all over the place, wouldn't you?

    I'd expect to see that sort of thing as a general catch all somewhere up the top.
     
    Campbell Ritchie
    Marshal
    Posts: 65821
    250
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Moving to our databases forum.
     
    Oh the stink of it! Smell my tiny ad!
    Java file APIs (DOC, XLS, PDF, and many more)
    https://products.aspose.com/total/java
    • Post Reply Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!