How To Use JDBI In Your Spring Boot Application

In a world where Hibernate is very popular, JDBI comes as a pleasant alternative. It gives you full control of the queries and provides a useful abstraction of the entities.

Unfortunately, Spring and JDBI don’t integrate so well. I thought that it would be something quite easy to do, but I spent more time than I imagined to make it work. Integrating libraries shouldn’t cause that much hassle.

In this tutorial, you’ll make a Spring Boot application that integrates with JDBI. The app is a simple TODO service that implements two endpoints: one to create a task, and one to retrieve it. The main idea is to communicate with the database and see how to use JDBI in the process.

Already know how JDBI and Spring work, and only want to figure out how to make them integrate? Go straight to Integrate JDBI and Spring Boot.

Prerequisites

This tutorial will use Postgres as the database. To complete it, you’ll need a Postgres server up and running.

You can always switch to another database, but remember to adjust the commands accordingly.

Basic knowledge on Spring and JDBI is helpful, although you probably can complete the tutorial without knowing too much of how they work.

Create Your Spring Boot App

Let’s start by creating your app through Spring Initialzr.

Fill in the following information:

• Project: Maven Project
• Language: Java
• Group: com.justanotherdevblog
• Artifact: todo-app
• Dependencies: web

Click on Generate.

The page will download a zip file. Extract the contents into the folder of your choice, which you can open with your favorite IDE/Editor.

Now you’re ready to start developing the app.

Add JDBI and Database Configurations

Let’s start by adding the dependencies you’re going to need.

Open the pom.xml file and add the following configuration inside the <properties> tag:

<jdbi.version>3.9.0</jdbi.version>

This property will define which version of the JDBI libraries you’re going to use.

In the same file, add the code below inside the <dependencies> tag:

<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-postgres</artifactId>
    <version>${jdbi.version}</version>
</dependency>
<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-sqlobject</artifactId>
    <version>${jdbi.version}</version>
</dependency>
<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-spring4</artifactId>
    <version>${jdbi.version}</version>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.6</version>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

The changes above will import the JDBI libraries, including the integrations with Spring and Postgres. You also added the Flyway library, which you’re going to use to create the table used by the service.

The next step is to create the SQL file that will create the table in the database.

Inside the folder resources, create the folder db/migration and add a file named V1___setup.sql with the following code inside it:

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE task
(
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    description          VARCHAR(200) NOT NULL
);

The cove above adds the pgcrypto extension to Postgres. You’ll use this extension to generate a random UUID to every new register you create. The code also defines the table task, that are going to store your list of tasks to do.

Next you need to declare the database properties.

Go to resources/application.properties and add the following information:

spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=postgres

The configuration above will connect to Postgres using the default settings and running on the localhost. If you have different values for them, go ahead and change it.

Make sure your database has no tables on it. Flyway default configuration requires an empty database, and it will fail to execute the SQL if this criteria is not met.

The last step in the configuration phase is to create a configuration class in Java code.

Go to src/main/java/com/justanotherdevblog/todoapp/configuration and create the following class:

@Configuration
public class DatabaseConfiguration {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource driverManagerDataSource() {
        return new DriverManagerDataSource();
    }

    @Bean
    public DataSourceTransactionManager dataSourceTransactionManager(DataSource dataSource) {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource);
        return dataSourceTransactionManager;
    }

    @Bean
    public Jdbi jdbi(DataSource dataSource) {
        return Jdbi.create(dataSource)
                .installPlugin(new SqlObjectPlugin())
                .installPlugin(new PostgresPlugin());
    }

}

The class above declares a JDBI bean, where you install plugins to work with the SQL Object API and Postgres. You’re also defining beans for the datasource and the transaction manager.

Now that you finished the configuration let’s start adding code to the application!

Create The Endpoints

Let’s start by creating the entity representing the table from the database.

On src/main/java/com/justanotherdevblog/todoapp/model create the following class:

public class Task {

    private UUID id;

    private String description;

    public UUID getId() {
        return id;
    }

    public void setId(UUID id) {
        this.id = id;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }
}

As you can see, Task is a POJO class. Its purpose is to store the information that you’ll save or retrieve from the database.

The next step is to make a request class, where you’ll receive the payload sent from the endpoint that creates the task.

On src/main/java/com/justanotherdevblog/todoapp/request/ add the following code:

public class TaskRequest {

    private String description;

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }
}

The database generates the id automatically, which means the payload only needs to have a description field.

You’ll also need a class to represent the data from the response. Let’s create it.

