JOIN FETCH, entity graphs and the pagination trap

Key points

• JOIN FETCH loads the association in the same query. It is the direct answer to N+1 - but it multiplies rows, so fetching two collections at once produces a cartesian product.
• The pagination trap: combining JOIN FETCH on a collection with setMaxResults makes Hibernate fetch every row and paginate in memory. It warns about this, and the warning is routinely ignored until the table grows.
• The fix for that is two queries: page the ids first, then fetch the entities and their collections for those ids.
• @EntityGraph does the same job declaratively per query, which keeps associations lazy globally while fetching them where needed - usually the cleanest option in Spring Data.
• Batch fetching is the pragmatic middle ground. @BatchSize(size = 25) turns 100 lazy loads into 4 queries using an IN clause, without restructuring anything.
• Never fix N+1 by making the association EAGER. That moves the cost to every load of the entity, including the many places that never touch the association.

Example

// N+1: one query for orders, then one per order for its lines.
List<Order> orders = em.createQuery("select o from Order o", Order.class).getResultList();
orders.forEach(o -> o.getLines().size());          // 1 + N queries

// JOIN FETCH - one query. distinct removes the duplicated parent rows.
List<Order> orders = em.createQuery(
        "select distinct o from Order o join fetch o.lines", Order.class).getResultList();

// THE TRAP - this silently loads the whole table into memory:
//   HHH000104: firstResult/maxResults specified with collection fetch;
//   applying in memory
em.createQuery("select distinct o from Order o join fetch o.lines", Order.class)
  .setMaxResults(20)      // paginated in MEMORY, after fetching everything
  .getResultList();

// FIX - page the ids, then fetch those entities.
List<Long> ids = em.createQuery("select o.id from Order o order by o.id", Long.class)
        .setMaxResults(20).getResultList();

List<Order> page = em.createQuery(
        "select distinct o from Order o join fetch o.lines where o.id in :ids",
        Order.class).setParameter("ids", ids).getResultList();

// CARTESIAN PRODUCT - two collections in one fetch:
//   10 lines x 5 payments = 50 rows per order. Fetch one collection per
//   query, or use @BatchSize for the second.

// Declarative, per query - keeps the association lazy everywhere else.
interface OrderRepository extends JpaRepository<Order, Long> {

    @EntityGraph(attributePaths = {"lines", "customer"})
    List<Order> findByStatus(OrderStatus status);
}

// Pragmatic middle ground: 100 lazy loads become 4 IN queries.
@Entity
class Order {
    @OneToMany(mappedBy = "order")
    @BatchSize(size = 25)
    private List<OrderLine> lines;
}

JOIN FETCH plus pagination on a collection paginates in memory - page the ids first, then fetch.