@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:
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:
@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:
- The method and its annotation: it returns a
Futureor aStream; aPageorSlicehas aPageable; at most onePageableorSort;@Modifyingreturnsint,boolor nothing;countQueryis for aPage;rowMapperandresultSetExtractorare not together, and are functions of aJdbcRowor aJdbcResult; no@Lock; the rows can become the result. - The text: each
:nameis a parameter of the method; no plain?; no#{...}other than#{#entityName}. - The entity placeholders: in
{Book.authorId},Bookis a@Tableclass of the application andauthorIda stored property (not@Transient, not a@MappedCollection). - Misspelled names: a table after
from,join,update,intoordelete from, a qualifiedalias.columnand an unqualified column, when the build does not know the name but knows a close one:
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
NamingStrategybean of your own hides the names derived from entities; - the build reads
spring.sql.migration.locationsandspring.sql.init.schema-locationsfromapplication.properties,application.ymlandapplication.yaml(also inconfig/),resources:locations only. Afile:location, a${placeholder}in one, a profile file that sets them,spring.config.import,spring.config.location,spring.config.additional-locationor 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;
@Queryneeds a SQL store: a repository on the in-memory store with a@Querymethod 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)
@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.