Jdbi 3 Developer Guide

Development documentation (3.54.1-SNAPSHOT)

This is the documentation for the current development state of Jdbi. All information in here reflects the current state of development on the master branch and is subject to change until it has been released as a numbered version.

Release documentation

1. Introduction to Jdbi 3

Jdbi provides convenient, idiomatic, access to relational data in Java. Jdbi 3 is the third major release, which introduces enhanced support for modern Java, countless refinements to the design and implementation, and enhanced support for modular development through plugins and extensions.

Jdbi is built on top of JDBC. If your data source has a JDBC driver, you can use it with Jdbi. It improves JDBC’s low-level interface, providing a more natural API that is easy to bind to your domain data types.

Jdbi is not an ORM. It is a convenience library to make Java database operations simpler and more pleasant to program than raw JDBC. While there is some ORM-like functionality, Jdbi goes to great length to ensure that there is no hidden magic that makes it hard to understand what is going on.

Jdbi does not hide SQL away. One of the design principles of Jdbi is that SQL is the native language of the database, and it is unnecessary to wrap it into code, deconstruct it, or hide it away. Being able to express a query in raw SQL makes it possible for programmers and data engineers to speak the same language and not fight translation layers.

Jdbi does not aim to provide a complete database management framework. It provides the building blocks that allow constructing the mapping between data and objects as appropriate for your application and the necessary primitives to execute SQL code against your database.

1.1. Quick Links

1.2. JDBI v2 (legacy version)

JDBI v2 is no longer under active development!

Jdbi v2 documentation

Already using Jdbi v2? See Upgrading from v2 to v3

1.3. Getting involved

Jdbi uses GitHub as the central development hub. Issues, Pull Requests and Discussions all happen here.

Please see our Contribution guide for more information on how to contribute to Jdbi.

1.4. Acknowledgements and Funding

Jdbi is a recipient of the Spotify FOSS 2023 Fund

2. Using Jdbi in your projects

2.1. License, Dependencies and availability

Jdbi is licensed under the commercial friendly Apache 2.0 license.

The core Jdbi module which offers programmatic access uses only slf4j and geantyref as hard dependencies.

All Jdbi modules are available through Maven Central. Jdbi also offers a BOM (Bill of materials) module for easy dependency management.

2.2. JVM version compatibility

Jdbi runs on all Java versions 17 or later. All releases are built with the latest LTS version of Java using Java 17 bytecode compatibility.

Jdbi ended support for Java 11 with version 3.50, and Java 8 with version 3.39. There may be occasional backports of security relevant things or major bugs, if necessary.

2.2.1. Virtual Threads

Jdbi supports virtual threads. Basic use cases are verified to work, and we welcome reports of any issues. As virtual thread support is still evolving, please make sure to verify against the latest JDK before reporting any issues.

2.3. Getting started

Jdbi has a flexible plugin architecture, which makes it easy to fold in support for your favorite libraries (Guava, JodaTime, Spring, Vavr) or database vendors (Oracle, Postgres, H2).

Jdbi is not an ORM. There is no session cache, "open session in view", change-tracking, or cajoling the library to understand your schema.

Jdbi provides straightforward mapping between SQL and data accessible through a JDBC driver. You bring your own SQL, and Jdbi executes it.

Jdbi provides several other modules, which enhance the core API with additional features.

2.3.1. Jdbi modules overview

Jdbi consists of a large number of modules. Not all are required when writing database code. This is a quick overview of the existing modules and their function:

Common modules

jdbi3-core: The core Jdbi library. Required by all other components.
jdbi3-sqlobject: SQL Object extension for declarative database access.

Testing support

jdbi3-testing: Testing framework support. Currently, only supports JUnit4 and JUnit5.
jdbi3-testcontainers: Support for arbitrary databases running in Testcontainers. Currently, only supports JUnit 5.

Telemetry support

jdbi3-opentelemetry: OpenTelemetry tracing support

External data types and libraries

The core module contains support for the Immutables library.

jdbi3-guava: Support for Google Guava collection and Optional types.
jdbi3-jodatime2: Support for JodaTime v2 data types.
jdbi3-vavr: Support for Vavr Tuples, Collections and Value arguments.

JSON Mapping

Support for various JSON libraries to map data from the database onto JSON types and vice versa.

jdbi3-jackson2: Support for Jackson v2.
jdbi3-jackson3: Support for Jackson v3.
jdbi3-gson: Support for Google Gson.
jdbi3-moshi: Support for Square Moshi.

Frameworks

