Skip to main content

JdbcClient in Spring Framework 7: Modern JDBC Without JPA (vs JdbcTemplate)

A tested guide to Spring’s JdbcClient on Spring Framework 7.0.10 and Boot 4.1.2: parameters, record mapping, single/optional/list, generated keys, the missing batch method, transactions, and the three quiet failures (withMaxRows, a global max-rows property, leaked streams).

You have a table and a Java record, and you want a list of records. Without JPA that has long meant JdbcTemplate: ? placeholders you count by position, a RowMapper you hand-write for every table, and a menu of queryFor… methods to choose between. None of it is hard. All of it is the same ceremony every time, and the ceremony is where the typos live. JdbcClient is Spring’s answer, and it is smaller than its reputation. It is a fluent front for the machinery you already have: you write the SQL, name the values, say what you want back, and the row-to-object mapping happens by column name. It was introduced in Spring Framework 6.1, so “JdbcClient in Spring Framework 7” deserves a precise sentence: the API you use today is the 6.1 API plus two small additions, and one of those additions is a trap. Both are measured below. This article walks from “why would I want this” to “why did my query silently return two rows”, in that order, on one small product table. Everything in it was run: the companion project is asmhatre/spring-data-demo/jdbcclient, where a test suite writes the twelve transcripts under output/ and a script writes two more by diffing real jars. Every code block and every console block below links to the file it came from. The deeper material is in the collapsible sections, so the page reads straight through if you only want the working path.
Versions. Spring Boot 4.1.2 (the newest GA when this was written), Spring Framework 7.0.10 (the newest 7.0.x; 7.1.0-RC2 exists but is a release candidate), JDK 25 (Temurin 25.0.5+8), H2 2.4.240 (the version Boot 4.1.2 manages; Maven Central’s newest H2 is 2.5.252), HikariCP 7.0.2, Maven 3.9.11. spring-jdbc-7.0.0.jar was published to Maven Central on 13 November 2025. The API history in this article was read from the real spring-jdbc jars with javap, not from release notes.

JdbcTemplate makes you write the same two things for every query

Take the simplest question a repository answers: “give me the product with this SKU, if there is one”. With JdbcTemplate you need a RowMapper that copies seven columns into a record by hand, and because queryForObject throws when there is no row, you need a try/catch to turn that exception into an Optional.

Source: src/main/java/com/ankurm/jdbcclient/ProductRepositoryJdbcTemplate.java

private static final RowMapper<Product> MAPPER = (rs, rowNum) -> new Product(
        rs.getLong("id"),
        rs.getString("sku"),
        rs.getString("name"),
        rs.getBigDecimal("price"),
        rs.getBoolean("in_stock"),
        rs.getObject("created_on", java.time.LocalDate.class),
        rs.getInt("stock_count"));

public Optional<Product> findBySku(String sku) {
    try {
        return Optional.of(jdbc.queryForObject("SELECT * FROM product WHERE sku = ?", MAPPER, sku));
    } catch (EmptyResultDataAccessException e) {
        return Optional.empty();
    }
}
Here is the same method with JdbcClient. There is no mapper and no try/catch: you pass the target class, and optional() already means “zero or one row”.

Source: src/main/java/com/ankurm/jdbcclient/ProductRepository.java

public Optional<Product> findBySku(String sku) {
    return jdbc.sql("SELECT * FROM product WHERE sku = :sku")
            .param("sku", sku)
            .query(Product.class)
            .optional();
}
The two repositories were run against the same data and compared. They return identical records, which matters because it means the shorter version is not doing something subtly different:

Output, from output/01-same-results.txt:

findBySku("MS-002")           JdbcClient == JdbcTemplate: true
findBySku("NOPE")             both Optional.empty: true
Before going further it helps to see where JdbcClient sits, because it answers the question every team asks: “is this a new database layer?” It is not.
your repositoryjdbc.sql(…).param(…).query(…).list() DefaultJdbcClient namedParamOpsconversionServicerowMapperCachea thin facade: no new engine JdbcTemplateNamedParameter-JdbcTemplate(unchanged) JDBCdriver Everything JdbcTemplate already does — connection handling, exception translation, transactions — still happens below the facade. What the facade adds is the chain on the left and a per-class cache of the row mappers it builds for you.
The diagram shows the whole idea. Your code talks to DefaultJdbcClient, which is a facade holding a NamedParameterJdbcOperations, a ConversionService and a map of row mappers. Below the facade is the JdbcTemplate family you already know, unchanged. So connection handling, exception translation and transaction participation are the ones you have today. What you gain is the chain on the left, and that is all it claims to be.
Intermediate: how JdbcClient is built, and how Boot gives you one

