Master Spring 6.1 JdbcClient: Simplify CRUD with Fluent API

1. Introduction

Spring Framework 6.1 introduces a new JdbcClient API, a wrapper over JdbcTemplate that provides a fluent API for database operations. Spring Boot 3.2 includes Spring Framework 6.1. To follow the tutorial, generate a Spring Boot project with Spring JDBC, MySQL driver and Testcontainers starters via https://start.spring.io/. At the time of writing, Spring Boot 3.2.0‑M2 is available.

2. Define the Bookmark entity

Create a Java record for Bookmark (Java 16+):

<code>import java.time.Instant;

public record Bookmark(Long id, String title, String url, Instant createdAt) {
}
</code>

3. Database schema

Corresponding MySQL table definition:

<code>CREATE TABLE `bookmarks` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `title` varchar(255) NOT NULL COMMENT 'title',
  `url` varchar(255) NOT NULL COMMENT 'url address',
  `created_at` timestamp NOT NULL COMMENT 'creation time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='bookmark table';
</code>

4. CRUD operations with JdbcClient

Repository skeleton:

<code>@Repository
@Transactional(readOnly = true)
public class BookmarkRepository {
    private final JdbcClient jdbcClient;

    public BookmarkRepository(JdbcClient jdbcClient) {
        this.jdbcClient = jdbcClient;
    }
    // ... CRUD methods ...
}
</code>

4.1 Find all bookmarks

Simple query using the default row mapper:

<code>public List<Bookmark> findAll() {
    String sql = "select id, title, url, created_at from bookmarks";
    return jdbcClient.sql(sql).query(Bookmark.class).list();
}
</code>

If custom mapping is required, implement a RowMapper:

<code>public List<Bookmark> findAll() {
    String sql = "select id, title, url, created_at from bookmarks";
    return jdbcClient.sql(sql).query(new BookmarkRowMapper()).list();
}

static class BookmarkRowMapper implements RowMapper<Bookmark> {
    @Override
    public Bookmark mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new Bookmark(
            rs.getLong("id"),
            rs.getString("title"),
            rs.getString("url"),
            rs.getTimestamp("created_at").toInstant()
        );
    }
}
</code>

4.2 Find by ID

Query a single bookmark by its identifier:

<code>public Optional<Bookmark> findById(Long id) {
    String sql = "select id, title, url, created_at from bookmarks where id = :id";
    return jdbcClient.sql(sql).param("id", id).query(Bookmark.class).optional();
}
</code>

4.3 Insert a new bookmark

Use MySQL's

INSERT … RETURNING

syntax together with a

KeyHolder

to obtain the generated primary key:

<code>@Transactional
public Long save(Bookmark bookmark) {
    String sql = "insert into bookmarks(title, url, created_at) values(:title,:url,:createdAt) returning id";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcClient.sql(sql)
        .param("title", bookmark.title())
        .param("url", bookmark.url())
        .param("createdAt", Timestamp.from(bookmark.createdAt()))
        .update(keyHolder);
    return keyHolder.getKeyAs(Long.class);
}
</code>

4.4 Update a bookmark

<code>@Transactional
public void update(Bookmark bookmark) {
    String sql = "update bookmarks set title = :title, url = :url where id = :id";
    int count = jdbcClient.sql(sql)
        .param("title", bookmark.title())
        .param("url", bookmark.url())
        .param("id", bookmark.id())
        .update();
    if (count == 0) {
        throw new RuntimeException("Bookmark not found");
    }
}
</code>

4.5 Delete a bookmark

<code>@Transactional
public void delete(Long id) {
    String sql = "delete from bookmarks where id = ?";
    int count = jdbcClient.sql(sql).param(1, id).update();
    if (count == 0) {
        throw new RuntimeException("Bookmark not found");
    }
}
</code>

5. Testing with Testcontainers

Create

src/test/resources/test_data.sql

to initialise the database:

<code>TRUNCATE TABLE bookmarks;

