N+1 Query Problem - Spring Boot / JPA Demo

Companion code for the tutorial The N+1 Problem: All Four Fetch Strategies Explained.

A Spring Boot 3.2 project that demonstrates all four Hibernate fetch strategies with real SQL query counts. Tests prove the N+1 problem exists before each fix is shown.

---

What is the N+1 problem?

Load a list of authors, then iterate and access each author's books. You asked for one thing but the ORM issues N extra queries behind the scenes:

• 1 query to load the authors
• N queries, one per author, to load their books

Five authors costs 6 queries. A thousand authors costs 1001. The count grows linearly with your data, which is why you never notice it in development and always notice it in production.

The tricky part: your code looks correct. There is no obvious mistake. The ORM just silently does the wrong thing unless you tell it otherwise.

---

FetchType vs FetchMode

Two concepts that are easy to mix up:

FetchType controls when loading happens:

• LAZY - load on demand, when your code first accesses the collection
• EAGER - load immediately when the parent entity loads

FetchMode controls how loading happens:

• SELECT - separate SELECT per entity (the default, the one that causes N+1)
• JOIN - single SQL JOIN loads everything at once
• SUBSELECT - one subquery loads all collections for all parents
• BATCH - groups SELECT queries into IN-clause batches

The N+1 problem comes from FetchMode.SELECT, not from FetchType.LAZY. Switching to EAGER does not fix it if you are still on SELECT mode. You end up with the same number of queries, just fired automatically instead of on demand. EAGER + JOIN, on the other hand, is perfectly fine.

---

Domain model

Author (id, name)
  └── Book[] (id, title, author_id)
        └── Review[] (id, rating, book_id)

Test data: 5 Authors, each with 3 Books, each Book with 2 Reviews. Reviews are seeded in tests to make the data more realistic but they are not the focus of any fetch strategy demonstration.

---

How query counting works

QueryCounter implements Hibernate's StatementInspector. Every SQL statement increments a ThreadLocal<AtomicInteger>. Tests call QueryCounter.reset() before the action and QueryCounter.getCount() after.

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

ThreadLocal means parallel test runs do not interfere with each other.

One other thing every test does: entityManager.flush(); entityManager.clear() before measuring. Without clearing the first-level cache, Hibernate returns already-loaded entities from memory without issuing SQL. That completely hides the N+1 problem and makes every test look like 1 query regardless of what is really happening.

---

Fetch strategies

Quick reference

FetchMode	FetchType	Queries	Pagination safe	Notes
SELECT (default)	LAZY	1 + N	Yes	N+1 fires on collection access
SELECT (default)	EAGER	1 + N	Yes	N+1 fires on load, cannot suppress
JOIN	EAGER	1	No	Single SQL JOIN, no N+1
JOIN FETCH / @EntityGraph	LAZY	1	No	On-demand JOIN per query
SUBSELECT	LAZY/EAGER	2	Yes	One subquery loads all collections
BATCH	LAZY	1 + ceil(N/size)	Yes	Best default for paginated endpoints

---

SELECT (the default) - problem

Files: LazySelectNPlusOneTest, EagerSelectNPlusOneTest

// LAZY: N+1 fires when you access the collection
List<Author> authors = authorRepository.findAll(); // 1 query
for (Author author : authors) {
    author.getBooks().size(); // 1 query per author
}
// total: 1 + 5 = 6 queries

// EAGER: same query count, but fires automatically on load
List<AuthorEager> authors = authorEagerRepository.findAll();
// books already loaded - still 1 + 5 = 6 queries, no loop needed

EAGER SELECT is strictly worse than LAZY SELECT. Same query count, but you can no longer avoid the extra queries when you do not need the books.

---

JOIN - solution

Files: JoinFetchSolutionTest, EntityGraphSolutionTest

Two ways to get JOIN loading that are tested in this project:

// 1. JPQL JOIN FETCH - on demand per query, entity stays LAZY
@Query("SELECT DISTINCT a FROM Author a JOIN FETCH a.books")
List<Author> findAllWithBooksJoinFetch();

