Simple data access with DbUtils

I’ve been working on a side project that is more a dynamic site than a web application like I’m used to do. In this project, there is no need to have mutable states on objets, so there is no point in using Java beans and JPA is overkill for the task at hand.

So, we started with plain old JDBC and immutable objects where data is passed on to the constructor. That looks pretty much like this:

Plain Old data object (Assembly.java) download

package be.noselus.model;

import com.google.common.base.Objects;

public class Assembly {

    public enum Level {
        DEPUTY_CHAMBER,
        SENAT,
        REGION,
        LOCAL,
        PROVINCE,
        EUROPE,
        FEDERAL,
        COMMUNITY
    }

    private final int id;
    private final String label;
    private final Level level;

    public Assembly(int id, String label, Level level) {
        this.id = id;
        this.label = label;
        this.level = level;
    }

    public int getId() {
        return id;
    }

    public String getLabel() {
        return label;
    }

    public Level getLevel() {
        return level;
    }

    @Override
    public int hashCode() {
        return Objects.hashCode(id, label, level);
    }

    @Override
    public boolean equals(final Object obj) {
        if (this == obj) {
            return true;
        }
        if (obj == null || getClass() != obj.getClass()) {
            return false;
        }
        final Assembly other = (Assembly) obj;
        return Objects.equal(this.id, other.id) && Objects.equal(this.label, other.label) && Objects.equal(this.level, other.level);
    }

    @Override
    public String toString() {
        return "Assembly{" +
                "id=" + id +
                ", label='" + label + '\'' +
                ", level=" + level +
                '}';
    }
}

Repository in JDBC (AssemblyRepositoryInDatabase.java) download

package be.noselus.repository;

import be.noselus.model.Assembly;

import javax.inject.Inject;
import javax.inject.Singleton;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

@Singleton
public class AssemblyRepositoryInDatabase implements AssemblyRepository {

    private final DataSource dataSource;

