Imported from jeanbisutti/poc-ia-perf (SKILL.md). Install upstream with npx skills add jeanbisutti/poc-ia-perf. Copyright stays with the author.

Fixing N+1 selects in Spring Data JPA / JPA / Hibernate

This skill is about remediation only: choosing and applying the right fix once an N+1 problem exists in JPA/Hibernate/Spring Data JPA code. It does not cover detection tooling, other ORMs (jOOQ, MyBatis), or GraphQL resolvers.

The core insight: there is no single "best" fix. The right fix depends on three questions about the call site:

1. Is the data read-only for this use case? (display, API response, export)
2. Is the query paginated (Pageable, setMaxResults) and does it fetch a collection?
3. How many collections (@OneToMany / @ManyToMany) need to be loaded together?

Answer these before touching code. Applying the wrong fix (e.g., JOIN FETCH on a paginated collection query) trades an N+1 for a worse problem (full-table load into memory).

Workflow

1. Triage the call site: find where the extra queries originate (an association accessed after the initial query, an EAGER mapping, a loop over entities).
2. Apply root-cause hygiene (section below) if the project doesn't have it yet — this prevents whole classes of N+1 and makes remaining ones explicit.
3. Pick the fix from the decision table.
4. Verify: enable SQL logging temporarily and confirm the number of queries is now constant — independent of the number of rows returned. Remove the logging after.

# Temporary verification only — do not leave enabled
spring.jpa.properties.hibernate.show_sql=false
logging.level.org.hibernate.SQL=DEBUG

Decision table

Thresholds that change the decision:

• If parents × children exceeds a few thousand joined rows, abandon Set + multi JOIN FETCH (cartesian product) in favor of split queries or batch fetching.
• If the parent query is paginated, never rely on JOIN FETCH of a collection or SUBSELECT — use the two-query pattern or batch fetching.

Step 0 — Root-cause hygiene (apply first, always)

Most N+1 problems trace back to two systemic causes. Fix these before (or alongside) any local fix, unless the user explicitly restricts the change surface.

(a) Make every association LAZY. JPA defaults @ManyToOne and @OneToOne to EAGER (@OneToMany/@ManyToMany default to LAZY). EAGER on to-one associations is the most common N+1 source: any JPQL query returning those entities triggers one extra select per row for each EAGER association not covered by a join.

@ManyToOne(fetch = FetchType.LAZY)   // never rely on the EAGER default
private Author author;

@OneToOne(fetch = FetchType.LAZY)
private PostDetails details;

EAGER cannot be overridden back to lazy by queries or entity graphs — LAZY is the only future-proof mapping. Fetch eagerly per use case instead (sections below).

(b) Disable Open-Session-In-View. Spring Boot defaults spring.jpa.open-in-view=true, which keeps the Hibernate session open during view rendering / response serialization. This masks LazyInitializationException by silently running one query per lazy access, outside the transaction — an invisible N+1.

spring.jpa.open-in-view=false

Never "fix" a LazyInitializationException by re-enabling OSIV or by setting hibernate.enable_lazy_load_no_trans=true. Both convert an explicit error into a hidden N+1. The correct response is an explicit fetch (JOIN FETCH, entity graph, projection) at the call site that needs the data.

(c) Set a global batch-fetch safety net. Batch fetching is disabled by default; Hibernate only batches entities/collections annotated with @BatchSize unless this global setting is present:

spring.jpa.properties.hibernate.default_batch_fetch_size=25
spring.jpa.properties.hibernate.query.in_clause_parameter_padding=true

This turns any residual N+1 into N/25 + 1 without changing code. Details in the batch-fetching section.

(d) Fail fast on the pagination trap (recommended in dev/test profiles):

spring.jpa.properties.hibernate.query.fail_on_pagination_over_collection_fetch=true

This converts Hibernate's in-memory-pagination warning into an exception so the dangerous combination can't ship silently.

Fix 1 — DTO projections (default choice for read-only)

When the use case is read-only, do not load entities at all. Select exactly the needed columns into a DTO. No managed entities → no lazy proxies → an N+1 is structurally impossible, and you also skip dirty checking and unneeded columns.