The fields and constructors of the implementation class, read from the 7.0.10 jar:

Output, from output/14-default-jdbcclient.txt:

DefaultJdbcClient (spring-jdbc 7.0.10), fields and constructors:
private final NamedParameterJdbcOperations namedParamOps;
private final ConversionService conversionService;
private final Map<Class<?>, RowMapper<?>> rowMapperCache;
public DefaultJdbcClient(javax.sql.DataSource);
public DefaultJdbcClient(JdbcOperations);
public DefaultJdbcClient(NamedParameterJdbcOperations, ConversionService);
public JdbcClient$StatementSpec sql(String);

Three constructors exist because there are three ways to start: from a DataSource, from an existing JdbcOperations (your JdbcTemplate, with whatever customisation it carries), or from a NamedParameterJdbcOperations plus a ConversionService. The static JdbcClient.create(…) methods wrap them. The rowMapperCache field is why asking for the same class twice does not rebuild the mapper.

You rarely call create yourself, because Spring Boot 4.1.2 ships a JdbcClientAutoConfiguration in its spring-boot-jdbc module. In the test context it produced exactly one JdbcClient bean, and the same context also held a JdbcTemplate and a NamedParameterJdbcTemplate, so you can mix styles in one application:

Output, from output/02-api-surface.txt:

any method with 'batch' in its name: false
beans of type JdbcClient in the Boot context: 1 ([jdbcClient])
implementation class: org.springframework.jdbc.core.simple.DefaultJdbcClient
beans Boot also provides: JdbcTemplate=true NamedParameterJdbcTemplate=true

Going deeper

Add the starter, inject JdbcClient, write the SQL

The setup is one starter and a database driver. This module uses in-memory H2 so that nothing needs installing; swap the driver and the URL for PostgreSQL or MySQL and nothing else changes.

Source: pom.xml

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <scope>runtime</scope>
</dependency>
The configuration is a URL, plus an explicit switch so Spring Boot runs schema.sql on start:

Source: src/main/resources/application.properties

spring.application.name=jdbcclient-demo
spring.datasource.url=jdbc:h2:mem:shop;DB_CLOSE_DELAY=-1
spring.sql.init.mode=always
One table, written the way real tables are written: snake_case column names, a unique column, a default, a check constraint. Those details matter later because each one produces a failure you will want to recognise.

Source: src/main/resources/schema.sql

CREATE TABLE IF NOT EXISTS product (
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    sku         VARCHAR(20)   NOT NULL UNIQUE,
    name        VARCHAR(100)  NOT NULL,
    price       DECIMAL(10,2) NOT NULL,
    in_stock    BOOLEAN       NOT NULL DEFAULT TRUE,
    created_on  DATE          NOT NULL DEFAULT CURRENT_DATE,
    stock_count INT           NOT NULL DEFAULT 0 CHECK (stock_count >= 0)
);
And one record to hold a row. Notice that the Java names are camelCase while the columns are snake_case.

Source: src/main/java/com/ankurm/jdbcclient/Product.java

