[[repositories.query-methods.details]]
= Defining Query Methods

The repository proxy has two ways to derive a store-specific query from the method name:

* By deriving the query from the method name directly.
* By using a manually defined query.

Available options depend on the actual store.
However, there must be a strategy that decides what actual query is created.
The next section describes the available options.

[[repositories.query-methods.query-lookup-strategies]]
== Query Lookup Strategies

The following strategies are available for the repository infrastructure to resolve the query.
ifeval::[{include-xml-namespaces} != false]
With XML configuration, you can configure the strategy at the namespace through the `query-lookup-strategy` attribute.
endif::[]
For Java configuration, you can use the `queryLookupStrategy` attribute of the `Enable{store}Repositories` annotation.
Some strategies may not be supported for particular datastores.

- `CREATE` attempts to construct a store-specific query from the query method name.
The general approach is to remove a given set of well known prefixes from the method name and parse the rest of the method.
You can read more about query construction in "`xref:repositories/query-methods-details.adoc#repositories.query-methods.query-creation[Query Creation]`".

- `USE_DECLARED_QUERY` tries to find a declared query and throws an exception if it cannot find one.
The query can be defined by an annotation somewhere or declared by other means.
See the documentation of the specific store to find available options for that store.
If the repository infrastructure does not find a declared query for the method at bootstrap time, it fails.

- `CREATE_IF_NOT_FOUND` (the default) combines `CREATE` and `USE_DECLARED_QUERY`.
It looks up a declared query first, and, if no declared query is found, it creates a custom method name-based query.
This is the default lookup strategy and, thus, is used if you do not configure anything explicitly.
It allows quick query definition by method names but also custom-tuning of these queries by introducing declared queries as needed.

[[repositories.query-methods.query-creation]]
== Query Creation

The query builder mechanism built into the Spring Data repository infrastructure is useful for building constraining queries over entities of the repository.

The following example shows how to create a number of queries:

.Query creation from method names
[source,java]
----
interface PersonRepository extends Repository<Person, Long> {

  List<Person> findByEmailAddressAndLastname(EmailAddress emailAddress, String lastname);

  // Enables the distinct flag for the query
  List<Person> findDistinctPeopleByLastnameOrFirstname(String lastname, String firstname);
  List<Person> findPeopleDistinctByLastnameOrFirstname(String lastname, String firstname);

  // Enabling ignoring case for an individual property
  List<Person> findByLastnameIgnoreCase(String lastname);
  // Enabling ignoring case for all suitable properties
  List<Person> findByLastnameAndFirstnameAllIgnoreCase(String lastname, String firstname);

  // Enabling static ORDER BY for a query
  List<Person> findByLastnameOrderByFirstnameAsc(String lastname);
  List<Person> findByLastnameOrderByFirstnameDesc(String lastname);
}
----

Parsing query method names is divided into subject and predicate.
The first part (`find…By`, `exists…By`) defines the subject of the query, the second part forms the predicate.
The introducing clause (subject) can contain further expressions.
Any text between `find` (or other introducing keywords) and `By` is considered to be descriptive unless using one of the result-limiting keywords such as a `Distinct` to set a distinct flag on the query to be created or <<repositories.limit-query-result,`Top`/`First` to limit query results>>.

The appendix contains the xref:repositories/query-keywords-reference.adoc#appendix.query.method.subject[full list of query method subject keywords] and xref:repositories/query-keywords-reference.adoc#appendix.query.method.predicate[query method predicate keywords including sorting and letter-casing modifiers].
However, the first `By` acts as a delimiter to indicate the start of the actual criteria predicate.
At a very basic level, you can define conditions on entity properties and concatenate them with `And` and `Or`.

The actual result of parsing the method depends on the persistence store for which you create the query.
However, there are some general things to notice:

- The expressions are usually property traversals combined with operators that can be concatenated.
You can combine property expressions with `AND` and `OR`.
You also get support for operators such as `Between`, `LessThan`, `GreaterThan`, and `Like` for the property expressions.
The supported operators can vary by datastore, so consult the appropriate part of your reference documentation.