On src/main/java/com/justanotherdevblog/todoapp/response create the following class:

public class TaskResponse {

    private UUID id;
    private String description;

    public TaskResponse(UUID id, String description) {
        this.id = id;
        this.description = description;
    }

    public UUID getId() {
        return id;
    }

    public String getDescription() {
        return description;
    }

}

The response contains both the description and the id. Since this class is used as a response from the create task endpoint, having an id allows API users to retrieve the task later on.

Now that you have all the classes that will be used by the endpoints, it’s time to create them.

On src/main/java/com/justanotherdevblog/todoapp/controller add the following class:

@RestController
@RequestMapping("/tasks")
public class TaskController {

    private TaskRepository taskRepository;

    public TaskController(TaskRepository taskRepository) {
        this.taskRepository = taskRepository;
    }

    @PostMapping
    public TaskResponse addTask(@RequestBody TaskRequest request) {
        Task task = taskRepository.insertTask(request.getDescription());
        return new TaskResponse(task.getId(), task.getDescription());
    }

    @GetMapping("/{taskId}")
    public TaskResponse getTask(@PathVariable("taskId") UUID taskId) {
        Task task = taskRepository.findById(taskId);
        return new TaskResponse(task.getId(), task.getDescription());
    }
    
}

This is a typical Spring controller. It expects GET and POST requests on the /tasks URI.

If the user sends a POST request, it will create a new task, save it on the database, and return the result. If the request is a GET, it will go to the database, retrieve the Task and return the result.

Wait… The code is not compiling yet. There is a class that you didn’t create and it’s used here.

The TaskRepository class represents the connection with the database. It’s here that the trick to make JDBI and Spring integrate happens. Without further ado, let’s see how you can make it work!

Integrate JDBI and Spring Boot

Let’s start by creating the interface that uses JDBI.

On src/main/java/com/justanotherdevblog/todoapp/repository create the following class:

@RegisterBeanMapper(Task.class)
public interface TaskJDBIRepository {

    @GetGeneratedKeys
    @SqlUpdate("insert into task (description) values (:description)")
    Task insertTask(@Bind("description") String description);

    @SqlQuery("select * from task where id = :id")
    Task findById(@Bind("id") UUID taskId);

}

This interface uses the SQL Objects API from JDBI, which allows you to use annotations to declare the SQL that executes in the database. Here you’re defining a method to insert tasks on the database. You’re also adding a method to find a task that contains an specific id.

There is a problem, though. Spring doesn’t know how to convert this interface into a usable bean. To fix this problem, you’re going to create a class that does the wiring for you. As you can imagine by now, this class is the on stopping your code from compiling.

On src/main/java/com/justanotherdevblog/todoapp/repository create the following class:

@Component
public class TaskRepository {

    private TaskJDBIRepository taskJDBIRepository;

    public TaskRepository(Jdbi jdbi) {
        this.taskJDBIRepository = jdbi.onDemand(TaskJDBIRepository.class);
    }

    public Task insertTask(String description) {
        return taskJDBIRepository.insertTask(description);
    }

    public Task findById(UUID taskId) {
        return taskJDBIRepository.findById(taskId);
    }
}

This class is annotated with @Component, which allows Spring to inject it into other classes. This is the class declared in the TaskController, and the one which is going to be exposed in the APIs.

The constructor receives the JDBI bean created at the very beginning of this tutorial. It then uses the onDemandmethod, passing the JDBI interface you created moments earlier.

The caveat here is that you need to duplicate all method signatures in both this class and the JDBI interface. I didn’t find an automated way of dealing with this problem. Although it’s quite annoying, it still allows you to use the SQL Objects API inside Spring.

Go to TaskController and import the class you just created. Your app is now complete!

Start your app, then execute the following command in your terminal:

curl -X POST \
  http://localhost:8080/tasks \
  -H 'Content-Type: application/json' \
  -d '{
	"description" : "do the dishes"
}'

You’ll receive a result similar to the one below:

{"id":"147f97d8-5201-4b1a-9167-02eebb2dba60","description":"do the dishes"}

Take the returned id and make a GET requests. Mine looked like this one:

curl http://localhost:8080/tasks/147f97d8-5201-4b1a-9167-02eebb2dba60

You’ll see that your app can go to the database and find the task you created in the previous step:

{"id":"147f97d8-5201-4b1a-9167-02eebb2dba60","description":"do the dishes"}

Congratulations! You now have an app running Spring and integrating with JDBI.

If you want to take a look at the final version, you can find the code on GitHub.