public record Product(Long id, String sku, String name, BigDecimal price,
                      boolean inStock, LocalDate createdOn, int stockCount) {
Now inject JdbcClient through the constructor and write queries. Every query in the repository follows the same four-stage shape, which is worth seeing once as a picture.
1. sql(“…”)the SQL text,:named or ? 2. valuesparam / params /paramSource 3. limits (7.0+)withMaxRowswithQueryTimeout 4. run itquery(X.class).list()or update() Nothing touches the database until stage 4. Stages 2 and 3 only fill in a description of the statement. That is why a forgotten terminal call compiles, runs, and does nothing.
Read the chain left to right: the SQL text, then the values, then (in Framework 7) optional limits, then a terminal call that actually runs it. Until that last call nothing has touched the database, which has one practical consequence: a statement you build and forget to finish compiles, runs, and does nothing, so always check that a query(…) ends in list(), single(), optional() or stream(). Two more reads from the repository, one with a numeric bound and one with a list of values:

Source: src/main/java/com/ankurm/jdbcclient/ProductRepository.java

public List<Product> findInStockCheaperThan(BigDecimal max) {
    return jdbc.sql("SELECT * FROM product WHERE in_stock = TRUE AND price < :max ORDER BY price")
            .param("max", max)
            .query(Product.class)
            .list();
}

public List<Product> findBySkus(List<String> skus) {
    return jdbc.sql("SELECT * FROM product WHERE sku IN (:skus) ORDER BY id")
            .param("skus", skus)
            .query(Product.class)
            .list();
}

Output, from output/01-same-results.txt:

findInStockCheaperThan(4000)  rows: 3, identical: true
findBySkus([KB-001, WC-005])  rows: 2, identical: true
Named parameters are the default to reach for. A positional ? is matched by its place in the SQL, so reordering a WHERE clause silently changes which value goes where. A :name is matched by name, so the SQL can be rearranged without touching the Java. The next section measures what each style does when you get it wrong.

Going deeper

Pick one parameter style per statement, and know what each mistake looks like

JdbcClient accepts values four ways, and the first rule is that one statement uses one style. The nine variants below were all run against the same table, and each returns the row you would expect.

Source: src/test/java/com/ankurm/jdbcclient/ParametersTest.java

// 1. positional ?
var a = jdbc.sql("SELECT name FROM product WHERE sku = ? AND in_stock = ?")
        .param("KB-001").param(true).query(String.class).list();

// 4. named
var d = jdbc.sql("SELECT name FROM product WHERE sku = :sku").param("sku", "KB-001").query(String.class).list();

// 6. a record, read component by component
record Filter(String sku, boolean inStock) { }
var f = jdbc.sql("SELECT name FROM product WHERE sku = :sku AND in_stock = :inStock")
        .paramSource(new Filter("KB-001", true)).query(String.class).list();

// 8. IN list
var h = jdbc.sql("SELECT sku FROM product WHERE sku IN (:skus) ORDER BY id")
        .param("skus", List.of("KB-001", "WC-005", "NOPE")).query(String.class).list();

// 9. LIKE needs the wildcard in the value, not in the SQL string
var i = jdbc.sql("SELECT name FROM product WHERE name LIKE :q ORDER BY id")
        .param("q", "%o%").query(String.class).list();

Output, from output/03-parameters.txt:

1  .param(v).param(v)                  ? placeholders  -> [Mechanical Keyboard]
2  .param(1, v)                        by index        -> [Mechanical Keyboard]
3  .params(List.of(..))                ? placeholders  -> [Wireless Mouse, Headset]
4  .param("sku", v)                    :named          -> [Mechanical Keyboard]
5  .params(Map.of(..))                 :named          -> [Mechanical Keyboard]
6  .paramSource(record)                :named, record  -> [Mechanical Keyboard]
7  :p used twice, one .param("p", 10)  :named          -> [KB-001, MS-002]
8  IN (:skus) with a 3-element list    expanded        -> [KB-001, WC-005] (NOPE matched nothing)
9  LIKE :q with "%o%"                 wildcard in value -> [Mechanical Keyboard, Wireless Mouse, Monitor 27 inch]
A few of those deserve a sentence. paramSource(record) reads each :name from the record component of the same name, which is how the insert later in this article avoids listing seven parameters. A name used twice (case 7) needs only one .param(…). An IN (:skus) takes a List and expands it (case 8). And for LIKE the wildcard belongs in the value, as in case 9, never concatenated into the SQL. Now the mistakes. These are the three you will actually make, with the exact exception each produced:

Output, from output/03-parameters.txt:

10 :named and ? in one statement    -> java.lang.IllegalStateException: Configure either named or indexed parameters, not both
11 typo in the parameter name (:sku, supplied skuu) -> org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'sku': No value registered for key 'sku'
12 two ? but one value                  -> org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [SELECT name FROM product WHERE sku = ? AND in_stock = ?]; Parameter "#2" is not set; SQL statement: SELECT name FROM product WHERE sku = ? AND in_stock = ? [90012-240]  [root cause: JdbcSQLDataException: Parameter "#2" is not set; SQL statement: SELECT name FROM product WHERE sku = ? AND in_stock = ? [90012-240]]
The third one is misleading, and it is the driver’s doing. A named parameter that is missing (or misspelled, case 11) is caught by Spring itself, with a clear message and a DataAccessException subclass that says so. A positional parameter that is missing reaches the driver, which complains, and Spring translates the driver’s complaint into DataIntegrityViolationException — an exception whose name sends you to look at your constraints rather than your parameter count. If you see that exception from a SELECT, count your question marks. This is another reason to prefer names.
Intermediate: nulls, and a note on database dependence

Passing a Java null works without a type, and so does passing one with an explicit java.sql.Types constant. Both ran, and both returned no rows, because sku = NULL is never true in SQL:

Output, from output/03-parameters.txt:

13 .param("sku", null)                  -> ran, rows: 0 (= NULL is never true)
   .param("sku", null, Types.VARCHAR)   -> ran, rows: 0 (= NULL is never true)

If you meant “rows where the column is null”, write IS NULL in the SQL. The explicit-type overload exists for drivers that cannot infer the type of a bare null; this run used H2, which can, so it shows that the overload is accepted rather than that it is needed.

The exact wording of the third error above (“Parameter #2 is not set”) is H2’s. This run only shows H2; another driver may word it differently or translate it to a different exception, so in production code match on the exception type and the SQL state, not on the message.

Going deeper

Rows become records by column name, and a missing column is an error, not a null

When you write query(Product.class), Spring builds a row mapper for you and keeps it in a map keyed by class. The reference documentation says the target can be a record, a class with a custom constructor, a class with bean properties or a class with plain fields. It does not spell out the matching rules, so they were tested rather than assumed. Here is the main case, followed by three other shapes of the same data:

Source: src/test/java/com/ankurm/jdbcclient/MappingTest.java

List<Product> all = jdbc.sql("SELECT * FROM product ORDER BY id").query(Product.class).list();

var slim = jdbc.sql("SELECT sku, name FROM product WHERE id = 1").query(Slim.class).single();

var lambda = jdbc.sql("SELECT sku, price FROM product WHERE id = 2")
        .query((rs, n) -> rs.getString("sku") + " costs " + rs.getBigDecimal("price"))
        .single();

Output, from output/04-mapping.txt:

1  SELECT * into the Product record (in_stock -> inStock, created_on -> createdOn, stock_count -> stockCount):
     Product[id=1, sku=KB-001, name=Mechanical Keyboard, price=4999.00, inStock=true, createdOn=2026-01-10, stockCount=12]
     Product[id=2, sku=MS-002, name=Wireless Mouse, price=1299.50, inStock=true, createdOn=2026-02-14, stockCount=40]
     Product[id=3, sku=MN-003, name=Monitor 27 inch, price=18999.00, inStock=false, createdOn=2026-03-01, stockCount=0]
     Product[id=4, sku=HS-004, name=Headset, price=2499.00, inStock=true, createdOn=2026-03-22, stockCount=7]
     Product[id=5, sku=WC-005, name=Webcam, price=3199.00, inStock=true, createdOn=2026-04-05, stockCount=3]
The record has seven components and the table has seven columns, so that is the easy case. Note that in_stock, created_on and stock_count landed in inStock, createdOn and stockCount with no annotation and no alias. A record with fewer components than columns works too (the extra columns are ignored), and so does a mutable bean with setters for some of the columns. A one-column query maps straight to a simple type. Failures are where mapping teaches you something. Each of these was run, and each throws:

Output, from output/04-mapping.txt:


6  record asks for colourName, SELECT has no such column: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT sku FROM product WHERE id = 1]  [root cause: JdbcSQLSyntaxErrorException: Column "colourName" not found [42122-240]]
7  record asks for primitive int missingCol, no such column: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT sku FROM product WHERE id = 1]  [root cause: JdbcSQLSyntaxErrorException: Column "missingCol" not found [42122-240]]
8  VARCHAR column into a LocalDate component: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [SELECT sku, name FROM product WHERE id = 1]; Cannot parse "DATE" constant "Mechanical Keyboard" [22007-240]  [root cause: NumberFormatException: Error at index 0 in: "Mechanical Keyb"]
9  SQL NULL into the primitive int stockCount: org.springframework.core.convert.ConversionFailedException: Failed to convert from type [null] to type [int] for value [null]  [root cause: IllegalArgumentException: A null value cannot be assigned to a primitive type]
A column that is not in the SELECT does not become null. Asking for a record component the query does not return throws BadSqlGrammarException, whose root cause is the driver saying “column not found” — for a reference type and for a primitive alike (items 6 and 7). So “optional columns” need an explicit alias or a smaller record. Similarly, a SQL NULL cannot go into a primitive int (item 9); if the column is nullable, the record component must be Integer, which the same transcript shows mapping the same NULL cleanly.
Reference: what the untyped query returns, and what the docs leave out

