General Assistant for Closing JDBC Objects

Question

It is highly recommended that you close JDBC objects (joins, statements, result sets) when they are executed using them. However, this creates many such codes:

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 was thinking about reducing the amount of (repeating) code to close objects by implementing a helper function. It takes objects as arguments and tries to call the close() method for each object (if the object has 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 summarized as follows:

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

This is a good thing? If not, what are the reasons? Is there a "better" way?

Answer 1

Personally, I will not use reflection in this case, when there are many good ways to do this without requiring it. Here are a few things you can do.

• Use Spring . Spring has a JdbcTemplate object that helps ease the redundancy of Jdbc coding. The template code is hidden in the JdbcTemplate implementation, so you can do what is important for your application.
• Use Java7 . Java7 provides a new language construct that simplifies the closure of objects that implement the AutoClosable interface.
• Create your own library to handle template code. If Spring is too heavy for your needs, you can easily do it all 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, disappear from sight and mind.

The Java7 path 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 this in java6, I would suggest working with aspectj and creating an annotation to wrap around a closed call.

Answer 3

You do not need Java 7, and you do not need AOP or Spring (and you NEED to reflect). You can simply invert the code and use the classes as a closed closure of this person (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 simply 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 can also be used, but getting results can be more problematic than with this explicit approach.

Answer 4

Close conn is enough, but it's a good code style to close res, stm secondly first.