Spring Data JPA

Pagination & Sorting

18 min Lesson 7 of 13

Pagination & Sorting

When a table grows to thousands or millions of rows, loading every record into memory for a single request is both slow and wasteful. Spring Data JPA solves this cleanly with two abstractions — Pageable and Sort — and wraps the result in a rich Page<T> object that carries not only the data but also the navigation metadata your UI needs.

The Core Abstractions

Spring Data introduces three types that work together:

Sort — describes the ORDER BY clause: one or more properties each with a direction (ASC or DESC). Immutable and composable.
Pageable — combines a zero-based page number, a page size, and an optional Sort. The most common implementation is PageRequest.
Page<T> — the result of a paginated query. Extends Slice<T> and adds the total element and page counts, which requires a separate COUNT(*) query.

Page vs Slice: Page<T> always issues a second COUNT query so it knows the total number of records and pages. Slice<T> skips the count and only knows whether a next slice exists. For infinite-scroll or cursor-based UIs, Slice is cheaper. For a traditional paginator showing "Page 3 of 47", you need Page.

Enabling Pagination in a Repository

Add Pageable as a parameter to any repository method and return Page<T> or Slice<T>. Spring Data JPA handles the rest — it injects the LIMIT and OFFSET (or equivalent) and, for Page, a wrapping COUNT query.

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ProductRepository extends JpaRepository<Product, Long> {

    // All products, paged
    Page<Product> findAll(Pageable pageable);

    // Filtered + paged — derived query still accepts Pageable
    Page<Product> findByCategory(String category, Pageable pageable);
}

The JpaRepository base already provides findAll(Pageable), so you only need to declare it when you add filter parameters.

Building a PageRequest

Use the static factory methods on PageRequest:

import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;

// Page 0, 20 items per page, no sorting
Pageable first20 = PageRequest.of(0, 20);

// Page 2, 10 items, sorted by price ascending
Pageable page2ByPrice = PageRequest.of(2, 10, Sort.by("price"));

// Multiple sort columns: name ASC, then price DESC
Sort multiSort = Sort.by(Sort.Order.asc("name"),
                         Sort.Order.desc("price"));
Pageable complex = PageRequest.of(0, 15, multiSort);

Page numbers are zero-based. Page 0 is the first page. If your REST API exposes one-based pages to clients, subtract 1 before passing to PageRequest.of(): PageRequest.of(apiPage - 1, size).

Working with the Page Result

The Page<T> object contains everything a list UI needs:

Page<Product> result = productRepository.findByCategory("electronics", pageable);

List<Product>   items       = result.getContent();       // current page data
int              pageNum     = result.getNumber();         // 0-based current page
int              pageSize    = result.getSize();           // items per page
long             totalItems  = result.getTotalElements();  // total rows in DB
int              totalPages  = result.getTotalPages();     // ceil(total / size)
boolean          hasNext     = result.hasNextPage();
boolean          hasPrev     = result.hasPreviousPage();
boolean          isFirst     = result.isFirst();
boolean          isLast      = result.isLast();

In a Spring MVC or REST controller, you can expose this directly:

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/products")
public class ProductController {

    private final ProductRepository repo;

    public ProductController(ProductRepository repo) {
        this.repo = repo;
    }

    @GetMapping
    public Page<Product> list(
            @RequestParam(defaultValue = "0")  int page,
            @RequestParam(defaultValue = "20") int size,
            @RequestParam(defaultValue = "id") String sortBy) {

        Sort sort = Sort.by(Sort.Direction.ASC, sortBy);
        Pageable pageable = PageRequest.of(page, size, sort);
        return repo.findAll(pageable);
    }
}

Spring's Jackson serializer turns Page<T> into a JSON envelope with content, totalElements, totalPages, number, and more — exactly what a front end needs to render a paginator.

Sorting Without Pagination

Sometimes you want ordered results without a page limit. Pass a Sort object directly:

import org.springframework.data.domain.Sort;
import java.util.List;

List<Product> allByPrice = productRepository.findAll(
        Sort.by(Sort.Order.asc("price")));

List<Product> recent = productRepository.findByCategory(
        "books",
        Sort.by(Sort.Direction.DESC, "createdAt"));

Using @Query with Pagination

Custom JPQL queries also accept Pageable. Annotate the method with @Query and add a Pageable parameter as the last argument:

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query("SELECT p FROM Product p WHERE p.price < :maxPrice AND p.stock > 0")
    Page<Product> findAvailable(@Param("maxPrice") BigDecimal maxPrice,
                                 Pageable pageable);
}

Spring Data injects the ORDER BY and LIMIT from Pageable into the generated SQL automatically. For the count query it wraps your JPQL in a SELECT COUNT(p) unless you provide a custom countQuery attribute.

Avoid sorting by unindexed columns on large tables. A Sort.by("description") on a TEXT column with millions of rows will force a full filesort. Index the columns you sort on — especially createdAt and status fields that are common sort targets in list UIs.

Web MVC Auto-Binding with Pageable

Spring MVC can bind Pageable directly from request parameters if you add @EnableSpringDataWebSupport to your configuration (it is enabled automatically in Spring Boot):

// Request: GET /products?page=1&size=10&sort=price,desc&sort=name,asc
@GetMapping
public Page<Product> list(Pageable pageable) {
    return repo.findAll(pageable);
}

The sort parameter can appear multiple times for multi-column sorting. This lets the front end drive pagination and ordering without any boilerplate in the controller.

Performance Notes

OFFSET cost: LIMIT 20 OFFSET 10000 still reads and discards 10,000 rows in most databases. For very deep pages, consider keyset (cursor) pagination instead.
COUNT query: Every Page result fires two SQL queries. If the total count is expensive (e.g., joining several tables), switch to Slice and hide the total from users, or cache the count separately.
Fetch joins and pagination: Hibernate logs a warning when you mix a fetch join (which inflates rows) with Pageable. It performs pagination in memory rather than in SQL, which can be catastrophic on large datasets. Fix it with a two-query approach: paginate IDs first, then fetch the full entities by those IDs.

Summary

PageRequest.of(page, size, sort) is your entry point. Pass it to any repository method that returns Page<T> and you get back data, navigation state, and total counts in one object. Use Sort.by() alone when you need ordering without page limits. Watch the two performance traps — deep OFFSET cost and fetch-join conflicts — and you will have a scalable, maintainable data layer.