Welcome to another Spring Boot tutorial.

In this post, we’ll implement a sample REST API that uses Spring Boot with Jbdi 3 and PostgreSQL.

· Prerequisites
· Overview
∘ What is Jdbi3?
∘ Why Jdbi3?
· Getting Started
· Project Structure
· Jdbi setup
∘ Book Model
∘ Create Book Repository interface
∘ Service Layer
∘ Book Controller
· Test the application
· Conclusion
· References

Prerequisites

This is the list of all the prerequisites for following this story:

  • Spring Boot 4+
  • Maven 3.6.3 or later
  • Java 21
  • PostgreSQL
  • Postman/insomnia or any other API testing tool.

Overview

What is Jdbi3?

Jdbi is an open-source Java library (Apache license) that uses lambda expressions and reflection to provide a friendlier, higher-level interface than JDBC to access the database. Jdbi 3 is the third major release which introduces enhanced support for Java 8, countless refinements to the design and implementation, and enhanced support for modular plugins.

Why Jdbi3?

  • It is open source
  • Offers full control of the queries
  • It can be combined with connection pools and high-availability features in the database drivers.
  • It can invoke a database stored procedure.
  • Simpler learning curve: If you know SQL, you already know 80% of Jdbi
  • Easy to optimize queries without fighting the framework

Getting Started

We will start by creating a simple Spring Boot project from start.spring.io, with the following dependencies: Spring Web, Spring Data JDBC, Lombok, PostgreSQL Driver, and Validation.

Now, add Jdbi3 dependencies in the pom.xml file


        <!-- Jdbi 3 Spring Plugin -->
        <dependency>
            <groupId>org.jdbi</groupId>
            <artifactId>jdbi3-spring</artifactId>
            <version>3.51.0</version>
        </dependency>

        <!-- Jdbi 3 Support for Postgres data types -->
        <dependency>
            <groupId>org.jdbi</groupId>
            <artifactId>jdbi3-postgres</artifactId>
            <version>3.51.0</version>
        </dependency>

jdbi3-spring: Provides support for all Spring Framework versions that are under OSS support.

jdbi3-postgres: provides enhanced integration with the PostgreSQL JDBC Driver.

Project Structure

Our project structure will look like this:

src
├── main
│   ├── java
│   │   └── com.example.springbootjdbi3
│   │       ├── configuration
│   │       │   └── DatabaseConfiguration.java     # JDBI & DataSource config
│   │       │
│   │       ├── controller
│   │       │   ├── AuthorController.java           # REST endpoints for authors
│   │       │   └── BookController.java             # REST endpoints for books
│   │       │
│   │       ├── entities
│   │       │   ├── Author.java                     # Author domain model
│   │       │   └── Book.java                       # Book domain model
│   │       │
│   │       ├── exception
│   │       │   └── DataNotFoundException.java      # Custom not-found exception
│   │       │
│   │       ├── repositories
│   │       │   ├── AuthorRepository.java           # JDBI DAO for authors
│   │       │   └── BookRepository.java             # JDBI DAO for books
│   │       │
│   │       ├── service
│   │       │   ├── AuthorService.java              # Author service interface
│   │       │   ├── BookService.java                # Book service interface
│   │       │   └── impl
│   │       │       ├── AuthorServiceImpl.java      # Author service implementation
│   │       │       └── BookServiceImpl.java        # Book service implementation
│   │       │
│   │       └── SpringBootJdbi3Application.java     # Application entry point
│   │
│   └── resources
│       ├── application.yml                         # App configuration
│       └── schema.sql                              # Database schema
│
└── test                                            # Unit & integration tests

Jdbi setup

The first step is to add the database connection parameters in the application.yml file:

spring:
  datasource:
    # database username
    username: xxxxx
    # username password
    password: xxxxx
    # JDBC URL
    url: jdbc:postgresql://localhost:5432/bookdb
    # JDBC driver name
    driver-class-name: org.postgresql.Driver

To integrate Jdbi 3 into a Spring Boot application, we need to explicitly configure how the application connects to the database, manages transactions, and exposes a Jdbi instance as a Spring bean.

@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());
    }

}

Jdbi does not manage transactions by itself. Instead, it integrates with Spring’s transaction infrastructure.

The next step is to create an SQL file to initialize the database by creating the necessary tables. Alternatively, you can use a database migration tool such as Liquibase or Flyway.

drop table if exists public.book;
drop table if exists public.author;

create table public.author
(
    id        serial primary key,
    firstname character varying(255),
    lastname  character varying(255)
);

create table public.book
(
    id          serial primary key,
    description text,
    isbn        character varying(255),
    page        integer          NOT NULL,
    price       double precision NOT NULL,
    title       character varying(100)
);

Book Model

Let’s start by creating a Book class representing the table from the database.

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Book {

    private Long id;

    @NotBlank
    @Size(max = 100)
    private String title;

    private int page;

    private String isbn;

    private String description;

    private double price;
}

Create Book Repository interface

The BookRepository interface uses JDBI’s SQL Objects API, which allows SQL queries to be declared using annotations. JDBI automatically generates the repository implementation and executes the SQL against the database.

