Both pagination and sorting are essential features for controlling the amount of data returned by a query and how that data is organized. ProjectionQuery provides a fluent API to apply both features in a simple and expressive way.
For all the following examples, consider that we have a
Customerentity and an associated projectionCustomerProjection, as defined in the Definition of Projection section.
To apply pagination to a query, you can use the paging method of ProjectionQuery. The paging method takes two parameters: first, which indicates the index of the first record, and size, which indicates the number of records to be returned.
ProjectionQuery<Customer, CustomerProjection> query = ProjectionQuery
.fromTo(Customer.class, CustomerProjection.class)
.paging(10, 20);
In this example, paging(10, 20) returns up to 20 records starting from index 10 (indices 10 through 29, if available). first is a row offset, not a page number. For page index 2 with 20 rows per page, use paging(40, 20).
Apply a stable sort order when paging, including a unique field such as id to break ties. For count metadata, see paginated execution.
select
id,
name
from customer
limit 20 offset 10
Sorting can be applied using the order method of ProjectionQuery. The order method takes the property path and the sorting direction (OrderDirection.ASC or OrderDirection.DESC).
ProjectionQuery<Customer, CustomerProjection> query = ProjectionQuery
.fromTo(Customer.class, CustomerProjection.class)
.order("age", OrderDirection.DESC)
.order("name", OrderDirection.ASC);
In this example, the query will sort the results first by age in descending order and, in case of a tie, by name in ascending order. The SQL generated for this projection would look something like this:
select
id,
name,
age
from customer
order by
age desc,
name asc
← Previous: Filters and Specifications · ↑ Back to top · Next → Executing Queries