Java Specialists' Java Training Europehome of the java specialists' newsletter

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

GitHub Subscribe Free RSS Feed

Closing Database Statements

by Dr. Heinz M. Kabutz
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: Please see our new "Extreme Java" course, combining concurrency, a little bit of performance and Java 8. Extreme Java - Concurrency & Performance for Java 8.

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

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) { = name; = 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 ( {
          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


Tips and Tricks Articles Related Java Course

Extreme Java - Concurrency and Performance for Java 8
Extreme Java - Advanced Topics for Java 8
Design Patterns
In-House Courses

© 2010-2016 Heinz Kabutz - All Rights Reserved Sitemap
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. is not connected to Oracle, Inc. and is not sponsored by Oracle, Inc.