ZetCode

Spring JdbcClient

last modified June 6, 2025

This tutorial demonstrates how to use Spring's new JdbcClient for database access.

JdbcClient is a new fluent API introduced in Spring Framework 6.1. It simplifies JDBC operations while maintaining flexibility. It provides a modern alternative to JdbcTemplate with a more intuitive API.

We'll use H2 in-memory database for all examples. JdbcClient works with any JDBC-compliant database.

<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>6.2.7</version>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>2.3.232</version>
    </dependency>
</dependencies>

Add these dependencies to use Spring JdbcClient with H2 database.

Basic CRUD Operations

This example shows basic create, read, update and delete operations.

Main.java
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;
import java.util.List;

record User(String name, String email) {}

void main() {
    DataSource dataSource = new DriverManagerDataSource(
        "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1", "sa", "");
    JdbcClient jdbClient = JdbcClient.create(dataSource);

    // Create table
    jdbClient.sql("""
            CREATE TABLE users (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255),
                email VARCHAR(255)
            )
        """).update();

    // Insert data
    jdbClient.sql("INSERT INTO users (name, email) VALUES (:name, :email)")
            .param("name", "Alice")
            .param("email", "alice@example.com")
            .update();
            
    jdbClient.sql("INSERT INTO users (name, email) VALUES (:name, :email)")
            .param("name", "Paul")
            .param("email", "paul@example.com")
            .update();

    // Fetch data
    List<User> users = jdbClient.sql("SELECT name, email FROM users")
            .query(User.class)
            .list();

    users.forEach(System.out::println);
}

The example creates a table, inserts records, and queries them back.

DataSource dataSource = new DriverManagerDataSource(
    "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1", "sa", "");

We create an H2 in-memory datasource. DB_CLOSE_DELAY=-1 keeps the database in memory after connection closes.

JdbcClient jdbClient = JdbcClient.create(dataSource);

Create JdbcClient instance with the datasource. JdbcClient is thread-safe.

jdbClient.sql("CREATE TABLE...").update();

Execute DDL statement to create table. The update() method executes the SQL.

jdbClient.sql("INSERT...").param("name", "Alice")...update();

Insert data using named parameters. Parameters are bound safely to prevent SQL injection.

List<User> users = jdbClient.sql("SELECT...").query(User.class).list();

Query records and map them to User records. JdbcClient automatically maps column names to record components.

Querying Single Records

This example demonstrates querying single records with optional results.

Main.java
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;
import java.util.Optional;

record User(String name, String email) {}

void main() {
    DataSource dataSource = new DriverManagerDataSource(
        "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1", "sa", "");
    JdbcClient jdbcClient = JdbcClient.create(dataSource);

    // Create table
    jdbcClient.sql("""
            CREATE TABLE users (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255),
                email VARCHAR(255)
            )
        """).update();

    // Insert data
    jdbcClient.sql("INSERT INTO users (name, email) VALUES (:name, :email)")
            .param("name", "Alice")
            .param("email", "alice@example.com")
            .update();

    // Fetch single record by ID
    Optional<User> user = jdbcClient.sql("SELECT name, email FROM users WHERE id = :id")
            .param("id", 1L)
            .query(User.class)
            .optional();

    user.ifPresent(System.out::println);
}

The example queries a single user record wrapped in Optional.

Optional<User> user = jdbcClient.sql("SELECT...").param("id", 1L)
        .query(User.class).optional();

Use optional() when querying for a single record that might not exist. It returns Optional.empty() if no results are found.

user.ifPresent(System.out::println);

We safely handle the Optional result. The record is printed only if present.

Using ParamSource with Records

This example shows how to use records as parameter sources.

Main.java
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;
import java.util.List;

record User(Long id, String name, String email) {}

void main() {
    DataSource dataSource = new DriverManagerDataSource(
        "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1", "sa", "");
    JdbcClient jdbcClient = JdbcClient.create(dataSource);

    // Create table
    jdbcClient.sql("""
            CREATE TABLE users (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255),
                email VARCHAR(255)
            )
        """).update();

    // Insert using paramSource with a record
    User newUser = new User(null, "Mark", "mark@example.com");
    int updated = jdbcClient.sql("INSERT INTO users (name, email) VALUES (:name, :email)")
            .paramSource(newUser)
            .update();

    System.out.println("Rows updated: " + updated);

    // Fetch data
    List<User> users = jdbcClient.sql("SELECT * FROM users")
            .query(User.class)
            .list();

    users.forEach(System.out::println);
}

