JPA N+1 and the Four JOIN FETCH Traps — MultipleBagFetchException, Pagination OOM, OneToOne LAZY

Table of contents

Why this article

“How do you solve N+1?” is the most common JPA interview question — which is exactly why depth shows up in the follow-up:

• What if you JOIN FETCH two collections?
• What about JOIN FETCH with setMaxResults?
• When does @OneToOne(LAZY) actually behave as LAZY, and when does it always fetch?

These three questions decide senior depth. This article measures all of them in one 4-depth domain.

1. The 4-depth domain

MerchantOwner (1)
  └─ OneToMany → Merchant (5/owner)
                     └─ OneToMany → AutoReplyRuleN1 (3/merchant)
                                         └─ OneToMany → ReplyHistory (4/rule)
                                                            └─ OneToOne mappedBy → ReplyHistoryMetadata

20 owners × 5 × 3 × 4 = 1,200 histories. Big enough to expose every trap.

2. S1 baseline — N+1

@Transactional(readOnly = true)
public void s1NPlusOne() {
    List<MerchantOwner> owners = ownerRepo.findAll();   // 1 SQL
    for (MerchantOwner o : owners) {
        for (Merchant m : o.getMerchants()) {            // LAZY — 1 SQL/owner
            for (AutoReplyRuleN1 r : m.getRules()) {     // LAZY — 1 SQL/merchant
                ...
            }
        }
    }
}

Expected: 1 + 20 + 100 = 121 SQL.

3. S2 — JOIN FETCH one level

@Query("SELECT DISTINCT o FROM MerchantOwner o LEFT JOIN FETCH o.merchants")
List<MerchantOwner> findAllJoinFetchMerchants();

One SQL with cartesian product, deduplicated by DISTINCT (Hibernate 6 also performs in-memory dedup automatically).

4. S3 — MultipleBagFetchException

@Query("SELECT DISTINCT m FROM Merchant m "
        + "LEFT JOIN FETCH m.rules r "
        + "LEFT JOIN FETCH m.owner.merchants")
List<Merchant> findAllTwoBags();

org.hibernate.loader.MultipleBagFetchException:
cannot simultaneously fetch multiple bags: [m.rules, owner.merchants]

Why

List is treated as a Bag — unordered. Two Bags joined together produce a cartesian whose row-to-element mapping is undefined for unordered collections.

Fixes

• Switch one to Set. Hibernate dedups automatically.
• Fetch one collection per query; let the second use @BatchSize.
• Or, fetch via two separate JPQL queries and let the persistence context stitch them.

Vlad Mihalcea — MultipleBagFetchException compares all three.

5. S5 — JOIN FETCH + Pagination → in-memory OOM

@Query("SELECT DISTINCT o FROM MerchantOwner o LEFT JOIN FETCH o.merchants")
List<MerchantOwner> findAllJoinFetchPaging(Pageable pageable);

Logs:

WARN  HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

Hibernate cannot push LIMIT to SQL because the cartesian breaks the row-to-owner mapping. So it loads all rows and keeps pageSize in memory. With 10K owners × 5 merchants average, that’s 50K rows materialised to return 20.

Fix

• Page parents only, then fetch children with an IN query.
• Or use @BatchSize so each lazy collection load batches K parents at a time.

Vlad Mihalcea — HHH000104 walks both patterns.

6. S4 — @OneToOne LAZY proxy limitation

@Entity class ReplyHistory {
    @OneToOne(mappedBy = "history", fetch = LAZY)
    private ReplyHistoryMetadata metadata;
}

Even though metadata is LAZY, every findAll() of ReplyHistory issues a SELECT for metadata.

Why

The owning side (@JoinColumn) can decide null-ness from the FK column — proxy works. The non-owning side (mappedBy) has no FK in the entity itself, so Hibernate must SELECT to know whether metadata is null. The intent of LAZY is impossible to honour without enhancement.

Fixes

• @MapsId — collapse 1:1 into PK = FK on the owning side, drop the mappedBy mapping. Single direction.
• Bytecode Enhancement with @LazyToOne(LazyToOneOption.NO_PROXY) — needs the gradle plugin.

Vlad Mihalcea — OneToOne LAZY.

7. S6 — @BatchSize and N/K+1

hibernate.default_batch_fetch_size=10 (or @BatchSize(size=10)):

-- N+1 baseline
SELECT * FROM owner;
SELECT * FROM merchant WHERE owner_id = ?;   -- 20 times

-- @BatchSize
SELECT * FROM owner;
SELECT * FROM merchant WHERE owner_id IN (?, ?, ?, ..., ?);   -- 2 times

121 → ~13. Not 1, but single-digit — the practical fix in many production codebases.

8. Decision tree

9. Conclusion

JOIN FETCH alone is not the answer. The trade-off space spans List vs Set, owning vs non-owning, pagination compatibility, and how Hibernate processes cartesian products. Without that map, the failure modes (OOM, surprise SELECTs) reproduce only in production.

Next: JPA saveAll IDENTITY bulk-insert trap.

References

Official

• Hibernate ORM — Fetching
• Hibernate ORM — @BatchSize

Vlad Mihalcea

• N+1 Query Problem
• MultipleBagFetchException
• HHH000104
• OneToOne LAZY

External

• Baeldung — @BatchSize

Sister posts

• JPA Dirty Checking cost
• JPA Optimistic Lock + retry stampede