Using JDBI with Spring Boot

1. Introduction

In a previous tutorial, we covered the basics of JDBI, an open-source library for relational database access that removes much of the boilerplate code related to direct JDBC usage.

This time, we’ll see how we can use JDBI  in a Spring Boot application. We’ll also cover some aspects of this library that make it a good alternative to Spring Data JPA in some scenarios.

2. Project Setup

First of all, let’s add the appropriate JDBI dependencies to our project. This time, we’ll use JDBI’s Spring integration plugin, which brings all required core dependencies. We’ll also bring in the SqlObject plugin, which adds some extra features to base JDBI that we’ll use in our examples:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
    <version>3.0.5</version>
</dependency>
<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-spring5</artifactId>
    <version>3.38.0</version>
</dependency>
<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-sqlobject</artifactId>
    <version>3.38.0</version> 
</dependency>

The latest version of those artifacts can be found in Maven Central:

• Spring Boot Start Data JDBC
• JDBI Spring Integration
• JDBI SqlObject Plugin

We also need a suitable JDBC driver to access our database. In this article we’ll use H2, so we must add its driver to our dependencies list as well:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.1.214</version>
    <scope>runtime</scope>
</dependency>

3. JDBI Instantiation and Configuration

We’ve already seen in our previous article that we need a Jdbi instance as our entry point to access JDBI’s API. As we’re in the Spring world, it makes sense to make an instance of this class available as a bean.

We’ll leverage Spring Boot’s auto-configuration capabilities to initialize a DataSource and pass it to a @Bean-annotated method which will create our global Jdbi instance.

We’ll also pass any discovered plugins and RowMapper instances to this method so that they’re registered upfront:

@Configuration
public class JdbiConfiguration {
    @Bean
    public Jdbi jdbi(DataSource ds, List<JdbiPlugin> jdbiPlugins, List<RowMapper<?>> rowMappers) {        
        TransactionAwareDataSourceProxy proxy = new TransactionAwareDataSourceProxy(ds);        
        Jdbi jdbi = Jdbi.create(proxy);

        // Register all available plugins
        log.info("[I27] Installing plugins... ({} found)", jdbiPlugins.size());
        jdbiPlugins.forEach(jdbi::installPlugin);

        // Register all available rowMappers
        log.info("[I31] Installing rowMappers... ({} found)", rowMappers.size());
        rowMappers.forEach(jdbi::registerRowMapper);
        return jdbi;
    }
}

Here, we’re using an available DataSource and wrapping it in a TransactionAwareDataSourceProxy. We need this wrapper in order to integrate Spring-managed transactions with JDBI, as we’ll see later.

Registering plugins and RowMapper instances is straightforward. All we have to do is call installPlugin and installRowMapper for every available JdbiPlugin and RowMapper, respectively. After that, we have a fully configured Jdbi instance that we can use in our application.

4. Sample Domain

Our example uses a very simple domain model consisting of just two classes: CarMaker and CarModel. Since JDBI does not require any annotations on our domain classes, we can use simple POJOs:

public class CarMaker {
    private Long id;
    private String name;
    private List<CarModel> models;
    // getters and setters ...
}

public class CarModel {
    private Long id;
    private String name;
    private Integer yearDate;
    private String sku;
    private Long makerId;
    // getters and setters ...
}

5. Creating DAOs

Now, let’s create Data Access Objects (DAOs) for our domain classes. JDBI SqlObject plugin offers an easy way to implement those classes, which resembles Spring Data’s way of dealing with this subject.

We just have to define an interface with a few annotations and, automagically, JDBI will handle all low-level stuff such as handling JDBC connections and creating/disposing of statements and ResultSets:

@UseClasspathSqlLocator
public interface CarMakerDao {
    @SqlUpdate
    @GetGeneratedKeys
    Long insert(@BindBean CarMaker carMaker);
    
    @SqlBatch("insert")
    @GetGeneratedKeys
    List<Long> bulkInsert(@BindBean List<CarMaker> carMakers);
    
    @SqlQuery
    CarMaker findById(Long id);
}

@UseClasspathSqlLocator
public interface CarModelDao {    
    @SqlUpdate
    @GetGeneratedKeys
    Long insert(@BindBean CarModel carModel);

    @SqlBatch("insert")
    @GetGeneratedKeys
    List<Long> bulkInsert(@BindBean List<CarModel> models);

    @SqlQuery
    CarModel findByMakerIdAndSku(@Bind("makerId") Long makerId, @Bind("sku") String sku );
}

Those interfaces are heavily annotated, so let’s take a quick look at each of them.

5.1. @UseClasspathSqlLocator

