The Java Specialists' Newsletter
Issue 1162005-11-28 Category: Tips and Tricks Java version: All

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.

Would you like to really understand Java concurrency? Join us for an in-depth study of how threading works in Java. During the course, you will learn how to write correct and fast multi-threaded Java code. Please click here if you would like to learn more.

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

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

Tips and Tricks Articles Related Java Course Discuss at The Java Specialist Club