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.