Bootique JDBC - v3.0


1. Bootique Integration with JDBC

JDBC is a standard and well-known API to access relational databases. To connect to a database you need a connection pool (javax.sql.DataSource). bootique-jdbc helps to configure one or more DataSources in your app. It also provides advanced APIs for connection pool monitoring and database testing. bootique-jdbc is used by applications directly, as well as by other Bootique modules that require database access (Cayenne, JOOQ, MyBatis, etc.).

Out of the box Bootique supports a choice of two connection pool providers:

Both are production-quality. From the application perspective the difference is in configuration and available metrics and health checks. Beyond that Bootique (and JDBC) gives you the API abstraction common to both, so you can pick either one.

Import one of these Bootique modules in your app, depending on your connection pool choice:

Maven
<!-- For Tomcat DataSource -->
<dependency>
    <groupId>io.bootique.jdbc</groupId>
    <artifactId>bootique-jdbc-tomcat</artifactId>
</dependency>
<!-- For HikariCP -->
<dependency>
    <groupId>io.bootique.jdbc</groupId>
    <artifactId>bootique-jdbc-hikaricp</artifactId>
</dependency>
Gradle
// For Tomcat DataSource
{
  implementation: 'io.bootique.jdbc:bootique-jdbc-tomcat'
}
// For HikariCP
{
  implementation: 'io.bootique.jdbc:bootique-jdbc-hikaricp'
}

Alternatively you may include an "instrumented" version of each module, that will enable a number of metrics for your running app.

You will also need to add JDBC driver jar to the app dependencies corresponding to your database.

2. Programming with "bootique-jdbc"

To work with а database you need an instance of javax.sql.DataSource. bootique-jdbc provides a way to configure one or more named DataSource in the app. Here is a minimal configuration example using HikariCP format. Full reference for both Tomcat and HikariCP configuration is provided in this chapter. (And don’t forget that you can print full configuration docs for your version of Bootique by running your app with -H flag).

jdbc:
  mysql:
    jdbcUrl: "jdbc:mysql://host/db"
    username: testuser
    password: secret

Once you have a named DataSource configured, you can inject a io.bootique.jdbc.DataSourceFactory and access configured DataSources by name:

@Inject
private DataSourceFactory dataSourceFactory;
DataSource dataSource = dataSourceFactory.forName("mysql");
try (Connection connection = dataSource.getConnection()) {
    // work with DB...
}

3. Metrics and Health Checks

You may use the "instrumented" version of one of the bootique-jdbc-* connection pools, that will expose a number of metrics and health checks:

Maven
<!-- For Tomcat DataSource -->
<dependency>
    <groupId>io.bootique.jdbc</groupId>
    <artifactId>bootique-jdbc-tomcat-instrumented</artifactId>
</dependency>
<!-- For HikariCP -->
<dependency>
    <groupId>io.bootique.jdbc</groupId>
    <artifactId>bootique-jdbc-hikaricp-instrumented</artifactId>
</dependency>
Gradle
// For Tomcat DataSource
{
  implementation: 'io.bootique.jdbc:bootique-jdbc-tomcat-instrumented'
}
// For HikariCP
{
  implementation: 'io.bootique.jdbc:bootique-jdbc-hikaricp-instrumented'
}

4. Testing

Bootique provides integration for both JUnit 4 and JUnit 5. This chapter will focus exclusively on the latter. JUnit 5 integration is much more capable.

"bootique-jdbc" test extensions provide a way to create a test database. This can be either an in-memory DB (Derby), or a real DB started via Docker / testcontainers library. The test database can be initialized with either a SQL script, Liquibase migrations, or custom code. There’s an API to create test data sets and perform assertions against DB data after the tested code is executed.

The main object used to control the DB in tests is DbTester. Depending on whether you want to use Derby or a real DB, you create it differently, as described in the following two sections.

4.1. In-memory Derby DB

Testing with an in-memory DB has the advantage of speed, and a disadvantage of being only an approximation of the production DB. If you decide to go this way, start by including the following dependency:

Maven
<dependency>
    <groupId>io.bootique.jdbc</groupId>
    <artifactId>bootique-jdbc-junit5-derby</artifactId>
    <scope>test</scope>
</dependency>
Gradle
{
  testImplementation: 'io.bootique.jdbc:bootique-jdbc-junit5-derby'
}

Then create a DbTester in a desired scope:

@BQTest (1)
public class DerbyTest {

    @BQTestTool (2)
    static final DbTester tester = DerbyTester.db();
}
1 DbTester requires containing test to be annotated with BQTest.
2 DbTester requires BQTestTool annotation.

4.2. Docker/Testcontainers DB

If you need a real database environment that is as close as possible to your production, you need to include a different dependency plus a few third-party libraries:

Maven
<dependency>
    <groupId>io.bootique.jdbc</groupId>
    <artifactId>bootique-jdbc-junit5-testcontainers</artifactId>
    <scope>test</scope>
</dependency>

<!-- DB-specific testcontainers module -->
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>mysql</artifactId>
    <scope>test</scope>
</dependency>

<!-- JDBC driver -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>test</scope>
</dependency>
Gradle
{
   testImplementation: 'io.bootique.jdbc:bootique-jdbc-junit5-testcontainers'
   testImplementation: 'org.testcontainers:mysql'
   testImplementation: 'mysql:mysql-connector-java'
}

Now you can create a DbTester using TcDbTester:

@BQTest
public class PostgresTest {

    @BQTestTool(BQTestScope.GLOBAL) (1)
    static final DbTester tester = TcDbTester.db("jdbc:tc:postgresql:11:///"); (2)
}
1 Often TcDbTester is created in the "GLOBAL" scope, as it may take a substantial time to start a database on Docker, so it makes sense to reuse it across many tests if at all possible.
2 Pass a testcontainers JDBC URL to specify the type of the DB, and the Docker container version you want to use for it. As an alternative you can pass an instance of JdbcDatabaseContainer, if you want to customize/manage the DB container and JDBC URL options are not sufficient for your setup.

4.3. DB initialization

No matter whether your are using Derby or a Docker DB, after starting the database, you often need to initialize it with the schema and maybe some test data. DbTester provides a number of convenient options for this, that can be used in any combination:

4.3.1. SQL script

@BQTestTool
static final DbTester tester = TcDbTester
        .db("jdbc:tc:postgresql:11:///")
        .initDB("classpath:my/app/postgres-init.sql"); (1)
1 SQL init script. Format is Bootique "resource" URL (i.e. may start with "classpath:" prefix). The default delimiter of statements in the file is ;. It can be changed via the initDB method second argument.

4.3.2. Custom function

@BQTestTool
static final DbTester tester = TcDbTester
        .db("jdbc:tc:postgresql:11:///")
        .initDB(c -> initDB(c)); (1)

static void initDB(Connection connection) throws SQLException {  (2)
    try (Statement statement = connection.createStatement()) {
        // do custom initialization
        // ...
    }
}
1 Calling a custom init function
2 A custom init function that takes java.sql.Connection as the only argument

4.3.3. Liquibase migrations

This is an attractive option if you are already using Liquibase. It ensures that your test schema exactly matches your real schema at any moment:

@BQTestTool
static final DbTester tester = TcDbTester
        .db("jdbc:tc:postgresql:11:///")
        .initDBWithLiquibaseChangelog("classpath:my/app/postgres-changelog.yml"); (1)
1 Liquibase changelog URL. Format is Bootique "resource" URL (i.e. may start with "classpath:" prefix).

4.4. Data reset before each test

Since the DB may be setup once and reused between multiple tests, it is important to reset it to a known state before each test. The following code does it by deleting all data from the specified tables:

@BQTestTool
static final DbTester tester = TcDbTester
        .db("jdbc:tc:postgresql:11:///")
        .deleteBeforeEachTest("t1", "t2", "t3"); (1)
1 A vararg that lists tables whose data needs to be deleted before each test. To avoid foreign key constraint violations during delete, you must pass tables names to this method in the "insert" order (e.g. if "t2" has an FK to "t1", "t2" must go after "t1").

4.5. Connecting DbTester with test BQRuntime

With DbTester you don’t need to worry about knowing your database URL and port (in fact the port is set dynamically) or write a special YAML. Instead you need to pass a "module" generated by DbTester that will (re)define a named DataSources on the fly:

@BQApp
static final BQRuntime app = Bootique.app()
        .autoLoadModules()
        .module(tester.moduleWithTestDataSource("mydb")) (1)
        .createRuntime();
1 This will create or replace Bootique configuration for "mydb" to connect to the test DB managed by DbTester.

A single DbTester can be used to bootstrap multiple BQRuntimes or multiple DataSources within a given runtime.

4.6. Using DbTester

In addition to bootstrapping a DB, DbTester provides rich API for managing test data sets and making assertions of the DB state. The most direct way to access the DB within a test is via a DataSource obtained from DbTester:

@Test
public void test1() {

    DataSource dataSource = tester.getDataSource();

    // ... use dataSource to connect to DB
}

The DataSource would allow you to run any operations against the DB, however normally you would use a more high-level API to work with data - a Table object. DbTester dynamically creates a Table instance for each table in the underlying DB. You can access it as follows:

Table myTable = tester.getTable("my_table");

Table supports a variety of operations against a single DB table. E.g.:

