In this article, we're taking a look at JDBI as an alternative to JDBC to access CockroachDB.

JDBI is not an ORM but a simple abstraction on top of JDBC that depend heavily on reflection and lambda expressions to provide a better developer experience. JDBC has been around for decades but is still a very verbose API to use in contrast.

Source Code

The source code for the examples of this article can be found on GitHub.

Introduction

The JDBI example is part of a project called Roach Data which showcases different data access frameworks and ORMs for the Java platform.

The purpose of this project is to showcase how CockroachDB can be used with a mainstream Java stack composed of Spring Boot and some of the available Spring Data modules, or data access frameworks.

It provides examples of the following:

• JDBC - using Spring Data JDBC

• JPA - using Spring Data JPA with Hibernate as ORM provider

• jOOQ - using Spring Boot with jOOQ

• MyBatis - using Spring Data MyBatis/JDBC

• Reactive - using Spring Data r2dbc with the reactive PSQL driver

• (new) JDBI - using JDBI with the PSQL driver

The demos are independent and use a similar schema and test workload.

JDBI Setup

To get started, add the Maven dependency:

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

Connecting to the database

Connecting is as simple as:

Jdbi jdbi = Jdbi.create("jdbc:postgresql://localhost:26257/roach_data?sslmode=disable", "root", "");

We will however use a HikariCP datasource, wrapped in a datasource logging proxy. This logging proxy will then (through interceptors) log all SQL operations, parameter binding values, batched or not batched and so on.

HikariDataSource hikariDS = new HikariDataSource();
hikariDS.setJdbcUrl("jdbc:postgresql://localhost:26257/roach_data?sslmode=disable");
hikariDS.setUsername("root");
hikariDS.setMaximumPoolSize(32);
hikariDS.setMinimumIdle(32);
hikariDS.setAutoCommit(true); 

DataSource ds = ProxyDataSourceBuilder
                .create(hikariDS)
                .asJson()
                .logQueryBySlf4j(SLF4JLogLevel.TRACE, "io.roach.SQL_TRACE")
                .multiline()
                .build();

Jdbi jdbi = Jdbi.create(ds);

Using Handles

JDBI uses handles which represent JDBC connections. By using lambda expressions we don't need to care about closing the resources.

private static List<String> readAccountNames(Handle handle) {
    Query query = handle.createQuery("SELECT name FROM account");
    return query.mapTo(String.class).collect(Collectors.toList());
}

List<String> names = jdbi.withHandle(JdbiApplication::readAccountNames);

Querying

Querying for information and mapping the results to single values or value objects is simple. Here's another example of a point lookup with parameter binding:

private static BigDecimal readBalance(Handle handle, String name) {
    Query query = handle.createQuery("SELECT balance FROM account WHERE name = ?");
    query.bind(0, name);
    return query.mapTo(BigDecimal.class).findOne()
            .orElseThrow(() -> new BusinessException("Account not found: " + name));
}

It's worth mentioning that the parameter binding starts at index 0 and not 1 as in JDBC.

Updating

Updating data is equally straightforward by using handles:

private static void updateBalance(Handle handle, String name, BigDecimal balance) {
    Update update = handle.createUpdate("UPDATE account SET balance = ?, updated=clock_timestamp() where name = ?");
    update.bind(0, balance);
    update.bind(1, name);
    if (update.execute() != 1) {
        throw new DataAccessException("Rows affected != 1  for " + name);
    }
}

Transactions

For transactions, we are going to use the SerializableTransactionRunner that will retry on transient SQL exceptions with state code 40001. There's a special inTransaction method in the handler for this purpose.

Jdbi jdbi = Jdbi.create(ds);
jdbi.setTransactionHandler(new SerializableTransactionRunner());

In the next example, we are both reading and writing in an explicit transaction. If there is a serialization conflict, the transaction will be rolled back and retried. The SerializableTransactionRunner is fairly simple however and doesn't do any exponential backoffs.

private static BigDecimal transfer(DataSource ds, List<Account> legs) {
    Jdbi jdbi = Jdbi.create(ds);
    jdbi.setTransactionHandler(new SerializableTransactionRunner());
    return jdbi.inTransaction(TransactionIsolationLevel.SERIALIZABLE, transactionHandle -> {
        BigDecimal total = BigDecimal.ZERO;
        BigDecimal checksum = BigDecimal.ZERO;

        for (Account leg : legs) {
            BigDecimal balance = readBalance(transactionHandle, leg.name);
            updateBalance(transactionHandle, leg.name, balance.add(leg.amount));
            checksum = checksum.add(leg.amount);
            total = total.add(leg.amount.abs());
        }

        if (checksum.compareTo(BigDecimal.ZERO) != 0) {
            throw new BusinessException(
                    "Sum of account legs must equal 0 (got " + checksum.toPlainString() + ")"
            );
        }

        return total;
    });
}

That's it for this very brief tutorial. There's a lot more stuff you can do with JDBI, so check out their website. From a CockroachDB standpoint, however, it's not much different to use JDBI than JDBC directly.

Conclusion

This article looks at JDBI as an alternative to JDBC for accessing CockroachDB. The example is part of Roach Data which is a project that provides examples of JDBC, JPA, jOOQ, MyBatis, Reactive, and JDBI. This article demonstrates how to use JDBI to read and write data in an explicit transaction with SerializableTransactionRunner.