Fixing N+1

Fix 1: JOIN FETCH

A fetch join tells Hibernate to load the association in the same SQL with a JOIN and initialise it. One query instead of N+1.

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("""
           select o from Order o
           join fetch o.items i
           join fetch i.product
           where o.user.id = :userId
           order by o.id desc
           """)
    List<Order> findByUserIdWithItems(@Param("userId") Long userId);
}

Log after the fix

Same endpoint, same 5 orders. Now there is exactly one SELECT:

GET /api/users/42/orders

Example output

DEBUG org.hibernate.SQL : select o1_0.id,i1_0.order_id,i1_0.id,i1_0.price,p1_0.id,p1_0.category_id,p1_0.description,p1_0.name,p1_0.price,p1_0.stock,i1_0.quantity,o1_0.status,o1_0.total,o1_0.user_id from orders o1_0 join order_items i1_0 on o1_0.id=i1_0.order_id join products p1_0 on p1_0.id=i1_0.product_id where o1_0.user_id=? order by o1_0.id desc
TRACE org.hibernate.orm.jdbc.bind : binding parameter (1:BIGINT) <- [42]

join fetch is an inner join: orders without items disappear. Use left join fetch to keep them.
In Hibernate 6 the duplicate parent rows produced by a collection join are removed automatically; distinct is no longer needed.
Fetching two List (bag) collections in one query throws MultipleBagFetchException. Use Set, or fetch the second collection in a second query.

Fix 2: @EntityGraph

An entity graph declares which associations to load, without writing JPQL. Spring Data lets you put it on derived query methods.

public interface OrderRepository extends JpaRepository<Order, Long> {

    @EntityGraph(attributePaths = {"items", "items.product"})
    List<Order> findByUserIdOrderByIdDesc(Long userId);

    @EntityGraph(attributePaths = "items")
    Optional<Order> findWithItemsById(Long id);
}

-- @EntityGraph uses LEFT JOINs (orders without items are kept)
select o1_0.id,i1_0.order_id,i1_0.id,i1_0.price,p1_0.id,p1_0.category_id,p1_0.description,p1_0.name,p1_0.price,p1_0.stock,
       i1_0.quantity,o1_0.status,o1_0.total,o1_0.user_id
from orders o1_0
left join order_items i1_0 on o1_0.id=i1_0.order_id
left join products p1_0 on p1_0.id=i1_0.product_id
where o1_0.user_id=? order by o1_0.id desc

Fix 3: Batch Fetching

Sometimes you cannot join (e.g. you page over orders, or you have several collections). Batch fetching keeps lazy loading but loads the collections of many parents in one IN (...) query. N+1 becomes 1 + N/batchSize.

Configuration and log

Turn it on globally or per association:

application.properties

spring.jpa.properties.hibernate.default_batch_fetch_size=25

// or on one mapping:
@OneToMany(mappedBy = "order")
@BatchSize(size = 25)                     // org.hibernate.annotations.BatchSize
private List<OrderItem> items = new ArrayList<>();

Example output

DEBUG org.hibernate.SQL : select o1_0.id,o1_0.status,o1_0.total,o1_0.user_id from orders o1_0 where o1_0.user_id=?
DEBUG org.hibernate.SQL : select i1_0.order_id,i1_0.id,i1_0.price,i1_0.product_id,i1_0.quantity from order_items i1_0 where i1_0.order_id in (?,?,?,?,?)

Note: On PostgreSQL, Hibernate 6.3+ binds the ids as one array parameter, so the log shows where i1_0.order_id = any (?) instead of a list of ?.

Batch Size Arithmetic

Example

How many SELECTs do 53 orders need with different batch sizes?

import java.util.*;

public class Main {
    public static void main(String[] args) {
        List<Long> orderIds = new ArrayList<>();
        for (long id = 1; id <= 53; id++) orderIds.add(id);

        System.out.println("orders: " + orderIds.size());
        System.out.println("no batching : " + (1 + orderIds.size()) + " queries");
        for (int batch : new int[] {10, 25, 50}) {
            int batches = (orderIds.size() + batch - 1) / batch;   // ceiling division
            System.out.println("batch " + batch + "    : " + (1 + batches) + " queries");
        }

        System.out.println("IN lists with batch size 25:");
        for (int from = 0; from < orderIds.size(); from += 25) {
            List<Long> chunk = orderIds.subList(from, Math.min(from + 25, orderIds.size()));
            System.out.println("  where order_id in (" + chunk.get(0) + " .. " + chunk.get(chunk.size() - 1) + ")  " + chunk.size() + " ids");
        }
    }
}

Output

orders: 53
no batching : 54 queries
batch 10    : 7 queries
batch 25    : 4 queries
batch 50    : 3 queries
IN lists with batch size 25:
  where order_id in (1 .. 25)  25 ids
  where order_id in (26 .. 50)  25 ids
  where order_id in (51 .. 53)  3 ids

Choosing a Fix

Fix	Queries	Best for
`join fetch` in `@Query`	1	a known screen that needs the association; custom filters
`@EntityGraph`	1	derived queries and `findById` without writing JPQL
Batch fetching	1 + N/size	pagination, several collections, safety net for the whole app
DTO projection	1	read-only lists that need only a few columns (see Queries)

Exercise: The product listing page calls product.getCategory().getName() for 50 products and runs 51 queries. Fix it with a repository method.

Show answer

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query("select p from Product p join fetch p.category where p.stock > 0")
    List<Product> findInStockWithCategory();

    // or, without JPQL:
    @EntityGraph(attributePaths = "category")
    List<Product> findByStockGreaterThan(int stock);
}