GeeCON Prague 2014*
The moose likes JDBC and the fly likes Best way to use a Connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Best way to use a Connection" Watch "Best way to use a Connection" New topic
Author

Best way to use a Connection

B Mayes
Ranch Hand

Joined: Apr 28, 2010
Posts: 47

Hi all,

My teammate and I have been working on an internal program and things are going pretty well at this point. He designed most of the JDBC stuff, and while it works -- I'm not very happy with the implementation. Basically, there is a static class variable like this:



Then inside of each method we obtain a new connection, and then close it out when the method finishes like this:





So my main concern (besides the crappy variable naming scheme), is that each method is opening a new connection and then closing it. This seems horribly inefficient. Couldn't we simply create one method that will obtain a connection if it's null, but return an existing connection otherwise? For example, I would like to do something like this:




Actually, even better would be to just store the database info as instance (or class) variables in the class and then just invoke getConnection() without any arguments. I'm curious though -- is there any reason that we shouldn't be doing things the second way? It seems like a much better way of handling connections to me...
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Use connection pooling.
If JDBC driver of your database doesn't provides connecion pooling, you can use for example this:
http://commons.apache.org/dbcp/

Simply initialize the pool, and if you need the connection in your program, get it from the pool, use it then close it,
the pool does the rest - you don't need to bother with maintaining connections.
B Mayes
Ranch Hand

Joined: Apr 28, 2010
Posts: 47

Very interesting -- I'll look into switching things to pools. I just re-implemented the entire code to use a persistent connection and it's noticeably faster than it was before. I think my team is small enough that I can get by without pooling -- but I'll definitely look into it. Thanks!!
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Best way to use a Connection