Derived queries
A derived query is a repository method whose name is the query: findByTitleContainingIgnoreCase(String text) loads the books whose title contains text, ignoring case. The build parses the name, checks it against the entity, and writes a constant query tree for it. The same method runs on every store: in memory, SQLite and PostgreSQL.
Why
Most finders are simple conditions on a few columns. Writing SQL for each one is noise, and SQL in strings is checked only when it runs. A method name is short, reads like the question, and the build checks it: findByTitel is a build error with did you mean 'title'?, not a failure in production.
Example
abstract interface class BookRepository implements ListCrudRepository<Book, int> {
Future<List<Book>> findByTitle(String title);
Future<List<Book>> findByTitleContainingIgnoreCase(String part);
Future<List<Book>> findByTitleAndYear(String title, int year);
Future<List<Book>> findByYearBetween(int from, int to);
Future<List<Book>> findByYearIn(List<int> years);
Future<List<Book>> findBySubtitleIsNull();
Future<List<Book>> findByInStockTrue();
Future<Book?> findOneByTitle(String title); // null when none
Future<Book> getByTitle(String title); // EmptyResultDataAccessException when none
Future<Book?> findFirstByOrderByYearAsc(); // the oldest
Future<List<Book>> findTop2ByOrderByYearDesc(); // the two newest
Future<List<Book>> findByInStockOrderByYearDescTitleAsc(bool inStock);
Stream<Book> streamByInStock(bool inStock); // rows as they come
Future<int> countByInStock(bool inStock);
Future<bool> existsByTitle(String title);
Future<int> deleteByYearLessThan(int year); // the number deleted
Future<void> removeByTitle(String title);
Future<List<Book>> deleteByInStock(bool inStock); // the deleted aggregates
}
The parts of a name
findTop5ByTitleContainingIgnoreCaseAndYearGreaterThanOrderByYearDesc reads as:
| Part | Here | Meaning |
|---|---|---|
| Subject | find |
What to do with the matches. |
| Limit | Top5 |
At most 5 results (First alone is 1). |
By |
By |
The conditions start. |
| Condition | TitleContainingIgnoreCase |
A property, an operator, and maybe IgnoreCase. |
And / Or |
And |
Joins conditions; And binds tighter than Or. |
| Condition | YearGreaterThan |
|
| Order | OrderByYearDesc |
The order of the results. |
Subjects
| Prefix | Does | Returns |
|---|---|---|
find, read, get, query, search, stream |
Loads the matches. | List, Iterable, Set, Stream, Page, Slice, or one entity |
watch |
Loads the matches and keeps them up to date, on a store that reports its changes. See Changes of rows. | Stream<List<Book>> |
changes |
The changes of the rows the conditions match, on such a store. | Stream<RowChange<Book>> |
count |
Counts the matches. | Future<int> |
exists |
Whether any matches. | Future<bool> |
delete, remove |
Deletes the matches, with their children. | Future<int> (the count), Future<void>, or the deleted aggregates (Future<List<Book>>) |
Distinct after the prefix (findDistinctByInStock) removes duplicates. Top<n> and First<n> limit the results; with a Pageable, the page's window is cut at that limit (findTop3By...(Pageable) with a page size of 2 gives pages of 2 and 1). findAll... and countBy with nothing after By match every aggregate.
Operators
| Keyword (and its aliases) | Parameters | Condition |
|---|---|---|
(none), Is, Equals |
1 | = value |
Not, IsNot |
1 | <> value |
Between, IsBetween |
2 | between from and to |
LessThan, IsLessThan, Before, IsBefore |
1 | < value |
LessThanEqual, IsLessThanEqual |
1 | <= value |
GreaterThan, IsGreaterThan, After, IsAfter |
1 | > value |
GreaterThanEqual, IsGreaterThanEqual |
1 | >= value |
IsNull, Null |
0 | is null |
IsNotNull, NotNull |
0 | is not null |
Like, IsLike |
1 | like value (your pattern) |
NotLike, IsNotLike |
1 | not like value |
StartingWith, IsStartingWith, StartsWith |
1 | like 'value%' |
EndingWith, IsEndingWith, EndsWith |
1 | like '%value' |
Containing, IsContaining, Contains |
1 | like '%value%' |
NotContaining, IsNotContaining, NotContains |
1 | not like '%value%' |
In, IsIn |
1 (a collection) | in (...) |
NotIn, IsNotIn |
1 (a collection) | not in (...) |
True, IsTrue |
0 | = true |
False, IsFalse |
0 | = false |
IgnoreCase after a condition compares in lower case; AllIgnoreCase at the end applies it to every condition. The parameters follow the conditions in order, then an optional Sort or Pageable.
Values that are text
- In
StartingWith,EndingWithandContaining, the argument is text, not a pattern:%,_and\in it are escaped by the starter, on every store. InLikethe pattern is yours. - A NUL character (
\u0000) in a text argument is refused before anything is sent: anInvalidDataArgumentException(anInvalidDataAccessApiUsageException), on every store. PostgreSQL cannot hold one, and SQLite'sLIKEstops reading at it. IgnoreCasefolds every letter, not only ASCII: on SQLite the driver registers alowerthat uses Dart'stoLowerCase, soДЮНАmatchesдюнаas in the in-memory store. On PostgreSQL it islower(), which folds what the database's locale knows.
Long In lists
On PostgreSQL a derived In or NotIn binds the list as one array parameter (= any($1), <> all($1)), so any length works. SQLite binds one value per element and allows 32 766 values per statement: a longer list is an InvalidDataArgumentException that says so. A null in the list is ignored by In, and makes a NotIn match nothing, as in SQL.
One result
| Return type | No match | More than one |
|---|---|---|
Future<Book?> |
null |
IncorrectResultSizeDataAccessException |
Future<Book> |
EmptyResultDataAccessException |
IncorrectResultSizeDataAccessException |
With First or Top, more than one match is not an error: the first is taken.
Order
OrderBy names one or more properties, each with Asc (the default) or Desc: OrderByYearDescTitleAsc. A Sort or Pageable parameter adds its orders after the name's. See Paging and sorting.
Transactions and locks
deleteByruns in a transaction of the repository's store, joining the caller's when there is one.- A finder marked
@Locklocks the rows it reads until the transaction ends (see Locks).
What the build checks
- Each property exists on the entity and is stored (not
@Transient), with a suggestion when it is misspelled. - The number of parameters matches the conditions; a
PageableorSortcomes last. Referencesare compared by their own type:findByEditor(AggregateReference<Editor, int>), orfindByAuthorId(int)for a@Referenceproperty.
In the Zed extension dart_spring, the language server reads names with the same grammar while you type: it underlines an unknown property with a suggestion, checks the parameter count, and shows the SQL the name stands for on hover.
In Spring (Java)
List<Book> findByTitleContainingIgnoreCase(String part);
Optional<Book> findFirstByOrderByYearAsc();
long countByInStock(boolean inStock);
The grammar and keywords are Spring Data's. Results are Futures or Streams, a single result is nullable instead of Optional, and the name is checked by the build instead of at startup.