Java Jdbi

last modified July 15, 2024

In this tutorial we show how to work with data with Jdbi.

Jdbi is a convenience library built on top of JDBC. It makes database programming much easier. It manages exceptions. It has tools for automatic resource management and mapping result sets to classes.

In the examples, we work with H2 and PostgreSQL databases.

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

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.3</version>
</dependency>

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.2.224</version>
</dependency>

For the examples, we need the following artifacts: jdbi3-core, postgresql, and h2.

Simple query

In the next example, we execute a simple query.

Main.java

import org.jdbi.v3.core.Jdbi;

void main() {

    String jdbcUrl = "jdbc:h2:mem:";

    Jdbi jdbi = Jdbi.create(jdbcUrl);

    int res = jdbi.withHandle(handle -> handle.createQuery("SELECT 2 + 2")
            .mapTo(Integer.class)
            .one());

    System.out.println(res);
}

The example creates an in-memory H2 database and executes the SELECT 2 + 2 statement.

String jdbcUrl = "jdbc:h2:mem:";

This is the connection to an in-memory H2 database.

Jdbi jdbi = Jdbi.create(jdbcUrl);

We create the main entry point with Jdbi.create. It is a configurable wrapper around a JDBC datasource. Use it to obtain Handle instances and provide configuration for all handles obtained from it.

int res = jdbi.withHandle(handle -> handle.createQuery("SELECT 2 + 2")
        .mapTo(Integer.class)
        .one());

The withHandle is a convenience function which manages the lifecycle of a handle and yields it to a callback for use by clients. A Handle represents a connection to the database system. It is a wrapper around a JDBC Connection object. Handle provides essential methods for transaction management, statement creation, and other operations tied to the database session.

The query returns an integer so we map the result to an integer with mapTo. Since the returned value is a scalar, we call one. It returns the only row in the result set. Returns null if the row itself is null.

$ java Main.java
4

Calling a database function

We call the H2VERSION function which returns the version of H2 database.

Main.java

import org.jdbi.v3.core.Jdbi;

void main() {

    String jdbcUrl = "jdbc:h2:mem:";

    Jdbi jdbi = Jdbi.create(jdbcUrl);

    String res = jdbi.withHandle(handle -> handle.createQuery("SELECT H2VERSION()")
            .mapTo(String.class)
            .one());

    System.out.println(res);
}

Since the function returns a string, we map thre result to a string with mapTo.

$ java Main.java 
2.2.224

For PostgreSQL, the VERSION database function returns the database version.

Main.java

import org.jdbi.v3.core.Jdbi;

void main() {

    String jdbcUrl = "jdbc:postgresql://localhost:5432/testdb";
    String user = "postgres";
    String password = "s$cret";

    Jdbi jdbi = Jdbi.create(jdbcUrl, user, password);

    String res = jdbi.withHandle(handle -> handle.createQuery("SELECT VERSION()")
            .mapTo(String.class)
            .one());

    System.out.println(res);
}

For this example we also provide the username and password.

$ java Main.java 
PostgreSQL 16.0, compiled by Visual C++ build 1935, 64-bit

Batches

A batch refers to a group of SQL statements that are submitted and executed together as a single unit. This functionality offers performance benefits and simplifies code for certain database operations.

Main.java

import org.jdbi.v3.core.Jdbi;

void main() {

    String jdbcUrl = "jdbc:postgresql://localhost:5432/testdb";
    String user = "postgres";
    String password = "s$cret";

    Jdbi jdbi = Jdbi.create(jdbcUrl, user, password);

    jdbi.withHandle(handle -> handle.createBatch()
            .add("DROP TABLE IF EXISTS cars")
            .add("CREATE TABLE cars(id serial PRIMARY KEY, name VARCHAR(255), price INT)")
            .add("INSERT INTO cars(name, price) VALUES('Audi',52642)")
            .add("INSERT INTO cars(name, price) VALUES('Mercedes',57127)")
            .add("INSERT INTO cars(name, price) VALUES('Skoda',9000)")
            .add("INSERT INTO cars(name, price) VALUES('Volvo',29000)")
            .add("INSERT INTO cars(name, price) VALUES('Bentley',350000)")
            .add("INSERT INTO cars(name, price) VALUES('Citroen',21000)")
            .add("INSERT INTO cars(name, price) VALUES('Hummer',41400)")
            .add("INSERT INTO cars(name, price) VALUES('Volkswagen',21600)")

            .execute());

    System.out.println("Table created and data inserted using JDBI batch.");
}