JPQL constructor expression (works everywhere):

public record PostSummary(Long id, String title, String authorName) {}

@Query("""
    select new com.example.PostSummary(p.id, p.title, p.author.name)
    from Post p
    where p.status = :status
    """)
List<PostSummary> findSummaries(@Param("status") Status status);

Spring Data derived projection — if the DTO (class or record) has a single constructor whose parameter names match entity attributes, Spring Data generates the constructor expression for you:

List<PostSummary> findByStatus(Status status);   // return type drives the projection

Interface-based (closed) projection — getters matching attribute names; Spring selects only those columns:

interface PostView { Long getId(); String getTitle(); }
List<PostView> findByTitleContaining(String token);

Pitfalls — these re-introduce the problem you're fixing:

• Nested interface projections over associations (e.g., AuthorView getAuthor()) make Spring load the full root entity behind a proxy wrapper, selecting all columns and potentially re-triggering N+1. Prefer flat projections (p.author.name as authorName) or constructor expressions.
• Open projections (@Value("#{...}") SpEL) also force full-entity loading.
• Never put a managed entity inside a constructor expression (new Dto(p, p.author.name) — the p drags entity loading back in). Select scalars.
• Constructor expressions reference the DTO by fully-qualified name in a string; renames only break at runtime. Keep DTOs colocated with their repository.
• DTOs are not managed: read-only. For updates, load entities with Fix 2/3.

For deep, hierarchical read models (parent + children DTOs in one shot), flat projections get awkward; Blaze-Persistence Entity Views solve this properly and are worth suggesting when the user already has complex nested DTOs.

Fix 2 — JOIN FETCH

Use when the caller needs managed entities (it will modify them, or genuinely needs the full object) plus their associations, and the query is not paginated over a collection fetch.

// to-one: safe with pagination, no row multiplication
@Query("select p from Post p join fetch p.author where p.id = :id")
Optional<Post> findWithAuthor(@Param("id") Long id);

// collection: use LEFT join fetch or parents without children disappear
@Query("select p from Post p left join fetch p.comments where p.id = :id")
Optional<Post> findWithComments(@Param("id") Long id);

The pagination trap. Combining a collection JOIN FETCH with Pageable/setMaxResults makes Hibernate drop the SQL LIMIT, load the entire result set, and paginate in memory (risk: OutOfMemory). Hibernate logs HHH000104 (5.x) / HHH90003004 (6.x): firstResult/maxResults specified with collection fetch; applying in memory. Fix with the two-query pattern (Fix 5) or batch fetching (Fix 4). Never ignore this warning.

DISTINCT — version-dependent:

• Hibernate 5: a collection JOIN FETCH duplicates parent references; the idiom was select distinct p ... plus the hint QueryHints.HINT_PASS_DISTINCT_THROUGH, false to keep DISTINCT out of the SQL.
• Hibernate 6+: parent deduplication is automatic, the hint was removed, and distinct is now always passed through to SQL. Therefore in Hibernate 6+, do not write distinct with a collection join fetch — it only adds a useless, costly SQL DISTINCT. When migrating 5→6, delete these hints and distincts.

MultipleBagFetchException. Join-fetching two List collections at once fails: cannot simultaneously fetch multiple bags. Do NOT "fix" it by switching to Set without thinking — that makes the query run as a cartesian product (comments × tags rows), acceptable only for tiny cardinalities. The robust fix is one query per collection; the persistence context stitches them onto the same parent instances:

List<Post> posts = repo.findAllWithComments(ids);  // left join fetch p.comments
repo.findAllWithTags(ids);                         // left join fetch p.tags — hydrates same instances

Other pitfalls: no on/with condition is allowed on a fetched association; a fetch loads all columns of the child entity (if you only display data, go back to Fix 1).

Fix 3 — @EntityGraph (declarative JOIN FETCH)

Same use cases and same limits as Fix 2, but declarative and reusable — handy on derived query methods where you don't want to hand-write JPQL, or to vary the fetch plan per repository method over the same query.

public interface PostRepository extends JpaRepository<Post, Long> {

