File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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 Make it so: Java DB Connections & Transactions this week in the JDBC 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!