Pagination & Sorting

Returning every matching row is fine for ten records and disastrous for ten million. Pagination asks the database for one window of results at a time, and sorting decides the order of that window. Spring Data JPA expresses both with a single Pageable parameter, so you rarely write LIMIT/OFFSET by hand. This page covers building Pageable instances, the return types Page, Slice, and List, and the cost trade-offs between them.

Building a Pageable

A Pageable describes which page you want and how it is sorted. Create one with the PageRequest.of(...) factory — page numbers are zero-based, so page 0 is the first page.

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

// page 0, 20 rows per page, no sorting
Pageable firstPage = PageRequest.of(0, 20);

// page 2, 20 rows, sorted by name ascending
Pageable sorted = PageRequest.of(2, 20, Sort.by("name").ascending());

// multiple sort orders
Pageable multi = PageRequest.of(0, 20,
        Sort.by(Sort.Order.desc("price"), Sort.Order.asc("name")));

Sort.by("name") defaults to ascending; chain .descending() to flip it. Sort.Order lets you mix directions across several properties in one Sort.

Repository Methods

Add a Pageable parameter to any derived or custom query and choose the return type that fits your needs.

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

public interface ProductRepository extends JpaRepository<Product, Long> {

    Page<Product> findByCategory(String category, Pageable pageable);

    Slice<Product> findByActiveTrue(Pageable pageable);

    List<Product> findByBrand(String brand, Pageable pageable);
}

Page vs Slice vs List

The return type controls how much work the database does. The single most important distinction: Page issues an extra COUNT query to learn the total, Slice does not, and List returns only the window with no metadata at all.

Aspect	Page<T>	Slice<T>	List<T>
Extra COUNT query?	Yes	No	No
Knows total elements/pages?	Yes (getTotalElements())	No	No
Knows if more exist?	Yes (hasNext())	Yes (hasNext())	No
Relative cost	Highest	Lower	Lowest
Best for	UI showing “page 3 of 50”	Infinite scroll / “load more”	Simple capped fetch

Tip: Use Slice for endless-scroll feeds where you only need a “next” button. Reserve Page for classic numbered pagination where the user must see the total count, since the COUNT can be expensive on large tables.

Using It in a Service

A Page exposes both the content and rich metadata, so a controller or service can build a paginated response without further queries.

import lombok.RequiredArgsConstructor;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

@Service
@RequiredArgsConstructor
public class ProductService {

    private final ProductRepository productRepository;

    public Page<Product> listByCategory(String category, int page, int size) {
        var pageable = PageRequest.of(page, size, Sort.by("name").ascending());
        Page<Product> result = productRepository.findByCategory(category, pageable);

        result.getTotalElements();  // e.g. 137
        result.getTotalPages();     // e.g. 7
        result.getNumber();         // current page index
        result.hasNext();           // true
        result.getContent();        // the List<Product> window

        return result;
    }
}

Generated SQL

For a Page, Hibernate runs two statements: a limited SELECT for the window and a COUNT to compute the total.

-- the page window
select p.* from product p
where p.category = ?
order by p.name asc
limit 20 offset 40

-- the extra count query (Page only)
select count(p.id) from product p
where p.category = ?

A Slice or List runs only the first statement (the windowed SELECT); the COUNT is skipped entirely, which is why they are cheaper on big tables.

Note: A Slice fetches size + 1 rows internally to decide hasNext(), then hands you exactly size. That one extra row is far cheaper than a full COUNT over the table.

Common Pitfalls

• Treating page numbers as one-based — PageRequest.of(1, 20) is the second page, not the first.
• Sorting by a property that is not mapped or not whitelisted, especially when the sort string comes from user input — validate it to avoid errors or injection-style abuse.
• Choosing Page everywhere and paying for a COUNT on huge tables when the UI never shows a total.
• Forgetting that the offset grows with the page number; very deep pages (offset 1000000) are slow regardless of return type — consider keyset pagination instead.

The web/controller side — accepting page parameters and returning paged responses over HTTP — is covered in Pagination & Sorting (Web).

Related Topics

• Pagination & Sorting (Web) — exposing pageable endpoints in controllers.
• Derived Query Methods — adding Pageable to method-name queries.
• JPQL & Native Queries — paginating custom @Query methods.
• Projections — returning lightweight DTOs from paged queries.
• Java Optional — handling possibly-empty page content safely.