Pagination, Sorting and Projections in Spring Data JPA

Pagination, Sorting and Projections in Spring Data JPA

Most production screens display data in pages. You would never load ten thousand rows into memory just to show ten on screen. Spring Data JPA provides first-class support for pagination, sorting, and projections so you can fetch exactly what you need efficiently.

How Pagination Works

Pagination requires two things from the database: a subset of rows for the current page and the total row count for the UI. Spring Data JPA handles both with a single method call by accepting a Pageable parameter and returning a Page object:

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> {

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

Calling repository.findByCategory("electronics", PageRequest.of(0, 10)) fetches the first 10 products in the "electronics" category. The returned Page object contains the items, total count, current page number, and total pages - everything the UI needs.

Sorting

You can sort by adding a Sort to the Pageable. Spring Data JPA translates it to an ORDER BY clause:

// Sort by price descending, then name ascending
Pageable sorted = PageRequest.of(0, 10,
    Sort.by(Sort.Direction.DESC, "price")
        .and(Sort.by(Sort.Direction.ASC, "name"))
);
Page<Product> results = repository.findByCategory("electronics", sorted);

The generated SQL adds ORDER BY price DESC, name ASC and uses the same query for both the data fetch and the count. Spring Data also supports derived sort methods like findByNameContainingOrderByNameAsc defined directly on the repository interface.

Projections: Fetching Only What You Need

Projections let you retrieve a subset of columns instead of loading the entire entity. Define an interface with only the getter methods you want and Spring Data JPA generates a query that selects only those columns:

public interface ProductSummary {
    String getName();
    double getPrice();
}

public interface ProductRepository extends JpaRepository<Product, Long> {

    List<ProductSummary> findByCategory(String category);
}

The generated SQL becomes SELECT name, price FROM products WHERE category = ? instead of selecting every column. This reduces memory usage and network transfer, especially for large tables. You can combine projections with pagination by returning Page<ProductSummary> instead of List<ProductSummary>.

Native Pagination

For complex queries, you can use @Query with native SQL and still paginate by passing a Pageable. Spring Data JPA will execute a separate count query automatically:

@Query(value = "SELECT p.name, p.price FROM products p WHERE p.category = :cat",
       countQuery = "SELECT COUNT(*) FROM products WHERE category = :cat",
       nativeQuery = true)
Page<ProductSummary> findSummaryByCategory(@Param("cat") String category, Pageable pageable);

Key Points

• Use Pageable and Page to paginate results with total count included.
• Sort.by() lets you define multi-column sort directions programmatically.
• Interface projections select only the columns you need, reducing memory and I/O.
• Projections combine with pagination via Page<Projection> return types.
• Native queries can paginate too - provide a separate countQuery for accurate totals.