Implementing Paginated Custom Queries in Spring Data JPA

The Problem: Incorrect Totals and Performance Risks in Custom Paging

When using Spring Data JPA's derived query methods (e.g., findByLastName), pagination is handled automatically. However, when you move to custom queries using @Query, Spring cannot always automatically derive the count query needed to calculate the total number of pages. This often results in incorrect total element counts or runtime exceptions when using complex joins or native SQL.

The Takeaway: To implement reliable pagination with custom queries, you must return a Page<T>, pass a Pageable parameter, and explicitly define a countQuery to ensure the total record count is calculated accurately without executing the full data result set.

Prerequisites

• Spring Boot 2.7+ or 3.x with spring-boot-starter-data-jpa.
• A JPA entity (e.g., Product) mapped to a database table.
• A repository interface extending JpaRepository or PagingAndSortingRepository.

Implementing the Paginated Repository

To enable pagination for a custom JPQL or native query, define a method that accepts Pageable and returns Page<T>. Use the countQuery attribute within the @Query annotation to provide the logic for calculating total records.

package com.example.demo.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import com.example.demo.entity.Product;

public interface ProductRepository extends JpaRepository {

    // JPQL Example: Returns a Page of Products based on a name pattern
    @Query(
        value = "SELECT p FROM Product p WHERE p.name LIKE %?1%",
        countQuery = "SELECT COUNT(p) FROM Product p WHERE p.name LIKE %?1%"
    )
    Page findByNamePattern(String namePattern, Pageable pageable);

    // Native SQL Example: Requires native = true and column-based countQuery
    @Query(
        value = "SELECT * FROM products WHERE status = ?1",
        countQuery = "SELECT count(*) FROM products WHERE status = ?1",
        nativeQuery = true
    )
    Page findByStatusNative(String status, Pageable pageable);
}

Handling Sort Properties

When creating a Pageable object, the Sort properties must match the entity attribute names for JPQL or the actual database column names for native queries. Using a property that does not exist in the entity will trigger an InvalidDataAccessApiUsageException.

Service Layer Implementation and Safety

Allowing clients to specify arbitrary page sizes can lead to memory exhaustion (Out Of Memory errors) if a user requests 1,000,000 records per page. Always enforce a maximum page size in the service layer.

package com.example.demo.service;

import org.springframework.data.domain.*;
import org.springframework.stereotype.Service;
import com.example.demo.repository.ProductRepository;
import com.example.demo.entity.Product;
import java.util.Page;

@Service
public class ProductService {
    private static final int MAX_PAGE_SIZE = 100;
    private final ProductRepository productRepository;

    public ProductService(ProductRepository productRepository) {
        this.productRepository = productRepository;
    }

    public Page getProducts(String pattern, int page, int size, String sortField) {
        // Guard against abusive page sizes
        int safeSize = Math.min(size, MAX_PAGE_SIZE);
        
        Pageable pageable = PageRequest.of(page, safeSize, Sort.by(sortField));
        return productRepository.findByNamePattern(pattern, pageable);
    }
}

Verification and Diagnostics

To verify that pagination is working as intended, you should check both the data slice and the count query execution.

1. SQL Log Verification

Enable Hibernate SQL logging in application.properties to ensure two queries are being fired: one for the data (containing LIMIT and OFFSET) and one for the count.

logging.level.org.hibernate.SQL=DEBUG

2. Functional Testing

Use @DataJpaTest to validate the repository behavior. Ensure that:

• page.getContent().size() matches the requested size (or total available records if less).
• page.getTotalElements() matches the actual number of records in the database matching the criteria.

Limitations and Recovery

Common Failure Points

Issue	Cause	Recovery Action
InvalidDataAccessApiUsageException	Sort property doesn't match entity field.	Verify Sort.by("fieldName") matches the Java entity attribute.
Incorrect Total Count	countQuery is missing or logically different from the main query.	Ensure countQuery mirrors the WHERE clause of the main query.
Performance Degradation	Large offsets in deep pagination.	Implement keyset pagination (seek method) for very large datasets.

Rollback Procedure

If a custom @Query causes instability, revert the method to a Spring Data derived query (e.g., findByNameContaining(String name, Pageable pageable)). This removes the need for a manual countQuery and allows Spring to generate the optimal SQL automatically.