How to Correctly Close Resources

Question

As I was cleaning up some code, FindBugs pointed me to a bit of JDBC code that uses Connection, CallableStatement, and ResultSet objects. Here's a snippet from that code:

CallableStatement cStmt = getConnection().prepareCall("...");
...
ResultSet rs = cStmt.executeQuery();

while ( rs.next() )
{
    ...
}

cStmt.close();
rs.close();
con.close();

FindBugs pointed out that these should be within a finally block. I started refactoring my code to do this and I started to wonder how to handle the code within the finally block.

It's possible that the creation of the CallableStatement of Connection objects would throw an exception, leaving my ResultSet object as null. When I try to close the ResultSet, I'd get a NullPointerException and my Connection would, in turn, never get closed. Indeed, this thread brings up the same concept and shows that wrapping your close() invocations in a null check is a good idea.

But what about other possible exceptions? According to the Java API spec, Statement.close() can throw a SQLException "if a database error occurs". So, even if my CallableStatement is not null and I can successfully call close() on it, I might still get an exception and not have a chance to close my other resources.

The only "fail safe" solution I can think of is to wrap each close() invocation in its own try/catch block, like this:

finally {

    try {
        cStmt.close();
    } catch (Exception e) { /* Intentionally Swallow  Exception */ }

    try {
        rs.close();
    } catch (Exception e) { /* Intentionally Swallow  Exception */ }

    try {
        con.close();
    } catch (Exception e) { /* Intentionally Swallow  Exception */ }

}

Boy, if that doesn't look awful. Is there a better way to go about this?

Answer 1

I think the best answer has already being mentioned, but I thought it could be interesing to mention that you could consider the new JDK 7 feature of autoclosable resources.

try{
    try(Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/hrdb", "obiwan", "kenobi"); 
        Statement stm = conn.createStatement(); 
        ResultSet rs = stm.executeQuery("select name from department")) {

        while(rs.next()){
            System.out.println(rs.getString("name"));
        }

    } 
}catch(SQLException e){
    //you might wanna check e.getSuppressed() as well
    //log, wrap, rethrow as desired.
}

Not all of us can migrate to JDK 7 now, but for those who can start playing with the developer preview, this offer an interesting way of doing things and certainly may deprecate other approaches in the near future.