# CockroachDB and JDBI: A Practical Example

In this article, we're taking a look at [JDBI](http://jdbi.org/) 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](https://github.com/cockroachlabs/roach-data).

# Introduction

The [JDBI](https://github.com/cockroachlabs/roach-data/blob/master/roach-data-jdbi/README.md) example is part of a project called [Roach Data](https://github.com/cockroachlabs/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:

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

# Connecting to the database

Connecting is as simple as:

```java
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.

```java
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.

```java
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:

```java
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:

```java
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.

```java
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.

```java
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.