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

One of the real values of abstracting this sort of rubbish out your code is that you'll make sure your close statement don't NPE (hopefully using the `acquire; try { use; } finally { release; }` idiom. — Tom Hawtin - tackline, Jul 19 '09 at 23:42
Duplicate: http://stackoverflow.com/questions/1072925/remove-boilerplate-from-db-code/1072949#1072949 — Nick Holt, Jul 20 '09 at 07:29

score 10 · Answer 1 · answered Jul 19 '09 at 22:40

10

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.

answered Jul 19 '09 at 22:40

Mark

28,783
8
63
92

+1 from me. This is exactly the right thing to do. Either using Spring as inspiration or incorporating it into your project will be beneficial. – duffymo Jul 19 '09 at 22:57
In addition to hiding this messy setup\teardown code, Spring will improve the exception handling in 2 ways: 1) it will handle the SQLException and 'regurgitate' it as something more meaningful 2) the exception that is 'regurgitated' will be unchecked, so you don't have to catch or throw anything. – javamonkey79 Jul 19 '09 at 23:14

Tom Hawtin - tackline · Answer 2 · 2021-09-21T10:12:07.660

4

You want the Execute Around idiom.

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

edited Sep 21 '21 at 10:12

answered Jul 19 '09 at 22:33

Tom Hawtin - tackline

145,806
30
211
305

cd1 · Accepted Answer · 2009-07-20T00:43:07.380

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.

Probably want to make that interface method throw at least SQLException. — Tom Hawtin - tackline, Jul 19 '09 at 23:41

Jim Ferrans · Answer 4 · 2009-07-20T13:04:53.513

1

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.

edited Jul 20 '09 at 13:04

answered Jul 19 '09 at 23:29

Jim Ferrans

30,582
12
56
83

score 0 · Answer 5 · answered Jul 20 '09 at 13:25

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

I suppose beauty is in the eye of the beholder. I personally find this form more "appealing" than the form presented in the question. — Adam Paynter, Jul 20 '09 at 22:01

Clean up repetitive setup and cleanup Java(JDBC) code

5 Answers5