Dropwizard JDBI3

The dropwizard-jdbi3 module provides you with managed access to JDBI, a flexible and modular library for interacting with relational databases via SQL.

Configuration

To create a managed, instrumented Jdbi instance, your configuration class needs a DataSourceFactory instance:

public class ExampleConfiguration extends Configuration {
    @Valid
    @NotNull
    private DataSourceFactory database = new DataSourceFactory();

    @JsonProperty("database")
    public void setDataSourceFactory(DataSourceFactory factory) {
        this.database = factory;
    }

    @JsonProperty("database")
    public DataSourceFactory getDataSourceFactory() {
        return database;
    }
}

Then, in your service’s run method, create a new JdbiFactory:

@Override
public void run(ExampleConfiguration config, Environment environment) {
    final JdbiFactory factory = new JdbiFactory();
    final Jdbi jdbi = factory.build(environment, config.getDataSourceFactory(), "postgresql");
    environment.jersey().register(new UserResource(jdbi));
}

This will create a new managed connection pool to the database, a health check for connectivity to the database, and a new Jdbi instance for you to use.

Your service’s configuration file will then look like this:

database:
  # the name of your JDBC driver
  driverClass: org.postgresql.Driver

  # the username
  user: pg-user

  # the password
  password: iAMs00perSecrEET

  # the JDBC URL
  url: jdbc:postgresql://db.example.com/db-prod

  # any properties specific to your JDBC driver:
  properties:
    charSet: UTF-8

  # the maximum amount of time to wait on an empty pool before throwing an exception
  maxWaitForConnection: 1s

  # the SQL query to run when validating a connection's liveness
  validationQuery: "/* MyService Health Check */ SELECT 1"

  # the timeout before a connection validation queries fail
  validationQueryTimeout: 3s

  # the minimum number of connections to keep open
  minSize: 8

  # the maximum number of connections to keep open
  maxSize: 32

  # whether or not idle connections should be validated
  checkConnectionWhileIdle: false

  # the amount of time to sleep between runs of the idle connection validation, abandoned cleaner and idle pool resizing
  evictionInterval: 10s

  # the minimum amount of time an connection must sit idle in the pool before it is eligible for eviction
  minIdleTime: 1 minute

Plugins

JDBI3 is built using plugins to add features to its core implementation. Dropwizard adds the sqlobject and guava plugins by default, but you are free to add other existing plugins you might need or create your own.

Usage

We highly recommend you use JDBI’s SQL Objects API, which allows you to write DAO classes as interfaces:

public interface MyDAO {
  @SqlUpdate("create table something (id int primary key, name varchar(100))")
  void createSomethingTable();

  @SqlUpdate("insert into something (id, name) values (:id, :name)")
  void insert(@Bind("id") int id, @Bind("name") String name);

  @SqlQuery("select name from something where id = :id")
  String findNameById(@Bind("id") int id);
}

final MyDAO dao = database.onDemand(MyDAO.class);

This ensures your DAO classes are trivially mockable, as well as encouraging you to extract mapping code (e.g., RowMapper -> domain objects) into testable, reusable classes.

Exception Handling

By adding the JdbiExceptionsBundle to your application, Dropwizard will automatically unwrap any thrown SQLException or JdbiException instances. This is critical for debugging, since otherwise only the common wrapper exception’s stack trace is logged.

Prepended Comments

If you’re using JDBI’s SQL Objects API (and you should be), dropwizard-jdbi3 will automatically prepend the SQL object’s class and method name to the SQL query as an SQL comment:

/* com.example.service.dao.UserDAO.findByName */
SELECT id, name, email
FROM users
WHERE name = 'Coda';

This will allow you to quickly determine the origin of any slow or misbehaving queries.