Generic helper for closing JDBC objects

Question

It is strongly suggested to close JDBC objects (connections, statements, result sets) when done using them. However, that produces loads of code like that:

Connection conn = null;
Statement stm = null;
ResultSet res = null;
try {
  // Obtain connection / statement, get results, whatever...
} catch (SQLException e) {
  // ...
} finally {
  if (res != null) { try { res.close(); } catch (SQLException ignore) {}}
  if (stm != null) { try { stm.close(); } catch (SQLException ignore) {}}
  if (conn != null) { try { conn.close(); } catch (SQLException ignore) {}}
}

Now I thought about reducing the amount of (repeating) code for closing the objects by implementing a helper function. It takes the objects as arguments and tries to invoke the method close() of each object (if the object does have such a method), using reflection.

public void close(Object... objects) {
  for (Object object : objects) {
    for (Method method : object.getClass().getMethods()) {
      if (method.getName().equals("close")) {
        try {
          method.invoke(object);
        } catch (Exception e) {
          e.printStackTrace();
        }
        break; // break on the methods, go for the next object
      }
    }
  }
}

The finally block can be reduced to this:

} finally {
  close(res, stm, conn);
}

Is that a good thing to do? If no, what are the reasons? Is there a "better" way?

Answer 1

Personally, I wouldn't use reflection in this case, when there are plenty of good ways to do this without needing it. Here are a couple things you can do.

1. Use Spring. Spring has a JdbcTemplate object that helps to alleviate the redundancy of Jdbc coding. The boilerplate code is hidden in the implementation of JdbcTemplate, so you are free to do what matters for your app.
2. Use Java7. Java7 provides a new language construct that makes it easier to close objects that implement the AutoClosable interface.
3. Create your own library to handle the boilerplate code. If Spring is too heavy for your needs, you can do all of the closing yourself easily in one base class, from which your Jdbc interaction classes can extend. You will have to write it once, but it can, for the most part, be out of sight and out of mind.

The Java7 way looks something like this:

try (
    Connection conn = getConnectionSomehow();
    Statement statement = getStatementFromConnSomehow(conn);
) {
    //use connection
    //use statement
} catch(SomeException ex) {
    //do something with exception
}//hey, check it out, conn and statement will be closed automatically! :)

Answer 2

This is fixed in java 7 with the new AutoCloseable feature and interface.

If you need to do it in java6 I would suggest working with aspectj and create an annotation for wrapping around the close call.

Answer 3

You don't need Java 7 and you don't need AOP or Spring (and you CERTAINLY don't need reflection). You can just invert the code and use classes as a poor man's closure like this (pseudocode):

public class DbExecutor {
    public static void exec(DbAction action) {
        Connection conn = null;
        try {
            action.consume(conn);
        } catch (SQLException e) {
            // ...
        } finally {
          if (conn != null) { try { conn.close(); } catch (SQLException ignore) {}}
        }

       }
}

public class DbAction {
    public abstract void consume(Connection conn) throws SQLException;

    public static class QueryOne extends DbAction {
        public List<String> myAnswer = new ArrayList<String>();

        @Override
        public abstract void consume(Connection conn) throws SQLException {
            Statement stm = conn.prepare(...);
            ResultSet res = stm.execute();
            while(res.hasNext()) {
                myAnswer.add(...);
            }
            //...
        }
    }

    public static class QueryTwo extends DbAction {...}
}

Then to use it you just do this:

DbAction.QueryOne qAction = new DbAction.QueryOne();
DbExecutor.exec(qAction);
System.out.println("found this many:" + qAction.myAnswer.size());

You are freed from connection management, you cannot forget to close, and closing the Connection should automatically take care of closing the Statement and ResultSet.

Note that anonymous inner classes could also be used, but getting results back can be more problematic than with this explicit approach.