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:

publicclass ExampleConfigurationextendsConfiguration{
@Valid
@NotNull
privateDataSourceFactorydatabase=newDataSourceFactory();
@JsonProperty("database")
publicvoidsetDataSourceFactory(DataSourceFactoryfactory){
this.database=factory;
}
@JsonProperty("database")
publicDataSourceFactorygetDataSourceFactory(){
returndatabase;
}
}

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

@Override
publicvoidrun(ExampleConfigurationconfig,Environmentenvironment){
finalJdbiFactoryfactory=newJdbiFactory();
finalJdbijdbi=factory.build(environment,config.getDataSourceFactory(),"postgresql");
environment.jersey().register(newUserResource(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:"/*MyServiceHealthCheck*/SELECT1"
# 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:

publicinterface MyDAO{
@SqlUpdate("create table something (id int primary key, name varchar(100))")
voidcreateSomethingTable();
@SqlUpdate("insert into something (id, name) values (:id, :name)")
voidinsert(@Bind("id")intid,@Bind("name")Stringname);
@SqlQuery("select name from something where id = :id")
StringfindNameById(@Bind("id")intid);
}
finalMyDAOdao=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 */
SELECTid,name,email
FROMusers
WHEREname='Coda';

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