
# @Query

`@Query('...')` gives a repository method its own SQL instead of a query derived from its name. Parameters are named (`:year`) and bound to the method's parameters by name; values are always bound, never written into the text. The build checks the method, the text, the parameters and the SpEL expressions, so most mistakes are build errors.

## Why

Derived queries cover simple conditions. A join, an aggregate function, a report or a bulk update needs SQL. In Spring that SQL is checked only when the repository is created, at startup. Here the build knows the entities and the migrations, and checks the text before anything runs: a misspelled column is a build error with a suggestion.

## Example

```dart
class BookCard {
  BookCard({required this.title, required this.authorName});

  final String title;
  final String authorName;   // filled from the column author_name
}

abstract interface class BookRepository implements ListCrudRepository<Book, int> {
  // {Book} is the entity's quoted table, {Book.authorId} its property's column.
  @Query('select {Book.title} from {Book} where {Book.authorId} = :authorId')
  Future<List<String>> titlesOf(int authorId);

  // Rows into a class of yours; paged, with a count statement.
  @Query(
    'select b.title, a.name as author_name from books b '
    'join authors a on a.id = b.author_id where b.year > :year',
    countQuery: 'select count(*) from books where year > :year',
  )
  Future<Page<BookCard>> cards(int year, Pageable page);

  // A change: returns the number of rows changed.
  @Modifying()
  @Query('update books set year = year + 1 where year > :year')
  Future<int> shift(int year);

  // A SpEL value from an argument, compiled to Dart by the build.
  @Query('select * from books where title = :#{#book.title}')
  Future<List<Book>> sameTitle(Book book);

  // A property of the environment, with a default.
  @Query(r'select * from books order by year desc limit ${shop.top-size:10}')
  Future<List<Book>> top();

  // A collection parameter is expanded to one marker per value.
  @Query('select * from books where id in (:ids)')
  Future<List<Book>> some(List<int> ids);
}
```

## The attributes

| Attribute | Meaning |
|---|---|
| `value` | The SQL of the method. |
| `countQuery` | For a method returning a `Page`: the SQL counting all results. |
| `rowMapper` | A `T Function(JdbcRow row)`, a static method or a top-level function given as a constant, that maps each row. The method returns a `List`, `Set`, `Stream`, `Page` or `Slice` of `T`, or one `T`. |
| `resultSetExtractor` | An `R Function(JdbcResult result)` that turns the whole result into what the method returns, for results that span rows (one to many). Not with `rowMapper`, and no `Pageable`. |
| `name` | Reserved for named queries; not supported yet (a build error). |

```dart
@Query(
  'select a.id, a.name, b.title from authors a join books b on b.author_id = a.id',
  resultSetExtractor: AuthorWithBooks.extract,
)
Future<List<AuthorWithBooks>> authorsWithBooks();
```

## What goes in the text

| Written | Becomes |
|---|---|
| `:name` | The method's parameter `name`, bound as a value. A collection becomes one marker per element. |
| `{Book}` | The quoted table of the entity `Book`, through the mapping (naming strategy, `@Table`). |
| `{Book.authorId}` | The quoted column of the property `authorId` (`author_id`). Not qualified: write `b.{Book.authorId}` in a join. |
| `:#{expression}` | A SpEL value, bound as a parameter. |
| `#{#entityName}` | The repository entity's quoted table. |
| `${key:default}` | A property of the environment, resolved at startup and written into the text. |

A plain `?` and any other `#{...}` are refused: use named parameters.

## Results

The rows become what the method returns, by its type:

| Return type | Rows become |
|---|---|
| the entity | Entities, from the columns of its table; children are loaded too. |
| a class of the application | Instances of it: columns by name, `author_name` into `authorName`. The generator describes the class. |
| `int`, `String`, a column type | The only column. |
| `Map<String, Object?>` | One map per row. |
| anything, with `rowMapper` or `resultSetExtractor` | What the function returns. |

Each in a `Future<List<T>>`, `Future<Set<T>>`, `Future<T?>`, `Future<T>`, `Stream<T>`, `Future<Page<T>>` or `Future<Slice<T>>`. A `Page` or `Slice` needs a `Pageable` parameter, and a `Sort` or `Pageable` parameter adds its order to the text.

## @Modifying

A query that changes data (`update`, `delete`, `insert`) needs `@Modifying()`. The method returns:

- `Future<int>`: the number of rows changed;
- `Future<bool>`: whether any row changed;
- `Future<void>`: nothing.

A `@Modifying` method has no `rowMapper` and no `Pageable`. A `void` method without `@Modifying` is a build error. Remember that a `@Modifying` query does not go through `save`: no `@Version` check, no auditing.

## SpEL values

A `:#{...}` value is written as Dart when the build can, a function of the arguments:

```dart
@Query('select * from books where title = :#{#book.title}')
Future<List<Book>> sameTitle(Book book);
// generated: expressions: [(arguments, context) => (arguments[0] as Book).title]
```

So a mistake is a build error with a suggestion, and nothing is interpreted at run time:

```text
BookRepository.sameTitle: the expression :#{#book.titel} is invalid:
  No property 'titel' on type 'Book'; did you mean 'title'?
```

| In an expression | Means |
|---|---|
| `#name` | The method's parameter `name`. |
| `#p0`, `#a0` | The first argument. |
| `#root` | The list of the arguments. |
| `#entityName` | The entity's quoted table. |
| `#methodName` | The method's name. |
| `@clock` | The bean named `clock`. |
| `@environment.getProperty('key', 'default')` | A property. |

An unknown `#variable`, a wrong call, a member of a value that can be null without `?.`, and a syntax error are build errors. What the compiler leaves alone (`T(...)`, `new`, selection and projection, `matches`, `instanceof`, Java-style `size()`, a member of `Object` or of a bean it does not know) is interpreted at run time, and only the classes of that method's arguments are described for it. `@Introspected()` on a class always describes it. See [SpEL](../spring-core-dart/spel/index.md).

## What the build checks

Every problem of an interface is one build error, with the others:

- **The method and its annotation:** it returns a `Future` or a `Stream`; a `Page` or `Slice` has a `Pageable`; at most one `Pageable` or `Sort`; `@Modifying` returns `int`, `bool` or nothing; `countQuery` is for a `Page`; `rowMapper` and `resultSetExtractor` are not together, and are functions of a `JdbcRow` or a `JdbcResult`; no `@Lock`; the rows can become the result.
- **The text:** each `:name` is a parameter of the method; no plain `?`; no `#{...}` other than `#{#entityName}`.
- **The entity placeholders:** in `{Book.authorId}`, `Book` is a `@Table` class of the application and `authorId` a stored property (not `@Transient`, not a `@MappedCollection`).
- **Misspelled names:** a table after `from`, `join`, `update`, `into` or `delete from`, a qualified `alias.column` and an unqualified column, when the build does not know the name but knows a close one:

```text
Invalid repository BookRepository:
  - BookRepository.cards: b.titel: no column titel in books, did you mean 'title'?
```

The build knows the tables and columns of the entities, and of the migrations and init scripts in `lib/resources/`: `db/migration/V*__*.sql`, `R__*.sql` and `schema.sql`, with `create table`, `alter table` (add, rename or drop a column, rename the table), `drop table` and `create view` with its select list, in the order they run.

The rule says nothing when unsure:

- a name nothing is close to is left alone (another schema's table, a function, a word of your database), and so is a name of one or two characters;
- the columns of a table or view the build only partly understands, of a `select *` and of a `${placeholder}` table are not checked;
- keywords, functions, aliases (of tables, columns and CTEs), casts and quoted text are never names;
- a `NamingStrategy` bean of your own hides the names derived from entities;
- the build reads `spring.sql.migration.locations` and `spring.sql.init.schema-locations` from `application.properties`, `application.yml` and `application.yaml` (also in `config/`), `resources:` locations only. A `file:` location, a `${placeholder}` in one, a profile file that sets them, `spring.config.import`, `spring.config.location`, `spring.config.additional-location` or the settings of a named data source turn the check off;
- with `{vendor}` in a location, the migrations of each database are alternatives: a column of any of them is known.

## At startup

What depends on the run time is checked when the repository is created, and every problem is listed in one error:

- `${property:default}` placeholders are resolved from the environment;
- `{Entity}` names are written through the mapping;
- the SpEL values the build did not compile are parsed;
- `@Query` needs a SQL store: a repository on the in-memory store with a `@Query` method fails the start.

## In the editor

The language server of the `dart_spring` extension (Zed) knows the data layer. Inside `@Query('...')` it colours the SQL, completes `{` (entities), `{Book.` (properties) and `:` (parameters), goes from `{Book}` to the class and from `:year` to the parameter, shows the table and column on hover, and reports what the build would while you type, plus a warning for a parameter the query never uses.

## In Spring (Java)

```java
@Query("select * from books where author_id = :authorId")
List<Book> byAuthor(@Param("authorId") long authorId);

@Modifying
@Query("update books set year = year + 1 where year > :year")
int shift(int year);
```

The parameters are matched by name without `@Param`. `{Entity}` and `{Entity.property}` placeholders are additions. `rowMapperClass` and `resultSetExtractorClass` are `rowMapper` and `resultSetExtractor`, given as functions. Spring checks the text when the repository is created; here the build does.

## See also

- [Derived queries](derived-queries.md)
- [JdbcTemplate and JdbcClient](jdbc.md)
- [Schema and migrations](schema.md)