The example demonstrates using a record as a parameter source.

User newUser = new User(null, "Mark", "mark@example.com");

Create a new User record. The id is null as it will be auto-generated.

jdbcClient.sql("INSERT...").paramSource(newUser).update();

Use paramSource to bind record properties to SQL parameters. Property names must match parameter names.

List<User> users = jdbcClient.sql("SELECT * FROM users")
        .query(User.class)
        .list();

Query all users and map to User records. The id column is automatically mapped to the record component.

Counting Records

This example shows how to execute scalar queries like count.

Main.java
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

record User(String name, String email) {}

void main() {
    DataSource dataSource = new DriverManagerDataSource(
        "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1", "sa", "");
    JdbcClient jdbcClient = JdbcClient.create(dataSource);

    // Create table
    jdbcClient.sql("""
            CREATE TABLE users (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255),
                email VARCHAR(255)
            )
        """).update();

    // Insert data
    jdbcClient.sql("INSERT INTO users (name, email) VALUES (:name, :email)")
            .param("name", "Alice")
            .param("email", "alice@example.com")
            .update();

    jdbcClient.sql("INSERT INTO users (name, email) VALUES (:name, :email)")
            .param("name", "Paul")
            .param("email", "paul@example.com")
            .update();

    // Count records
    Integer count = jdbcClient.sql("SELECT COUNT(*) FROM users")
            .query(Integer.class)
            .single();

    System.out.println("Total users: " + count);
}

The example counts records in the users table.

Integer count = jdbcClient.sql("SELECT COUNT(*) FROM users")
        .query(Integer.class)
        .single();

For scalar queries, specify the return type and use single. It expects exactly one row with one column.

System.out.println("Total users: " + count);

Print the count result. The query returns the number of rows in the table.

Advanced Querying

This example demonstrates more advanced querying techniques.

Main.java
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;
import java.util.List;
import java.util.Map;

record User(Long id, String name, String email) {}

void main() {
    DataSource dataSource = new DriverManagerDataSource(
        "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1", "sa", "");
    JdbcClient jdbcClient = JdbcClient.create(dataSource);

    // Create table
    jdbcClient.sql("""
            CREATE TABLE users (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255),
                email VARCHAR(255)
            )
        """).update();

    // Insert sample data
    List.of(
        new User(null, "Alice", "alice@example.com"),
        new User(null, "Bob", "bob@example.com"),
        new User(null, "Charlie", "charlie@example.com")
    ).forEach(user -> {
        jdbcClient.sql("INSERT INTO users (name, email) VALUES (:name, :email)")
                .paramSource(user)
                .update();
    });

    // Query with filtering and sorting
    List<User> filteredUsers = jdbcClient.sql("""
            SELECT id, name, email FROM users 
            WHERE name LIKE :pattern 
            ORDER BY name DESC
        """)
        .param("pattern", "A%")
        .query(User.class)
        .list();

    System.out.println("Filtered users:");
    filteredUsers.forEach(System.out::println);

    // Query to Map
    List<Map<String, Object>> usersAsMaps = jdbcClient.sql("SELECT * FROM users")
            .query()
            .listOfRows();

    System.out.println("\nUsers as maps:");
    usersAsMaps.forEach(System.out::println);
}

The example shows filtered queries and mapping to different result types.

List<User> filteredUsers = jdbcClient.sql("SELECT...WHERE name LIKE :pattern")
        .param("pattern", "A%")
        .query(User.class)
        .list();

Filter records using SQL WHERE clause with named parameter. The query returns only users with names starting with 'A'.

List<Map<String, Object>> usersAsMaps = jdbcClient.sql("SELECT * FROM users")
        .query()
        .listOfRows();

Query can return results as Maps when no specific mapping is specified. Each row becomes a Map of column names to values.

usersAsMaps.forEach(System.out::println);

Print the map representation of each row. Useful for debugging or when the structure isn't known in advance.

Source

JdbcClient reference

In this tutorial we've explored Spring's JdbcClient for database access. It provides a modern, fluent API for common JDBC operations.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Java tutorials.