If you do not want a class, query() with no argument returns untyped rows. The keys come back exactly as the driver reports them, which on H2 means upper case, though lookup is case-insensitive:

Output, from output/04-mapping.txt:

2  a record with fewer components than columns: Slim[sku=KB-001, name=Mechanical Keyboard]
3  a JavaBean with setters for three of the seven columns: Bean[sku=KB-001, price=4999.00, inStock=true]
4  a single column to a simple type: [12, 40, 0, 7, 3] and Wireless Mouse

Two limits of what is claimed here. First, the matching rules above (snake_case column to camelCase component) were observed on H2 with records, one bean and one record-with-fewer-components; the reference documentation does not state them, so treat them as behaviour you have seen rather than a contract. Second, the reference calls this automatic mapping an implicit SimplePropertyRowMapper and describes it as the replacement for BeanPropertyRowMapper and DataClassRowMapper, so if you want different matching rules you can construct and pass your own mapper to query(RowMapper).

Going deeper

single(), optional(), list(): what zero, one and many rows do

The terminal call decides what “no row” and “too many rows” mean. This is the single most useful table in the article, and it came from running all four calls against queries that return 0, 1 and 4 rows:

Output, from output/05-result-shapes.txt:

                      0 rows                         1 row        4 rows
list()                []                             [Mechanical Keyboard] 4 items
single()              EmptyResult..                  Mechanical Keyboard IncorrectResultSize..
optional()            Optional.empty                 Optional[Mechanical Keyboard] IncorrectResultSize..
set()                 []                             [Mechanical Keyboard] 4 items
Read it by column. With zero rows, list() and set() return empty collections and optional() returns an empty Optional, while single() throws. With four rows, single() throws and so does optional() — an Optional means “zero or one”, and it will not quietly pick the first of four. The two exceptions have their own messages:

Output, from output/05-result-shapes.txt:

single() on 0 rows: org.springframework.dao.EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0
single() on 4 rows: org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 4
EmptyResultDataAccessException extends IncorrectResultSizeDataAccessException: true
The last line matters for how you catch them: EmptyResultDataAccessException is a subclass of IncorrectResultSizeDataAccessException, so one catch of the parent handles “none” and “too many” together.
Aggregates always return one row, so they never behave like “no result”. A SELECT MAX(price) over zero matching rows returns one row containing SQL NULL. The transcript shows the consequence: optional() gives an empty Optional, but single() throws TypeMismatchDataAccessException with the message “Result value is null but no null value expected”. If a query can legitimately produce a NULL aggregate, use optional().

Output, from output/05-result-shapes.txt:

untyped singleValue() of COUNT(*): 5 (Long)
untyped optionalValue() with no row: Optional.empty
optional() over SELECT MAX(price) with no matching rows: Optional.empty
single() over the same query: org.springframework.dao.TypeMismatchDataAccessException: Result value is null but no null value expected

Going deeper

Writes: update(), generated keys, and the batch method that is not there

update() returns the number of rows changed, which is the cheapest correctness check you have. For an insert you usually also want the generated id, and the way to get it is a KeyHolder. Combining paramSource(record) with a named key column keeps the whole thing short:

Source: src/main/java/com/ankurm/jdbcclient/ProductRepository.java

public long insert(Product p) {
    KeyHolder keys = new GeneratedKeyHolder();
    jdbc.sql("INSERT INTO product (sku, name, price, in_stock, stock_count) "
                    + "VALUES (:sku, :name, :price, :inStock, :stockCount)")
            .paramSource(p)
            .update(keys, "id");
    return keys.getKey().longValue();
}
And the results of the three insert styles that were tried, including the one without a key column name:

Output, from output/06-generated-keys.txt:

insert via paramSource(record) and update(keys, "id"): generated id = 6
update(keys) with no column names: key columns = [ID, CREATED_ON]
update(keys, "id"):                  key columns = [ID], id = 8
a row inserted without created_on / in_stock / stock_count got the column defaults: in_stock=true stock_count=0 created_on=today: true
Name the key column. With update(keys) and no column name, H2 returned two key columns: ID and also CREATED_ON, because that column has a database default. Code that calls keys.getKey() on an unnamed holder is relying on the driver to return exactly one generated column, and a default on another column can change that. Passing "id" as in the repository above returned exactly ID. This behaviour is H2’s; other drivers return generated columns differently, which is one more reason to be explicit.
Now the part that surprises people who have read about JdbcClient but not used it: it has no batch method. This was checked by listing every method on JdbcClient and its three spec interfaces by reflection, and by diffing the jars across five releases (next section). The reference documentation says the same thing in different words: it points you to SimpleJdbcInsert, SimpleJdbcCall or plain JdbcTemplate for batch work.

Output, from output/02-api-surface.txt:

