[[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[]
include::repositories-scrolling.adoc[]
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.
