I love reading Alex’s The Daily WTF and I noticed the recent Finally WTF is relevant to JDBC in an important way. All *good* JDBC developers already know you should close your result sets, statements, and connections (in that order) in a finally block when you are done with them, but do you all know how they should be closed? In particular, while a finally block will be entered under most circumstances, there’s no guarantee every line of code will be executed. Consider the following code I often come across:
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
// Do stuff
...
} catch (Exception e) {
// Do exception recovery stuff
...
} finally {
try {
rs.close();
stmt.close();
con.close();
} catch (Exception e) { }
}
Now, can you figure out what’s wrong with this code? Imagine if the result set rs is never populated and stays null. The first line of the finally block will throw a NullPointerException, and the stmt.close() and con.close() will never be executed. In other words, your failure to close a result set would lead to a connection never be closed even though it was in a finally block! Sure the code is guaranteed to enter the finally block, but if it fails on the first line, the rest of the code will be skipped. Next, compare this other common but still incorrect solution:
...
} finally {
try {
if(rs!=null) {rs.close();}
if(stmt!=null) {stmt.close();}
if(con!=null) {con.close();}
} catch (Exception e) { }
}
This solution is a little safer in that it avoids NullPointerExceptions, but it’s equally as useless as the first solution in that there are a number of reasons why the first line of code could still fail, for example if the result set is already closed. This solution actually worries me the most because clearly the developer went through the trouble of setting up the finally block and null pointer catches, but failed to fully understand how a finally block works. Now, I present a superior solution:
...
} finally {
try {rs.close();} catch (Exception e) {}
try {stmt.close();} catch (Exception e) {}
try {con.close();} catch (Exception e) {}
}
Now, is this solution safe? See that if rs or stmt fail to close, the call to con.close() will still be executed. Granted you could get fancy by adding logic to handle/log the exceptions or even catch Throwable (although catching Throwable’s never a good practice), but that’s a bit overkill. You could also nest your finally block with more finally blocks (just add finally block to the rs’s try/catch and put the rest inside it… and so on) although I tend to prefer this solution since it’s more readable.
Lastly, you could make helper methods for closing the the objects to make the code easier to work with such as:
...
} finally {
DBUtil.close(rs);
DBUtil.close(stmt);
DBUtil.close(con);
}
Where the try/catch is inside the helper methods. Keep in mind this last solution isn’t really different from the previous solution, just a code management improvement.
Hey Scott, it’s Bud Lefkof I was a consultant at Octagon while you were there. very cool site you have. Just wanted to mention for this Blogpost you could use the excellent apache Commons DBUtils. It has methods like DButisl.closeQuietly(rs); etc and cool DbUtils.commitAndCloseQuietly(conn); for doing mutliple things at once while handling NULLS and quelling SQLExceptions.
http://commons.apache.org/dbutils/
Melloware
http://www.melloware.com
Why should we bother closing the DB resources? They’re all automatically closed when they’re garbage collected.
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html#close%28%29
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#close%28%29
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Connection.html#close%28%29
@Adam
You make the mistaken assumption that you can predict what the garbage collector is going to do. In fact, the garbage collector isn’t likely to run until the system is close to running out of memory. If you never close any DB resources, you will run out of connections before you run out of memory. Also, keep in mind the garbage collector doesn’t have to run, nothing about the behavior is guaranteed and its far from the garbage collectors job to manage database resources.
Understood. I also supposed that if we close() the resource, then it can be reused immediately instead of waiting for the next garbage collection cycle.
One other question: Why would should we bother calling rs.close and stmt.close, when con.close is defined to automatically close those resources for us?
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Connection.html#close%28%29
> Releases this Connection object’s database and JDBC resources immediately instead of waiting for them to be automatically released.
In fact, looking at this JavaDoc, it seems that Sun’s intention is for developers to rely on connections being closed during garbage collection, and that calling close() is only required for special cases.
@Adam
Actually closing connections in large enough systems isn’t that common, especially with database pooling. Connections can be used for a lot of transactions, and the statements/resultsets use resources while the connection is open. Also, not all statements return result sets, such as statement.executeUpdate(), so you have to close it explicitly.
The general rule of thumb for JDBC development is explicitly close a resource (often through a finally block) as soon as it is not needed, or at least before the code does any other time consuming work such as calling other resources/connections. Many JDBC performance problems are caused by developers forgetting to close resources properly.
Perhaps, if every connection had exactly one statement and every statement had exactly one result set, your argument would make sense, but in practice this is not the case. In particular, such a system would not support transactions since the connection would be closed between any two calls.
I also understand that the implementation supplied by various JDBC driver vendors have their own idiosyncrasies. The JavaDoc seems vague, and there’s no guarantee how the JDBC driver implements it.
So it seems the only way to actually guarantee that one’s ResultSets, Statements and Connections get closed is by closing them yourself.
I’m happy now, thanks.
@Adam
Also, this assumes the database driver properly closes all resources when you close a connection. Some older driver don’t.
Pingback: Connecting to MS Access File via JDBC in 64-bit Java | Down Home Country Coding With Scott Selikoff and Jeanne Boyarsky