- The method parser supports setting an `IgnoreCase` flag for individual properties (for example, `findByLastnameIgnoreCase(…)`) or for all properties of a type that supports ignoring case (usually `String` instances -- for example, `findByLastnameAndFirstnameAllIgnoreCase(…)`).
Whether ignoring cases is supported may vary by store, so consult the relevant sections in the reference documentation for the store-specific query method.

- You can apply static ordering by appending an `OrderBy` clause to the query method that references a property and by providing a sorting direction (`Asc` or `Desc`).
To create a query method that supports dynamic sorting, see "`xref:repositories/query-methods-details.adoc[Paging, Iterating Large Results, Sorting & Limiting]`".

[[repositories.query-methods.query-property-expressions]]
== Property Expressions

Property expressions can refer only to a direct property of the managed entity, as shown in the preceding example.
At query creation time, you already make sure that the parsed property is a property of the managed domain class.
However, you can also define constraints by traversing nested properties.
Consider the following method signature:

[source,java]
----
List<Person> findByAddressZipCode(ZipCode zipCode);
----

Assume a `Person` has an `Address` with a `ZipCode`.
In that case, the method creates the `x.address.zipCode` property traversal.
The resolution algorithm starts by interpreting the entire part (`AddressZipCode`) as the property and checks the domain class for a property with that name (uncapitalized).
If the algorithm succeeds, it uses that property.
If not, the algorithm splits up the source at the camel-case parts from the right side into a head and a tail and tries to find the corresponding property -- in our example, `AddressZip` and `Code`.
If the algorithm finds a property with that head, it takes the tail and continues building the tree down from there, splitting the tail up in the way just described.
If the first split does not match, the algorithm moves the split point to the left (`Address`, `ZipCode`) and continues.

Although this should work for most cases, it is possible for the algorithm to select the wrong property.
Suppose the `Person` class has an `addressZip` property as well.
The algorithm would match in the first split round already, choose the wrong property, and fail (as the type of `addressZip` probably has no `code` property).

To resolve this ambiguity you can use `_` inside your method name to manually define traversal points.
So our method name would be as follows:

[source,java]
----
List<Person> findByAddress_ZipCode(ZipCode zipCode);
----

Because we treat the underscore character as a reserved character, we strongly advise following standard Java naming conventions (that is, not using underscores in property names but using camel case instead).

[[repositories.collections-and-iterables]]
== Repository Methods Returning Collections or Iterables

Query methods that return multiple results can use standard Java `Iterable`, `List`, and `Set`.
Beyond that, we support returning Spring Data's `Streamable`, a custom extension of `Iterable`, as well as collection types provided by https://www.vavr.io/[Vavr].
Refer to the appendix explaining all possible xref:repositories/query-return-types-reference.adoc#appendix.query.return.types[query method return types].

[[repositories.collections-and-iterables.streamable]]
=== Using Streamable as Query Method Return Type

You can use `Streamable` as alternative to `Iterable` or any collection type.
It provides convenience methods to access a non-parallel `Stream` (missing from `Iterable`) and the ability to directly `….filter(…)` and `….map(…)` over the elements and concatenate the `Streamable` to others:

.Using Streamable to combine query method results
[source,java]
----
interface PersonRepository extends Repository<Person, Long> {
  Streamable<Person> findByFirstnameContaining(String firstname);
  Streamable<Person> findByLastnameContaining(String lastname);
}

Streamable<Person> result = repository.findByFirstnameContaining("av")
  .and(repository.findByLastnameContaining("ea"));
----

[[repositories.collections-and-iterables.streamable-wrapper]]
=== Returning Custom Streamable Wrapper Types

Providing dedicated wrapper types for collections is a commonly used pattern to provide an API for a query result that returns multiple elements.
Usually, these types are used by invoking a repository method returning a collection-like type and creating an instance of the wrapper type manually.
You can avoid that additional step as Spring Data lets you use these wrapper types as query method return types if they meet the following criteria:

. The type implements `Streamable`.
. The type exposes either a constructor or a static factory method named `of(…)` or `valueOf(…)` that takes `Streamable` as an argument.

The following listing shows an example:

[source,java]
----
class Product {                                         <1>
  MonetaryAmount getPrice() { … }
}

@RequiredArgsConstructor(staticName = "of")
class Products implements Streamable<Product> {         <2>

