Java – MySQL and JDBC Connection Pool: unclosed statements

connection-poolingjavajdbcMySQL

I am reviewing a big pile of existing code, trying to find unclosed connections that would cause the connection pool to run out or throw other erros.

In some places I see the connection is returned to the pool, the ResultSet is closed, but the PreparedStatement is not closed.

in pseudo code it would look like this:

Connection conn = null;
try {
   conn = MyJdbcTemplateHolder.getNewConnectionFromPool();
   PreparedStatement ps = conn.prepareStatement(sql, ...);
   ResultSet rs = st.executeQuery();

   // do stuff with results

} catch(Exception e) {
   // exception
} finally {
   rs.close();
   MyJdbcTemplateHolder.returnConnectionToPool(conn);
   //***** Here is what's missing: st.close(); *****
}

The question is: can the open statement cause issues because it wasn't explicitly closed? Or is closing the ResultSet and returning the connection enough?

Obviously I am not talking about one open statement – we have a pool of 100 connections and dozens of places in the code where this issue may come up.

  • MySQL version is 5.1
  • My JDBC jar is mysql-connector-java-5.1.11-bin.jar

Best Answer

The answer is yes, it can cause issues. As is discussed here in SO:

if you don't close connection-related resources in reverse order after you're done with them (or in a finally block), you're at risk. Connection pools vary on how they handle these, but it is worrisome - a minimum - that an improperly closed set of resources is thrown back into the pool.

In case it was unclear (and you may already know this), proper closing of resources is discussed further here:

Note that in forthcoming Java 7, there will be some help here:

http://www.javaspecialists.eu/archive/Issue190.html

in which a new try-with-resources statement is introduced in Java, which automatically closes any AutoCloseable resources referenced in the try statement.

Related Topic