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.

Query Lookup Strategies

The following strategies are available for the repository infrastructure to resolve the query. With XML configuration, you can configure the strategy at the namespace through the query-lookup-strategy attribute. 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 “repositories.query-methods.query-creation”.

  • USE_DECLARED_QUERY tries to find a declared query and throws an exception if cannot find one. The query can be defined by an annotation somewhere or declared by other means. Consult 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 (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.

Query Creation

The query builder mechanism built into Spring Data repository infrastructure is useful for building constraining queries over entities of the repository. The mechanism strips the prefixes find…By, read…By, query…By, count…By, and get…By from the method and starts parsing the rest of it. The introducing clause can contain further expressions, such as a Distinct to set a distinct flag on the query to be created. However, the first By acts as delimiter to indicate the start of the actual criteria. At a very basic level, you can define conditions on entity properties and concatenate them with And and Or. The following example shows how to create a number of queries:

Example 1. Query creation from method names
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);
}

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 “repositories.special-parameters”.

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:

List<Person> findByAddressZipCode(ZipCode zipCode);

Assume a Person has an Address with a ZipCode. In that case, the method creates the property traversal x.address.zipCode. 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:

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

Special parameter handling

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 and Sort, to apply pagination and sorting to your queries dynamically. The following example demonstrates these features:

Example 2. Using Pageable, Slice, and Sort in query methods
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, Pageable pageable);
APIs taking Sort and Pageable expect non-null values to be handed into methods. If you don’t want to apply any sorting or pagination use Sort.unsorted() and Pageable.unpaged().

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 only knows 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 only need 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.

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.

Paging and Sorting

Simple sorting expressions can be defined by using property names. Expressions can be concatenated to collect multiple criterias into one expression.

Example 3. Defining sort expressions
Sort sort = Sort.by("firstname").ascending()
  .and(Sort.by("lastname").descending());

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

Example 4. Defining sort expressions using the type-safe API
TypedSort<Person> person = Sort.sort(Person.class);

TypedSort<Person> sort = person.by(Person::getFirstname).ascending()
  .and(person.by(Person::getLastname).descending());

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

Example 5. Defining sort expressions using the Querydsl API
QSort sort = QSort.by(QPerson.firstname.asc())
  .and(QSort.by(QPerson.lastname.desc()));

Limiting Query Results

The results of query methods can be limited by using the first or top keywords, which can be used interchangeably. An optional numeric value can be appended 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:

Example 6. Limiting the result size of a query with Top and First
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. Also, for the queries limiting 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 pages available), it is applied within the limited result.

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.

Repository Methods Returning Collections or Iterables

Query methods that return multiple results can use standard Java Iterable, List, Set. Beyond that we support returning Spring Data’s Streamable, a custom extension of Iterable, as well as collection types provided by Vavr.

Using Streamable as Query Method Return Type

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

Example 7. Using Streamable to combine query method results
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"));

Returning Custom Streamable Wrapper Types

Providing dedicated wrapper types for collections is a commonly used pattern to provide API on a query execution 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. That additional step can be avoided as Spring Data allows to use these wrapper types as query method return types if they meet the following criterias:

  1. The type implements Streamable.

  2. The type exposes either a constructor or a static factory method named of(…) or valueOf(…) taking Streamable as argument.

A sample use case looks as follows:

class Product { (1)
  MonetaryAmount getPrice() { … }
}

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

  private Streamable<Product> streamable;

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

interface ProductRepository implements Repository<Product, Long> {
  Products findAllByDescriptionContaining(String text); (4)
}
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 via Products.of(…) (factory method created via the Lombok annotation).
3 The wrapper type exposes additional API calculating new values on the Streamable<Product>.
4 That wrapper type can be used as query method return type directly. No need to return Stremable<Product> and manually wrap it in the repository client.

Support for Vavr Collections

Vavr is a library to embrace functional programming concepts in Java. It ships with a custom set of collection types that can be used as query method return types.

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

The types in the first column (or subtypes thereof) can be used as quer method return types and will get the types in the second column used as implementation type depending on the Java type of the actual query result (thrid column). Alternatively, Traversable (Vavr the Iterable equivalent) can be declared and we derive the implementation class from the actual return value, i.e. a java.util.List will be turned into a Vavr List/Seq, a java.util.Set becomes a Vavr LinkedHashSet/Set etc.

Null Handling of Repository Methods

As of Spring Data 2.0, repository CRUD methods that return an individual aggregate instance use Java 8’s Optional to indicate the potential absence of a value. Besides that, Spring Data supports returning the following wrapper types on query methods:

  • com.google.common.base.Optional

  • scala.Option

  • io.vavr.control.Option

Alternatively, query methods can choose not to use a wrapper type at all. The absence of a query result is then indicated by returning null. Repository methods returning collections, collection alternatives, wrappers, and streams are guaranteed never to return null but rather the corresponding empty representation. See “repository-query-return-types” for details.

