public Interface CursoredPage<T>

A page of data retrieved to satisfy a given page request, with a cursor for each result on the page. A repository method which returns the type CursoredPage uses cursor-based pagination to determine page boundaries.

Compared to offset-based pagination, cursor-based pagination reduces the possibility of missed or duplicate results when records are inserted, deleted, or updated in the database between page requests. Cursor-based pagination is possible when a query result set has a well-defined total order, that is, when the results are sorted by a list of entity fields which forms a unique key on the result set. This list of entity fields must be the entity fields of the combined sort criteria of the repository method, in the same order of precedence. This could just be the identifier field of the entity, or it might be some other combination of fields which uniquely identifies each query result.

When cursor-based pagination is used, a next page request is made relative to the last entity of the current page and a previous page request is made relative to the first entity of the current page. Alternatively, a page request might be made relative to an arbitrary starting point in the result set, that is, with an arbitrary value of the key.

The key for a given element of the result set is represented by an instance of Cursor.

To use cursor-based pagination, declare a repository method with return type CursoredPage and with a special parameter (after the normal query parameters) of type PageRequest, for example:

@OrderBy("lastName")
@OrderBy("firstName")
@OrderBy("id")
CursoredPage<Employee> findByHoursWorkedGreaterThan(int hours, PageRequest pageRequest);

In initial page may be requested using an offset-based page request:

page = employees.findByHoursWorkedGreaterThan(1500, PageRequest.ofSize(50));

The next page may be requested relative to the end of the current page, as follows:

page = employees.findByHoursWorkedGreaterThan(1500, page.nextPageRequest());

Here, the instance of PageRequest returned by CursoredPage#nextPageRequest() is based on a key value encapsulated in an instance of Cursor and identifying the last result on the current page.

A PageRequest based on an explicit cursor may be constructed by calling PageRequest#afterCursor(PageRequest.Cursor). The key component values of the cursor supplied to this method must match the list of sorting criteria specified by OrderBy annotations or OrderBy name pattern of the repository method and the Sort and Order parameters of the repository method. For example:

Employee emp = ...
PageRequest pageRequest =
        PageRequest.ofPage(5)
                   .size(50)
                   .afterCursor(Cursor.forKey(emp.lastName, emp.firstName, emp.id));
page = employees.findByHoursWorkedGreaterThan(1500, pageRequest);

By making the query for the next page relative to observed values, instead of to a numerical position, cursor-based pagination is less vulnerable to changes made to data in between page requests. Adding or removing entities is possible without causing unexpected missed or duplicate results. Cursor-based pagination does not prevent misses and duplicates if the entity properties which are the sort criteria for existing entities are modified or if an entity is re-added with different sort criteria after having previously been removed.

Cursor-based Pagination with @Query

Cursor-based pagination involves generating and appending additional restrictions involving the key fields to the WHERE clause of the query. For this to be possible, a user-provided JDQL or JPQL query must end with a WHERE clause to which additional conditions may be appended.

Sorting criteria must be specified independently of the user-provided query, either via the OrderBy annotation or, or by passing Sort or jakarta.data.Order. For example:

@Query("WHERE ordersPlaced >= ?1 OR totalSpent >= ?2")
@OrderBy("zipcode")
@OrderBy("birthYear")
@OrderBy("id")
CursoredPage<Customer> getTopBuyers(int minOrders, float minSpent,
                                    PageRequest pageRequest);

Only queries which return entities may be used with cursor-based pagination because cursors are created from the entity attribute values that form the unique key.

Page Numbers and Totals

Page numbers, total numbers of elements across all pages, and total count of pages are not accurate when cursor-based pagination is used and should not be relied upon.

Database Support for Cursor-based Pagination

A repository method with return type CursoredPage must raise UnsupportedOperationException if the database itself is not capable of cursor-based pagination.