How many JDBC connections in Java ?

Question

I have a Java program consisting of about 15 methods. And, these methods get invoked very frequently during the exeuction of the program. At the moment, I am creating a new connection in every method and invoking statements on them (Database is setup on another machine on the network).

What I would like to know is: Should I create only one connection in the main method and pass it as an argument to all the methods that require a connection object since it would significantly reduce the number of connections object in the program, instead of creating and closing connections very frequently in every method.

I suspect I am not using the resources very efficiently with the current design, and there is a lot of scope for improvement, considering that this program might grow a lot in the future.

Answer 1

You should use a connection pool for that.

That way you could ask for the connection and release it when you are finish with it and return it to the pool

If another thread wants a new connection and that one is in use, a new one could be created. If no other thread is using a connection the same could be re-used.

This way you can leave your app somehow the way it is ( and not passing the connection all around ) and still use the resources properly.

Unfortunately first class ConnectionPools are not very easy to use in standalone applications ( they are the default in application servers ) Probably a microcontainer ( such as Sping ) or a good framework ( such as Hibernate ) could let you use one.

They are no too hard to code one from the scratch though.

:)

This google search will help you to find more about how to use one.

Skim through

Answer 2

You can either pass in the connection or better yet use something like Jakarta Database Connection Pooling. http://commons.apache.org/dbcp/

Answer 3

Yes, you should consider re-using connections rather than creating a new one each time. The usual procedure is:

• make some guess as to how many simultaneous connections your database can sensibly handle (e.g. start with 2 or 3 per CPU on the database machine until you find out that this is too few or too many-- it'll tend to depend on how disk-bound your queries are)
• create a pool of this many connections: essentially a class that you can ask for "the next free connection" at the beginning of each method and then "pass back" to the pool at the end of each method
• your getFreeConnection() method needs to return a free connection if one is available, else either (1) create a new one, up to the maximum number of connections you've decided to permit, or (2) if the maximum are already created, wait for one to become free
• I'd recommend the Semaphore class to manage the connections; I actually have a short article on my web site on managing a resource pool with a Semaphore with an example I think you could adapt to your purpose

A couple of practical considerations:

• For optimum performance, you need to be careful not to "hog" a connection while you're not actually using it to run a query. If you take a connection from the pool once and then pass it to various methods, you need to make sure you're not accidentally doing this.
• Don't forget to return your connections to the pool! (try/finally is your friend here...)
• On many systems, you can't keep connections open 'forever': the O/S will close them after some maximum time. So in your 'return a connection to the pool' method, you'll need to think about 'retiring' connections that have been around for a long time (build in some mechanism for remembering, e.g. by having a wrapper object around an actual JDBC Connection object that you can use to store metrics such as this)
• You may want to consider using prepared statements.
• Over time, you'll probably need to tweak the connection pool size

Answer 4

If your application is single-threaded, or does all its database operations from a single thread, it's ok to use a single connection. Assuming you don't need multiple connections for any other reason, this would be by far the simplest implementation.

Depending on your driver, it may also be feasible to share a connection between threads - this would be ok too, if you trust your driver not to lie about its thread-safety. See your driver documentation for more info.

Typically the objects below "Connection" cannot safely be used from multiple threads, so it's generally not advisable to share ResultSet, Statement objects etc between threads - by far the best policy is to use them in the same thread which created them; this is normally easy because those objects are not generally kept for too long.

Answer 5

Many JDBC drivers do connection pooling for you, so there is little advantage doing additional pooling in this case. I suggest you check the documentation for you JDBC driver.

Another approach to connection pools is to

• Have one connection for all database access with synchronised access. This doesn't allow concurrency but is very simple.
• Store the connections in a ThreadLocal variable (override initialValue()) This works well if there is a small fixed number of threads.

Otherwise, I would suggest using a connection pool.