JDBC: What resources you have to close and when?
Posted by Jakub Holý on February 18, 2013
I was never sure what resources in JDBC must be explicitely closed and wasn’t able to find it anywhere explained. Finally my good colleague, Magne Mære, has explained it to me:
In JDBC there are several kinds of resources that ideally should be closed after use. Even though every Statement and PreparedStatement is specified to be implicitly closed when the Connection object is closed, you can’t be guaranteed when (or if) this happens, especially if it’s used with connection pooling. You should explicitly close your Statement and PreparedStatement objects to be sure. ResultSet objects might also be an issue, but as they are guaranteed to be closed when the corresponding Statement/PreparedStatement object is closed, you can usually disregard it.
Summary: Always close PreparedStatement/Statement and Connection. (Of course, with Java 7+ you’d use the try-with-resources idiom to make it happen automatically.)
PS: I believe that the close() method on pooled connections doesn’t actually close them but just returns them to the pool.
A request to my dear users: References to any good resources would be appreciate.
Frisian said
LMGTFY: http://www.javalobby.org/java/forums/t18930.html#91835154
The simplest template, that works correctly.
Jara Hamala said
Cau Jakube,
JSR-000221, 9.4.4.1, Connection.close:
“An application calls the method Connection.close() to indicate that it has
finished using a connection. All Statement objects created from a given
Connection object will be closed when the close method for the Connection
object is called.”
As you pointed out, It’s a bit more complicated when pooling is involved. I have a JBoss 5.1 EAP sources here and this is from the WrappedConnection:
public void close() throws SQLException { closed = true; if (mc != null) { if (trackStatements != BaseWrapperManagedConnectionFactory.TRACK_STATEMENTS_FALSE_INT) { synchronized (this) { if (statements != null) { for (Iterator<Map.Entry> i = statements.entrySet().iterator(); i.hasNext(); ) { Map.Entry entry = i.next(); WrappedStatement ws = entry.getKey(); if (trackStatements == BaseWrapperManagedConnectionFactory.TRACK_STATEMENTS_TRUE_INT) { Throwable stackTrace = entry.getValue(); log.warn("Closing a statement you left open, please do your own housekeeping", stackTrace); } try { ws.internalClose(); } catch (Throwable t) { log.warn("Exception trying to close statement:", t); } } } } } mc.closeHandle(this); } mc = null; dataSource = null; }A snippet from a documentation:
“track-statements : This boolean element specifies whether to check for unclosed statements when a connection is returned to the pool. If true, a warning message is issued for each unclosed statement. If the log4j category org.jboss.resource.adapter.jdbc.WrappedConnection has trace level enabled, a stack trace of the connection close call is logged as well. This is a debug feature that can be turned off in production.”
The moral of the story: “Always do your housekeeping!”
Jara
Jakub Holý said
Hi Jara, nice to hear from you
Thank you for supporting the point that closing statements manually is required with an example from a popular open source server. Migth the force be with you! 