InvalidDataAccessApiUsageException: Pageable unsupported for native @Query without LIMIT/OFFSET
0 reputation · 12 Nov 2025, 18:38 UTC
When executing a native SQL query annotated with @Query in Spring Data JPA, passing a Pageable without an explicit LIMIT/OFFSET clause triggers an InvalidDataAccessApiUsageException. This indicates that pagination is unsupported for that query.
The goal is to paginate large datasets efficiently while preventing accidental full result retrieval. Constraints include avoiding memory exhaustion, ensuring consistent behavior across repositories, and handling multi-table joins where total counts can be inaccurate. Additionally, the framework offers no built-in global maximum page size, requiring a consistent enforcement strategy.
What is the recommended approach to: 1) enforce a global maximum page size across all repository methods without custom resolvers? 2) correctly add LIMIT/OFFSET to native queries so that Pageable is honored? 3) mitigate off-by-one errors in total counts when using Page with multi-table joins?
spring