Skills101

AdvancedAdvanced Java & Spring Boot · Lesson 19 of 20 · Updated ·

Pagination and Sorting in Spring Boot — Pageable, Page, Sort and Keyset Pagination

Our course API's GET /api/courses returns every course. With 20 courses that is fine. With 50,000 it means a slow SQL query, a lot of memory on the server, and a huge JSON response that the phone app then has to download and display. Real APIs return data one page at a time: "give me 10 courses, sorted by fee, starting from page 3". This lesson covers the maths, the Spring Data tools, and the mistakes that make pages skip or repeat rows.

1. Page, size and offset

Offset pagination uses two numbers from the client:

  • size — how many items per page;
  • page — which page, counted from 0 in Spring Data.

From these, the server computes the offset — how many rows to skip — and asks the database for one slice: ... ORDER BY ... LIMIT size OFFSET offset (or the database's equivalent syntax).

FormulaMeaning
offset = page × sizeRows to skip
totalPages = ceil(totalElements / size)Number of pages
hasNext = page + 1 < totalPagesIs there another page?

Here is that arithmetic in plain Java, on a list of 23 course ids with size = 5:

// Not compiled: not part of the course projects — a plain-Java demo you can run as Main.java
import java.util.*;

public class Main {
    static <T> List<T> page(List<T> all, int page, int size) {
        int from = Math.min(page * size, all.size());
        int to = Math.min(from + size, all.size());
        return all.subList(from, to);
    }

    public static void main(String[] args) {
        List<Integer> ids = new ArrayList<>();
        for (int i = 1; i <= 23; i++) ids.add(i);
        int size = 5;
        int totalPages = (int) Math.ceil(ids.size() / (double) size);
        System.out.println("total=" + ids.size() + ", size=" + size + ", totalPages=" + totalPages);
        for (int p : new int[]{0, 1, 4, 5}) {
            boolean hasNext = p + 1 < totalPages;
            System.out.println("page " + p + " (offset " + p * size + "): " + page(ids, p, size) + " hasNext=" + hasNext);
        }
    }
}
total=23, size=5, totalPages=5
page 0 (offset 0): [1, 2, 3, 4, 5] hasNext=true
page 1 (offset 5): [6, 7, 8, 9, 10] hasNext=true
page 4 (offset 20): [21, 22, 23] hasNext=false
page 5 (offset 25): [] hasNext=false

The last page (page 4) is short, and a page past the end (page 5) is simply empty — not an error. Spring Data behaves the same way.

23 courses split into pages of 5: page 0 holds 1 to 5 at offset 0, up to page 4 holding 21 to 23 at offset 20; totalPages is ceil(23/5) = 5

2. Pageable, Page and Sort in Spring Data

Spring Data JPA does this arithmetic and the SQL for you. The three types to know:

TypeWhat it is
PageableThe request: page number, size and sort. Create one with PageRequest.of(page, size, sort)
SortThe order: Sort.by("title"), Sort.by(Sort.Direction.DESC, "fee")
Page<T>The answer: getContent(), getTotalElements(), getTotalPages(), getNumber(), hasNext()

JpaRepository already has findAll(Pageable). Derived query methods can take a Pageable too:

// Not compiled: a short sketch — the course projects do not page their results
public interface CourseRepository extends JpaRepository<Course, Long> {
    Page<Course> findByTitleContainingIgnoreCase(String text, Pageable pageable);
}

// in CourseService
public Page<CourseResponse> list(int page, int size) {
    Sort order = Sort.by(Sort.Direction.DESC, "fee").and(Sort.by("id"));   // id = tie-breaker
    Page<Course> courses = repository.findAll(PageRequest.of(page, size, order));
    return courses.map(CourseResponse::from);                             // entity -> DTO
}

To fill a Page, Hibernate runs two queries: one for the rows of this page, and a COUNT(*) query for the total. If the client only needs "is there more?" (for example, infinite scrolling in an app), return a Slice<T> instead. A slice skips the count query: it asks for size + 1 rows and uses the extra one only to know whether a next slice exists.

3. A paged REST endpoint

The controller reads page and size from the query string — GET /api/courses?page=2&size=10 — with safe defaults and a maximum size:

// Not compiled: a short sketch of a paged endpoint
public record PageResponse<T>(List<T> content, int page, int size, long totalElements, int totalPages) {
    static <T> PageResponse<T> of(Page<T> p) {
        return new PageResponse<>(p.getContent(), p.getNumber(), p.getSize(), p.getTotalElements(), p.getTotalPages());
    }
}

@GetMapping
public PageResponse<CourseResponse> list(@RequestParam(defaultValue = "0") int page,
                                         @RequestParam(defaultValue = "10") int size) {
    int safeSize = Math.min(Math.max(size, 1), 100);      // never let a client ask for 1,000,000 rows
    return PageResponse.of(courseService.list(Math.max(page, 0), safeSize));
}

The response then looks like this:

{
  "content": [ { "id": 7, "title": "Spring Boot", "fee": 4999, "seats": 40 } ],
  "page": 2,
  "size": 10,
  "totalElements": 21,
  "totalPages": 3
}

Why a small PageResponse record instead of returning Page directly? The JSON of Spring's own PageImpl class contains many internal fields and is not a stable format — recent Spring Data versions log a warning when you serialize it as it is. Your own DTO keeps the API contract under your control, just like the entity-to-DTO rule from the REST lesson.

Spring MVC can also build a Pageable from the request for you (a controller parameter of type Pageable reads ?page=0&size=10&sort=fee,desc). It is convenient, but then validate the sort field: a client could otherwise sort on any property name, including ones that are not indexed or should not be exposed. A small whitelist such as Set.of("title", "fee") is enough.

4. Sorting needs a tie-breaker

Suppose 12 courses all cost ₹4,999 and you sort by fee only. The database is free to return those 12 rows in any order — and the order can differ between the query for page 0 and the query for page 1. The result: a course appears on two pages, and another course appears on none.

The fix is simple: always end the sort with a unique column, usually the id: Sort.by(DESC, "fee").and(Sort.by("id")). Now every row has exactly one position, so pages never overlap.

5. Deep pages: keyset pagination

Offset pagination has a hidden cost. For OFFSET 10000 LIMIT 10, the database must still walk past 10,000 rows and throw them away. Page 1,000 is much slower than page 1.

Keyset pagination (also called seek pagination) remembers the last row the client saw and asks for rows after it: WHERE id > 10000 ORDER BY id LIMIT 10. With an index on id, the database jumps straight to the right place, so every page is equally fast. It also does not skip or repeat rows when new rows are inserted while the user is scrolling.

OFFSET 10000 LIMIT 10 reads and discards ten thousand rows before returning ten; WHERE id > 10000 LIMIT 10 uses the index to jump straight to row 10001

The trade-off: with keyset pagination you cannot jump directly to "page 57" — you can only go to the next (or previous) page from where you are. That is perfect for feeds and infinite scroll, and less good for a numbered page bar. Spring Data supports it through its scrolling API (Window<T> results with a ScrollPosition).

Offset (Page)Keyset
Jump to any page numberYesNo
Speed on deep pagesGets slowerStays fast
Rows inserted while browsingCan cause skips/repeatsStable
Typical useAdmin tables with page numbersFeeds, infinite scroll, exports

Common mistakes

  • Assuming pages start at 1. In Spring Data, PageRequest.of(1, 10) is the second page.
  • No maximum page size, so a client can request size=1000000 and load the whole table.
  • Sorting only on a non-unique column, so rows jump between pages.
  • Returning Page<Course> entities from the controller instead of a DTO.
  • Using Page (with its extra COUNT(*) query) on a huge table when the client only needs "load more".
  • Passing a client's sort parameter straight into the query without checking the property name.

Quick recap

  • Offset pagination: offset = page × size, totalPages = ceil(total / size); pages start at 0.
  • PageRequest.of(page, size, Sort...) → Page<T> with content and totals; Slice<T> skips the count query.
  • Return your own page DTO, cap the page size, and whitelist sort fields.
  • Always end a sort with a unique column such as id.
  • For deep pages and feeds, keyset pagination (WHERE id > last) stays fast.

Practice what you learned

📝 Quiz: Pagination and Sorting5 questions · instant answers & explanations · +10 XP · counts towards your free certificateTake the quiz →