I’m an active member of the Tomcat-users mailing list and I see lots of folks that post questions about not being able to get a new database connection. The answer is simple: you have exhausted their JDBC connection pool. The answer is not so simple because the reasons for the situation can often be different, but most likely, your application is not properly handing pooled connections. Read on for information on how to code your app to properly handle such pooled connections.
I won’t go on and on about what a good idea connection pools are. That is either self-evident or covered elsewhere. What is not properly covered elsewhere is how to write your code correctly. They’re all throwaway examples that work under the best circumstances but fall apart when anything goes wrong.
When using a pooled connection, the idea is that you shouldn’t have to go out of your way to treat it specially. Technically, the techniques shown in this post are not just useful for pooled connections: it’s simply how you should code JDBC interactions, pooled or otherwise. The basics are simply: acquire your connection, and make sure you close everything before your method completes. Unfortunately, folks often forget that exceptions can be thrown and your cleanup code might not actually get called.
Below are two annotated code samples that should demonstrate everything you should be doing when handling JDBC connections. I hope this helps some folks out there.
The first code snippet is for when you are not engaging in a SQL transaction. Sure, you could use the transactional code in all cases, but the non-transactional one is simpler and will make your code easier to read and understand. It also won’t give you any “cannot rollback connection that isn’t in a transaction” errors. My code throws application-specific exceptions to demonstrate how to shield calling code from the “complexities” of JDBC.
// It's important that these references are /outside/ the try/catch
// and that they are set to null. The compiler will enforce this; I'm
// just being explicit.
Connection conn = null;
PreparedStatement ps = null; // I prefer PreparedStatements; use Statement if you want
ResultSet rs = null;
try
{
conn = ...; // Get your connection however you want
// Issue your queries and get your results.
// Remember: if you want to issue multiple queries and/or
// work with multiple result sets, either declare more Statement
// and ResultSet locals above and duplicate all cleanup logic for them,
// or make sure you close each object along the way before you
// try to re-use the reference.
}
catch (SQLException sqle)
{
// NEVER swallow exceptions. At least log them using "sql.printStackTrace()"
// Note that the root exception is being passed-along to the
// application-specific exception. This allows error messages to include
// the /full/ stack trace.
throw new ApplicationException("Error in database code", sqle);
}
finally
{
// Anyone who has done any real work in Java will know that a 'finally'
// block will be run after the 'try' block regardless of any exception
// activity.
// A couple of things to note, here:
// 1. Close objects in the proper order: result, then statement,
// then connection.
// 2. Each close gets its own try/catch block. You don't want
// the connection to be leaked just because the result set
// failed to close properly.
// 3. Don't throw any exceptions in a finally block. If there is
// already an exception "in the air", you'll shoot it down
// and replace it with a new one. The original exception is
// almost certainly more useful.
// 4. NEVER swallow an exception. At least log the error.
// 5. This cleanup code has whitespace removed for brevity.
// 6. This cleanup code lends itself to being put into a separate
// method. I usually have a 'close' method that takes 3 arguments:
// Connection, Statement, ResultSet and does the same thing.
if(null != rs) try { rs.close(); } catch (SQLException sqle)
{ sqle.printStackTrace(); }
if(null != ps) try { ps.close(); } catch (SQLException sqle)
{ sqle.printStackTrace(); }
if(null != conn) try { conn.close(); } catch (SQLException sqle)
{ sqle.printStackTrace(); }
}
That’s it. Not really that hard, but if you don’t have your try/catch block straight and the proper code in the finally block, they you are asking to leak connections or other stuff. Java might protect you from a lot of things by collecting its own garbage, but you can still bring down your database server by opening way too many connections and then leaking them all.
Transactions bring another problem to the table because you have to do a rollback unless you want the transaction to commit. Most connection pools default to “auto-commit” mode, and the JDBC spec states that calling setAutoCommit(true) commits a transaction if one was in progress. That means that you need to ensure that your transaction is rolled-back… otherwise it will be committed for you, and that’s probably not what you want.
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try
{
conn = ...; // Get your connection however you want
conn.setAutoCommit(false); // BEGIN
// Issue your queries and get your results.
conn.commit(); // COMMIT
}
catch (SQLException sqle)
{
// DO NOT allow the call to rollback to throw an exception. See the
// notes in the 'finally' block in the last example. You could even
// create your own 'rollback' method to simply do this to clean-up
// your code a bit.
if(null != conn) try { conn.rollback(); } catch (SQLException sqle1)
{ sqle1.printStackTrace(); }
throw new ApplicationException("Error in database code", sqle);
}
catch (ApplicationException ae)
{
// Yes, catch ApplicationException. Anything that the current method can
// throw /must/ be caught and re-thrown.
if(null != conn) try { conn.rollback(); } catch (SQLException sqle)
{ sqle.printStackTrace(); }
throw ae; // Re-throw the same exception
}
catch (RuntimeException rte)
{
// You wouldn't want a NullPointerException to end up committing your
// partial transaction, would you?
if(null != conn) try { conn.rollback(); } catch (SQLException sqle)
{ sqle.printStackTrace(); }
throw rte; // Re-throw the same exception
}
catch (Error e)
{
// Errors, too!
if(null != conn) try { conn.rollback(); } catch (SQLException sqle)
{ sqle.printStackTrace(); }
throw e; // Re-throw the same error
}
finally
{
// Here, I'm taking my own advice to put the cleanup code into a
// separate method. See how nice this looks?
this.close(conn, ps, rs);
}
Following the code samples above will keep your DBAs very happy, and you’ll never have post a message to a mailing list and have to admit that you really didn’t know what you were doing when it comes to JDBC programming.
I have a few more general tips for JDBC, connection pools, etc.:
- In development, set your connection pool to a fixed size of 1 connection. This will let you know immediately if you have any deadlock potential in your code when trying to request more than one connection at a time from the pool. If you do request more than one connection at a time, then there is a possibility that you could deadlock your application waiting on database connections. Just do it. It won’t hurt, and you might be surprised to find out that you forgot to ‘close’ a connection (and therefore return it to the pool). Using this technique will find the error much faster than others.
- Use a “validation query”. When you request a connection from the pool, you can have the pool validate that the connection is still valid (i.e. connected, etc.) by issuing a simple query to the database. Make this a simple query. Queries like “SELECT * FROM purchase” are bad ideas. Choose something like “SELECT 1 FROM DUAL” (for you Oracle folks) or “SELECT 1” for databases that don’t require a table name for trivial SELECT queries. If you’re using MySQL, use “/* ping */ SELECT 1” since recent versions of Connector/J can detect the “/* ping */” part of the query and issue a super-cheap connection test that doesn’t even involve the query parser. Why issue a trivial query when you don’t have to?
Your Catchphrase Here 