jdbi3-guice: Support dependency injection and modules with Google Guice.
jdbi3-spring: Provides support for all Spring Framework versions that are under OSS support.
jdbi3-spring5: Provides a Spring Framework version 5 compatible factory bean to set up Jdbi singleton. This module is deprecated, please migrate to the spring module.
jdbi3-jpa: Some support for JPA annotations.

Database specific types and functions

While Jdbi supports any data source that has a JDBC driver "out of the box", its support is limited to the standard JDBC types and mappings. Some databases have additional data types and mappings, and support is added using the following modules:

jdbi3-postgres: Support for Postgres data types.
jdbi3-mysql: Support for MySQL data types.
jdbi3-sqlite: Support for sqlite data types.
jdbi3-oracle12: Support Oracle returning DML statements.
jdbi3-postgis: Support for PostGIS types.

SQL rendering

SQL statements can be rendered before they are sent to the database driver. Unless explicitly configured, Jdbi uses a simple render engine that replaces <…> placeholders. It is possible to replace this engine with other template engines.

jdbi3-stringtemplate4: Use the StringTemplate 4 template engine to render SQL statements.
jdbi3-commons-text: Use Apache Commons Text to render SQL statements.
jdbi3-freemarker: Use Apache Freemarker to render SQL statements.

Cache support

jdbi3-caffeine-cache: Use the Caffeine caching library for SQL template and parse caching.
jdbi3-noop-cache: Turn off SQL template and parse caching for testing and debugging.
jdbi3-guava-cache: Use the Guava Cache caching library for SQL template and parse caching.

The guava caching module is considered experimental.

Additional Language support

Jdbi can be used from any language and technology running on the JVM that can use Java code (Kotlin, Scala, Clojure, JRuby etc).

For Jdbi, Java is a first class citizen. We provide additonal modules for other JVM languages such as Kotlin that allow more idiomatic access to Jdbi functionality.

jdbi3-kotlin: Automatically map Kotlin data classes.
jdbi3-kotlin-sqlobject: Kotlin support for the SQL Object extension.

2.3.2. External modules

The following modules are maintained outside the main Jdbi tree:

Module	Source	Javadoc	Site	Description
jdbi3-guava-cache	Github	Javadoc	Site	Experimental support for the Guava cache library.

Module

Source

Javadoc

Site

Description

jdbi3-guava-cache

Github

Javadoc

Site

Experimental support for the Guava cache library.

The additional modules are usually released in sync with the main release. If any of the links above for a release version do not resolve, a release is missing by accident. In that case, please file an issue on the Bug Tracker.

2.3.3. Build tools

All Jdbi modules are available through Maven Central, so any project that uses a dependency management tool (Apache Maven, Gradle, sbt, leiningen, Apache Ivy etc.) can access these.

For Apache Maven:

<dependencies>
    <dependency>
        <groupId>org.jdbi</groupId>
        <artifactId>jdbi3-core</artifactId>
        <version>3.54.1-SNAPSHOT</version>
    </dependency>
</dependencies>

For Gradle:

dependencies {
    implementation("org.jdbi:jdbi3-core:3.54.1-SNAPSHOT")
}

When using multiple Jdbi modules, it is important that all modules use the same version. There is no guarantee that mixing versions will work.

Jdbi offers a BOM (Bill of Materials) that can be used to provide a consistent version for all Jdbi components.

For Apache Maven:

<dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>org.jdbi</groupId>
            <artifactId>jdbi3-bom</artifactId>
            <type>pom</type>
            <version>3.54.1-SNAPSHOT</version>
            <scope>import</scope>
        </dependency>
    </dependencies>
</dependencyManagement>

adds the Jdbi BOM module into the dependencyManagement section.

Jdbi components in use are declared in the <dependencies> section without a version:

<dependencies>
    <dependency>
        <groupId>org.jdbi</groupId>
        <artifactId>jdbi3-core</artifactId>
    </dependency>
</dependencies>

For Gradle:

dependencies {
    // Load bill of materials (BOM) for Jdbi.
    implementation(platform("org.jdbi:jdbi3-bom:3.54.1-SNAPSHOT"))
}

adds the BOM as dependency constraints to Gradle.

Jdbi components are declared without versions:

dependencies {
    implementation("org.jdbi:jdbi3-core")
}

2.3.4. @Alpha and @Beta Annotations

The @Alpha and @Beta annotations mark APIs as unstable. Each of these annotations signifies that a public API (public class, method or field) is subject to incompatible changes, or even removal, in a future release. Any API bearing these annotations is exempt from any compatibility guarantees.

Alpha — Alpha APIs are intended as early preview of features that might eventually get promoted.
Beta — It is generally safe for applications to depend on beta APIs, at the cost of some extra work during upgrades. However, libraries (which get included on an application classpath) should not depend on Beta APIs as the classpath is outside the control of the library.