The @UseClasspathSqlLocator annotation tells JDBI that actual SQL statements associated with each method are located at external resource files. By default, JDBI will lookup a resource using the interface’s fully qualified name and method. For instance, given an interface’s FQN of a.b.c.Foo with a findById() method, JDBI will look for a resource named a/b/c/Foo/findById.sql.

This default behavior can be overridden for any given method by passing the resource name as the value for the @SqlXXX annotation.

5.2. @SqlUpdate/@SqlBatch/@SqlQuery

We use the @SqlUpdate, @SqlBatch, and @SqlQuery annotations to mark data-access methods, which will be executed using the given parameters. Those annotations can take an optional string value, which will be the literal SQL statement to execute – including any named parameters – or when used with @UseClasspathSqlLocator, the resource name containing it.

@SqlBatch-annotated methods can have collection-like arguments and execute the same SQL statement for every available item in a single batch statement. In each of the above DAO classes, we have a bulkInsert method that illustrates its use. The main advantage of using batch statements is the added performance we can achieve when dealing with large data sets.

5.3. @GetGeneratedKeys

As the name implies, the @GetGeneratedKeys annotation allows us to recover any generated keys as a result of successful execution. It’s mostly used in insert statements where our database will auto-generate new identifiers and we need to recover them in our code.

5.4. @BindBean/@Bind

We use @BindBean and @Bind annotations to bind the named parameters in the SQL statement with method parameters. @BindBean uses standard bean conventions to extract properties from a POJO – including nested ones. @Bind uses the parameter name or the supplied value to map its value to a named parameter.

6. Using DAOs

To use those DAOs in our application, we have to instantiate them using one of the factory methods available in JDBI.

In a Spring context, the simplest way is to create a bean for every DAO using the onDemand method:

@Bean
public CarMakerDao carMakerDao(Jdbi jdbi) {        
    return jdbi.onDemand(CarMakerDao.class);       
}

@Bean
public CarModelDao carModelDao(Jdbi jdbi) {
    return jdbi.onDemand(CarModelDao.class);
}

The onDemand-created instance is thread-safe and uses a database connection only during a method call. Since JDBI we’ll use the supplied TransactionAwareDataSourceProxy, this means we can use it seamlessly with Spring-managed transactions.

While simple, the approach we’ve used here is far from ideal when we have to deal with more than a few tables. One way to avoid writing this kind of boilerplate code is to create a custom BeanFactory. Describing how to implement such a component is beyond the scope of this tutorial, though.

7. Transactional Services

Let’s use our DAO classes in a simple service class that creates a few CarModel instances given a CarMaker populated with models. First, we’ll check if the given CarMaker was previously saved, saving it to the database if needed. Then, we’ll insert every CarModel one by one.

If there’s a unique key violation (or some other error) at any point, the whole operation must fail and a full rollback should be performed.

JDBI provides a @Transaction annotation, but we can’t use it here as it is unaware of other resources that might be participating in the same business transaction. Instead, we’ll use Spring’s @Transactional annotation in our service method:

@Service
public class CarMakerService {
    
    private CarMakerDao carMakerDao;
    private CarModelDao carModelDao;

    public CarMakerService(CarMakerDao carMakerDao,CarModelDao carModelDao) {        
        this.carMakerDao = carMakerDao;
        this.carModelDao = carModelDao;
    }    
    
    @Transactional
    public int bulkInsert(CarMaker carMaker) {
        Long carMakerId;
        if (carMaker.getId() == null ) {
            carMakerId = carMakerDao.insert(carMaker);
            carMaker.setId(carMakerId);
        }
        carMaker.getModels().forEach(m -> {
            m.setMakerId(carMaker.getId());
            carModelDao.insert(m);
        });                
        return carMaker.getModels().size();
    }
}

The operation’s implementation itself is quite simple: we’re using the standard convention that a null value in the id field implies this entity has not yet been persisted to the database. If this is the case, we use the CarMakerDao instance injected in the constructor to insert a new record in the database and get the generated id.

Once we have the CarMaker‘s id, we iterate over the models, setting the makerId field for each one before saving it to the database.

All those database operations will happen using the same underlying connection and will be part of the same transaction. The trick here lies in the way we’ve tied JDBI to Spring using TransactionAwareDataSourceProxy and creating onDemand DAOs. When JDBI requests a new Connection, it will get an existing one associated with the current transaction, thus integrating its lifecycle to other resources that might be enrolled.

8. Conclusion

In this article, we’ve shown how to quickly integrate JDBI into a Spring Boot application. This is a powerful combination in scenarios where we can’t use Spring Data JPA for some reason but still want to use all other features such as transaction management, integration and so on.