repositories-paging-sorting.adoc

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:

Example 1. Using Pageable, Slice, Sort and Limit 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, Sort sort, Limit limit);

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

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 of the special parameters described above are mutually exclusive. Please consider the following list of invalid parameter combinations. Parameters Example Reason Pageable & Sort findBy…​(Pageable page, Sort sort) Pageable already defines Sort Pageable & 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 this number.

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

Table 1. Consuming Large Query Results

Method	Amount of Data Fetched	Query Structure	Constraints
List<T>	All results.	Single query.	Query results can exhaust all memory. Fetching all data can be time-intensive.
Streamable<T>	All results.	Single query.	Query results can exhaust all memory. Fetching all data can be time-intensive.
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 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. 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.	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.

Paging and Sorting

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

Example 2. Defining sort expressions

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.

Example 3. Defining sort expressions by using the type-safe API

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:

Example 4. Defining sort expressions by using the Querydsl API

QSort sort = QSort.by(QPerson.firstname.asc())
  .and(QSort.by(QPerson.lastname.desc()));

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:

Example 5. 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 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.