    @EntityGraph(attributePaths = {"author", "comments"})
    List<Post> findByStatus(Status status);            // ad-hoc graph

    @EntityGraph(value = "Post.withComments", type = EntityGraphType.FETCH)
    Optional<Post> findWithGraphById(Long id);         // named graph
}

@NamedEntityGraph(name = "Post.withComments",
    attributeNodes = @NamedAttributeNode("comments"))
@Entity
public class Post { ... }

Semantics: FETCH (jakarta.persistence.fetchgraph) treats only listed attributes as eager, everything else lazy; LOAD (loadgraph) adds listed attributes to what the mapping already declares eager. Spring Data defaults to FETCH. Note Hibernate still loads statically-EAGER attributes even under a fetch graph — one more reason the mapping itself must be LAZY (Step 0).

Pitfalls:

• Same pagination trap as JOIN FETCH when the graph includes a collection. Hibernate 6 may not log the warning for entity-graph fetches (it warns on explicit join fetch), but the in-memory pagination behavior is the same — absence of the warning does not make it safe.
• A graph cannot demote an EAGER attribute to lazy.
• Multiple List collections in one graph hit the same bag/cartesian issues as Fix 2.

Fix 4 — Batch fetching (@BatchSize / default_batch_fetch_size)

The best global and pagination-safe remediation. Instead of one select per uninitialized lazy proxy/collection, Hibernate groups pending initializations into IN (?, ?, ...) queries: N+1 becomes N/M + 1.

spring.jpa.properties.hibernate.default_batch_fetch_size=25
spring.jpa.properties.hibernate.query.in_clause_parameter_padding=true

// or locally, on a specific association or entity class
@OneToMany(mappedBy = "post", fetch = FetchType.LAZY)
@BatchSize(size = 25)
private Set<PostComment> comments;

Why it's often the right default: it composes with pagination (the parent query keeps its SQL LIMIT — the batch only initializes proxies of the current page), causes no cartesian products, no MultipleBagFetchException, and covers all lazy access paths, including ones nobody profiled yet.

in_clause_parameter_padding=true matters alongside it: the last batch has a variable size, which would otherwise generate many distinct SQL strings (3 params, 4 params, …) and thrash the execution-plan cache. Padding rounds the parameter count up to the next power of two by repeating the last value, so plans get reused.

Pitfalls:

• Typical sizes 16–100; larger values mean bigger IN lists (watch DB limits and memory). Powers of two pair nicely with padding. Measure before tuning past 25–50.
• Batching applies to proxies attached to an open session; detached entities won't batch.
• It optimizes reads only. JDBC write batching is a different setting (hibernate.jdbc.batch_size).
• It's a mitigation (fewer round-trips), not the minimal-query shape — for hot paths, a targeted Fix 1/2/5 is still better.

Fix 5 — Two-query pattern (collection fetch + pagination, done right)

The canonical resolution of the HHH000104/HHH90003004 trap. Paginate on IDs first (clean SQL LIMIT, no row multiplication), then fetch entities + collection for just those IDs (no pagination on the fetch query):

// 1) page the IDs only
@Query(value = "select p.id from Post p where p.title like :t order by p.id",
       countQuery = "select count(p) from Post p where p.title like :t")
Page<Long> findPageOfIds(@Param("t") String t, Pageable pageable);

// 2) fetch the page's entities with their collection
@Query("select p from Post p left join fetch p.comments where p.id in :ids order by p.id")
List<Post> findWithCommentsByIds(@Param("ids") List<Long> ids);

Assemble in a service method: run query 1, feed page.getContent() into query 2, rebuild a Page with PageImpl(entities, pageable, page.getTotalElements()). Re-apply the sort in query 2 (or reorder in memory by the ID list) — IN does not preserve order.

Total cost: 2 queries + 1 count, constant regardless of page size — and the LIMIT runs in the database, where it belongs. For multiple collections, combine with the split-query approach from Fix 2 (one fetch query per collection over the same IDs).

Fix 6 — @Fetch(FetchMode.SUBSELECT)

Hibernate-specific. When a list of parents is loaded, the first access to one child collection loads all collections of all loaded parents in a single query that re-embeds the original query as a subselect:

@OneToMany(mappedBy = "dept", fetch = FetchType.LAZY)
@Fetch(FetchMode.SUBSELECT)
private List<Employee> employees;
// SQL: select ... from employee where dept_id in (select id from dept where <original query>)

Use only when the parent query is unpaginated and you genuinely need all collections of the loaded set. Danger: Hibernate re-runs the original query in the subselect without its LIMIT/OFFSET — with a paginated parent query you can pull the children of the entire table into memory. When in doubt, prefer @BatchSize (Fix 4), which respects pagination.

Special cases

Inverse @OneToOne (mappedBy side). fetch = LAZY on the side without the FK column does not work: Hibernate must query the child table anyway to know whether to inject a proxy or null (it has no FK value to build the proxy from). Result: one extra select per parent, systematically. Fixes, in order of preference:

1. Share the primary key with @MapsId on the owning (child) side — no FK probe needed, true lazy works, and the two tables share IDs:

   @Entity class PostDetails {
       @Id Long id;
       @OneToOne(fetch = FetchType.LAZY) @MapsId @JoinColumn(name = "id")
       private Post post;
   }

   Then simply drop the inverse side from Post and query details by ID when needed (same value as the post ID).
2. Bytecode enhancement (hibernate-enhance-maven-plugin with enableLazyInitialization) if the inverse mapping must stay.
3. Remodel as @ManyToOne + unique constraint on the owning side.
4. Otherwise, always load it explicitly via JOIN FETCH / entity graph.

@ElementCollection. Always lazy-loads one query per parent; JOIN FETCH works but multiplies rows. Usually best served by @BatchSize on the collection, or by promoting it to a real entity if it keeps causing trouble.

Inheritance. Polymorphic queries over JOINED/TABLE_PER_CLASS hierarchies can fan out into per-subclass queries or huge unions. Prefer DTO projections for read paths, and query concrete subtypes when the use case allows.

Second-level/query cache. In Hibernate 5, the query cache stored only entity IDs; on a cold entity cache each ID replayed a select — a cache-induced N+1. Hibernate 6 stores fuller data. Either way: the cache is not a fetch strategy. Fix the fetch plan first; add caching only for genuinely hot, mostly-immutable data.

Version notes (Hibernate 5 → 6/7, Spring Boot 3)

• Spring Boot 3.x = Hibernate 6.x + jakarta.persistence.* (hints included).
• Warning code for the pagination trap: HHH000104 (5.x) → HHH90003004 (6.x).
• Hibernate 6 deduplicates join-fetched parents automatically; distinct now reaches the SQL; HINT_PASS_DISTINCT_THROUGH is gone. Remove legacy distinct + hint combos when migrating.
• Recent Hibernate 7.x releases improve the paginated-collection-fetch case by restructuring the SQL; verify against the exact version in the project's build file before relying on it — and the two-query pattern remains correct everywhere.
• @BatchSize, @Fetch, @MapsId-based lazy tricks: @BatchSize and @Fetch are Hibernate-proprietary (not portable JPA).

Anti-patterns — never do these

• "Fixing" lazy-loading errors with spring.jpa.open-in-view=true or hibernate.enable_lazy_load_no_trans=true (hides the N+1, moves queries out of the transaction).
• Switching associations to EAGER as a fix — it globalizes the problem to every query that touches the entity.
• findAll() (or any entity query) followed by a loop that maps entities to DTOs while touching lazy associations — replace with a projection query (Fix 1).
• JOIN FETCH of a collection on a Pageable method because "the warning is just a warning".
• Blindly converting List to Set to silence MultipleBagFetchException on large collections (cartesian product).

Final verification checklist

• Query count in SQL logs is constant when the result size grows.
• No HHH000104/HHH90003004 in logs; fail_on_pagination_over_collection_fetch enabled in dev/test.
• All @ManyToOne/@OneToOne are fetch = LAZY; spring.jpa.open-in-view=false.
• default_batch_fetch_size + in_clause_parameter_padding set globally.
• Read-only endpoints use projections, not entities.
• Any paginated + collection case uses the two-query pattern or batch fetching.