The example creates a cars table in the database. A new batch is created with the createBatch method. Statements are added with the add method. Finally, the whole batch is run with execute.

The one method

Queries are executed with select. The one method returns when you expect the result to contain exactly one row. This method returns null only if the returned row maps to null and throws an exception if the result has zero or multiple rows.

Main.java

import org.jdbi.v3.core.Jdbi;

void main() {

    String jdbcUrl = "jdbc:postgresql://localhost:5432/testdb";
    String user = "postgres";
    String password = "s$cret";

    Jdbi jdbi = Jdbi.create(jdbcUrl, user, password);

    String res = jdbi.withHandle(handle -> 
            handle.select("SELECT name FROM cars WHERE id = ?", 3)
                    .mapTo(String.class)
                    .one());


    System.out.println(res);
}

The example returns the name of a car for the specified Id.

$ java Main.java
Skoda

The findOne method

The findOne method returns an Optional.

Main.java

import org.jdbi.v3.core.Jdbi;

import java.util.Optional;

void main() {

    String jdbcUrl = "jdbc:postgresql://localhost:5432/testdb";
    String user = "postgres";
    String password = "s$cret";

    Jdbi jdbi = Jdbi.create(jdbcUrl, user, password);

    int id = 3;

    Optional<String> res = jdbi.withHandle(handle ->
            handle.select("SELECT name FROM cars WHERE id = ?", id)
                    .mapTo(String.class)
                    .findOne());

    res.ifPresentOrElse(System.out::println, () -> System.out.println("N/A"));
}

The example uses the Optional type for the return value.

Binding of parameters

Parameter binding is a mechanism to prevent SQL injection vulnerabilities and improve code readability. You can bind values to placeholders within your SQL statements, and JDBI handles setting the actual values for the database execution.

Types of binding:

positional
named
bean binding

The following is a positional binding example:

Main.java

import org.jdbi.v3.core.Jdbi;
import org.jdbi.v3.core.mapper.reflect.ConstructorMapper;

import java.util.Optional;

void main() {

    String jdbcUrl = "jdbc:postgresql://localhost:5432/testdb";
    String user = "postgres";
    String password = "s$cret";

    Jdbi jdbi = Jdbi.create(jdbcUrl, user, password);

    int id = 3;

    Optional<Car> res = jdbi.withHandle(handle -> 
            handle.select("SELECT * FROM cars WHERE id = ?", id)
                    .registerRowMapper(Car.class, ConstructorMapper.of(Car.class))
                    .mapTo(Car.class)
                    .findOne());

    res.ifPresentOrElse(System.out::println, () -> System.out.println("N/A"));
}

public record Car(int id, String name, int price) {
}

The example maps a row to a Car record. The registerRowMapper method registers an automatic mapper for the Car.

The following example uses a named binding.

Main.java

import org.jdbi.v3.core.Jdbi;
import org.jdbi.v3.core.mapper.reflect.ConstructorMapper;

import java.util.Optional;

void main() {

    String jdbcUrl = "jdbc:postgresql://localhost:5432/testdb";
    String user = "postgres";
    String password = "s$cret";

    Jdbi jdbi = Jdbi.create(jdbcUrl, user, password);

    int id = 3;

    Optional<Car> res = jdbi.withHandle(handle -> 
            handle.select("SELECT * FROM cars WHERE id = :id")
                    .registerRowMapper(Car.class, ConstructorMapper.of(Car.class))
                    .bind("id", id)
                    .mapTo(Car.class)
                    .findOne());

    res.ifPresentOrElse(System.out::println, () -> System.out.println("N/A"));
}

