Hibernate Pagination Limits with Join Fetching

Hibernate Pagination Limits with Join Fetching

Hibernate exposes pagination through setFirstResult() and setMaxResults() on the Query interface. The generated SQL is dialect-dependent, using LIMIT/OFFSET or window functions according to the configured Database Dialect.

A documented constraint appears when those methods are combined with JOIN FETCH on collection associations such as One-to-Many or Many-to-Many. The joined row product no longer maps one row to one root entity, so the database cannot apply the limit to the root count directly.

Open Behavior

In that situation Hibernate may fall back to in-memory pagination, retrieving the full result set and filtering in Java, which raises heap pressure and can contribute to OutOfMemoryError in a large persistence context.

• Which association mappings and fetch configurations cause Hibernate to switch from database-level to in-memory pagination?
• How does the configured dialect affect whether the limit is pushed down or applied in Java?