Note that the presence of this annotation implies nothing about the quality or performance of the API in question, only the fact that it is not "API-frozen."

Add Alpha and Beta to your IDE’s "unstable API usage" blacklist.

2.3.5. Internal packages

Any class in a package that is marked as internal (contains the word "internal") is not a part of the public API and may change in a backwards incompatible way. These classes and interfaces may be used across the Jdbi code base and can change (or be removed) without deprecation or announcement.

2.3.6. Mixing Jdbi component versions

There is no guarantee that Jdbi components of different versions (e.g. jdbi3-core version 1.2.3 and jdbi-sqlobject version 1.3.1) work together. Jdbi versioning is the public API exposed to consumers. All Jdbi components used in a project or service should use the same version. See the section on Build tools on how to use the BOM module to provide consistent versions for all components.

3. API Overview

Jdbi’s API comes in two flavors:

3.1. Fluent API

The Core API provides a fluent, imperative interface. Use Builder style objects to wire up your SQL to rich Java data types.

Jdbi jdbi = Jdbi.create("jdbc:h2:mem:test"); // (H2 in-memory database)

List<User> users = jdbi.withHandle(handle -> {
    handle.execute("CREATE TABLE \"user\" (id INTEGER PRIMARY KEY, \"name\" VARCHAR)");

    // Inline positional parameters
    handle.execute("INSERT INTO \"user\" (id, \"name\") VALUES (?, ?)", 0, "Alice");

    // Positional parameters
    handle.createUpdate("INSERT INTO \"user\" (id, \"name\") VALUES (?, ?)")
            .bind(0, 1) // 0-based parameter indexes
            .bind(1, "Bob")
            .execute();

    // Named parameters
    handle.createUpdate("INSERT INTO \"user\" (id, \"name\") VALUES (:id, :name)")
            .bind("id", 2)
            .bind("name", "Clarice")
            .execute();

    // Named parameters from bean properties
    handle.createUpdate("INSERT INTO \"user\" (id, \"name\") VALUES (:id, :name)")
            .bindBean(new User(3, "David"))
            .execute();

    // Easy mapping to any type
    return handle.createQuery("SELECT * FROM \"user\" ORDER BY \"name\"")
            .mapToBean(User.class)
            .list();
});

assertThat(users).containsExactly(
        new User(0, "Alice"),
        new User(1, "Bob"),
        new User(2, "Clarice"),
        new User(3, "David"));

See the chapter introducing the core API for details about Jdbi’s fluent API.

3.2. Declarative API

The SQL Object extension is an additional module, which provides a declarative API.

Define the SQL to execute and the shape of the results by creating an annotated Java interface.

// Declare the API using annotations on a Java interface
public interface UserDao {
    @SqlUpdate("CREATE TABLE \"user\" (id INTEGER PRIMARY KEY, \"name\" VARCHAR)")
    void createTable();

    @SqlUpdate("INSERT INTO \"user\" (id, \"name\") VALUES (?, ?)")
    void insertPositional(int id, String name);

    @SqlUpdate("INSERT INTO \"user\" (id, \"name\") VALUES (:id, :name)")
    void insertNamed(@Bind("id") int id, @Bind("name") String name);

    @SqlUpdate("INSERT INTO \"user\" (id, \"name\") VALUES (:id, :name)")
    void insertBean(@BindBean User user);

    @SqlQuery("SELECT * FROM \"user\" ORDER BY \"name\"")
    @RegisterBeanMapper(User.class)
    List<User> listUsers();
}

Then attach the interface to a Jdbi instance and execute the methods on the resuling class to execute the SQL queries.

Jdbi jdbi = Jdbi.create("jdbc:h2:mem:test");
jdbi.installPlugin(new SqlObjectPlugin());

// Jdbi implements your interface based on annotations
List<User> userNames = jdbi.withExtension(UserDao.class, dao -> {
    dao.createTable();

    dao.insertPositional(0, "Alice");
    dao.insertPositional(1, "Bob");
    dao.insertNamed(2, "Clarice");
    dao.insertBean(new User(3, "David"));

    return dao.listUsers();
});

assertThat(userNames).containsExactly(
        new User(0, "Alice"),
        new User(1, "Bob"),
        new User(2, "Clarice"),
        new User(3, "David"));

See the chapter on SQL objects for more details about the declarative API. The declarative API uses the fluent API "under the hood" and the two styles can be mixed.

4. Core API concepts

4.1. The Jdbi class

The Jdbi class is the main entry point into the library.

Each Jdbi instance maintains a set of configuration settings and wraps a JDBC DataSource.

Jdbi instances are thread-safe and do not own any database resources.

