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

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 2

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.