@Repository
@RegisterBeanMapper(Book.class)
public interface BookRepository {
    @SqlQuery("select * from book")
    List<Book> findAll();

    @SqlQuery("select * from book where id = :id")
    Book findById(@Bind("id") long id);

    @Transaction
    @SqlUpdate("delete from book where id = :id")
    int deleteById(@Bind("id") long id);

    @Transaction
    @SqlUpdate("update book set title = :title, page=:page , isbn=:isbn , description=:description , price=:price where id = :id")
    int update(@BindBean Book book);

    @Transaction
    @GetGeneratedKeys
    @SqlUpdate("insert into book (title, page, isbn, description, price) values (:title, :page, :isbn, :description, :price)")
    int insert(@BindBean Book book);
}

– @SqlUpdate annotation for operations that modify data (i.e., inserts, updates, deletes).
– @SqlQuery annotation for select operations.
– @GetGeneratedKeys annotation may be used on a @SqlUpdate or @SqlBatch method to return the keys generated from the SQL statement.
– @RegisterBeanMapper(Book.class) annotation to tell Jdbi to convert the returned row into a Book object.
– @Transaction annotation tells Jdbi to wrap a specific method call in a transaction.

JDBI provides a rich set of SQL Object annotations that simplify Spring integration, following a repository implementation approach similar to Spring Data.

Service Layer

In the service layer, the BookServiceImpl class is automatically provided with an instance of BookRepository via the JDBI bean. This allows the service to perform database operations without manually creating or wiring the repository.

@Service
public class BookServiceImpl implements BookService {

    private final BookRepository repository;

    public BookServiceImpl(Jdbi jdbi) {
        this.repository = jdbi.onDemand(BookRepository.class);
    }

    @Override
    public Book create(Book book) {
        return getOne(repository.insert(book));
    }

    @Override
    public List<Book> getAll(){
        return repository.findAll();
    }

    @Override
    public Book getOne(long id) {
        Book book = repository.findById(id);
        if(ObjectUtils.isEmpty(book)){
           throw new DataNotFoundException(MessageFormat.format("Book id {0} not found", String.valueOf(id)));
        }
        return book;
    }

    @Override
    public int deleteById(long id) {
        return repository.deleteById(id);
    }

}

On-demand instances have an open-ended lifecycle, acquiring and releasing a database connection for each method call. They are thread-safe and can be reused throughout the application.

Book Controller

@Slf4j
@RestController
@RequestMapping("/api/book")
public class BookController {

    private final BookService entityService;

  public BookController(BookService entityService) {
  this.entityService = entityService;
 }


    /**
     * {@code POST  /book} : Create a new Book.
     *
     * @param book the Book to create.
     * @return the {@link ResponseEntity} with status {@code 201 (Created)} and with body the new Book.
     */
    @PostMapping()
    public ResponseEntity<Book> createBook(@RequestBody @Valid Book book) {
        log.debug("REST request to save Book : {}", book);
        return new ResponseEntity<>(entityService.create(book), HttpStatus.CREATED);
    }

    /**
     * {@code GET  /Book} : get all the Books.
     *
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and the list of Book in body.
     */

    @GetMapping()
    public ResponseEntity<List<Book>> getAllBook() {
     log.debug("REST request to get all books");
        List<Book> lst = entityService.getAll();

        return new ResponseEntity<>(lst,HttpStatus.OK);
    }

    /**
     * {@code GET  /book/:id} : get the "id" Book.
     *
     * @param id the id of the Book to retrieve.
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and with body the Book, or with status {@code 404 (Not Found)}.
     */
    @GetMapping(value = "/{id}")
    public ResponseEntity<Book> getOneBook(@PathVariable("id") Long id) {
        log.debug("REST request to get book : {}", id);
        Book e = entityService.getOne(id);

        return new ResponseEntity<>(e, HttpStatus.OK);
    }

}

Test the application

Now we can run our application and test it.

  • Create a new Book
POST http://localhost:8080/api/book
  • Get All Book
GET http://localhost:8080/api/book
  • Get the book by Id
GET http://localhost:8080/api/book/104

Conclusion

Well done !!. In this story, we built a REST API application using Spring Boot, JDBI 3, and PostgreSQL. We designed a layered architecture with controllers, services, and repositories, and used JDBI’s SQL Object API to simplify database access.

Integrating Jdbi 3 with Spring Boot provides a powerful, SQL-first approach to building REST APIs, while minimizing the overhead associated with traditional ORM frameworks. It provides precise control over database interactions and the flexibility needed for complex queries. For production environments, it’s recommended to use a connection pool such as HikariCP and implement robust error and exception handling to ensure reliability and performance.

The complete source code is available on GitHub.

Support me through GitHub Sponsors.

Thank you for reading!! See you in the next story.

Last updated: December 2025

References

👉 Link to Medium blog

, , , ,

Related Posts

Docker Java Redis Spring Boot Spring Cloud

Rate Limiting with Spring Cloud Gateway and Redis

Editorial Team
Flutter Keycloak Spring Boot Spring Cloud

Keycloak for Smart Devices: A Practical Guide to Device Code Flow 2/2

Editorial Team