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

Source: https://skills101.in/learn/spring-boot/spring-boot-pagination-sorting — Skills101 free tutorial (Advanced Java & Spring Boot). Updated 2026-11-21.

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).

| Formula | Meaning |
|---|---|
| `offset = page × size` | Rows to skip |
| `totalPages = ceil(totalElements / size)` | Number of pages |
| `hasNext = page + 1 < totalPages` | Is there another page? |

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

```java
// 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);
        }
    }
}
```

```text
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](https://skills101.in/learn-img/spring-boot/page-math.svg)

## 2. Pageable, Page and Sort in Spring Data

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

| Type | What it is |
|---|---|
| `Pageable` | The request: page number, size and sort. Create one with `PageRequest.of(page, size, sort)` |
| `Sort` | The 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:

```java
// 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:

```java
// 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:

```json
{
  "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](https://skills101.in/learn-img/spring-boot/offset-vs-keyset.svg)

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 number | Yes | No |
| Speed on deep pages | Gets slower | Stays fast |
| Rows inserted while browsing | Can cause skips/repeats | Stable |
| Typical use | Admin tables with page numbers | Feeds, 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 quiz and free certificate: https://skills101.in/learn/spring-boot/spring-boot-pagination-sorting