any method with 'batch' in its name: false
The fix is not painful, and Boot already gave you the bean: inject NamedParameterJdbcTemplate next to JdbcClient and use batchUpdate with SqlParameterSourceUtils.createBatch, which turns a list of records into one parameter source per row. The same named SQL string works in both. Here is the loop of single inserts and the batch, as the test ran them:

Source: src/test/java/com/ankurm/jdbcclient/WritesTest.java

String sql = "INSERT INTO product (sku, name, price, in_stock, stock_count) VALUES (:sku, :name, :price, :inStock, :stockCount)";

long t0 = System.nanoTime();
for (Product p : items) jdbc.sql(sql).paramSource(p).update();
long loopMs = (System.nanoTime() - t0) / 1_000_000;
assertThat(repo.count()).isEqualTo(5 + n);

jdbc.sql("DELETE FROM product WHERE sku LIKE 'B-%'").update();

t0 = System.nanoTime();
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(items);
int[] counts = named.batchUpdate(sql, batch);

Output, from output/07-batch.txt:

2000 inserts into in-memory H2 (no network round trip, so the gap understates a real database):
  a loop of jdbc.sql(..).update()          : finished (timing varies per run, not asserted)
  one NamedParameterJdbcTemplate.batchUpdate: finished, 2000 result codes returned
  loop took more than batch (indicative, one run): true
rows in the table after the batch: 2005 (5 seed rows + 2000)
Read that comparison for what it is. The run was on in-memory H2, where there is no network between your code and the database, so the loop pays almost no round-trip cost. The batch still finished first in that one run, but that is a result about H2, not a multiplier you can quote for a database across a network, where one round trip per row is the dominant cost. No speed-up figure is claimed here for that reason.
Intermediate: the four ways an insert can fail, and what Spring turns them into

Four statements that each break something, and the Spring exception each became, with the SQL state the database reported:

Output, from output/08-exceptions.txt:

duplicate unique value          -> DuplicateKeyException  (database said SQLState 23505)
NULL into a NOT NULL column     -> DataIntegrityViolationException  (database said SQLState 23502)
CHECK constraint violated       -> DataIntegrityViolationException  (database said SQLState 23513)
typo in the SQL                 -> BadSqlGrammarException  (database said SQLState 42001)
all four are unchecked DataAccessException subclasses: true

Everything here is an unchecked DataAccessException, so nothing forces a try/catch. The useful split is that DuplicateKeyException is the one worth catching deliberately (“that SKU already exists” is a normal business outcome), while DataIntegrityViolationException covers the NOT NULL and CHECK cases and usually means a bug. The SQL state values (23505, 23502, 23513, 42001) are standard, so they are the portable thing to log.

Going deeper

Transactions: JdbcClient joins them, but nothing starts one for you

JdbcClient takes part in Spring-managed transactions exactly as you would hope. The trap is the other direction: if no transaction is active, each statement is its own transaction and commits immediately. To see the difference, here is a service that moves stock from one product to another with two UPDATE statements. The second one will violate the CHECK (stock_count >= 0) constraint when the source runs out.

Source: src/main/java/com/ankurm/jdbcclient/StockService.java

@Transactional
public void moveStockAtomic(long from, long to, int qty) {
    move(from, to, qty);
}

/** Same body, no @Transactional: every statement auto-commits on its own. */
public void moveStockNoTx(long from, long to, int qty) {
    move(from, to, qty);
}

Source: src/main/java/com/ankurm/jdbcclient/StockService.java

private void move(long from, long to, int qty) {
    jdbc.sql("UPDATE product SET stock_count = stock_count + :q WHERE id = :id")
            .param("q", qty).param("id", to).update();                 // first UPDATE
    jdbc.sql("UPDATE product SET stock_count = stock_count - :q WHERE id = :id")
            .param("q", qty).param("id", from).update();               // CHECK (stock_count >= 0) may fail here
}
The webcam has 3 in stock and the keyboard 12, so moving 5 from the webcam to the keyboard must fail at the second statement. The same body was run twice, once with @Transactional and once without.
With @Transactional UPDATE keyboard +512 → 17 (not committed) UPDATE webcam −53 − 5 = −2, CHECK fails ROLLBACK, one connectionkeyboard = 12, webcam = 3 Without it UPDATE keyboard +512 → 17, auto-commit UPDATE webcam −5same CHECK failure nothing to roll backkeyboard = 17, webcam = 3 The two runs execute the same method body. Only the annotation differs, and it decides whether five units of stock vanish.
Left to right in each row: the first UPDATE succeeds, the second hits the constraint. In the top row both are inside one transaction, so the failure undoes the first as well. In the bottom row the first UPDATE had already committed by the time the second failed, so stock was created from nothing: the keyboard gained 5 and the webcam lost none. The transcript has the actual numbers:

