The N+1 Problem: All Four Fetch Strategies Explained

The N+1 problem is one of those bugs that hides in plain sight. Your application works correctly, your tests pass, and somewhere in production a page that should run one database query is running hundreds. This tutorial walks through all four Hibernate fetch strategies with actual SQL query counts measured in tests.

All code is in the companion repo: github.com/umur/n-plus-one-problem. Run mvn test and watch 15 tests pass.

Two Concepts to Separate First

Most explanations conflate two orthogonal JPA concepts. Understanding the difference is the foundation for everything else.

FetchType controls when loading happens:

• FetchType.LAZY: load the collection on demand, when your code first accesses it
• FetchType.EAGER: load the collection immediately when the parent entity is loaded

FetchMode controls how loading happens:

• SELECT: issue a separate SELECT per entity (the default)
• JOIN: load everything in a single SQL JOIN
• SUBSELECT: load all collections in one subquery
• BATCH: group SELECT queries into IN batches

The N+1 problem comes from FetchMode.SELECT, not from FetchType.LAZY. EAGER does not fix N+1 if you are still using SELECT mode. It just fires the extra queries automatically instead of on demand. EAGER combined with JOIN mode produces a single query with no N+1 at all.

How Query Counts Are Measured

Every test uses a custom Hibernate StatementInspector that intercepts every SQL statement:

public class QueryCounter implements StatementInspector {
    private static final ThreadLocal<AtomicInteger> COUNT =
        ThreadLocal.withInitial(AtomicInteger::new);

    @Override
    public String inspect(String sql) {
        COUNT.get().incrementAndGet();
        return sql;
    }

    public static void reset() { COUNT.get().set(0); }
    public static int getCount() { return COUNT.get().get(); }
}

Registered via application.properties:

spring.jpa.properties.hibernate.session_factory.statement_inspector=
  com.umurinan.nplusone.util.QueryCounter

Every test calls entityManager.flush(); entityManager.clear(); before measuring. Without clearing the first-level cache, Hibernate serves already-loaded entities from memory and never issues the extra SQL, completely hiding the N+1 problem.

Strategy 1: SELECT (the default)

FetchMode.SELECT is the Hibernate default. It issues a separate SELECT for each collection access. Whether you use LAZY or EAGER, the number of queries is the same: 1 + N.

LAZY SELECT: the query fires when your code accesses the collection:

@OneToMany(mappedBy = "author", fetch = FetchType.LAZY)
private List<Book> books = new ArrayList<>();

List<Author> authors = authorRepository.findAll();  // 1 query

for (Author author : authors) {
    author.getBooks().size();  // 1 query per author
}
// total: 1 + N queries

EAGER SELECT: the same queries fire, but automatically on load:

@OneToMany(mappedBy = "author", fetch = FetchType.EAGER)
// FetchMode is still SELECT by default
private List<BookEager> books = new ArrayList<>();

// Loading authors triggers books queries immediately.
// You never call getBooks() - Hibernate fires the N queries anyway.
List<AuthorEager> authors = authorEagerRepository.findAll();
// total: 1 + N queries - same as LAZY SELECT

EAGER SELECT is strictly worse. The query count is identical, but you can no longer avoid the N extra queries when you do not need the books at all.

@Test
@DisplayName("PROBLEM: SELECT strategy causes 1+N queries with both LAZY and EAGER")
void selectStrategyAlwaysCausesNPlusOne() {
    QueryCounter.reset();
    List<Author> authors = authorRepository.findAll();
    for (Author author : authors) {
        author.getBooks().size();
    }
    assertThat(QueryCounter.getCount()).isEqualTo(6); // 1 + 5 authors
}

Strategy 2: JOIN

FetchMode.JOIN loads the parent and all collections in a single SQL JOIN. This eliminates every extra query.

The test suite covers two:

JPQL JOIN FETCH: on demand in a query, entity stays LAZY:

@Query("SELECT DISTINCT a FROM Author a JOIN FETCH a.books")
List<Author> findAllWithBooksJoinFetch();

@EntityGraph: declarative alternative, entity stays LAZY:

@EntityGraph(attributePaths = {"books"})
@Query("SELECT a FROM Author a")
List<Author> findAllWithBooksEntityGraph();

Both produce a single SQL query, but the join type differs:

-- JOIN FETCH: INNER JOIN (excludes authors with no books)
SELECT DISTINCT a.*, b.*
FROM author a
INNER JOIN book b ON b.author_id = a.id