There are a few ways to create a Jdbi instance:

use a JDBC URL:

// H2 in-memory database
Jdbi jdbi = Jdbi.create("jdbc:h2:mem:test");

directly use a DataSource object which was created outside Jdbi.

DataSource ds = ...
Jdbi jdbi = Jdbi.create(ds);

from a custom class that implements ConnectionFactory.

This allows for custom implementations of connection providers such as HA failover, proxy solutions etc.

from a single JDBC Connection.

Please note that there is no magic. Any Handle or operation will use this one connection provided. If the connection is thread-safe, then multiple threads accessing the database will work properly, if the connection object is not thread-safe, then Jdbi will not be able to do anything about it.

Production code rarely provides a connection object directly to a Jdbi instance. It is useful for testing and debugging.

Applications create a single, shared Jdbi instance per data source, and set up any common configuration there. See Configuration for more details.

Jdbi does not provide connection pooling or other High Availability features, but it can be combined with other software that does.

4.2. Handle

A Handle wraps an active database connection.

Handle instances are created by a Jdbi instance to provide a database connection e.g. for a single HTTP request or event callback). Handles are intended to be short-lived and must be closed to release the database connection and possible other resources.

A Handle is used to prepare and run SQL statements against the database, and manage database transactions. It provides access to fluent statement APIs that can bind arguments, execute the statement, and then map any results into Java objects.

A Handle inherits configuration from the Jdbi at the time it is created. See Configuration for more details.

Handles and all attached query objects such as Batch, Call, Query, Script and Update should be used by a single thread and are not thread-safe.

They may be used by multiple threads as long as there is coordination that only one thread at a time is accessing them. Managing Handles and query objects across threads is error-prone and should be avoided.

4.2.1. Obtaining a managed handle

The most convenient way to get access to a handle is by using the withHandle or useHandle methods on the Jdbi class. These methods use callbacks and provide a fully managed handle that is correctly closed and all related resources are released. withHandle allows the callback to return a result while useHandle is just executing operations that do not need to return any value.

Providing a return value from a query using the Jdbi#withHandle() method:

List<String> names = jdbi.withHandle(handle ->
    handle.createQuery("select name from contacts")
          .mapTo(String.class)
          .list());
assertThat(names).contains("Alice", "Bob");

Executing an operation using the Jdbi#useHandle() method:

jdbi.useHandle(handle -> {
    handle.execute("create table contacts (id int primary key, name varchar(100))");
    handle.execute("insert into contacts (id, name) values (?, ?)", 1, "Alice");
    handle.execute("insert into contacts (id, name) values (?, ?)", 2, "Bob");
});

You may notice the "consumer" vs "callback" naming pattern in a few places in Jdbi. with- methods return a value and use objects named -Callback. use- methods do not return a value, and use objects named -Consumer. When referring to both type of objects, the term "callback" is used throughout the documentation.

The Nesting Callbacks with managed Handles and Transactions chapter has more information about nesting callbacks with managed handle objects.

4.2.2. Managing the Handle lifecycle manually

The Jdbi#open() method returns an unmanaged handle.

The Java try-with-resources construct can be used to manage the lifecycle of the handle:

try (Handle handle = jdbi.open()) {
    result = handle.execute("insert into contacts (id, name) values (?, ?)", 3, "Chuck");
}

An unmanaged handle must be used when a stateful object should be passed to the calling code. Stateful objects are iterators and streams that do not collect data ahead of time in memory but provide a data stream from the database through the JDBC Connection to the calling code. Using a callback does not work here because the connection would be closed before the calling code can consume the data.

try (Handle handle = jdbi.open()) {
    Iterator<String> names = handle.createQuery("select name from contacts where id = 1")
            .mapTo(String.class)
            .iterator();
    assertThat(names).hasNext();
    String name = names.next();
    assertThat(name).isEqualTo("Alice");
    assertThat(names).isExhausted();
}

When using Jdbi#open(), you should consider using try-with-resource or a try-finally block to ensure the handle is closed and the database connection is released. Failing to release the handle will leak connections. It is recommended to use a managed handle whenever possible.

4.3. Statement types

Any database operation within Jdbi uses a statement. Multiple types of statements exist:

Query - SQL statements that return results, e.g. a SELECT or any statement with a RETURNING clause. See createQuery
Update - SQL statements that return no value such as INSERT, UPDATE, DELETE or a DDL operation. See createUpdate
Batch - a set of operations that are executed as a batch. See createBatch
Call - execute a stored procedure. See createCall
Script - a SQL script containing multiple statements separated by ; that is executed as a batch. See also createScript
MetaData - SQL Metadata access.

4.3.1. Queries

A Query is a