116Closing Database Statements

Author: Dr. Heinz M. KabutzDate: 2005-11-28Java Version: 1.0Category: Tips and Tricks

Abstract: Don't Repeat Yourself. The mantra of the good Java programmer. But database code often leads to this antipattern. Here is a neat simple solution from the Jakarta Commons DbUtils project.

Welcome to the 116th edition of The Java(tm) Specialists' Newsletter. On Wednesday we are celebrating our 5th anniversary, and to mark this occasion, we have completely revamped our entire website. Please check it out!

Some new functions are available, e.g. you can now jump more easily to other newsletters in the same category. I would love to hear your comments :)

There are lots of people that helped with this team effort: Jonathan Selibowitz did the layout and graphic design, Danell Pienaar did the copywriting, Peter Koolman helped with the HTML implementation of the layout, John Green helped me implement the Java backend and my ISP Hetzner helped getting this site running under Tomcat.

javaspecialists.teachable.com: Please visit our new self-study course catalog to see how you can upskill your Java knowledge.

Closing Database Statements

Most of the memory leaks that I have seen with Java occurred when developers forgot to close database statements. This usually is compounded by violation of the DRY principle (Don't Repeat Yourself), so that a mistake in closing logic propagates to many places.

On another note, when I have to use JDBC directly, I usually use only PreparedStatement. This helps in many ways, especially in delimiting Strings and escaping special characters. So, instead of writing:

    ...
    Statement st = con.createStatement();
    st.executeUpdate("INSERT INTO subscribers (name, email) " +
      "VALUE ('" + name + "','" + email + "')");
    ...

I rather write the following code:

    ...
    PreparedStatement st = con.prepareStatement(
        "INSERT INTO subscribers (name, email) VALUE (?, ?)");
    st.setString(1, name);
    st.setString(2, email);
    st.executeUpdate();
    ...

A few years ago, I wanted to purchase O'Gradys crisps at the Pick 'n Pay Supermarket online shop. The result was a SQL exception displayed on their ASP page. The PreparedStatement eliminates this problem. There are other benefits and disadvantages with PreparedStatement, that I want to deal with in another newsletter.

However, the problem still remains of how to write code that closes the statements and result sets reliably. You want to always attempt to close the statements, whether an exception occurs or not.

In the Jakarta Commons DbUtils project, functions are provided to ensure that your statements are also always closed. DbUtils is designed to be (according to the docs):

• Small - you should be able to understand the whole package in a short amount of time.
• Transparent - DbUtils doesn't do any magic behind the scenes. You give it a query, it executes it and cleans up for you.
• Fast - You don't need to create a million temporary objects to work with DbUtils.

This is how you would use its QueryRunner:

import org.apache.commons.dbutils.QueryRunner;
import java.sql.*;

public class Database2 {
  private QueryRunner queryRunner = new QueryRunner();
  public int insertSubscriber(Connection con, String name, String email)
      throws SQLException {
    String sql = "INSERT INTO subscribers (name, email) VALUE (?, ?)";
    Object[] params = { name, email };
    return queryRunner.update(con, sql, params);
  }
}
    

The QueryRunner's methods are threadsafe since it does not remember any state. Inside the update() method, the class ensures that the statement is closed afterwards.

It is also fairly easy to run queries. Let's say we have a Subscriber class and we want the QueryRunner to return a collection of these:

public class Subscriber {
  private String name;
  private String email;
  public Subscriber(String name, String email) {
    this.name = name;
    this.email = email;
  }
  public String getName() {
    return name;
  }
  public String getEmail() {
    return email;
  }
}

QueryRunner.query() now takes as a parameter a ResultSetHandler implementation, which I would recommend you make an anonymous inner class:

  public Collection<Subscriber> selectSubscribers(Connection con)
      throws SQLException {
    String sql = "SELECT name, email FROM subscribers";
    Object[] params = null;
    ResultSetHandler handler = new ResultSetHandler() {
      public Object handle(ResultSet rs) throws SQLException {
        Collection<Subscriber> result = new ArrayList<Subscriber>();
        while (rs.next()) {
          int column = 1;
          String name = rs.getString(column++);
          String email = rs.getString(column++);
          result.add(new Subscriber(name, email));
        }
        return result;
      }
    };
    return (Collection<Subscriber>) queryRunner.query(
        sql, params, handler);
  }
    

In addition, I recommend wrapping the call to queryRunner.query() and queryRunner.update() in methods, so that you can easily change the implementation. This also allows you to retry a few times in case of temporary database failure.

I never want to write database statement closing code again!

Please spend a few minutes looking at our new website and send me your comments, good and bad :)

Kind regards

Heinz

Comments

We are always happy to receive comments from our readers. Feel free to send me a comment via email or discuss the newsletter in our JavaSpecialists Slack Channel (Get an invite here)

Related Articles

Browse the Newsletter Archive