  private final Streamable<Product> streamable;

  public MonetaryAmount getTotal() {                    <3>
    return streamable.stream()
      .map(Priced::getPrice)
      .reduce(Money.of(0), MonetaryAmount::add);
  }


  @Override
  public Iterator<Product> iterator() {                 <4>
    return streamable.iterator();
  }
}

interface ProductRepository implements Repository<Product, Long> {
  Products findAllByDescriptionContaining(String text); <5>
}
----
<1> A `Product` entity that exposes API to access the product's price.
<2> A wrapper type for a `Streamable<Product>` that can be constructed by using `Products.of(…)` (factory method created with the Lombok annotation).
    A standard constructor taking the `Streamable<Product>` will do as well.
<3> The wrapper type exposes an additional API, calculating new values on the `Streamable<Product>`.
<4> Implement the `Streamable` interface and delegate to the actual result.
<5> That wrapper type `Products` can be used directly as a query method return type.
You do not need to return `Streamable<Product>` and manually wrap it after the query in the repository client.

[[repositories.collections-and-iterables.vavr]]
=== Support for Vavr Collections

https://www.vavr.io/[Vavr] is a library that embraces functional programming concepts in Java.
It ships with a custom set of collection types that you can use as query method return types, as the following table shows:

[options=header]
|====
|Vavr collection type|Used Vavr implementation type|Valid Java source types
|`io.vavr.collection.Seq`|`io.vavr.collection.List`|`java.util.Iterable`
|`io.vavr.collection.Set`|`io.vavr.collection.LinkedHashSet`|`java.util.Iterable`
|`io.vavr.collection.Map`|`io.vavr.collection.LinkedHashMap`|`java.util.Map`
|====

You can use the types in the first column (or subtypes thereof) as query method return types and get the types in the second column used as implementation type, depending on the Java type of the actual query result (third column).
Alternatively, you can declare `Traversable` (the Vavr `Iterable` equivalent), and we then derive the implementation class from the actual return value.
That is, a `java.util.List` is turned into a Vavr `List` or `Seq`, a `java.util.Set` becomes a Vavr `LinkedHashSet` `Set`, and so on.


[[repositories.query-streaming]]
== Streaming Query Results

You can process the results of query methods incrementally by using a Java 8 `Stream<T>` as the return type.
Instead of wrapping the query results in a `Stream`, data store-specific methods are used to perform the streaming, as shown in the following example:

.Stream the result of a query with Java 8 `Stream<T>`
[source,java]
----
@Query("select u from User u")
Stream<User> findAllByCustomQueryAndStream();

Stream<User> readAllByFirstnameNotNull();

@Query("select u from User u")
Stream<User> streamAllPaged(Pageable pageable);
----

NOTE: A `Stream` potentially wraps underlying data store-specific resources and must, therefore, be closed after usage.
You can either manually close the `Stream` by using the `close()` method or by using a Java 7 `try-with-resources` block, as shown in the following example:

.Working with a `Stream<T>` result in a `try-with-resources` block
[source,java]
----
try (Stream<User> stream = repository.findAllByCustomQueryAndStream()) {
  stream.forEach(…);
}
----

NOTE: Not all Spring Data modules currently support `Stream<T>` as a return type.

[[repositories.query-async]]
== Asynchronous Query Results

