Clean up repetitive setup and cleanup Java(JDBC) code

Question

I've too many methods that repeatedly do something like

Statement stmt = null;
ResultSet rstmt = null;
try {
    stmt = conn.createStatement();
    rstmt = stmt.executeQuery(...);
    while (rstmt.next()) {
     //handle rows
    }


} catch (SQLException e) {
    //handle errors

} finally {
    try {rstmt.close();} catch (SQLException ex) {}
    try {stmt.close();} catch (SQLException ex) {}
}

This setup/teardown/cleanup of statements and resultsets is repetive and hides the interesting pieces of code.

Is there any pattern or idiom for handling this(without introducing any external framework) ?

Answer 1

You want the Execute Around idiom.

You may want to ask 'What is the "Execute Around" idiom?'.

(And if you like ASCII diagrams: my weblog on 'Factoring out exception handling')

Answer 2

Have a look at SimpleJDBCTemplate in Spring Framework. This does exactly what you want.

If you don't want to introduce an external framework then just use it for some inspiration to implement your own.

Answer 3

You should reconsider using Java persistence managers like iBatis and Hibernate. These automate a lot of the boilerplate away. I've been using iBatis, where the SQL statements are all neatly packaged and named in XML files, and the code volume has to be about 25% of a raw JDBC approach. You could gradually refactor your system to use iBatis.

Answer 4

you can create a method that receives the SQL query and an object to handle the ResultSet. for example:

private void executeSql(String sql, ResultSetHandler handler) {
  Statement stmt = null;
  ResultSet rstmt = null;
  try {
    stmt = conn.createStatement();
    rstmt = stmt.executeQuery(sql);
    while (rstmt.next()) {
      handler.handle(rstmt);
    }
  }
  catch (SQLException e) {
    //handle errors
  }
  finally {
    try {rstmt.close();} catch (SQLException ex) {}
    try {stmt.close();} catch (SQLException ex) {}
  }
}

with ResultSetHandler being an interface:

public interface ResultSetHandler {
  void handle(ResultSet rs) throws SQLException;
}

and you can create an object of an anonymous class implementing that interface, so it won't clutter too much.

Answer 5

Although it does not eliminate the set-up and tear-down logic, I often prefer this style for making JDBC interactions more pleasant:

Statement statement = connection.createStatement();
try {
    ResultSet results = statement.executeQuery(...);
    try {
        while (results.next()) {
            //handle rows
        }
    } finally {
        results.close();
    }
} finally {
    statement.close();
}

By nesting the try blocks, you automatically ensure that both results and statement will have their close() methods called without resorting to the try/catch statements in your finally block. As well, by starting the try blocks immediately after acquiring your objects, you don't need to worry about checking for null values (unless, of course, connection.createStatement() or statement.executeQuery(...) return null - In that case, you have bigger issues).