InvalidDataAccessApiUsageException: Pageable unsupported for native @Query without LIMIT/OFFSET

Resolving the Pageable Native Query Exception

The InvalidDataAccessApiUsageException occurs because Spring Data JPA cannot automatically append pagination dialect logic (like LIMIT and OFFSET) to a native SQL string. Unlike JPQL, native queries are passed directly to the database; the framework requires you to explicitly define how the total count is calculated to support the Page<T> return type.

1. Enforcing a Global Maximum Page Size

Since Spring Data JPA does not provide a global configuration property to cap page sizes, the most reliable approach without custom resolvers is to enforce the limit at the Service Layer. This ensures that regardless of the Pageable object passed from the controller, the repository never receives a request for an unsafe number of records.

public Page<User> getUsers(Pageable pageable) {
    int maxPageSize = 100;
    int size = Math.min(pageable.getPageSize(), maxPageSize);
    Pageable cappedPageable = PageRequest.of(pageable.getPageNumber(), size, pageable.getSort());
    return userRepository.findUsersNative(cappedPageable);
}

2. Implementing Pageable in Native Queries

To make Pageable honored in a native query, you must provide a countQuery attribute within the @Query annotation. Spring uses this second query to determine the total number of elements, which allows it to calculate the total pages and content slice.

@Query(value = "SELECT * FROM users WHERE status = :status", 
       countQuery = "SELECT count(*) FROM users WHERE status = :status", 
       nativeQuery = true)
Page<User> findUsersNative(@Param("status") String status, Pageable pageable);

Note: You do not manually write LIMIT or OFFSET in the SQL string itself. When a countQuery is provided, Spring Data JPA appends the database-specific pagination syntax to the main query automatically based on the Pageable parameters.

3. Mitigating Count Errors in Multi-Table Joins

When using joins, a standard count(*) often returns the number of joined rows rather than the number of root entities, leading to inflated total counts and off-by-one errors in pagination metadata. To fix this, use COUNT(DISTINCT ... ) on the primary key of the root entity in your countQuery.

• Incorrect: SELECT count(*) FROM users u JOIN orders o ON u.id = o.user_id
• Correct: SELECT count(DISTINCT u.id) FROM users u JOIN orders o ON u.id = o.user_id

Verification Steps

To ensure the implementation is correct, perform the following checks:

1. Enable SQL logging (logging.level.org.hibernate.SQL=DEBUG) to verify that two queries are executed: one for the count and one containing the LIMIT/OFFSET clause.
2. Compare the totalElements value of the returned Page object against a manual SELECT COUNT(DISTINCT id) executed directly in your database console.

Diagnostic Detail Required: Which database dialect (e.g., PostgreSQL, Oracle, SQL Server) are you using? Some older versions of specific dialects handle offset pagination differently, which may require a manual LIMIT implementation if the automatic append fails.