Fix and regression-test a JPA N+1 query

• Keep the entity association lazy by default.
• Fetch only the customer required by this use case.
• Map to a DTO while the fetch plan is active.
• Clear the persistence context and Hibernate statistics before measuring.
• Use multiple orders and customers so a false-positive one-row test cannot pass.

1. Express this use case with a dedicated repository method.
2. Apply @EntityGraph(attributePaths = "customer") to that method.
3. Map the bounded results to DTOs inside a read transaction.
4. In an integration test, seed data, flush, clear, then reset Hibernate statistics.
5. Execute the use case and assert both the result and a bounded statement count.

interface OrderRepository extends org.springframework.data.jpa.repository.JpaRepository<Order, Long> {
    @org.springframework.data.jpa.repository.EntityGraph(attributePaths = "customer")
    java.util.List<Order> findTop100ByStatusOrderByCreatedAtDesc(OrderStatus status);
}

@org.springframework.stereotype.Service
class PaidOrderQuery {
    private final OrderRepository orders;
    PaidOrderQuery(OrderRepository orders) { this.orders = orders; }

    @org.springframework.transaction.annotation.Transactional(readOnly = true)
    java.util.List<OrderRow> latestPaid() {
        return orders.findTop100ByStatusOrderByCreatedAtDesc(OrderStatus.PAID).stream()
            .map(order -> new OrderRow(
                order.getId(), order.getCustomer().getName(), order.getTotal()))
            .toList();
    }
}

// In a PostgreSQL integration test with hibernate.generate_statistics=true:
entityManager.flush();
entityManager.clear();
var statistics = entityManager.getEntityManagerFactory()
    .unwrap(org.hibernate.SessionFactory.class).getStatistics();
statistics.clear();

var rows = query.latestPaid();
org.assertj.core.api.Assertions.assertThat(rows).hasSize(20);
org.assertj.core.api.Assertions.assertThat(statistics.getPrepareStatementCount()).isEqualTo(1);

The service materializes O(n) bounded results. The repair changes round trips from O(n) to O(1) for this association, though the joined result and object materialization remain O(n).

• A to-many fetch with pagination can duplicate roots or break page semantics; this solution is specifically for a to-one association.
• A warm persistence context can hide N+1, which is why the test clears it.
• Second-level cache can also hide queries and should be controlled in the test.
• Several entity graphs can still create a wide or cartesian query; fetch only what the response needs.

• Increase orders from 2 to 50 and prove statement count remains bounded.
• Use several different customers so identity-map reuse cannot hide the issue.
• Remove the entity graph and verify the regression test fails.
• Assert the query still returns the required DTO fields and ordering.

• Changing the relationship to EAGER affects every query and still does not guarantee one SQL statement.
• A one-order test cannot reveal growth with N.
• Logging SQL without asserting a bound leaves the regression unprotected.
• Fetching multiple collections in one query can replace N+1 with cartesian explosion.