This week's book giveaway is in the JavaFX forum.
We're giving away four copies of Introducing JavaFX 8 Programming and have Herbert Schildt on-line!
See this thread for details.
The moose likes JDBC and Relational Databases 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

Win a copy of Introducing JavaFX 8 Programming this week in the JavaFX forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Best way to use a Connection" Watch "Best way to use a Connection" New topic

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:

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!!
I agree. Here's the link:
subject: Best way to use a Connection
It's not a secret anymore!