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.

Answer 2

Use Lombok's cleanup, if you can:

@Cleanup
Connection c = ...
@Cleanup
statement = c.prepareStatement(...);
@Cleanup
rs = statement.execute(...);

This works translates to three nested try-finally blocks and works fine with exception. Never ever swallow an exception without a very good reason!

An alternative:

Write an own utility method like this:

public static void close(ResultSet rs, Statement stmt, Connection con) throws SQLException {
    try {
        try {
            if (rs!=null) rs.close();
        } finally {
            if (stmt!=null) stmt.close();
        }
    } finally {
        if (con!=null) con.close();
    }
}

and use it in

try {
    Connection con = ...
    Statement stmt = ...
    ResultSet rs = ...
} finally {
    close(rs, stmt, con);
}

and let the Exception bubble up or handle it as you want.

Answer 3

Well basically that is what you do except you first of all don't necessarily swallow the exception (you can null check and at least LOG the exception). Second, you can set up a nice utility class with things like

public static void close(ResultSet rs) {
   try { if (rs != null) rs.close();
   } catch (SQLException (e) {
      log.error("",e);
   } 

}

Then you just static import that class.

your finally becomes something like

finally {
     close(resultset);
     close(statement);
     close(connection);
}

which really isn't that hideous.

Answer 4

You only have to close the Connection.

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

From docs.oracle.com:

A Statement object is automatically closed when it is garbage collected. When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

Calling close() on a Connection releases its database and JDBC resources.

Answer 5

The only way I know to hide all that ugly try-catch boilerplate code is to utilize something like Spring's JBDC Template.

Answer 6

You can wrap one block into another block:

try{
  Connection c = ...
    try{
      statement = c.prepareStatement(...);
      try{
        rs = statement.execute(...);
      }finally{
        rs.close();
      }
    }finally{
      statement.close()
    }
  }finally{
    c.close();
  }
}catch(SQLException e){}

Use the lowermost catch-block for everything that might crop up