public record Car(int id, String name, int price) {
}

In addition, rather than passing the value in the select method, we call the bind method in which we bind the id name to the id variable's value.

$ java Main.java
Car[id=3, name=Skoda, price=9000]

Mapping rows to a list

In the next example we map multiple rows to a list of cars.

Main.java

import org.jdbi.v3.core.Jdbi;
import org.jdbi.v3.core.mapper.reflect.ConstructorMapper;

import java.util.List;

void main() {

    String jdbcUrl = "jdbc:postgresql://localhost:5432/testdb";
    String user = "postgres";
    String password = "s$cret";

    Jdbi jdbi = Jdbi.create(jdbcUrl, user, password);

    List<Car> cars = jdbi.withHandle(handle -> 
            handle.select("SELECT * FROM cars")
                    .registerRowMapper(Car.class, ConstructorMapper.of(Car.class))
                    .mapTo(Car.class)
                    .list());

    if (cars.isEmpty()) {

        System.out.println("No cars found.");
    } else {
        
        System.out.println("List of cars:");
        cars.forEach(System.out::println);
    }
}

public record Car(int id, String name, int price) {
}

In the example, we select all cars from the table. We map the returned rows to a list of cars.

$ java Main.java
List of cars:
Car[id=1, name=Audi, price=52642]
Car[id=2, name=Mercedes, price=57127]
Car[id=3, name=Skoda, price=9000]
Car[id=4, name=Volvo, price=29000]
Car[id=5, name=Bentley, price=350000]
Car[id=6, name=Citroen, price=21000]
Car[id=7, name=Hummer, price=41400]
Car[id=8, name=Volkswagen, price=21600]

SqlObjects

Jdbi SqlObjects is an extension for the Jdbi library that provides a declarative way to interact with relational databases in Java.

<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-sqlobject</artifactId>
    <version>3.45.2</version>
</dependency>

We need to add the jdbi3-sqlobject dependency.

Main.java

import org.jdbi.v3.core.Jdbi;
import org.jdbi.v3.core.mapper.reflect.ConstructorMapper;
import org.jdbi.v3.sqlobject.SqlObject;
import org.jdbi.v3.sqlobject.SqlObjectPlugin;
import org.jdbi.v3.sqlobject.statement.SqlQuery;

import java.util.Optional;

void main() {

    String jdbcUrl = "jdbc:postgresql://localhost:5432/testdb";
    String user = "postgres";
    String password = "s$cret";

    Jdbi jdbi = Jdbi.create(jdbcUrl, user, password);
    jdbi.installPlugin(new SqlObjectPlugin());

    jdbi.registerRowMapper(Car.class, ConstructorMapper.of(Car.class));
    CarDao carDao = jdbi.onDemand(CarDao.class);

    int searchId = 2;
    Optional<Car> car = carDao.findById(searchId);

    if (car.isPresent()) {
        System.out.println("Car found: " + car.get());
    } else {
        System.out.println("Car with id " + searchId + " not found.");
    }

}

public record Car(int id, String name, int price) {
}

public interface CarDao extends SqlObject {

    @SqlQuery("SELECT * FROM cars WHERE id = ?")
    Optional<Car> findById(int id);
}

In the program, we define the query with the @SqlQuery for the function declaration in the CarDao interface.

CarDao carDao = jdbi.onDemand(CarDao.class);

The DAO object is created with onDemand.

int searchId = 2;
Optional<Car> car = carDao.findById(searchId);

We call the findById on the DAO object.

In this article we have worked with Java Jdbi library.

Ebooks

Java Jdbi

Simple query

Calling a database function

Batches

The one method

The findOne method

Binding of parameters

Mapping rows to a list

SqlObjects