Bootique - v3 JDBC Module Documentation

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

Bootique JDBC - v3.0

1. Bootique Integration with JDBC

2. Programming with "bootique-jdbc"

3. Metrics and Health Checks

4. Testing

4.1. In-memory Derby DB

4.2. Docker/Testcontainers DB

4.3. DB initialization

4.3.1. SQL script

4.3.2. Custom function

4.3.3. Liquibase migrations

4.4. Data reset before each test

4.5. Connecting DbTester with test BQRuntime

4.6. Using DbTester

5. Configuration Reference

5.1. Tomcat JDBC

5.2. HikariCP