@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

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).
@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:

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:

@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:

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.

What the build checks

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

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:

At startup

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

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)

@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