JdbcTemplate and JdbcClient
When a repository is not the right tool (a report, a bulk import, a statement the mapping does not know), use SQL directly. The starter makes three beans over the primary data source: JdbcTemplate (positional ? markers), NamedParameterJdbcTemplate (:name markers) and JdbcClient (Spring 6.1's fluent API over both). They take part in the running transaction, translate errors into DataAccessExceptions, and log like the repositories.
Why
Repositories cover aggregates. Plain SQL is still needed for aggregates of rows (count, sum, group by), for data that is not an entity, and for statements the application tunes by hand. The templates remove the connection handling, the binding and the error translation, and keep values as parameters, never pasted into the text.
Example
@Service()
class Reports {
Reports(this.jdbc, this.named, this.client);
final JdbcTemplate jdbc;
final NamedParameterJdbcTemplate named;
final JdbcClient client;
Future<int> booksAfter(int year) =>
jdbc.queryForObject<int>('select count(*) from books where year > ?', [year]);
Future<List<String>> titles(int authorId) => named.queryForList<String>(
'select title from books where author_id = :author',
{'author': authorId},
);
Future<Book?> find(int id) => client
.sql('select * from books where id = :id')
.param('id', id)
.query<Book>() // a class the generator describes: columns by name
.optional();
Future<int> rename(int id, String title) => client
.sql('update books set title = ? where id = ?')
.param(title)
.param(id)
.update();
}
JdbcTemplate
| Method | Returns |
|---|---|
query<T>(sql, params, mapper) |
Each row mapped with mapper. |
queryWithExtractor<T>(sql, params, extractor) |
What extractor makes of the whole JdbcResult. |
queryForObject<T>(sql, [params, mapper]) |
The one row, mapped, or its single column as a T. No row is an EmptyResultDataAccessException, more than one an IncorrectResultSizeDataAccessException. |
queryForList<T>(sql, [params, mapper]) |
The single column of each row (or each row mapped). |
queryForMap(sql, [params]) |
The one row as a Map<String, Object?>. |
queryForMaps(sql, [params]) |
Each row as a map. |
queryForStream<T>(sql, params, mapper) |
A Stream of mapped rows, read as the database sends them. |
update(sql, [params]) |
The number of rows changed. |
updateReturningKey(sql, [params]) |
The generated key of an insert. |
updateWithKeyHolder(sql, params, holder) |
The count; the key in the KeyHolder (holder.keyAs<int>()). |
batchUpdate(sql, batch) |
One statement run for each list of values; the counts. |
execute(sql) |
Runs a statement without parameters (DDL). |
params is a List<Object?>, bound to the ? markers in order.
NamedParameterJdbcTemplate
The same methods, with :name markers and the values given as a Map<String, Object?> or as an object whose class the generator describes (its properties by name). A collection value is expanded to one marker per element: where id in (:ids). Values whose names look secret are masked in the logs (see Logging SQL).
await named.update(
'insert into visits (page, at) values (:page, :at)',
{'page': '/books', 'at': DateTime.now()},
);
await named.batchUpdate('delete from visits where id = :id', [
{'id': 1},
{'id': 2},
]);
JdbcClient
| Step | Meaning |
|---|---|
client.sql(text) |
Starts a statement. |
.param(name, value) |
A named value. |
.param(value) |
The next positional value. |
.params(map) or .params(list) |
Several values at once. |
.paramSource(object) |
Named values from an object's properties. |
.query<T>([mapper]) |
A query; without a mapper, by the type T: the only column for a simple type (int, String, an enum), a map for Map<String, Object?>, a data-class mapping for a described class (author_name fills authorName). |
.list(), .set(), .single(), .optional(), .stream() |
The results of a query. |
.update([keyHolder]) |
Runs a change and returns the rows changed. |
One statement uses named or positional parameters, not both: mixing them is an InvalidDataAccessApiUsageException.
Mapping rows by hand
A RowMapper<T> is a T Function(JdbcRow row). row['title'] reads a column by its label (any case); the JdbcRowReaders extension gives typed reads:
Book mapBook(JdbcRow row) => Book(
id: row.int_('id'),
title: row.string('title'),
year: row.intOrNull('year'),
);
final books = await jdbc.query('select * from books', const [], mapBook);
| Reader | Reads |
|---|---|
string, stringOrNull |
text |
int_, intOrNull |
integers |
double_, doubleOrNull |
numbers (an int widens) |
bool_, boolOrNull |
booleans (0 is false) |
dateTime, dateTimeOrNull |
instants (an ISO 8601 text is parsed) |
bytesOrNull |
bytes |
A non-null reader given a null fails with an InvalidDataAccessApiUsageException: use the OrNull form. SingleColumnRowMapper, ColumnMapRowMapper and DataClassRowMapper are the mappers the templates use by type.
Transactions and timeouts
- A template statement inside a
@Transactionalmethod or aTransactionTemplateruns on the transaction's connection; outside, it takes a connection for itself, in autocommit. spring.jdbc.template.query-timeoutbounds each statement of the template beans (500ms,10s); none by default. A statement past it is aQueryTimeoutException.
Several databases
Each spring.datasources.<name> gets <name>JdbcTemplate, <name>NamedParameterJdbcTemplate and <name>JdbcClient, qualified <name>:
Reports(@Qualifier('stats') JdbcClient stats) : _stats = stats;
In Spring (Java)
jdbc.queryForObject("select count(*) from books where year > ?", Integer.class, year);
client.sql("select * from books where id = :id").param("id", id).query(Book.class).optional();
The API is Spring's, adapted to Dart: results are Futures or Streams, the type is a type argument (query<Book>()) instead of a Class, and optional() returns a nullable value.