An endpoint that returns a page of 50 root records with their child collections is issuing hundreds of SQL statements. Walk through how you would decide between a fetch join, an entity graph, batch fetching, or a projection to fix it.

## Step 1 — measure before you choose "Hundreds of statements" needs a shape, not a guess. Turn on SQL logging or, better, add an assertion that counts statements for that endpoint (Hibernate's `Statistics.getQueryExecutionCount()` / `getPrepareStatementCount()`, or a JDBC proxy in tests). You are looking for which of these you have: - 1 root select + 50 child selects → classic per-root lazy initialization. - 1 root select + 50 × M selects → lazy initialization at two levels (children, then grandchildren). - 1 huge join returning tens of thousands of rows → someone already "fixed" it with fetch joins and created a cartesian product. The fix differs for each, so skipping the measurement is how teams end up trading N+1 for a memory blow-up. ## Step 2 — ask what the response actually contains The single highest-leverage question. If the endpoint serialises `id`, `name`, `total` and a child count, loading managed entities is pure overhead: you pay hydration, dirty-check snapshots and persistence-context memory for objects you immediately convert to JSON. **Projection** wins here: ```java em.createQuery(""" select new com.acme.OrderRow(o.id, o.placedAt, c.name, count(i.id)) from Order o join o.customer c left join o.items i where o.status = :s group by o.id, o.placedAt, c.name """, OrderRow.class) ``` One statement, no proxies, no fetch-plan reasoning, and paginates correctly because there is one row per root. For read-only list screens this is usually the right answer, and a candidate who reaches for it first is signalling real production experience. ## Step 3 — if you genuinely need entities ### One collection, no pagination `LEFT JOIN FETCH` is simplest. One statement, root rows repeated per child, roots de-duplicated (automatically in Hibernate 6; `select distinct` historically). ### One collection, with pagination This is the trap. A collection fetch join plus `setFirstResult`/`setMaxResults` makes Hibernate abandon the SQL limit and paginate **in memory**, after loading every matching row (the historical `HHH000104` warning). Correct, and potentially fatal on a large table. The two-query pattern is the standard fix: ```java List<Long> ids = em.createQuery( "select o.id from Order o where o.status = :s order by o.placedAt desc", Long.class) .setParameter("s", status) .setFirstResult(offset).setMaxResults(50) .getResultList(); List<Order> page = em.createQuery( "select o from Order o left join fetch o.items where o.id in :ids order by o.placedAt desc", Order.class) .setParameter("ids", ids) .getResultList(); ``` Query one is paginated correctly because it returns one row per root. Query two fetches complete collections for exactly those roots. Two statements, bounded memory, correct paging. ### Several collections Joining two to-many associations in one statement multiplies their cardinalities — 50 roots × 10 items × 5 tags is 2,500 rows to transfer and de-duplicate. With two `List`s Hibernate refuses outright. Options: - **Separate queries into the same persistence context.** Run the root query, then `select o from Order o join fetch o.tags where o in :roots`. Because the persistence context guarantees one instance per identity, the second query populates the *same* objects you already hold. Three small statements beat one cartesian one. - **`@BatchSize` or `@Fetch(SUBSELECT)`** on the collection: Hibernate initializes lazily but in batches (`where order_id in (?,?,?,…)`) or via a subselect replaying the original query. This turns 50 statements into 1–3 without any query rewriting and works no matter which code path triggered the load. It's a mapping-level lever rather than a per-query one, which is both its strength (fixes all call sites) and its weakness (global). ### Reuse and runtime-chosen plans If four endpoints need "order + customer + items + product", writing that fetch clause four times is duplication that drifts. A `@NamedEntityGraph` (or a programmatically-built graph for an `expand=` parameter) names the plan once and attaches it to any query — or to `find()`, which cannot carry a fetch clause at all. The cost is that Hibernate, not you, chooses the join shape. ## Step 4 — what not to do - **Do not set the mapping to `EAGER`.** It fixes this endpoint and taxes every other query, including the ones that only need the root. Mappings lazy, plans per-use-case. - **Do not filter a fetched collection alias** (`join fetch o.items i where i.active = true`). The managed collection then misrepresents the database and a flush with `orphanRemoval` can delete rows. - **Do not stack fetch joins until the response is complete.** Each to-many multiplies rows; watch the row count, not just the query count. - **Do not stop at "it's faster now."** Add a statement-count assertion so the next lazy `getX()` call in a mapper does not silently reintroduce the storm. ## The decision in one line Need fields → project. Need entities + one collection → fetch join, with ID-paging if paginated. Need entities + several collections → separate queries or batch fetching. Need the same plan repeatedly or at runtime → entity graph.