Answer to the Core Questions
Should Hibernate enforce a maximum default page size? No. Hibernate 6.x deliberately leaves setMaxResults() optional; queries without it return all rows that match the criteria, as dictated by the underlying database.
How to apply a global or per‑session limit without code changes? Use a Hibernate Integrator or event listener that inspects the QueryParameters and injects setMaxResults() when the value is missing or exceeds a chosen threshold.
Distinct + Pagination + Ordering behavior – When a query contains DISTINCT together with ORDER BY and pagination, Hibernate translates the query to SQL with DISTINCT applied first, followed by ORDER BY, and finally the database’s LIMIT/OFFSET. The returned page is only stable if the ORDER BY clause yields a deterministic ordering; non‑unique order columns can produce different rows on each page.
Confirmed Facts (Based on Hibernate 6.x Source & Documentation)
- Hibernate does not provide a configuration property such as
hibernate.default_max_results to set a default limit.
- Executing a query without
setMaxResults() produces SQL without a LIMIT or OFFSET clause; the JDBC driver returns all matching rows.
- Spring Data JPA’s
PageableDefault is a framework‑level solution, not a Hibernate core feature.
- When
DISTINCT is used, Hibernate translates to SQL that applies DISTINCT before ordering, and the database applies LIMIT/OFFSET afterward.
Recommended Steps for a Global Default Page Size
Create a custom Integrator that registers an Interceptor or EventListener:
public class MaxResultsIntegrator implements Integrator {
@Override
public void integrate(MetadataImplementor metadata, SessionFactoryImplementor sessionFactory, SessionFactoryServiceRegistry serviceRegistry) {
serviceRegistry.getService(Interceptor.class).addListener(new MaxResultsInterceptor());
}
// other methods omitted for brevity
}
public class MaxResultsInterceptor implements QueryParametersInterceptor {
private static final int DEFAULT_MAX = 200; // choose an appropriate value
@Override
public void beforeQuery(QueryParameters queryParameters) {
if (queryParameters.getMaxResults() == null) {
queryParameters.setMaxResults(DEFAULT_MAX);
}
}
}
Register the integrator in hibernate.integrator_provider or via META-INF/services/org.hibernate.integrator.spi.Integrator:
hibernate.integrator_provider=org.example.MaxResultsIntegratorProvider
Verify by enabling show_sql=true and executing a query without setMaxResults(); the generated SQL should now contain LIMIT 200 (or your chosen value).
Run regression tests for any queries that previously relied on unlimited fetches to confirm that truncation does not affect business logic.
If you need a per‑session override, inject a SessionEventListener that sets a different maxResults value for the current session context.
Handling DISTINCT with ORDER BY
To guarantee stable pagination when using DISTINCT, ensure that the ORDER BY clause references a column (or set of columns) that uniquely identifies each row in the result set. If the order is non‑deterministic, the subset returned by a given page may vary between executions, which can break pagination expectations.
Practical Verification Steps
- Turn on SQL logging:
hibernate.show_sql=true, hibernate.format_sql=true.
- Run a query without
setMaxResults() and confirm the absence of LIMIT in the log.
- Apply the interceptor, rerun the query, and check that
LIMIT 200 appears.
- Execute a
SELECT DISTINCT query with ORDER BY nonUniqueColumn, setFirstResult(0), setMaxResults(10), and observe that the 10 rows may differ if the order column is not unique.
Next Steps & Diagnostic Detail
If you require a different default limit for certain modules or environments, consider parameterizing the DEFAULT_MAX value via a system property or environment variable and reading it in the interceptor.
Do you need to enforce the limit only for particular query types (e.g., SELECT vs. UPDATE) or globally across all sessions? This detail will influence whether you use a session‑level or factory‑level interceptor.
hibernate
reflex