Spring Boot Course · Lesson 9

The N+1 Query Problem

Your paginated endpoints look fast and correct. But turn on the SQL log and GET /api/books fires eight queries for one page of seven books — one for the page, then one more for every single book to load its author. That's the N+1 problem: the most common performance trap in JPA, and a favourite interview question. This lesson makes it visible, makes your app refuse to hide it, then kills it with one annotation.

What N+1 actually is

You run 1 query to fetch a list of parents. Then, for each of the N parents, the ORM quietly fires one more query to fetch a related child. One page of 20 books that each touch their author becomes 21 database round-trips — and it scales with your data, not your code. The query count is invisible in the JSON response; the only way to see it is to watch the SQL.

Step 1 — Turn on the SQL log and watch it happen

Hibernate can print every statement it runs. Switch it on (this is the logging-based alternative we flagged back in Lesson 5 — no show-sql needed):

src/main/resources/application.properties

# Log every SQL statement Hibernate executes
logging.level.org.hibernate.SQL=DEBUG

Restart, then hit the books list and count the SQL lines:

curl -s "http://localhost:8080/api/books?size=20"    # 7 seeded books, distinct authors

-- 1: the page of books
select b1_0.id,b1_0.author_id,b1_0.title,b1_0.year from book b1_0 order by b1_0.title,b1_0.id offset ? rows fetch first ? rows only
-- then ONE of these per book, to load its author:
select a1_0.id,a1_0.name from author a1_0 where a1_0.id=?
select a1_0.id,a1_0.name from author a1_0 where a1_0.id=?
select a1_0.id,a1_0.name from author a1_0 where a1_0.id=?
select a1_0.id,a1_0.name from author a1_0 where a1_0.id=?
select a1_0.id,a1_0.name from author a1_0 where a1_0.id=?
select a1_0.id,a1_0.name from author a1_0 where a1_0.id=?
select a1_0.id,a1_0.name from author a1_0 where a1_0.id=?

One query for the page, seven for the authors — 1 + 7 = 8. The same smell is on the authors endpoint, just mirrored: GET /api/authors runs one query for the page of authors, then one per author to load their books collection.

-- GET /api/authors?size=20  ->  1 + 7 again (the to-many direction)
select a1_0.id,a1_0.name from author a1_0 order by a1_0.name,a1_0.id offset ? rows fetch first ? rows only
select b1_0.author_id,b1_0.id,b1_0.title,b1_0.year from book b1_0 where b1_0.author_id=?   -- x7

Step 2 — Why you never noticed

Those lazy loads happen during DTO mapping, in the controller — after the service returned. That should be impossible: a lazy association needs an open persistence session, and the transaction that loaded the books is long over. It works because of a Spring Boot default called Open Session in View (OSIV): it holds the Hibernate session open for the entire web request, straight through JSON rendering. Convenient — it's why entity-to-DTO mapping "just works" — but it's exactly what lets N+1 hide. The queries fire; you just never feel them.

Step 3 — Make it fail loud

Turn OSIV off. Now the session closes when the transaction that loaded the data ends — in our app, the repository call itself — and any lazy access during mapping has no session to run in:

src/main/resources/application.properties

# Close the session at the end of the service layer, not the end of the request
spring.jpa.open-in-view=false

Restart and hit the books list again. The hidden N+1 becomes a loud, immediate failure:

HTTP 500
org.hibernate.LazyInitializationException:
  Could not initialize proxy [com.example.library.author.Author#019f48…] - no session

The switch comes down to who owns the session and how long it lives:

OSIV-on isn't "no boundary" — it's a much wider one (the whole request) that makes lazy loading always succeed. That convenience is exactly what hides the bug; narrowing the session back to the query that loaded the data is what surfaces it.

Step 4 — Fix the to-one with @EntityGraph

The book needs its author, so fetch them together. @EntityGraph tells Spring Data which associations to pull in the same query — redeclare the repository methods that feed the DTO mapping and annotate them:

src/main/java/com/example/library/book/BookRepository.java

public interface BookRepository extends JpaRepository<Book, UUID> {

    @Override
    @EntityGraph(attributePaths = "author")
    Page<Book> findAll(Pageable pageable);

    @Override
    @EntityGraph(attributePaths = "author")
    Optional<Book> findById(UUID id);

    @EntityGraph(attributePaths = "author")
    Page<Book> findByAuthorName(String name, Pageable pageable);
}

That's the whole fix for the books side. Restart, hit the list, and the eight queries collapse to one — a single LEFT JOIN that carries the author along:

select b1_0.id, a1_0.id, a1_0.name, b1_0.title, b1_0.year
from book b1_0
left join author a1_0 on a1_0.id = b1_0.author_id
order by b1_0.title, b1_0.id
offset ? rows fetch first ? rows only

Step 5 — Fix the authors side, two shapes

With OSIV off, the author endpoints are still failing, and they show a subtler truth: the right fix depends on the association's shape. The paginated list and the single-author detail want different things.

The list: send a summary, not the whole library

Why does a page of authors need to inline every author's complete book collection? It doesn't — that's a heavy payload and the source of the to-many N+1. Return the compact AuthorSummary (id + name) you already built in Lesson 6. It touches no lazy collection, so there's nothing to N+1, and the query is a clean single SELECT:

src/main/java/com/example/library/author/AuthorController.java — the list handler

@GetMapping   // list: lightweight summary (id + name), no inline books
public Page<AuthorSummary> all(@PageableDefault(size = 20, sort = "name") Pageable pageable) {
    return authors.findAll(Pageables.withIdTiebreaker(pageable)).map(AuthorSummary::from);
}

-- GET /api/authors  ->  one query, no per-author book fetch
select a1_0.id, a1_0.name from author a1_0 order by a1_0.name, a1_0.id offset ? rows fetch first ? rows only

The detail: fetch the collection with @EntityGraph

A single author's page legitimately wants the full book list — and here there's no pagination on the query, so fetching the collection is a clean join. Add the graph to the author's findById:

src/main/java/com/example/library/author/AuthorRepository.java

public interface AuthorRepository extends JpaRepository<Author, UUID> {

    @Override
    @EntityGraph(attributePaths = "books")
    Optional<Author> findById(UUID id);
}

-- GET /api/authors/{id}  ->  one query, author + books joined
select a1_0.id, a1_0.name, b1_0.author_id, b1_0.id, b1_0.title, b1_0.year
from author a1_0
left join book b1_0 on a1_0.id = b1_0.author_id
where a1_0.id = ?

Every endpoint is now green again with one query each, and LazyInitializationException is gone — because every association a controller touches is fetched on purpose.

Before and after

Same endpoints, same data, one annotation's difference:

Check yourself

Answer from memory before revealing.

Read this next

Primary source: Vlad Mihalcea's N+1 query problem with JPA and Hibernate — the definitive explanation, from the author of High-Performance Java Persistence. Follow it with The Open Session in View anti-pattern for the OSIV argument in full, and Baeldung's JPA Entity Graph for more @EntityGraph patterns.

New vocabulary from this lesson lives in the Glossary — your quick-reference for every term we use.