Pagination & Sorting

#Introduction

#Why Pagination?

With 5 million employees:

List<Employee> employees = repository.findAll();   // ❌

loads every row into memory — high memory use, slow responses, heavy network traffic and a poor user experience. Return one page at a time instead:

#Pageable and PageRequest

A Pageable describes which page, how big, and how to sort:

Pageable first = PageRequest.of(0, 10);                       // page 0, 10 rows
Pageable fourth = PageRequest.of(3, 50);                      // 4th page, 50 rows
Pageable sorted = PageRequest.of(0, 10, Sort.by("salary").descending());

JpaRepository already provides:

Page<Employee> findAll(Pageable pageable);

#The Page Object

Page<Employee> page = repository.findAll(PageRequest.of(0, 5));

#The SQL Behind a Page

select … from employee e1_0 limit ?, ?          -- offset 0, 5 rows
select count(e1_0.id) from employee e1_0        -- for total pages and elements

Offset = page number × page size.

A Page runs two queries: the content query, then a count query. Spring Data skips the count when it can work out the total itself — for example, the first page returned fewer rows than the page size.

#Pagination with Query Methods

Page<Employee> findByDepartment(String department, Pageable pageable);

Page<Employee> page = repository.findByDepartment("IT", PageRequest.of(0, 10));
// WHERE department = ? LIMIT 10 OFFSET 0

Any derived or @Query method can take a Pageable as its last parameter.

#Sorting

repository.findAll(Sort.by("salary"));                     // ORDER BY salary ASC
repository.findAll(Sort.by("salary").descending());        // ORDER BY salary DESC

Sort sort = Sort.by("department").and(Sort.by("salary").descending());
// ORDER BY department ASC, salary DESC

Sort typeSafe = Sort.sort(Employee.class).by(Employee::getSalary).descending();   // refactoring-safe

Sorting and paging together:

Pageable pageable = PageRequest.of(0, 10, Sort.by("salary").descending());
// ORDER BY salary DESC LIMIT 10

#Paginated REST Endpoints

Accept a Pageable parameter and Spring MVC builds it from the query string:

@GetMapping("/employees")
public Page<EmployeeDto> list(@PageableDefault(size = 20, sort = "id") Pageable pageable) {
    return repository.findAll(pageable).map(EmployeeDto::from);
}

GET /employees?page=0&size=20
GET /employees?page=2&size=20&sort=salary,desc
GET /employees?sort=department&sort=salary,desc

#Page vs Slice

Slice<Employee> findByDepartment(String department, Pageable pageable);

Social feeds (Instagram, LinkedIn) don't show "page 3 of 1,200" — they only need "is there more?", so Slice fits.

#Deep Pages Are Slow — Keyset Pagination

SELECT … FROM employee ORDER BY id LIMIT 20 OFFSET 100000;

The database must read and skip 100,000 rows to return 20. Keyset (seek) pagination continues from the last row seen instead:

SELECT … FROM employee WHERE id > :lastSeenId ORDER BY id LIMIT 20;

List<Employee> findTop20ByIdGreaterThanOrderByIdAsc(Integer lastSeenId);

Spring Data's Scrolling API

Spring Data 3.1+ supports keyset pagination directly with Window and ScrollPosition:

Window<Employee> findTop20ByDepartmentOrderByIdAsc(String department, ScrollPosition position);

Window<Employee> window = repository.findTop20ByDepartmentOrderByIdAsc("IT", ScrollPosition.keyset());
while (!window.isEmpty()) {
    window.forEach(this::process);
    if (!window.hasNext()) break;
    window = repository.findTop20ByDepartmentOrderByIdAsc("IT", window.positionAt(window.size() - 1));
}

Great for feeds, exports, batch jobs and very large tables.

#Performance Tips

• Keep page sizes reasonable — 10, 20, 25, 50 or 100; never PageRequest.of(0, 100000).
• Always sort, with a unique tie-breaker — Sort.by("createdDate").descending().and(Sort.by("id")). Without a deterministic order, rows can repeat or vanish between pages.
• Index columns used for filtering and sorting.
• Use Slice when you don't need totals.
• Provide a cheaper countQuery for complex @Query methods returning Page.
• Map to DTOs so pages carry only the fields the client needs.

#A Real Example

Page<Order> orders = orderRepository.findByStatus(
        OrderStatus.SHIPPED,
        PageRequest.of(0, 25, Sort.by("createdDate").descending().and(Sort.by("id"))));

Latest shipped orders, 25 per page, with total pages and records, using efficient LIMIT/OFFSET SQL.

#Key Takeaways

• Use Pageable/PageRequest (0-based) instead of loading everything.
• Page = content + totals (two queries); Slice = content + "has next".
• Always sort deterministically; index sort and filter columns.
• Accept Pageable in controllers, return DTO pages, and cap the page size.
• Use keyset pagination (ScrollPosition) for deep or continuously growing data.