N+1 query problems during paginated retrieval in JHipster entity repositories

Triggers for N+1 Queries in Paginated Retrieval

In JHipster-generated repositories, N+1 queries during pagination occur when the application iterates over the Page<T> result set and accesses relationship properties that were not loaded in the initial query. The primary conditions that cause this are:

• Lazy Loading (FetchType.LAZY): This is the JHipster default. The initial query fetches only the root entity. When the JSON serializer (Jackson) or a service accesses a related entity for each row in the page, Hibernate executes a separate SELECT for every single item.
• Eager Loading (FetchType.EAGER) on Collections: While intended to prevent N+1, FetchType.EAGER on @OneToMany or @ManyToMany mappings often triggers separate selects per row during pagination because Hibernate cannot safely apply LIMIT and OFFSET to a result set that contains joined collection rows without duplicating root entities.

The Count Query and Fetch Joins

When returning a Page<T>, Spring Data JPA executes two distinct queries: one to retrieve the data slice and one to calculate the total element count for pagination metadata.

Interaction with Fetch Joins: If you use a JOIN FETCH in a custom @Query to solve the N+1 problem, Hibernate typically ignores the fetch join during the count query. This is an optimization; counting the total rows does not require loading the associated entities. However, if you use JOIN FETCH on a collection while paginating, Hibernate will fetch all matching records into memory and perform pagination in-memory (HHH000104 warning), which can lead to OutOfMemoryError on large datasets.

Identifying Patterns in Logs

You can distinguish between expected pagination and N+1 patterns by enabling SQL logging in application.yml:

spring.jpa.properties.hibernate.show_sql: true

Pattern	Log Signature	Diagnosis
Expected	1 SELECT (with LIMIT/OFFSET) → 1 SELECT (COUNT)	Healthy pagination.
N+1	1 SELECT (LIMIT/OFFSET) → N SELECTs (targeting related table)	Lazy/Eager loading trigger.
In-Memory	1 SELECT (no LIMIT/OFFSET) → Hibernate Warning HHH000104	Fetch join on collection.

Recommended Resolution Steps

1. For To-One relationships: Use @EntityGraph or a custom JPQL query with JOIN FETCH to load the relationship in the primary data query.
2. For To-Many relationships: Avoid JOIN FETCH with pagination. Instead, use a separate query to fetch collections for the IDs present in the current page, or use a DTO projection to fetch only required fields.
3. Verification: Check the logs to ensure the number of SELECT statements is constant (e.g., 2 or 3) regardless of the size parameter.

Diagnostic Detail Needed: Are the N+1 queries occurring on @ManyToOne (single entity) or @OneToMany (collections) mappings? The resolution strategy differs significantly between the two.