-- @EntityGraph: LEFT OUTER JOIN (includes authors with no books)
SELECT DISTINCT a.*, b.*
FROM author a
LEFT OUTER JOIN book b ON b.author_id = a.id

A third option is @Fetch(FetchMode.JOIN) placed directly on the entity mapping. It works, but requires FetchType.EAGER, which means books are always loaded even when you do not need them. JPQL JOIN FETCH and @EntityGraph are preferred because the entity stays LAZY and you opt in per query.

@Test
@DisplayName("SOLUTION: JOIN strategy loads authors and books in exactly 1 query")
void joinFetchProducesOneQuery() {
    QueryCounter.reset();
    List<Author> authors = authorRepository.findAllWithBooksJoinFetch();
    for (Author author : authors) {
        author.getBooks().size();  // already loaded, no additional queries
    }
    assertThat(QueryCounter.getCount()).isEqualTo(1);
}

One caveat: JPQL JOIN FETCH and @EntityGraph do not work safely with setMaxResults() (pagination). Hibernate applies the limit in-memory after loading the full result set and logs a warning. For paginated endpoints, use BATCH or SUBSELECT instead.

Strategy 3: SUBSELECT

FetchMode.SUBSELECT loads all collections across all parent entities in a single subquery. Touch any author's books for the first time and Hibernate fires one query that loads books for every author simultaneously.

@Fetch(FetchMode.SUBSELECT)
@OneToMany(mappedBy = "author", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
private List<BookSubselect> books = new ArrayList<>();

Generated SQL reuses the original parent query as a subselect:

SELECT * FROM book_subselect
WHERE author_id IN (
    SELECT id FROM author_subselect
)

@Test
@DisplayName("SOLUTION: SUBSELECT loads all authors and books in exactly 2 queries")
void subselectProducesTwoQueries() {
    QueryCounter.reset();
    List<AuthorSubselect> authors = authorSubselectRepository.findAll();  // query 1

    for (AuthorSubselect author : authors) {
        author.getBooks().size();
        // first access fires the subselect for ALL authors  (query 2)
        // subsequent accesses hit already-loaded collections
    }

    assertThat(QueryCounter.getCount()).isEqualTo(2);  // always 2, regardless of N
}

Always 2 queries regardless of how many authors you have. Tradeoff: it always loads all books across all authors. You cannot load books for just one author using this strategy.

Strategy 4: BATCH

@BatchSize groups lazy SELECT queries into IN-clause batches. Instead of one query per author, Hibernate loads books for multiple authors at once.

@BatchSize(size = 3)
@OneToMany(mappedBy = "author", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
private List<BookBatch> books = new ArrayList<>();

With 5 authors and batchSize=3, Hibernate issues two batch queries:

SELECT * FROM book_batch WHERE author_id IN (1, 2, 3)  -- batch 1
SELECT * FROM book_batch WHERE author_id IN (4, 5)     -- batch 2

@Test
@DisplayName("SOLUTION: @BatchSize(size=3) reduces 6 queries to ceil(5/3)+1 = 3 queries")
void batchSizeReducesQueries() {
    QueryCounter.reset();
    List<AuthorBatch> authors = authorBatchRepository.findAll();  // query 1

    for (AuthorBatch author : authors) {
        author.getBooks().size();
        // author 1: loads batch [1,2,3]   (query 2)
        // author 2: already in cache      (no query)
        // author 3: already in cache      (no query)
        // author 4: loads batch [4,5]     (query 3)
        // author 5: already in cache      (no query)
    }

    assertThat(QueryCounter.getCount()).isEqualTo(3);  // 1 + ceil(5/3)
}

The query count formula is 1 + ceil(N / batchSize). BATCH is the best fit for paginated endpoints where JOIN is not an option. You can also apply it globally via spring.jpa.properties.hibernate.default_batch_fetch_size=25, which reduces N+1 across your entire application without changing any query.

Quick Reference

The Rule of Thumb

Use LAZY SELECT as your default. It is the right default. Then fix N+1 where it actually appears. For non-paginated list endpoints, use JOIN FETCH or @EntityGraph. For paginated endpoints, set hibernate.default_batch_fetch_size=25 globally and you have eliminated most N+1 problems without changing a single query. For read-only reporting, use DTO projections. Only use EAGER when the JOIN strategy is attached. EAGER SELECT is always worse than LAZY SELECT.

The tests in the companion repo make this concrete. Each scenario is a passing test with an assertion on the exact query count. Clone it, run mvn test, and the output tells you exactly what each strategy costs.