A Guide to Apache Commons DbUtils

1. Overview

Apache Commons DbUtils is a small library that makes working with JDBC a lot easier.

In this article, we’ll implement examples to showcase its features and capabilities.

2. Setup

2.1. Maven Dependencies

First, we need to add the commons-dbutils and h2 dependencies to our pom.xml:

<dependency>
    <groupId>commons-dbutils</groupId>
    <artifactId>commons-dbutils</artifactId>
    <version>1.7</version>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.196</version>
</dependency>

You can find the latest version of commons-dbutils and h2 on Maven Central.

2.2. Test Database

With our dependencies in place, let’s create a script to create the tables and records we’ll use:

CREATE TABLE employee(
    id int NOT NULL PRIMARY KEY auto_increment,
    firstname varchar(255),
    lastname varchar(255),
    salary double,
    hireddate date,
);

CREATE TABLE email(
    id int NOT NULL PRIMARY KEY auto_increment,
    employeeid int,
    address varchar(255)
);

INSERT INTO employee (firstname,lastname,salary,hireddate)
  VALUES ('John', 'Doe', 10000.10, to_date('01-01-2001','dd-mm-yyyy'));
// ...
INSERT INTO email (employeeid,address)
  VALUES (1, '[email protected]');
// ...

All example test cases in this article will use a newly created connection to an H2 in-memory database:

public class DbUtilsUnitTest {
    private Connection connection;

    @Before
    public void setupDB() throws Exception {
        Class.forName("org.h2.Driver");
        String db
          = "jdbc:h2:mem:;INIT=runscript from 'classpath:/employees.sql'";
        connection = DriverManager.getConnection(db);
    }

    @After
    public void closeBD() {
        DbUtils.closeQuietly(connection);
    }
    // ...
}

2.3. POJOs

Finally, we’ll need two simple classes:

public class Employee {
    private Integer id;
    private String firstName;
    private String lastName;
    private Double salary;
    private Date hiredDate;

    // standard constructors, getters, and setters
}

public class Email {
    private Integer id;
    private Integer employeeId;
    private String address;

    // standard constructors, getters, and setters
}

3. Introduction

The DbUtils library provides the QueryRunner class as the main entry point for most of the available functionality.

This class works by receiving a connection to the database, a SQL statement to be executed, and an optional list of parameters to supply values for the placeholders of the query.

As we’ll see later, a few methods also receive a ResultSetHandler implementation – which is responsible for transforming ResultSet instances into the objects our application expects.

Of course, the library already provides several implementations that handle the most common transformations, such as lists, maps, and JavaBeans.

4. Querying Data

Now that we know the basics, we’re ready to query our database.

Let’s start with a quick example of obtaining all the records in the database as a list of maps using a MapListHandler:

@Test
public void givenResultHandler_whenExecutingQuery_thenExpectedList()
  throws SQLException {
    MapListHandler beanListHandler = new MapListHandler();

    QueryRunner runner = new QueryRunner();
    List<Map<String, Object>> list
      = runner.query(connection, "SELECT * FROM employee", beanListHandler);

    assertEquals(list.size(), 5);
    assertEquals(list.get(0).get("firstname"), "John");
    assertEquals(list.get(4).get("firstname"), "Christian");
}

Next, here’s an example using a BeanListHandler to transform the results into Employee instances:

@Test
public void givenResultHandler_whenExecutingQuery_thenEmployeeList()
  throws SQLException {
    BeanListHandler<Employee> beanListHandler
      = new BeanListHandler<>(Employee.class);

    QueryRunner runner = new QueryRunner();
    List<Employee> employeeList
      = runner.query(connection, "SELECT * FROM employee", beanListHandler);

    assertEquals(employeeList.size(), 5);
    assertEquals(employeeList.get(0).getFirstName(), "John");
    assertEquals(employeeList.get(4).getFirstName(), "Christian");
}

For queries that return a single value, we can use a ScalarHandler:

@Test
public void givenResultHandler_whenExecutingQuery_thenExpectedScalar()
  throws SQLException {
    ScalarHandler<Long> scalarHandler = new ScalarHandler<>();

    QueryRunner runner = new QueryRunner();
    String query = "SELECT COUNT(*) FROM employee";
    long count
      = runner.query(connection, query, scalarHandler);

    assertEquals(count, 5);
}

To learn all the ResultSerHandler implementations, you can refer to the ResultSetHandler documentation.

4.1. Custom Handlers

We can also create a custom handler to pass to QueryRunner‘s methods when we need more control on how the results will be transformed into objects.

This can be done by either implementing the ResultSetHandler interface or extending one of the existing implementations provided by the library.

Let’s see how the second approach looks. First, let’s add another field to our Employee class:

public class Employee {
    private List<Email> emails;
    // ...
}

Now, let’s create a class that extends the BeanListHandler type and sets the email list for each employee:

public class EmployeeHandler extends BeanListHandler<Employee> {

    private Connection connection;

    public EmployeeHandler(Connection con) {
        super(Employee.class);
        this.connection = con;
    }

    @Override
    public List<Employee> handle(ResultSet rs) throws SQLException {
        List<Employee> employees = super.handle(rs);

        QueryRunner runner = new QueryRunner();
        BeanListHandler<Email> handler = new BeanListHandler<>(Email.class);
        String query = "SELECT * FROM email WHERE employeeid = ?";

        for (Employee employee : employees) {
            List<Email> emails
              = runner.query(connection, query, handler, employee.getId());
            employee.setEmails(emails);
        }
        return employees;
    }
}

Notice we are expecting a Connection object in the constructor so that we can execute the queries to get the emails.

Finally, let’s test our code to see if everything is working as expected:

@Test
public void
  givenResultHandler_whenExecutingQuery_thenEmailsSetted()
    throws SQLException {
    EmployeeHandler employeeHandler = new EmployeeHandler(connection);

    QueryRunner runner = new QueryRunner();
    List<Employee> employees
      = runner.query(connection, "SELECT * FROM employee", employeeHandler);

    assertEquals(employees.get(0).getEmails().size(), 2);
    assertEquals(employees.get(2).getEmails().size(), 3);
}

4.2. Custom Row Processors

In our examples, the column names of the employee table match the field names of our Employee class (the matching is case insensitive). However, that’s not always the case – for instance when column names use underscores to separate compound words.

In these situations, we can take advantage of the RowProcessor interface and its implementations to map the column names to the appropriate fields in our classes.

Let’s see how this looks like. First, let’s create another table and insert some records into it:

CREATE TABLE employee_legacy (
    id int NOT NULL PRIMARY KEY auto_increment,
    first_name varchar(255),
    last_name varchar(255),
    salary double,
    hired_date date,
);

INSERT INTO employee_legacy (first_name,last_name,salary,hired_date)
  VALUES ('John', 'Doe', 10000.10, to_date('01-01-2001','dd-mm-yyyy'));
// ...

Now, let’s modify our EmployeeHandler class:

public class EmployeeHandler extends BeanListHandler<Employee> {
    // ...
    public EmployeeHandler(Connection con) {
        super(Employee.class,
          new BasicRowProcessor(new BeanProcessor(getColumnsToFieldsMap())));
        // ...
    }
    public static Map<String, String> getColumnsToFieldsMap() {
        Map<String, String> columnsToFieldsMap = new HashMap<>();
        columnsToFieldsMap.put("FIRST_NAME", "firstName");
        columnsToFieldsMap.put("LAST_NAME", "lastName");
        columnsToFieldsMap.put("HIRED_DATE", "hiredDate");
        return columnsToFieldsMap;
    }
    // ...
}

Notice we are using a BeanProcessor to do the actual mapping of columns to fields and only for those that need to be addressed.

Finally, let’s test everything is ok:

@Test
public void
  givenResultHandler_whenExecutingQuery_thenAllPropertiesSetted()
    throws SQLException {
    EmployeeHandler employeeHandler = new EmployeeHandler(connection);

    QueryRunner runner = new QueryRunner();
    String query = "SELECT * FROM employee_legacy";
    List<Employee> employees
      = runner.query(connection, query, employeeHandler);

    assertEquals((int) employees.get(0).getId(), 1);
    assertEquals(employees.get(0).getFirstName(), "John");
}

5. Inserting Records

The QueryRunner class provides two approaches to creating records in a database.

The first one is to use the update() method and pass the SQL statement and an optional list of replacement parameters. The method returns the number of inserted records:

@Test
public void whenInserting_thenInserted() throws SQLException {
    QueryRunner runner = new QueryRunner();
    String insertSQL
      = "INSERT INTO employee (firstname,lastname,salary, hireddate) "
        + "VALUES (?, ?, ?, ?)";

    int numRowsInserted
      = runner.update(
        connection, insertSQL, "Leia", "Kane", 60000.60, new Date());

    assertEquals(numRowsInserted, 1);
}

The second one is to use the insert() method that, in addition to the SQL statement and replacement parameters, needs a ResultSetHandler to transform the resulting auto-generated keys. The return value will be what the handler returns:

@Test
public void
  givenHandler_whenInserting_thenExpectedId() throws SQLException {
    ScalarHandler<Integer> scalarHandler = new ScalarHandler<>();

    QueryRunner runner = new QueryRunner();
    String insertSQL
      = "INSERT INTO employee (firstname,lastname,salary, hireddate) "
        + "VALUES (?, ?, ?, ?)";

    int newId
      = runner.insert(
        connection, insertSQL, scalarHandler,
        "Jenny", "Medici", 60000.60, new Date());

    assertEquals(newId, 6);
}

6. Updating and Deleting

The update() method of the QueryRunner class can also be used to modify and erase records from our database.

Its usage is trivial. Here’s an example of how to update an employee’s salary:

@Test
public void givenSalary_whenUpdating_thenUpdated()
 throws SQLException {
    double salary = 35000;

    QueryRunner runner = new QueryRunner();
    String updateSQL
      = "UPDATE employee SET salary = salary * 1.1 WHERE salary <= ?";
    int numRowsUpdated = runner.update(connection, updateSQL, salary);

    assertEquals(numRowsUpdated, 3);
}

And here’s another to delete an employee with the given id:

@Test
public void whenDeletingRecord_thenDeleted() throws SQLException {
    QueryRunner runner = new QueryRunner();
    String deleteSQL = "DELETE FROM employee WHERE id = ?";
    int numRowsDeleted = runner.update(connection, deleteSQL, 3);

    assertEquals(numRowsDeleted, 1);
}

7. Asynchronous Operations

DbUtils provides the AsyncQueryRunner class to execute operations asynchronously. The methods on this class have a correspondence with those of QueryRunner class, except that they return a Future instance.

Here’s an example to obtain all employees in the database, waiting up to 10 seconds to get the results:

@Test
public void
  givenAsyncRunner_whenExecutingQuery_thenExpectedList() throws Exception {
    AsyncQueryRunner runner
      = new AsyncQueryRunner(Executors.newCachedThreadPool());

    EmployeeHandler employeeHandler = new EmployeeHandler(connection);
    String query = "SELECT * FROM employee";
    Future<List<Employee>> future
      = runner.query(connection, query, employeeHandler);
    List<Employee> employeeList = future.get(10, TimeUnit.SECONDS);

    assertEquals(employeeList.size(), 5);
}

8. Conclusion

In this tutorial, we explored the most notable features of the Apache Commons DbUtils library.

We queried data and transformed it into different object types, inserted records obtaining the generated primary keys and updated and deleted data based on a given criteria. We also took advantage of the AsyncQueryRunner class to asynchronously execute a query operation.