JdbcClient

Run plain SQL with one fluent API: bind parameters by name, map rows straight into records and read generated keys without boilerplate.

SyntaxjdbcClient.sql("SELECT ... WHERE id = :id") .param("id", id) .query(Type.class) // or query(rowMapper) .optional(); // list() | single() | stream() jdbcClient.sql("UPDATE ...").param(...).update(); // rows affected

JdbcClient is the modern way to run SQL you write yourself. It arrived in Spring Framework 6.1 as a fluent front end over JdbcTemplate and NamedParameterJdbcTemplate, and Spring Boot creates a JdbcClient bean whenever there is a single DataSource. Add spring-boot-starter-jdbc (or the JPA or Flyway starters, which pull it in) and inject it.

The shape of every call

Every statement follows the same chain: sql(...), then zero or more param(...) calls, then either query(...) with a terminal method or update().

List<Booking> upcoming = jdbc.sql("""
        SELECT id, guest_name, starts_at FROM booking
        WHERE starts_at > :from ORDER BY starts_at""")
    .param("from", Instant.now())
    .query(Booking.class)
    .list();

query(Booking.class) maps each row onto the record's components, and a column such as guest_name lands in a component called guestName. For a single column, pass a simple type: query(Long.class).single(). When the mapping is not one to one, pass a RowMapper lambda instead.

Picking the terminal method

  • list() returns every row, and an empty list when nothing matches.
  • optional() is the right choice for lookups by id: empty when there is no row.
  • single() throws EmptyResultDataAccessException for zero rows and IncorrectResultSizeDataAccessException for more than one.
  • stream() reads rows lazily, but holds the connection open until you close the stream, so always use it in try-with-resources.

Parameters, never concatenation

Named parameters (:from) and positional ones (? with param(value)) both become JDBC bind variables, which is what protects you from SQL injection. Pick one style per statement. The mistake that ships to production is building SQL with string concatenation or a text block formatted(...) call because a value looked harmless. Column names and sort directions cannot be bound, so check them against a fixed allow list instead.

paramSource(command) binds every record component or bean property by name, which keeps inserts short. For generated ids, pass a KeyHolder to update along with the key column name.

Errors and transactions

Failures arrive as Spring's DataAccessException hierarchy, not SQLException: a unique constraint violation becomes DuplicateKeyException, which you can map to a 409. JdbcClient joins any transaction started by @Transactional, so a service method that runs three statements commits or rolls back all three together.

Example

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

import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

record Booking(Long id, String guestName, Instant startsAt) {}

record NewBooking(String guestName, Instant startsAt) {}

@Repository
public class BookingRepository {

    private final JdbcClient jdbc;

    public BookingRepository(JdbcClient jdbc) {
        this.jdbc = jdbc;
    }

    public Optional<Booking> findById(long id) {
        return jdbc.sql("SELECT id, guest_name, starts_at FROM booking WHERE id = :id")
                .param("id", id)
                .query(Booking.class)
                .optional();
    }

    public List<Booking> findUpcoming(Instant from, int limit) {
        return jdbc.sql("""
                SELECT id, guest_name, starts_at
                FROM booking
                WHERE starts_at > :from
                ORDER BY starts_at
                LIMIT :limit""")
                .param("from", from)
                .param("limit", limit)
                .query(Booking.class)
                .list();
    }

    public long insert(NewBooking booking) {
        KeyHolder keys = new GeneratedKeyHolder();
        jdbc.sql("INSERT INTO booking (guest_name, starts_at) VALUES (:guestName, :startsAt)")
                .paramSource(booking)
                .update(keys, "id");
        return keys.getKeyAs(Long.class);
    }

    public boolean cancel(long id) {
        return jdbc.sql("DELETE FROM booking WHERE id = ?")
                .param(id)
                .update() == 1;
    }
}

When to use it

  • Load a booking by id for a GET endpoint and return 404 when optional() comes back empty.
  • Insert a new order from a request record with paramSource and return the generated id in the Location header.
  • Run a hand-tuned reporting query with window functions that would be awkward to express in JPQL.

More examples

A custom RowMapper for a nested value

Automatic mapping only works when columns line up with record components. Here two columns build one nested Money value, so a RowMapper does the work. Keep the mapper in a static constant: it is stateless and thread-safe, and every query in the class can reuse it.

Example · java
import java.math.BigDecimal;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.stereotype.Repository;

record Money(BigDecimal amount, String currency) {}

record Invoice(long id, String customer, Money total) {}

@Repository
public class InvoiceQueries {

    private static final RowMapper<Invoice> INVOICE = (rs, rowNum) -> new Invoice(
            rs.getLong("id"),
            rs.getString("customer"),
            new Money(rs.getBigDecimal("total_amount"), rs.getString("total_currency")));

    private final JdbcClient jdbc;

    public InvoiceQueries(JdbcClient jdbc) {
        this.jdbc = jdbc;
    }

    public List<Invoice> unpaidFor(String customer) {
        return jdbc.sql("""
                SELECT id, customer, total_amount, total_currency
                FROM invoice
                WHERE customer = :customer AND paid_at IS NULL
                ORDER BY id""")
                .param("customer", customer)
                .query(INVOICE)
                .list();
    }
}

Counting and checking existence

count(*) and EXISTS always return exactly one row, so single() is safe and its exception would point to a real bug. Asking the database for a boolean is far cheaper than loading every matching row into a list and checking whether it is empty.

Example · java
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.stereotype.Service;

@Service
public class RoomAvailability {

    private final JdbcClient jdbc;

    public RoomAvailability(JdbcClient jdbc) {
        this.jdbc = jdbc;
    }

    public long bookingsForRoom(long roomId) {
        return jdbc.sql("SELECT count(*) FROM booking WHERE room_id = :roomId")
                .param("roomId", roomId)
                .query(Long.class)
                .single();
    }

    public boolean emailTaken(String email) {
        return jdbc.sql("SELECT EXISTS (SELECT 1 FROM guest WHERE lower(email) = lower(:email))")
                .param("email", email)
                .query(Boolean.class)
                .single();
    }
}

Streaming a large export

list() would hold every guest in memory at once. stream() with a fetch size reads in chunks, and the PostgreSQL driver only honours the fetch size inside a transaction, hence readOnly = true. The try-with-resources block is what releases the connection; forgetting it leaks one connection per export.

Example · java
import java.io.IOException;
import java.io.Writer;
import java.util.stream.Stream;

import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

record GuestRow(long id, String email) {}

@Service
public class GuestExport {

    private final JdbcClient jdbc;

    public GuestExport(JdbcClient jdbc) {
        this.jdbc = jdbc;
    }

    @Transactional(readOnly = true)
    public void writeCsv(Writer out) throws IOException {
        try (Stream<GuestRow> rows = jdbc.sql("SELECT id, email FROM guest ORDER BY id")
                .withFetchSize(500)
                .query(GuestRow.class)
                .stream()) {
            for (GuestRow row : (Iterable<GuestRow>) rows::iterator) {
                out.write(row.id() + "," + row.email() + "\n");
            }
        }
    }
}

Related

sb jdbctemplatesb choosing between jpa and jdbcsb transactional basicssb database migrations with flywaysb keyset pagination

Discussion

  • Be the first to comment on this lesson.