Configuring Hibernate join‑fetch pagination with distinct results in Red Hat JBoss EAP

The Core Conflict: Join Fetching vs. Pagination

In Red Hat JBoss EAP 7.x (Hibernate 5.x), combining setFirstResult/setMaxResults with a JOIN FETCH on a collection causes a fundamental conflict. Because a join fetch multiplies the number of rows returned by the database (one row per child entity), Hibernate cannot apply LIMIT and OFFSET at the SQL level without truncating the parent entities' child collections.

When this occurs, Hibernate triggers the HHH000104 warning and performs in-memory pagination. This means the entire result set is loaded into the JVM before being sliced, which can lead to OutOfMemoryError on large datasets.

Recommended Resolution: The Two-Step ID Pattern

To ensure pagination happens at the database level while still retrieving child associations, avoid the JOIN FETCH in the initial paginated query. Instead, use a two-step approach:

1. Fetch Paginated IDs: Execute a query to retrieve only the distinct primary keys of the parent entities using setFirstResult and setMaxResults.
2. Fetch Full Entities: Use the retrieved IDs in a second query with a JOIN FETCH to load the parents and their children (e.g., WHERE p.id IN (:ids)).

Addressing Specific Constraints

Strategy	Effectiveness	Trade-off
DistinctRootEntityResultTransformer	Removes duplicates in the Java list.	Does not fix the in-memory pagination issue; the database still sends all rows.
Keyset Pagination (Seek)	Highly stable for high-write traffic.	Requires a strictly ordered, unique column; more complex to implement than offset.
ScrollableResults	Reduces JVM memory pressure.	Holds a JDBC cursor open longer, increasing connection consumption and potential lock contention.

Implementation and Verification

To verify that your configuration is avoiding in-memory pagination, enable SQL logging in your JBoss EAP standalone configuration or via log4j/logback:

logger.org.hibernate.SQL = DEBUG

Verification Checklist:

• Confirm the generated SQL for the ID query contains LIMIT and OFFSET (or the dialect equivalent).
• Check server logs for the absence of the HHH000104 warning.
• Verify that the number of root entities returned matches the setMaxResults value exactly, regardless of the number of children.

Diagnostic Requirement

To refine this recommendation: Are the child collections typically small (e.g., < 50 items) or potentially massive? If they are small, @BatchSize may be a simpler alternative to the two-step pattern.