Output, from output/09-transactions.txt:

start: keyboard(1)=12 webcam(5)=3
@Transactional move of 5 from webcam to keyboard -> DataIntegrityViolationException
  after:  keyboard(1)=12 webcam(5)=3   (both UPDATEs rolled back)
same body, no @Transactional                      -> DataIntegrityViolationException
  after:  keyboard(1)=17 webcam(5)=3   (the first UPDATE had already committed)
The missing annotation produced no warning. Both runs threw the same exception, DataIntegrityViolationException. A test that only checks “the method threw” passes both. The only way to see the difference is to read the data back afterwards, which is what the transcript does. If a method issues more than one write, either annotate it or write the test that reads the rows back.
Intermediate: programmatic transactions

You can also open a transaction yourself with TransactionTemplate and use JdbcClient inside it, which is useful when only part of a method needs to be atomic. In the test, a runtime exception inside the callback undid the earlier update, and a later read inside the transaction saw the uncommitted change:

Output, from output/09-transactions.txt:

TransactionTemplate, UPDATE then RuntimeException -> IllegalStateException; mouse(2)=40 (was 40)
inside a transaction a later SELECT sees the earlier UPDATE: 55; after commit: 55

The first line is a rollback (the mouse stayed at 40 although the callback had set it to 100). The second is the normal case: inside the transaction the read sees 55 because it is on the same connection, and after commit everyone sees 55.

Going deeper

Streams, row limits and timeouts are where JdbcClient bites

Everything so far fails loudly. This section is about three features that fail quietly, and one of them was added in Framework 7. Here is exactly what 7.0 changed, found by diffing the public signatures of JdbcClient and its spec interfaces in real spring-jdbc jars:

Output, from output/13-api-diff.txt:

public signatures of JdbcClient + StatementSpec + ResultQuerySpec + MappedQuerySpec, diffed between releases
6.1.0: 40 signatures
6.2.0: 41 signatures
  6.1.0 -> 6.2.0:
    >   public default Optional<Object> optionalValue();
6.2.19: 41 signatures
  6.2.0 -> 6.2.19: no change
7.0.0: 45 signatures
  6.2.19 -> 7.0.0:
    >   public abstract JdbcClient$StatementSpec withFetchSize(int);
    >   public abstract JdbcClient$StatementSpec withMaxRows(int);
    >   public abstract JdbcClient$StatementSpec withQueryTimeout(int);
    >   public static JdbcClient create(org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations, org.springframework.core.convert.ConversionService);
7.0.10: 45 signatures
  7.0.0 -> 7.0.10: no change
So from 6.1 to 6.2 there was one addition, optionalValue(), and 6.2 stayed the same through 6.2.19. Framework 7.0.0 added withFetchSize, withMaxRows and withQueryTimeout on the statement, plus a create overload that takes a ConversionService; 7.0.10 is identical to 7.0.0. Those three “with” methods are the new part, and the first thing to learn about them is how withMaxRows behaves:

Source: src/test/java/com/ankurm/jdbcclient/StreamAndLimitsTest.java

List<String> capped = jdbc.sql("SELECT sku FROM product ORDER BY id").withMaxRows(2).query(String.class).list();

Output, from output/11-limits.txt:

5 rows in the table
list()                    -> 5 rows
withMaxRows(2).list()     -> 2 rows [KB-001, MS-002]   (no warning, no exception)
a query that would run for minutes with withQueryTimeout(1) -> QueryTimeoutException (about 1 s)
withMaxRows truncates without telling you. Five rows exist, the limit is two, and the call returns two rows with no warning and no exception. It is a cap, not an assertion. Use it for “show me a sample” and never for correctness-sensitive reads. withQueryTimeout, by contrast, is loud: the deliberately huge query was cancelled after about a second with QueryTimeoutException, which is the behaviour you want from a safety limit.
The same cap can arrive without you writing it. Spring Boot’s spring.jdbc.template.* properties are documented for JdbcTemplate, and the auto-configured JdbcClient was tested to see whether it inherits them:

Output, from output/12-template-properties.txt:

spring.jdbc.template.max-rows=2 in application properties, 5 rows in the table
auto-configured JdbcClient: SELECT sku FROM product -> 2 rows
One property silently capped every JdbcClient query. With spring.jdbc.template.max-rows=2 in the configuration, an ordinary SELECT sku FROM product through the auto-configured JdbcClient returned 2 of 5 rows. So the property does reach JdbcClient, and a global row cap set “to be safe” will make reports and exports quietly incomplete. Search your configuration for it before you debug a missing-rows ticket.
The third quiet failure is the stream. stream() returns rows lazily, which is good for large results, and it keeps a database connection checked out until the stream is closed. The repository method is one line:

Source: src/main/java/com/ankurm/jdbcclient/ProductRepository.java

public Stream<Product> streamAll() {
    return jdbc.sql("SELECT * FROM product ORDER BY id").query(Product.class).stream();
}
To make a leak visible in a test, the pool was shrunk to two connections with a 500 ms wait (spring.datasource.hikari.maximum-pool-size=2, connection-timeout=500). Two streams were opened and never closed, then one ordinary query was issued:
pool: maximum-pool-size = 2 connection 1held by stream s1 connection 2held by stream s2 repo.count()waits for a free one after 500 msCannotGetJdbc-ConnectionException Nothing is wrong with the third query. The first two calls borrowed both connections and never gave them back. In production the pool is bigger and the wait is longer, so the same bug looks like a slow, then dead, application.
The picture is the whole bug. Each unclosed stream is holding a connection, so with both connections held, the third caller has nothing to borrow and gives up. In production you would see it as requests that start taking longer, then as timeouts, with no stack trace pointing at the code that leaked. The transcript:

Output, from output/10-stream-leak.txt:

Hikari pool: maximum-pool-size=2, connection-timeout=500 ms
rows in the table, via count(): 5
two streamAll() calls left open, then one ordinary count():
  -> CannotGetJdbcConnectionException after about 500 ms
  root cause: HikariPool-N - Connection is not available, request timed out after 500ms (...)
after closing both streams, count() works again: 5
try-with-resources: names of rows 1..2: [Mechanical Keyboard, Wireless Mouse]
and the connection is back: count() = 5
The fix is the last three lines: put the stream in try-with-resources so the connection returns to the pool when you are done.

Source: src/test/java/com/ankurm/jdbcclient/StreamAndLimitsTest.java

try (Stream<Product> ok = repo.streamAll()) {
    t.line("try-with-resources: names of rows 1..2: %s", ok.limit(2).map(Product::name).toList());
}
Reference: what is and is not verified about fetch size

The 7.0 addition withFetchSize is on the interface (see the signature diff above), but this article does not claim a measured effect for it. Fetch size is a hint to the driver about how many rows to pull per round trip; whether it changes anything depends on the driver and on whether it streams, and H2 in memory would show no difference either way. What was verified is that the method exists from 7.0.0, and that withMaxRows and withQueryTimeout behave as shown. If you tune fetch size, measure against your real database.

Going deeper

JdbcClient, JdbcTemplate, Spring Data JDBC or JPA: which one

Nothing above needs an opinion; this section does, so treat it as guidance rather than a measured result. The question is how much of the “table to object and back” work you want a framework to own.
You need…Reach forWhy
a few queries, reports, or SQL that is the point of the codeJdbcClientyou write the SQL, the framework maps rows; nothing else to learn
an existing JdbcTemplate codebasekeep it, add JdbcClient where you touch itboth can be injected side by side, as the bean listing above shows
batch inserts or updatesNamedParameterJdbcTemplate (or SimpleJdbcInsert)JdbcClient has no batch method
CRUD for many entities, relationships, change trackingSpring Data JPA or Spring Data JDBCyou would otherwise rebuild that layer by hand, one repository method at a time
non-blocking accessSpring Data R2DBCJdbcClient is blocking JDBC
Should you use JdbcClient at all? If the honest description of your data layer is “twenty tables, each with create, read, update, delete, and most of them related”, then writing every statement yourself is rebuilding an ORM slowly, and the failures in this article (silent truncation, leaked streams, a forgotten transaction) are the price of the control you are not using. JdbcClient is at its best when the SQL is the interesting part: reporting queries, projections across joins, a handful of hot paths, or a small service where pulling in JPA would be the heavier choice. It is not a reason to remove JPA from an application that already has it.

Related reading on this blog: the Spring Framework 6 to 7 migration guide for what else changed in 7.0, and Multiple DataSources in Spring Boot 4 if you need more than one database — JdbcClient.create(DataSource) exists for exactly that, though this article did not test it.

Further reading

No Comments yet!

This site uses Akismet to reduce spam. Learn how your comment data is processed.