You can run repository queries asynchronously by using {spring-framework-docs}/integration/scheduling.html[Spring's asynchronous method running capability].
This means the method returns immediately upon invocation while the actual query occurs in a task that has been submitted to a Spring `TaskExecutor`.
Asynchronous queries differ from reactive queries and should not be mixed.
See the store-specific documentation for more details on reactive support.
The following example shows a number of asynchronous queries:

[source,java]
----
@Async
Future<User> findByFirstname(String firstname);               <1>

@Async
CompletableFuture<User> findOneByFirstname(String firstname); <2>
----
<1> Use `java.util.concurrent.Future` as the return type.
<2> Use a Java 8 `java.util.concurrent.CompletableFuture` as the return type.

[[repositories.special-parameters]]
== Paging, Iterating Large Results, Sorting & Limiting

To handle parameters in your query, define method parameters as already seen in the preceding examples.
Besides that, the infrastructure recognizes certain specific types like `Pageable`, `Sort` and `Limit`, to apply pagination, sorting and limiting to your queries dynamically.
The following example demonstrates these features:

ifdef::feature-scroll[]
.Using `Pageable`, `Slice`, `ScrollPosition`, `Sort` and `Limit` in query methods
[source,java]
----
Page<User> findByLastname(String lastname, Pageable pageable);

Slice<User> findByLastname(String lastname, Pageable pageable);

Window<User> findTop10ByLastname(String lastname, ScrollPosition position, Sort sort);

List<User> findByLastname(String lastname, Sort sort);

List<User> findByLastname(String lastname, Sort sort, Limit limit);

List<User> findByLastname(String lastname, Pageable pageable);
----
endif::[]

ifndef::feature-scroll[]
.Using `Pageable`, `Slice`, `Sort` and `Limit` in query methods
[source,java]
----
Page<User> findByLastname(String lastname, Pageable pageable);

Slice<User> findByLastname(String lastname, Pageable pageable);

List<User> findByLastname(String lastname, Sort sort);

List<User> findByLastname(String lastname, Sort sort, Limit limit);

List<User> findByLastname(String lastname, Pageable pageable);
----
endif::[]

IMPORTANT: APIs taking `Sort`, `Pageable` and `Limit` expect non-`null` values to be handed into methods.
If you do not want to apply any sorting or pagination, use `Sort.unsorted()`, `Pageable.unpaged()` and `Limit.unlimited()`.

The first method lets you pass an `org.springframework.data.domain.Pageable` instance to the query method to dynamically add paging to your statically defined query.
A `Page` knows about the total number of elements and pages available.
It does so by the infrastructure triggering a count query to calculate the overall number.
As this might be expensive (depending on the store used), you can instead return a `Slice`.
A `Slice` knows only about whether a next `Slice` is available, which might be sufficient when walking through a larger result set.

Sorting options are handled through the `Pageable` instance, too.
If you need only sorting, add an `org.springframework.data.domain.Sort` parameter to your method.
As you can see, returning a `List` is also possible.
In this case, the additional metadata required to build the actual `Page` instance is not created (which, in turn, means that the additional count query that would have been necessary is not issued).
Rather, it restricts the query to look up only the given range of entities.

NOTE: To find out how many pages you get for an entire query, you have to trigger an additional count query.
By default, this query is derived from the query you actually trigger.

[IMPORTANT]
====
Special parameters may only be used once within a query method. +
Some special parameters described above are mutually exclusive.
Please consider the following list of invalid parameter combinations.

|===
| Parameters | Example | Reason

| `Pageable` and `Sort`
| `findBy...(Pageable page, Sort sort)`
| `Pageable` already defines `Sort`

| `Pageable` and `Limit`
| `findBy...(Pageable page, Limit limit)`
| `Pageable` already defines a limit.

|===

The `Top` keyword used to limit results can be used to along with `Pageable` whereas `Top` defines the total maximum of results, whereas the Pageable parameter may reduce this number.
====

[[repositories.scrolling.guidance]]
=== Which Method is Appropriate?

The value provided by the Spring Data abstractions is perhaps best shown by the possible query method return types outlined in the following table below.
The table shows which types you can return from a query method

.Consuming Large Query Results
[cols="1,2,2,3"]
|===
| Method|Amount of Data Fetched|Query Structure|Constraints

| <<repositories.collections-and-iterables,`List<T>`>>
| All results.
| Single query.
| Query results can exhaust all memory. Fetching all data can be time-intensive.

| <<repositories.collections-and-iterables.streamable,`Streamable<T>`>>
| All results.
| Single query.
| Query results can exhaust all memory. Fetching all data can be time-intensive.

| <<repositories.query-streaming,`Stream<T>`>>
| Chunked (one-by-one or in batches) depending on `Stream` consumption.
| Single query using typically cursors.
| Streams must be closed after usage to avoid resource leaks.

| `Flux<T>`
| Chunked (one-by-one or in batches) depending on `Flux` consumption.
| Single query using typically cursors.
| Store module must provide reactive infrastructure.

| `Slice<T>`
| `Pageable.getPageSize() + 1` at `Pageable.getOffset()`
| One to many queries fetching data starting at `Pageable.getOffset()` applying limiting.
a| A `Slice` can only navigate to the next `Slice`.

* `Slice` provides details whether there is more data to fetch.
* Offset-based queries becomes inefficient when the offset is too large because the database still has to materialize the full result.

ifdef::feature-scroll[]
| Offset-based `Window<T>`
| `limit + 1` at `OffsetScrollPosition.getOffset()`
| One to many queries fetching data starting at `OffsetScrollPosition.getOffset()` applying limiting.
a| A `Window` can only navigate to the next `Window`.
endif::[]

* `Window` provides details whether there is more data to fetch.
* Offset-based queries becomes inefficient when the offset is too large because the database still has to materialize the full result.

| `Page<T>`
| `Pageable.getPageSize()`  at `Pageable.getOffset()`
| One to many queries starting at `Pageable.getOffset()` applying limiting. Additionally, `COUNT(…)` query to determine the total number of elements can be required.
a| Often times, `COUNT(…)` queries are required that are costly.

* Offset-based queries becomes inefficient when the offset is too large because the database still has to materialize the full result.

ifdef::feature-scroll[]
| Keyset-based `Window<T>`
| `limit + 1` using a rewritten `WHERE` condition
| One to many queries fetching data starting at `KeysetScrollPosition.getKeys()` applying limiting.
a| A `Window` can only navigate to the next `Window`.

* `Window` provides details whether there is more data to fetch.
* Keyset-based queries require a proper index structure for efficient querying.
* Most data stores do not work well when Keyset-based query results contain `null` values.
* Results must expose all sorting keys in their results requiring projections to select potentially more properties than required for the actual projection.
endif::[]

|===

[[repositories.paging-and-sorting]]
=== Paging and Sorting

You can define simple sorting expressions by using property names.
You can concatenate expressions to collect multiple criteria into one expression.

.Defining sort expressions
[source,java]
----
Sort sort = Sort.by("firstname").ascending()
  .and(Sort.by("lastname").descending());
----

For a more type-safe way to define sort expressions, start with the type for which to define the sort expression and use method references to define the properties on which to sort.

.Defining sort expressions by using the type-safe API
[source,java]
----
TypedSort<Person> person = Sort.sort(Person.class);

Sort sort = person.by(Person::getFirstname).ascending()
  .and(person.by(Person::getLastname).descending());
----

NOTE: `TypedSort.by(…)` makes use of runtime proxies by (typically) using CGlib, which may interfere with native image compilation when using tools such as Graal VM Native.

If your store implementation supports Querydsl, you can also use the generated metamodel types to define sort expressions:

.Defining sort expressions by using the Querydsl API
[source,java]
----
QSort sort = QSort.by(QPerson.firstname.asc())
  .and(QSort.by(QPerson.lastname.desc()));
----

ifdef::feature-scroll[]
endif::[]

[[repositories.limit-query-result]]
== Limiting Query Results

You can limit the results of query methods by using the `first` or `top` keywords, which you can use interchangeably.
You can append an optional numeric value to `top` or `first` to specify the maximum result size to be returned.
If the number is left out, a result size of 1 is assumed.
The following example shows how to limit the query size:

.Limiting the result size of a query with `Top` and `First`
[source,java]
----
User findFirstByOrderByLastnameAsc();

User findTopByOrderByAgeDesc();

Page<User> queryFirst10ByLastname(String lastname, Pageable pageable);

Slice<User> findTop3ByLastname(String lastname, Pageable pageable);

List<User> findFirst10ByLastname(String lastname, Sort sort);

List<User> findTop10ByLastname(String lastname, Pageable pageable);
----

The limiting expressions also support the `Distinct` keyword for datastores that support distinct queries.
Also, for the queries that limit the result set to one instance, wrapping the result into with the `Optional` keyword is supported.

If pagination or slicing is applied to a limiting query pagination (and the calculation of the number of available pages), it is applied within the limited result.

NOTE: Limiting the results in combination with dynamic sorting by using a `Sort` parameter lets you express query methods for the 'K' smallest as well as for the 'K' biggest elements.
