Home of The JavaSpecialists' Newsletter

116Closing Database Statements

Posted: 2005-11-28Category: Tips and TricksJava Version: AllDr. Heinz M. Kabutz
 

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.

NEW: Refactoring to Java 8 Lambdas and Streams Workshop Are you currently using Java 6 or 7 and would like to see how Java 8 can improve your code base? Are you tired of courses that teach you a whole bunch of techniques that you cannot apply in your world? Check out our one day intensive Refactoring to Java 8 Lambdas and Streams Workshop.

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

 

Related Articles

Browse the Newsletter Archive

About the Author

demo

Java Champion, author of the Javaspecialists Newsletter, conference speaking regular... About Heinz

Java Training

We deliver relevant courses, by top Java developers to produce more resourceful and efficient programmers within their organisations.

Java Consulting

Nobody ever wants to call a Java performance consultant, but with first-hand experience repairing and improving commercial Java applications - JavaSpecialists are a good place to start...

Threading Emergency?

If your system is down, we will review it for 15 minutes and give you our findings for just 1 € without any obligation.