CODEWITHSUNDEEP
javajdbcrefactoring
nos
nos

Reputation: 229264

Clean up repetitive setup and cleanup Java(JDBC) code

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

Upvotes: 5

Views: 2036

Answers (5)

Tom Hawtin - tackline
Tom Hawtin - tackline

Reputation: 147154

You want the Execute Around idiom.

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

Upvotes: 4

Adam Paynter
Adam Paynter

Reputation: 46938

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

Upvotes: 0

Jim Ferrans
Jim Ferrans

Reputation: 31012

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.

Upvotes: 1

cd1
cd1

Reputation: 16534

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.

Upvotes: 4

Mark
Mark

Reputation: 29139

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.

Upvotes: 10

Related Questions