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

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

What the build checks

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.

See also