Clean up repetitive setup and cleanup Java(JDBC) code

java, jdbc, refactoring

Solution

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.

Problem

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) ?

Original source

Related problems