// delete table contents
myTable.deleteAll();
// insert test data
myTable.insertColumns("id", "c1")
        .values(1, "x1")
        .values(2, "x2")
        .exec();
// check that data matches expectations
myTable.matcher().eq("c1", "x1").assertOneMatch();

Explore the Table class for more capabilities such as loading data from CSV, running updates, etc.

5. Configuration Reference

jdbc is the root element of a configuration. It is a map of named data source configurations:

jdbc:
  ds1: ...
  ds2: ...

DataSource properties depend on whether you are using Tomcat or Hikari (or your own) connection pool and are documented below.

5.1. Tomcat JDBC

Tomcat DataSource is produced by io.bootique.jdbc.tomcat.TomcatManagedDataSourceFactory and supports the properties listed below. A typical config might look like this:

jdbc:
  mysql:
    url: "jdbc:mysql://host/db"
    username: testuser
    password: secret
Table 1. Tomcat DataSource Property Reference - Essential Properties
Property Default Description

type

Can be omitted if bootique-jdbc-tomcat is the only DataSourceFactory implementation in the app dependencies. In rare cases when there is more than one implementation, set to tomcat.

defaultAutoCommit

initialSize

maxActive

maxIdle

maxWait

minIdle

password

Database user password

url

Database URL. Required.

username

Database user name.

validationQuery

The query that will be executed just before a connection is given to the app from the pool to validate that the connection to the database is still alive.

Table 2. Tomcat DataSource Property Reference - Other Properties
Property Default Description

abandonWhenPercentageFull

alternateUsernameAllowed

commitOnReturn

dataSourceJNDI

defaultCatalog

defaultReadOnly

defaultTransactionIsolation

driverClassName

fairQueue

ignoreExceptionOnPreLoad

initSQL

jdbcInterceptors

jmxEnabled

jmxObjectName

logAbandoned

logValidationErrors

maxAge

minEvictableIdleTimeMillis

numTestsPerEvictionRun

propagateInterruptState

removeAbandoned

removeAbandonedTimeout

rollbackOnReturn

testOnBorrow

testOnReturn

testWhileIdle

testOnConnect

timeBetweenEvictionRunsMillis

useDisposableConnectionFacade

useEquals

useLock

validationQueryTimeout

validatorClassName

validationInterval

5.2. HikariCP

HikariCP DataSource is produced by io.bootique.jdbc.hikaricp.HikariCPManagedDataSourceFactory and supports the properties listed below (mostly matching HikariCP docs. In fact most of the descriptions are adopted from that document). A typical config might look like this:

jdbc:
  mysql:
    jdbcUrl: "jdbc:mysql://host/db"
    username: testuser
    password: secret
Table 3. HikariCP DataSource Property Reference - Essential Properties
Property Default Description

type

Can be omitted if bootique-jdbc-hikaricp is the only DataSourceFactory implementation in the app dependencies. In rare cases when there is more than one implementation, set to either hikari or hikari-instrumented.

autoCommit

true

Controls the default auto-commit behavior of connections returned from the pool.

connectionTestQuery

The query that will be executed just before a connection is given to the app from the pool to validate that the connection to the database is still alive. This is for "legacy" drivers that do not support the JDBC4 Connection.isValid() API.

connectionTimeout

30000

Long value representing milliseconds. Controls the maximum number of milliseconds that the app will wait for a connection from the pool. If this time is exceeded without a connection becoming available, a SQLException will be thrown. Lowest acceptable connection timeout is 250 ms.

idleTimeout

600000

Long value representing milliseconds. Controls the maximum amount of time that a connection is allowed to sit idle in the pool. This setting only applies when minimumIdle is defined to be less than maximumPoolSize. Idle connections will not be retired once the pool reaches minimumIdle connections. Whether a connection is retired as idle or not is subject to a maximum variation of +30 seconds, and average variation of +15 seconds. A connection will never be retired as idle before this timeout. A value of 0 means that idle connections are never removed from the pool. The minimum allowed value is 10000ms (10 seconds).

jdbcUrl

Database URL. Required for "DriverManager-based" configuration. (The alternative is "DataSource-based" config).

maximumPoolSize

10

Controls the maximum size that the pool is allowed to reach, including both idle and in-use connections. This value will determine the maximum number of actual connections to the database backend. When the pool reaches this size, and no idle connections are available, calls to getConnection() will block for up to connectionTimeout milliseconds before timing out.

minimumIdle

same as maximumPoolSize

Controls the minimum number of idle connections that HikariCP tries to maintain in the pool. If the idle connections dip below this value and total connections in the pool are less than maximumPoolSize, HikariCP will make a best effort to add additional connections quickly and efficiently. However, for maximum performance and responsiveness to spike demands, it is recommended not setting this value and instead allowing HikariCP to act as a fixed size connection pool.