// 2. @EntityGraph - declarative alternative, entity stays LAZY
@EntityGraph(attributePaths = {"books"})
@Query("SELECT a FROM Author a")
List<Author> findAllWithBooksEntityGraph();

Both produce a single SQL query. The difference is in the join type:

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

-- @EntityGraph produces LEFT OUTER JOIN (authors with no books are included)
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) directly on the entity mapping. This works but requires FetchType.EAGER, meaning books are always loaded even when you do not need them. JPQL JOIN FETCH and @EntityGraph are preferred because they let the entity stay LAZY and you opt in to loading per query.

The catch: JPQL JOIN FETCH and @EntityGraph do not work safely with Pageable. Hibernate applies the page limit in memory after fetching everything, which defeats the point. Use BATCH for paginated endpoints.

---

SUBSELECT - solution

File: SubselectSolutionTest

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

The first time any author's books are accessed, Hibernate loads books for every author in one shot:

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

Always 2 queries, regardless of how many authors you have. The downside: it always loads all books for all authors, even if you only needed one.

---

BATCH - solution

File: BatchSizeSolutionTest

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

Instead of WHERE author_id = ? repeated N times, Hibernate groups IDs into IN clauses:

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

With 5 authors and size=3: 1 + ceil(5/3) = 3 queries.

Works with pagination. Can be applied globally:

spring.jpa.properties.hibernate.default_batch_fetch_size=25

That one line reduces N+1 across your entire application without touching any query.

---

Project structure

src/
├── main/java/com/umurinan/nplusone/
│   ├── entity/
│   │   ├── Author.java               LAZY books (baseline)
│   │   ├── Book.java
│   │   └── Review.java               seeded in tests for realistic data
│   ├── entity/eager/
│   │   ├── AuthorEager.java          EAGER + SELECT (still N+1)
│   │   └── BookEager.java
│   ├── entity/batch/
│   │   ├── AuthorBatch.java          @BatchSize(size=3)
│   │   └── BookBatch.java
│   ├── entity/subselect/
│   │   ├── AuthorSubselect.java      @Fetch(FetchMode.SUBSELECT)
│   │   └── BookSubselect.java
│   ├── dto/
│   │   └── AuthorBookCountDto.java   record(id, name, bookCount)
│   ├── repository/
│   │   ├── AuthorRepository.java     findAll, joinFetch, entityGraph, dto
│   │   ├── AuthorEagerRepository.java
│   │   ├── AuthorBatchRepository.java
│   │   └── AuthorSubselectRepository.java
│   └── util/
│       └── QueryCounter.java         StatementInspector SQL counter
└── test/java/com/umurinan/nplusone/
    ├── problem/
    │   ├── LazySelectNPlusOneTest.java    asserts 6 queries (1+N)
    │   └── EagerSelectNPlusOneTest.java   asserts 6 queries (1+N)
    └── solution/
        ├── JoinFetchSolutionTest.java     asserts 1 query
        ├── EntityGraphSolutionTest.java   asserts 1 query
        ├── BatchSizeSolutionTest.java     asserts 3 queries
        ├── SubselectSolutionTest.java     asserts 2 queries
        └── DtoProjectionSolutionTest.java asserts 1 query

---

Running the tests

# All 15 tests
mvn test

# Just the problem tests
mvn test -Dtest="LazySelectNPlusOneTest,EagerSelectNPlusOneTest"

# Just the solution tests
mvn test -Dtest="JoinFetchSolutionTest,EntityGraphSolutionTest,BatchSizeSolutionTest,SubselectSolutionTest,DtoProjectionSolutionTest"

# One strategy at a time
mvn test -Dtest=BatchSizeSolutionTest

# See the SQL alongside test results
mvn test 2>&1 | grep -E "Hibernate:|Tests run"

Expected: 15 tests, 0 failures, BUILD SUCCESS

---

Tech stack

• Java 17
• Spring Boot 3.2
• Spring Data JPA / Hibernate 6.3
• H2 in-memory database
• JUnit 5 + AssertJ
• Maven