Introduction

The N+1 query problem in Spring Data JPA occurs when loading a list of N parent entities triggers N additional SELECT queries to load each parent's lazy-loaded associations. For example, fetching 100 orders triggers 1 query for the orders list, then 100 separate queries to load the customer for each order -- 101 queries total. This causes severe performance degradation, especially when nested associations compound the problem (orders -> customers -> addresses). The N+1 problem is insidious because the code looks correct and works fine with small datasets, only becoming a production issue with realistic data volumes.

Symptoms

Application logs show excessive queries:

sql
Hibernate: select o.id, o.total, o.customer_id from orders o
Hibernate: select c.id, c.name from customers c where c.id = ?
Hibernate: select c.id, c.name from customers c where c.id = ?
Hibernate: select c.id, c.name from customers c where c.id = ?
-- ... repeated 100 times

P6Spy or datasource-proxy reveals the count:

bash
Total queries executed: 101
SELECT queries: 101
Average query time: 2ms
Total time: 345ms (would be 15ms with a single JOIN query)

Or slow endpoint response:

bash
$ curl -w "%{time_total}s" http://localhost:8080/api/orders
# Returns 200 but takes 4.5 seconds for 500 orders

Common Causes

  • Lazy-loaded association accessed in a loop: order.getCustomer().getName() inside a loop
  • Spring Data default fetch type: @ManyToOne is EAGER by default but @OneToMany is LAZY, causing N+1 when accessed
  • Serialization triggers lazy loading: Jackson serializes an entity graph, triggering lazy loads one by one
  • No fetch plan specified: Relying on default lazy loading without specifying what to fetch
  • DTO projection still triggers entity loading: Using entities instead of DTO projections
  • Collection-valued association in JOIN FETCH: Fetching multiple collections causes Cartesian product

Step-by-Step Fix

Step 1: Use JOIN FETCH for specific queries

```java public interface OrderRepository extends JpaRepository<Order, Long> {

@Query("SELECT DISTINCT o FROM Order o " + "JOIN FETCH o.customer c " + "JOIN FETCH c.address a " + "WHERE o.status = :status") List<Order> findByStatusWithCustomerAndAddress(@Param("status") String status); } ```

This generates a single SQL query:

sql
SELECT DISTINCT o.*, c.*, a.*
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN addresses a ON c.address_id = a.id
WHERE o.status = ?

Step 2: Use @EntityGraph for reusable fetch plans

```java public interface OrderRepository extends JpaRepository<Order, Long> {

@EntityGraph(attributePaths = {"customer", "customer.address"}) List<Order> findByStatus(String status);

@EntityGraph(attributePaths = {"items", "items.product"}) List<Order> findAllWithItems(); } ```

Step 3: Use batch fetching for collections

java
@Entity
public class Customer {
    @OneToMany(mappedBy = "customer", fetch = FetchType.LAZY)
    @BatchSize(size = 50)  // Loads customers in batches of 50
    private List<Order> orders = new ArrayList<>();
}

With @BatchSize(size = 50), Hibernate loads up to 50 customers' orders in a single query using IN (?,?,?,?,...) instead of 50 individual queries.

Step 4: Use DTO projection to avoid entity loading

```java public interface OrderRepository extends JpaRepository<Order, Long> {

@Query("SELECT new com.example.dto.OrderSummaryDTO(o.id, o.total, c.name) " + "FROM Order o JOIN o.customer c WHERE o.status = :status") List<OrderSummaryDTO> findOrderSummariesByStatus(@Param("status") String status); }

public record OrderSummaryDTO(Long orderId, BigDecimal total, String customerName) {} ```

This generates a single query and returns lightweight DTOs -- no entity loading at all.

Prevention

  • Enable Hibernate SQL logging in staging: spring.jpa.show-sql=true and spring.jpa.properties.hibernate.format_sql=true
  • Use datasource-proxy or P6Spy to count queries per request in integration tests
  • Add a test assertion that verifies query count for critical endpoints
  • Use @EntityGraph on all repository methods that return entity lists
  • Prefer DTO projections over entity returns for list endpoints
  • Consider Hibernate 6's @Fetch(FetchMode.SUBSELECT) for collection-valued associations