password

Database user password

username

Database user name.

Table 4. HikariCP DataSource Property Reference - Other Properties
Property Default Description

allowPoolSuspension

false

Controls whether the pool can be suspended and resumed through JMX

catalog

driver default

The default catalog for databases that support the concept of catalogs. If this property is not specified, the default catalog defined by the JDBC driver is used.

connectionInitSql

A SQL statement that will be executed after every new connection creation before adding it to the pool. If this SQL is not valid or throws an exception, it will be treated as a connection failure and the standard retry logic will be followed.

dataSourceClassName

The name of the DataSource class provided by the JDBC driver. Consult the documentation for your specific JDBC driver to get this class name. XA data sources are not supported. XA requires a real transaction manager. You do not need this property if you are using jdbcUrl for "old-school" DriverManager-based JDBC driver configuration.

dataSourceJNDI

JNDI name to use for DataSource lookup. JNDI is a rather foreign concept to Bootique, so this is rarely used if ever.

dataSourceProperties

A map of properties passed the DataSource created for dataSourceClassName property. Ignored if dataSourceClassName is not in use and jdbcUrl is used.

driverClassName

Used to load a specific driver class via DriverManager. Usually cab be omitted, as the driver can be resolved from jdbcUrl.

initializationFailTimeout

1

Controls whether the pool will "fail fast" if the pool cannot be seeded with an initial connection successfully. Any positive number is taken to be the number of milliseconds to attempt to acquire an initial connection; the application thread will be blocked during this period. If a connection cannot be acquired before this timeout occurs, an exception will be thrown. This timeout is applied after the connectionTimeout period. If the value is zero, HikariCP will attempt to obtain and validate a connection. If a connection is obtained, but fails validation, an exception will be thrown and the pool not started. However, if a connection cannot be obtained, the pool will start, but later efforts to obtain a connection may fail. A value less than zero will bypass any initial connection attempt, and the pool will start immediately while trying to obtain connections in the background. Consequently, later efforts to obtain a connection may fail.

isolateInternalQueries

false

Determines whether HikariCP isolates internal pool queries, such as the connection alive test, in their own transaction. Since these are typically read-only queries, it is rarely necessary to encapsulate them in their own transaction. This property only applies if autoCommit is disabled.

leakDetectionThreshold

0

Long value representing milliseconds. Controls the amount of time that a connection can be out of the pool before a message is logged indicating a possible connection leak. A value of 0 means leak detection is disabled. Lowest acceptable value for enabling leak detection is 2000 (2 seconds).

maxLifetime

1800000 (30 minutes)

Long value representing milliseconds. Controls the maximum lifetime of a connection in the pool. An in-use connection will never be retired, only when it is closed will it then be removed. On a connection-by-connection basis, minor negative attenuation is applied to avoid mass-extinction in the pool. We strongly recommend setting this value, and it should be several seconds shorter than any database or infrastructure imposed connection time limit. A value of 0 indicates no maximum lifetime (infinite lifetime), subject of course to the idleTimeout setting. The minimum allowed value is 30000ms (30 seconds).

readOnly

false

Boolean. Controls whether Connections obtained from the pool are in read-only mode by default. Some databases do not support the concept of read-only mode, while others provide query optimizations when the Connection is set to read-only. Whether you need this property or not will depend largely on your application and database.

registerMbeans

false

Boolean. Controls whether or not JMX Management Beans ("MBeans") are registered or not.

schema

driver default

Default schema for databases that support the concept of schemas. If not specified, the default schema defined by the JDBC driver is used.

transactionIsolation

driver default

Controls the default transaction isolation level of connections returned from the pool. If not specified, the default transaction isolation level defined by the JDBC driver is used. Only use this property if you have specific isolation requirements that are common for all queries. The value of this property is the constant name from the Connection class such as TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ, etc.

validationTimeout

5000

Long value representing milliseconds. Controls the maximum amount of time that a connection will be tested for aliveness. This value must be less than the connectionTimeout. Lowest acceptable validation timeout is 250 ms.

"hikari-instrumented" inherits from "hikari" and supports an additional set of properties:

Table 5. HikariCP Instrumented Property Reference
Property Default Description

health.connection99Percent

Represented by DurationRangeFactory object documented below.

health.connectivity

Configures "aliveness" health check. Represented by DurationRangeFactory object documented below.

Table 6. DurationRangeFactory Property Reference
Property Default Description

critical

5s

Critical threshold duration. E.g. 5ms, 2s, 1hr

max

Max threshold duration. E.g. 5ms, 2s, 1hr

min

Min threshold duration. E.g. 5ms, 2s, 1hr

warning

Warning threshold duration. E.g. 5ms, 2s, 1hr