Paging and sorting
A Pageable asks for one window of results (page number, page size, order); a Sort asks for an order alone. Give either as the last parameter of a derived query or a @Query method, or call findAllSorted and findAllPaged. A Page answer knows the total; a Slice only knows whether another page follows.
Why
A list screen or an API never loads a whole table. It loads 20 rows, in an order the user chose, and needs to know whether there are more. Writing LIMIT, OFFSET, ORDER BY and a count by hand for every finder is repetitive and easy to get wrong, and an order taken from a request is an injection risk when pasted into SQL. With Pageable and Sort the store writes all of that, and sort properties are checked against the entity.
Example
abstract interface class BookRepository
implements
ListCrudRepository<Book, int>,
ListPagingAndSortingRepository<Book, int> {
Future<Page<Book>> findByYearAfter(int year, Pageable pageable);
Future<Slice<Book>> findByInStockIsTrue(Pageable pageable);
Future<List<Book>> findByInStock(bool inStock, Sort sort);
}
// Page 0, 20 per page, newest first, then by title.
final request = PageRequest.of(
0,
20,
Sort.byOrders([const SortOrder.desc('year'), const SortOrder.asc('title')]),
);
final page = await books.findByYearAfter(1950, request);
print('${page.numberOfElements} of ${page.totalElements}, page ${page.number + 1} of ${page.totalPages}');
if (page.hasNext) {
final next = await books.findByYearAfter(1950, page.nextPageable());
}
final all = await books.findAllSorted(Sort.by(['title']));
final first = await books.findAllPaged(PageRequest.of(0, 10));
Pageable and PageRequest
| Member | Meaning |
|---|---|
PageRequest.of(page, size, [sort]) |
Page page (from 0) of size items, ordered by sort. |
Pageable.unpaged([sort]) |
Every result, maybe sorted. |
pageNumber, pageSize, offset, sort |
The request (an unpaged request has no number, size or offset). |
next(), previousOrFirst(), first(), withPage(n), hasPrevious |
Other pages. |
PageRequest.of refuses a negative page, a size below one, and a page and size whose offset would pass 2^53 - 1: an InvalidPagingRequestException (an ArgumentError). So an offset never wraps around to another page. A Pageable of your own with a negative offset is refused by the stores.
Sort and SortOrder
| Member | Meaning |
|---|---|
Sort.by(['title', 'year']) |
Ascending by each property, in order. |
Sort.byOrders([SortOrder.desc('year'), ...]) |
Each order as given. |
Sort.unsorted |
No order. |
sort.and(other), descending(), ascending() |
Combine or flip. |
SortOrder.asc(p), SortOrder.desc(p), SortOrder(p, direction:) |
One step. |
order.ignoringCase() |
Compare in lower case. |
order.withNullHandling(NullHandling.nullsLast) |
native (the store's choice), nullsFirst, nullsLast. |
Direction.fromString('desc') |
asc or desc, case ignored. |
A sort step is SortOrder here, Spring's Sort.Order: @Order orders beans.
Page and Slice
| Member | Slice |
Page |
|---|---|---|
content, hasContent, numberOfElements |
yes | yes |
number, size, sort |
yes | yes |
isFirst, isLast, hasNext, hasPrevious |
yes | yes |
nextPageable(), previousPageable() |
yes | yes |
map((item) => ...) |
yes | yes |
totalElements, totalPages |
no | yes |
- A
Pageruns a second query, a count. For a@Querymethod returning aPage, give its count statement incountQuery. - A
Slicereads one more row than the page size to know whether another follows, and runs no count. Prefer it for "load more" lists over big tables. Page.empty([pageable])is an empty page.
The order of the results
The order is the name's OrderBy first, then the Sort or the Pageable's sort. Without any order, the store's order is not guaranteed: page through a table only with a sort.
Input from requests
A Sort or Pageable built from request parameters never reaches the SQL as text:
- sort properties are entity properties, written as their columns; for a
@Query, identifiers, quoted (a.b); - directions are only
ascanddesc; - a
@Querythat returns an entity checks the name against the entity's properties and columns, and one that returns a class against that class's properties (authorNamesorts byauthor_name) and the entity's; - a property that does not exist is a
PropertyReferenceExceptionwhose message writes the name escaped, so a name with a line break cannot forge a log line.
Two rules remain yours, because only you know them:
- cap the size. A client that sends
size=1000000000gets the whole table. The web glue caps it atspring.data.web.pageable.max-page-size(see In a web application); - allow-list the sort properties. A sort is an
ORDER BYoracle: a client can learn the order of values it cannot read, such as a password hash.
In Spring (Java)
Page<Book> findByYearAfter(int year, Pageable pageable);
PageRequest.of(0, 20, Sort.by(Sort.Order.desc("year"), Sort.Order.asc("title")));
repository.findAll(Sort.by("title"));
The names are Spring Data's, except SortOrder (Spring's Sort.Order), findAllSorted and findAllPaged (Spring's overloaded findAll), and Sort.by taking a list.