
# 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 `DataAccessException`s, 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

```dart
@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](logging.md)).

```dart
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:

```dart
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 `@Transactional` method or a `TransactionTemplate` runs on the transaction's connection; outside, it takes a connection for itself, in autocommit.
- `spring.jdbc.template.query-timeout` bounds each statement of the template beans (`500ms`, `10s`); none by default. A statement past it is a `QueryTimeoutException`.

## Several databases

Each `spring.datasources.<name>` gets `<name>JdbcTemplate`, `<name>NamedParameterJdbcTemplate` and `<name>JdbcClient`, qualified `<name>`:

```dart
Reports(@Qualifier('stats') JdbcClient stats) : _stats = stats;
```

## In Spring (Java)

```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 `Future`s or `Stream`s, the type is a type argument (`query<Book>()`) instead of a `Class`, and `optional()` returns a nullable value.

## See also

- [@Query](query.md)
- [Transactions](transactions.md)
- [Logging SQL](logging.md)