Nullability Annotations

You can express nullability constraints for repository methods by using Spring Framework’s nullability annotations. They provide a tooling-friendly approach and opt-in null checks during runtime, as follows:

  • @NonNullApi: Used on the package level to declare that the default behavior for parameters and return values is to not accept or produce null values.

  • @NonNull: Used on a parameter or return value that must not be null (not needed on a parameter and return value where @NonNullApi applies).

  • @Nullable: Used on a parameter or return value that can be null.

Spring annotations are meta-annotated with JSR 305 annotations (a dormant but widely spread JSR). JSR 305 meta-annotations let tooling vendors such as IDEA, Eclipse, and Kotlin provide null-safety support in a generic way, without having to hard-code support for Spring annotations. To enable runtime checking of nullability constraints for query methods, you need to activate non-nullability on the package level by using Spring’s @NonNullApi in package-info.java, as shown in the following example:

Example 8. Declaring Non-nullability in package-info.java
@org.springframework.lang.NonNullApi
package com.acme;

Once non-null defaulting is in place, repository query method invocations get validated at runtime for nullability constraints. If a query execution result violates the defined constraint, an exception is thrown. This happens when the method would return null but is declared as non-nullable (the default with the annotation defined on the package the repository resides in). If you want to opt-in to nullable results again, selectively use @Nullable on individual methods. Using the result wrapper types mentioned at the start of this section continues to work as expected: An empty result is translated into the value that represents absence.

The following example shows a number of the techniques just described:

Example 9. Using different nullability constraints
package com.acme;                                                       (1)

import org.springframework.lang.Nullable;

interface UserRepository extends Repository<User, Long> {

  User getByEmailAddress(EmailAddress emailAddress);                    (2)

  @Nullable
  User findByEmailAddress(@Nullable EmailAddress emailAdress);          (3)

  Optional<User> findOptionalByEmailAddress(EmailAddress emailAddress); (4)
}
1 The repository resides in a package (or sub-package) for which we have defined non-null behavior.
2 Throws an EmptyResultDataAccessException when the query executed does not produce a result. Throws an IllegalArgumentException when the emailAddress handed to the method is null.
3 Returns null when the query executed does not produce a result. Also accepts null as the value for emailAddress.
4 Returns Optional.empty() when the query executed does not produce a result. Throws an IllegalArgumentException when the emailAddress handed to the method is null.

Nullability in Kotlin-based Repositories

Kotlin has the definition of nullability constraints baked into the language. Kotlin code compiles to bytecode, which does not express nullability constraints through method signatures but rather through compiled-in metadata. Make sure to include the kotlin-reflect JAR in your project to enable introspection of Kotlin’s nullability constraints. Spring Data repositories use the language mechanism to define those constraints to apply the same runtime checks, as follows:

Example 10. Using nullability constraints on Kotlin repositories
interface UserRepository : Repository<User, String> {

  fun findByUsername(username: String): User     (1)

  fun findByFirstname(firstname: String?): User? (2)
}
1 The method defines both the parameter and the result as non-nullable (the Kotlin default). The Kotlin compiler rejects method invocations that pass null to the method. If the query execution yields an empty result, an EmptyResultDataAccessException is thrown.
2 This method accepts null for the firstname parameter and returns null if the query execution does not produce a result.

Streaming query results

The results of query methods can be processed incrementally by using a Java 8 Stream<T> as 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:

Example 11. Stream the result of a query with Java 8 Stream<T>
@Query("select u from User u")
Stream<User> findAllByCustomQueryAndStream();

Stream<User> readAllByFirstnameNotNull();

@Query("select u from User u")
Stream<User> streamAllPaged(Pageable pageable);
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:
Example 12. Working with a Stream<T> result in a try-with-resources block
try (Stream<User> stream = repository.findAllByCustomQueryAndStream()) {
  stream.forEach(…);
}
Not all Spring Data modules currently support Stream<T> as a return type.

Async query results

Repository queries can be run asynchronously by using Spring’s asynchronous method execution capability. This means the method returns immediately upon invocation while the actual query execution occurs in a task that has been submitted to a Spring TaskExecutor. Asynchronous query execution is different from reactive query execution and should not be mixed. Refer to store-specific documentation for more details on reactive support. The following example shows a number of asynchronous queries:

@Async
Future<User> findByFirstname(String firstname);               (1)

@Async
CompletableFuture<User> findOneByFirstname(String firstname); (2)

@Async
ListenableFuture<User> findOneByLastname(String lastname);    (3)
1 Use java.util.concurrent.Future as the return type.
2 Use a Java 8 java.util.concurrent.CompletableFuture as the return type.
3 Use a org.springframework.util.concurrent.ListenableFuture as the return type.