Pageable and Sort

Never Load the Whole Table

A store may have 2 lakh products. Loading them all to show 20 wastes memory and time. Spring Data adds pagination to any repository method that takes a Pageable parameter; Hibernate turns it into LIMIT/OFFSET.

public interface ProductRepository extends JpaRepository<Product, Long> {
    Page<Product> findByCategoryName(String category, Pageable pageable);
}

Pageable pageable = PageRequest.of(2, 20, Sort.by("price").descending().and(Sort.by("id")));
Page<Product> page = productRepository.findByCategoryName("Books", pageable);

-- page index 2, size 20 -> skip 40 rows (MySQL)
select p1_0.id,p1_0.category_id,p1_0.description,p1_0.name,p1_0.price,p1_0.stock
from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id
where c1_0.name=?
order by p1_0.price desc,p1_0.id
limit ?,?
-- count query for totalElements
select count(p1_0.id) from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=?

Page Math

Example

Page numbers in Spring Data start at 0. Here is how offset, total pages and the last page are calculated for 53 products, 10 per page.

public class Main {
    public static void main(String[] args) {
        int total = 53, size = 10;
        int totalPages = (total + size - 1) / size;
        System.out.println("totalElements=" + total + ", size=" + size + ", totalPages=" + totalPages);
        for (int page = 0; page < totalPages; page++) {
            int offset = page * size;
            int rows = Math.min(size, total - offset);
            boolean first = page == 0, last = page == totalPages - 1;
            System.out.printf("page %d -> limit %d,%d  rows %2d  first=%-5s last=%-5s%n",
                    page, offset, size, rows, first, last);
        }
    }
}

Output

totalElements=53, size=10, totalPages=6
page 0 -> limit 0,10  rows 10  first=true  last=false
page 1 -> limit 10,10  rows 10  first=false last=false
page 2 -> limit 20,10  rows 10  first=false last=false
page 3 -> limit 30,10  rows 10  first=false last=false
page 4 -> limit 40,10  rows 10  first=false last=false
page 5 -> limit 50,10  rows  3  first=false last=true

Sorting

Sort.by("price"), Sort.by(Sort.Direction.DESC, "createdAt"), Sort.by("category.name") (sort by a joined field).
Sort without paging: List<Product> findByStockGreaterThan(int s, Sort sort).
Typed: Sort.sort(Product.class).by(Product::getPrice).descending().
Always add a unique tie-breaker (e.g. id). With only price, products with equal prices can appear on two pages or on none.

Note: Sort properties coming from the URL are validated against the entity, so ?sort=password on a non-existing field fails with PropertyReferenceException. Still, allow only a whitelist of sortable fields in public APIs.