2

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
Galz
  • 6,713
  • 4
  • 33
  • 39

1 Answers1

2

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.

Community
  • 1
  • 1
kvista
  • 5,039
  • 1
  • 23
  • 25
  • 1
    +1 - YES! Short answer. Close everything JDBC in the reverse order that you opened it. Always, always, always. This needs to be an ingrained habit strong enough to fix it if you see it not done. – rfeak Mar 02 '11 at 16:34
  • Thanks kvista, I get it, and that's what I was afraid of... I think there is going to be a lot of work on this code. Does anyone know what kind of behavior I should expect because of the unclosed statements? Or what happens to them under the cover? – Galz Mar 02 '11 at 17:15
  • My personal experience has been the DB running out of connections, as they were not being cleaned up and lingered. The Pool would ask for new ones and eventually the DB wouldn't supply anymore. Over time the DB would eventually clean up the connections, but it was slower than the rate at which they were being "leaked". – rfeak Mar 02 '11 at 17:40
  • 1
    @Galz: I believe - but I'm not 100% positive - that at least one side effect with MySQL Connector J's driver (implementation of java.sql) is that the Statement is never unregistered with the Connection (see http://www.docjar.com/src/api/com/mysql/jdbc/StatementImpl.java), which possibly prevents the Connection from being cleaned up / garbage collected, and would seem to cause a memory leak, if nothing else. – kvista Mar 02 '11 at 17:55