aspose file tools*
The moose likes JDBC and the fly likes Database best practice Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Database best practice" Watch "Database best practice" New topic
Author

Database best practice

Gareth Baker
Greenhorn

Joined: Aug 08, 2007
Posts: 4
I have been trying to research best programing practices in relation to database queries and I have read a few websties which give the advice that you should always close Statements, PreparedStatements, ResultSet, etc before closing the connection and in doing so not to wait until the garbage collector decides to clean up these objects.

What facts are there to suggest that I will have better resource usage if I close statements and ResultSets before closing the connection? Has anyone seen a website i.e IBM, Sun, other that highly recommend this practice?
Is there any advantage to do the following?

Example:

Tempora Telora
Ranch Hand

Joined: Jun 20, 2005
Posts: 83
I have written against sybase and oracle. Closing your the connection after executing the statement I believe is a large part of controlling performance.

Here is a scenario. You have a small website where you have 20 users logged in at one time. If you didnt close your connection then that would be 20 concurrent connections to your db that are open.

vs

Only creating a connection when running a query/update/etc... against the db for the 20 users.

Great performance closing the connection after running your query.. Just be agile with your coding. If you know you are going to have to make a couple of query calls (for a single connection user) then try and do that in the same connection.
Tempora Telora
Ranch Hand

Joined: Jun 20, 2005
Posts: 83
I have written against sybase and oracle. Closing your the connection after executing the statement I believe is a large part of controlling performance.

Here is a scenario. You have a small website where you have 20 users logged in at one time. If you didnt close your connection then that would be 20 concurrent connections to your db that are open.

vs

Only creating a connection when running a query/update/etc... against the db for the 20 users.

Great performance closing the connection after running your query.. Just be agile with your coding. If you know you are going to have to make a couple of query calls (for a single connection user) then try and do that in the same connection.
Gareth Baker
Greenhorn

Joined: Aug 08, 2007
Posts: 4
Im not talking about closing the connection but rather do I get better resource usage by closing SQL Statements and or ResultSet before closing the connection?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30309
    
150

Originally posted by Gareth Baker:
Im not talking about closing the connection but rather do I get better resource usage by closing SQL Statements and or ResultSet before closing the connection?

It depends on the database driver implementation. The only way to find out for sure is to try it under load on your system and do some profiling. That said, it is good practice to always close the resources. What if you change drivers or upgrade the database in the future.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30309
    
150

Moving to our JDBC/database forum.
 
 
subject: Database best practice