    @Inject
    public AssemblyRepositoryInDatabase(final DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public Assembly findId(final int id) {
        try (Connection db = dataSource.getConnection();
             PreparedStatement stat = db.prepareStatement("SELECT * FROM assembly WHERE id = ?;")) {

            stat.setInt(1, id);
            stat.execute();
            final ResultSet resultSet = stat.getResultSet();
            resultSet.next();
            return assemblyMapping(resultSet);
        } catch (SQLException e) {
            throw new RuntimeException("Error retrieving assembly with id " + id, e);
        }
    }

    @Override
    public List<Assembly> getAssemblies() {
        List<Assembly> result = new ArrayList<>();
        try (Connection db = dataSource.getConnection();
             PreparedStatement stat = db.prepareStatement("SELECT * FROM assembly;")) {

            stat.execute();
            final ResultSet resultSet = stat.getResultSet();
            while (resultSet.next()) {
                Assembly assembly = assemblyMapping(resultSet);
                result.add(assembly);
            }
        } catch (SQLException e) {
            throw new RuntimeException("Error retrieving assemblies", e);
        }
        return result;
    }

    private Assembly assemblyMapping(final ResultSet resultSet) throws SQLException {
        final int foundId = resultSet.getInt("id");
        final String label = resultSet.getString("label");
        final String level = resultSet.getString("level");

        return new Assembly(foundId, label, Assembly.Level.valueOf(level));
    }
}

It’s working, and it’s understandable, but there are a few try catches and some boiler plate code that I’m not very fond of, so I tried to find a tool to make it better.

There are quite a lot of tools to help there. Spring JDBC template, JDBI, Commons DbUtils, JOOQ, MyBatis, sql2o,… Given I did not wanna do javabeans, I needed something pretty low level and not assuming the JavaBean convention was used, which odly does reduce quite significantly the pool of candidates.

Anyway, after looking around I decided to go for DbUtils. I started converting my Repository and ended up with this:

Out of the box DbUtils (AssemblyRepositoryPlainDbUtils.java) download

package be.noselus.repository;

import be.noselus.model.Assembly;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.AbstractListHandler;

import javax.inject.Inject;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class AssemblyRepositoryPlainDbUtils implements AssemblyRepository {

    private final DataSource dataSource;

    @Inject
    public AssemblyRepositoryPlainDbUtils(final DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public Assembly findId(final int id) {
        QueryRunner run = new QueryRunner(dataSource);
        ResultSetHandler<Assembly> assemblyResultSetHandler = new AssemblyResultSetHandler();
        Assembly assembly = null;
        try {
            assembly = run.query("SELECT * FROM assembly WHERE id = ?;", assemblyResultSetHandler, id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        if (assembly == null) {
            throw new RuntimeException("Error retrieving assembly with id " + id);
        }
        return assembly;
    }

    @Override
    public List<Assembly> getAssemblies() {
        QueryRunner run = new QueryRunner(dataSource);
        ResultSetHandler<List<Assembly>> assemblyResultSetHandler = new AssemblyListHandler();
        try {
            return run.query("SELECT * FROM assembly;", assemblyResultSetHandler);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private static class AssemblyResultSetHandler implements ResultSetHandler<Assembly> {
        @Override
        public Assembly handle(final ResultSet resultSet) throws SQLException {
            if (!resultSet.next()) {
                return null;
            }
            return map(resultSet);
        }
    }

    private static class AssemblyListHandler extends AbstractListHandler<Assembly> {
        @Override
        protected Assembly handleRow(final ResultSet resultSet) throws SQLException {
            return map(resultSet);
        }
    }

    private static Assembly map(final ResultSet resultSet) throws SQLException {
        final int foundId = resultSet.getInt("id");
        final String label = resultSet.getString("label");
        final String level = resultSet.getString("level");

        return new Assembly(foundId, label, Assembly.Level.valueOf(level));
    }
}

Meh.

My implemented methods are a little bit shorter, but overall, I have more code, still have try catches, and two extra classes. Doesn’t look much simpler to me.

But then again, I had the feeling that lots of the extra code could be easily managed separatly and in a generic way. So, I applied the main rule of TDD : mercyless refactoring.

I’ve got two ResultsetHandlers that are very close to each other and call the same method, so, I decided to turn this method into a function. As I’m still in Java 7 on that project, this means creating an interface with a single method.

ResultSet mappers will convert a ResultSet into an object (ResultSetMapper.java) download

package be.noselus.repository;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Map a ResultSet into an instance of T
 *
 * @param <T> the typed object in which the content of the ResultSet should be converted.
 */

public interface ResultSetMapper<T> {

    T map(ResultSet resultSet) throws SQLException;
}

Next I will extract the two inner classes and make then generic enough so that I can reuse them for all my Repositories:

ResultsetHandler that will use a ResultSetMapper to convert a ResultSet to a typed object (MapperBasedResultSetHandler.java) download

package be.noselus.repository;

import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.ResultSet;
import java.sql.SQLException;

public class MapperBasedResultSetHandler<T> implements ResultSetHandler<T> {

    private final ResultSetMapper<T> mapper;

    public MapperBasedResultSetHandler(final ResultSetMapper<T> mapper) {
        this.mapper = mapper;
    }

    @Override
    public T handle(final ResultSet resultSet) throws SQLException {
        if (!resultSet.next()) {
            return null;
        }
        return mapper.map(resultSet);
    }
}

ResultsetHandler that will use a ResultSetMapper to convert a ResultSet to a list of typed objects (MapperBasedResultSetListHandler.java) download

package be.noselus.repository;

import org.apache.commons.dbutils.handlers.AbstractListHandler;

import java.sql.ResultSet;
import java.sql.SQLException;

public class MapperBasedResultSetListHandler<T> extends AbstractListHandler<T> {

    private final ResultSetMapper<T> mapper;

    public MapperBasedResultSetListHandler(final ResultSetMapper<T> mapper) {
        this.mapper = mapper;
    }

    @Override
    protected T handleRow(final ResultSet resultSet) throws SQLException {
        return mapper.map(resultSet);
    }

}

This is much better. The code has shrinked already. The only annoying thing left is the try catches for the SQLExceptions. To get rid of them, I decided to make an adapter, that will encapsulate the QueryRunner of DbUtils and do the try catches for me in order to convert checked exceptions into unchecked exceptions.

Adapt the Query Runner to avid checked exceptions and narrow down the API to my needs (QueryRunnerAdapter.java) download

package be.noselus.repository;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import javax.sql.DataSource;
import java.sql.SQLException;

public class QueryRunnerAdapter {

    private final QueryRunner runner;

    public QueryRunnerAdapter(final DataSource datasource) {
        this.runner = new QueryRunner(datasource);
    }

    public <T> T query(final String sql, final ResultSetHandler<T> rsh, final Object... params)  {
        try {
            return runner.query(sql, rsh, params);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public <T> T query(final String sql, final ResultSetHandler<T> rsh) {
        try {
            return runner.query(sql, rsh);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

and the final result for my repository is much closer to what I was looking for: small and easy to maintain

Cleaner repository (AssemblyRepositoryDbUtils.java) download

package be.noselus.repository;

import be.noselus.model.Assembly;
import org.apache.commons.dbutils.ResultSetHandler;

import javax.inject.Inject;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class AssemblyRepositoryDbUtils implements AssemblyRepository, ResultSetMapper<Assembly> {

    private final DataSource dataSource;

    @Inject
    public AssemblyRepositoryDbUtils(final DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public Assembly findId(final int id) {
        QueryRunnerAdapter run = new QueryRunnerAdapter(dataSource);
        ResultSetHandler<Assembly> assemblyResultSetHandler = new MapperBasedResultSetHandler<>(this);
        Assembly assembly = run.query("SELECT * FROM assembly WHERE id = ?;", assemblyResultSetHandler, id);
        if (assembly == null) {
            throw new RuntimeException("Error retrieving assembly with id " + id);
        }
        return assembly;
    }

    @Override
    public List<Assembly> getAssemblies() {
        QueryRunnerAdapter run = new QueryRunnerAdapter(dataSource);
        ResultSetHandler<List<Assembly>> assemblyResultSetHandler = new MapperBasedResultSetListHandler<>(this);
        return run.query("SELECT * FROM assembly;", assemblyResultSetHandler);
    }

    @Override
    public Assembly map(final ResultSet resultSet) throws SQLException {
        final int foundId = resultSet.getInt("id");
        final String label = resultSet.getString("label");
        final String level = resultSet.getString("level");

        return new Assembly(foundId, label, Assembly.Level.valueOf(level));
    }
}

Hope this help.

Nicolas Hurion

Building tools to help people.

Simple Data Access With DbUtils