INSERT INTO bookmarks(title, url, created_at) VALUES
('How (not) to ask for Technical Help?', 'https://sivalabs.in/how-to-not-to-ask-for-technical-help', CURRENT_TIMESTAMP),
('Getting Started with Kubernetes', 'https://sivalabs.in/getting-started-with-kubernetes', CURRENT_TIMESTAMP),
('Few Things I learned in the HardWay in 15 years of my career', 'https://sivalabs.in/few-things-i-learned-the-hardway-in-15-years-of-my-career', CURRENT_TIMESTAMP),
('All the resources you ever need as a Java & Spring application developer', 'https://sivalabs.in/all-the-resources-you-ever-need-as-a-java-spring-application-developer', CURRENT_TIMESTAMP),
('SpringBoot Integration Testing using Testcontainers Starter', 'https://sivalabs.in/spring-boot-integration-testing-using-testcontainers-starter', CURRENT_TIMESTAMP),
('Testing SpringBoot Applications', 'https://sivalabs.in/spring-boot-testing', CURRENT_TIMESTAMP);
</code>

Annotate the test class with

@Sql("/test-data.sql")

so the script runs before each test. The following JUnit test demonstrates CRUD verification using the Testcontainers MySQL JDBC URL:

<code>package com.sivalabs.bookmarks.domain;

import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.ImportAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.JdbcClientAutoConfiguration;
import org.springframework.boot.test.autoconfigure.jdbc.JdbcTest;
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.test.context.jdbc.Sql;

import java.time.Instant;
import java.util.List;
import java.util.Optional;

import static org.assertj.core.api.Assertions.assertThat;

@JdbcTest(properties = {
    "spring.test.database.replace=none",
    "spring.datasource.url=jdbc:tc:mysql:5.7.34:///db"
})
@ImportAutoConfiguration(JdbcClientAutoConfiguration.class)
@Sql("/test-data.sql")
class BookmarkRepositoryTest {

    @Autowired
    JdbcClient jdbcClient;

    BookmarkRepository bookmarkRepository;

    @BeforeEach
    void setUp() {
        bookmarkRepository = new BookmarkRepository(jdbcClient);
    }

    @Test
    void shouldFindAllBookmarks() {
        List<Bookmark> bookmarks = bookmarkRepository.findAll();
        assertThat(bookmarks).isNotEmpty();
        assertThat(bookmarks).hasSize(6);
    }

    @Test
    void shouldCreateBookmark() {
        Bookmark bookmark = new Bookmark(null, "My Title", "https://sivalabs.in", Instant.now());
        Long id = bookmarkRepository.save(bookmark);
        assertThat(id).isNotNull();
    }

    @Test
    void shouldGetBookmarkById() {
        Bookmark bookmark = new Bookmark(null, "My Title", "https://sivalabs.in", Instant.now());
        Long id = bookmarkRepository.save(bookmark);
        Optional<Bookmark> optional = bookmarkRepository.findById(id);
        assertThat(optional).isPresent();
        assertThat(optional.get().id()).isEqualTo(id);
    }

    @Test
    void shouldEmptyWhenBookmarkNotFound() {
        Optional<Bookmark> optional = bookmarkRepository.findById(9999L);
        assertThat(optional).isEmpty();
    }

    @Test
    void shouldUpdateBookmark() {
        Bookmark bookmark = new Bookmark(null, "My Title", "https://sivalabs.in", Instant.now());
        Long id = bookmarkRepository.save(bookmark);
        Bookmark changed = new Bookmark(id, "My Updated Title", "https://www.sivalabs.in", bookmark.createdAt());
        bookmarkRepository.update(changed);
        Bookmark updated = bookmarkRepository.findById(id).orElseThrow();
        assertThat(updated.title()).isEqualTo(changed.title());
    }

    @Test
    void shouldDeleteBookmark() {
        Bookmark bookmark = new Bookmark(null, "My Title", "https://sivalabs.in", Instant.now());
        Long id = bookmarkRepository.save(bookmark);
        bookmarkRepository.delete(id);
        Optional<Bookmark> optional = bookmarkRepository.findById(id);
        assertThat(optional).isEmpty();
    }
}
</code>

The Testcontainers JDBC URL automatically starts a MySQL container for the tests.

6. Conclusion

The new JdbcClient API offers a fluent way to implement the data‑access layer with JDBC. While JdbcTemplate remains usable, the tutorial recommends adopting JdbcClient for future projects.