00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

JPA N+1 Problem and Fetch Strategies

Last updated:

The JPA N+1 problem happens when your code runs one query to load a list of entities, then one extra query for each of them to load related data. A page of 50 orders quietly becomes 51 database round trips. It is easy to miss in development, where tables are small, and it is one of the most common reasons a Spring Boot API slows down in production.

This tutorial shows how the N+1 problem appears in the SQL log, why lazy loading causes it and why eager loading doesn't fix it. It then walks through every fetch strategy that does fix it: JOIN FETCH, @EntityGraph, batch fetching, subselect fetching and DTO projections, with the actual query counts measured on Spring Boot 4.1 with Hibernate 7. It finishes with a complete example, the pitfalls of each fix, and a test that counts queries so the problem cannot come back.

1. What Is the N+1 Problem in JPA?

The name describes the query count. 1 query loads the main list, and then N more queries load something related, one for each row in that list. Each query is fast on its own. The problem is the number of trips to the database.

In this tutorial's catalog, a product belongs to a category and has reviews. Showing 6 products with their category names should need 1 query. With the N+1 problem it needs up to 7, and with 500 products it needs up to 501.

The JPA and Hibernate tutorial explains entities and relationships if you need a refresher. This page focuses on fetching: when and how Hibernate loads related data.

2. Why the N+1 Problem Matters

  • Latency adds up. Every query is a network round trip. At 2 ms per trip, 500 extra queries add a full second to one API call.
  • It hides in development. With ten rows on a local database, 11 queries feel instant. The same code against production data is slow.
  • It grows with your data. The query count follows the number of rows, so an endpoint gets slower as the business grows, with no code change.
  • It loads the database. Hundreds of small queries per request use connections and CPU that other requests need.

3. How Lazy Loading Causes N+1 Queries

Relationships marked FetchType.LAZY are not loaded with the entity. Instead, Hibernate puts a proxy in the field: a stand-in object that knows only the related entity's id. The first time your code calls a method on the proxy, such as product.getCategory().getName(), Hibernate runs a query to load the real data. Lazy collections, such as product.getReviews(), work the same way.

That is fine for one product. In a loop over many products, each first access is another query. Two details affect the exact count:

  • The persistence context, Hibernate's per-transaction cache of loaded entities, loads each category only once. Six products in three categories cause 3 extra queries, not 6.
  • Collections are loaded per owner, so six products' reviews cause 6 extra queries.
N+1 queries compared with a single fetch joinLeft: the service calls findAll, which runs one select on products, then reading each product category runs a separate select on categories for id 1, 2 and 3, for 4 queries in total. Right: findAllWithCategory runs one select that joins products and categories, for 1 query in total. Lazy loading in a loop findAll(), then p.getCategory().getName() Service Database select products select category id=1 select category id=2 select category id=3 4 queries (1 + N) JOIN FETCH or @EntityGraph findAllWithCategory() Service Database select products join categories 1 query
Figure 1: reading lazy categories in a loop sends one extra query per distinct category; a fetch join loads the same data in a single round trip.

4. Setup: Make Every Query Visible

You cannot fix what you cannot see. The example uses Spring Data JPA, Spring MVC and an H2 in-memory database:

pom.xml 
<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-webmvc</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

Turn on SQL logging in development so every statement appears in the console:

spring.application.name=catalog
spring.datasource.url=jdbc:h2:mem:catalog
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.defer-datasource-initialization=true
spring.jpa.open-in-view=false
logging.level.org.hibernate.SQL=debug

logging.level.org.hibernate.SQL=debug prints each SQL statement through your logger. spring.jpa.open-in-view=false stops the database session from staying open while JSON is written, which section 9.4 shows is important. The sample data (data.sql in section 7) has 3 categories, 6 products and 8 reviews.

Version note: Spring Boot 4.1 ships Hibernate ORM 7.4 and renames the web starter to spring-boot-starter-webmvc. Everything on this page also works on Spring Boot 3.x (Hibernate 6.6) except the pagination behaviour in section 9.2, which changed in Hibernate 7.

5. How to Spot the N+1 Problem in the SQL Log

This looks like harmless service code:

ProductCatalogService.java (slow version) 
@Transactional(readOnly = true)
public List<String> productLabels() {
    return productRepository.findAll().stream()
            .map(p -> p.getName() + " (" + p.getCategory().getName() + ")")
            .toList();
}

The SQL log tells a different story. One query loads the products, then one query per distinct category:

Console 
DEBUG org.hibernate.SQL : select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0
DEBUG org.hibernate.SQL : select c1_0.id,c1_0.name from categories c1_0 where c1_0.id=?
DEBUG org.hibernate.SQL : select c1_0.id,c1_0.name from categories c1_0 where c1_0.id=?
DEBUG org.hibernate.SQL : select c1_0.id,c1_0.name from categories c1_0 where c1_0.id=?

Reading each product's reviews is worse, because every collection is its own query:

Console (reading p.getReviews() for each product) 
DEBUG org.hibernate.SQL : select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0
DEBUG org.hibernate.SQL : select r1_0.product_id,r1_0.id,r1_0.comment,r1_0.rating from reviews r1_0 where r1_0.product_id=?
DEBUG org.hibernate.SQL : select r1_0.product_id,r1_0.id,r1_0.comment,r1_0.rating from reviews r1_0 where r1_0.product_id=?
DEBUG org.hibernate.SQL : select r1_0.product_id,r1_0.id,r1_0.comment,r1_0.rating from reviews r1_0 where r1_0.product_id=?
DEBUG org.hibernate.SQL : select r1_0.product_id,r1_0.id,r1_0.comment,r1_0.rating from reviews r1_0 where r1_0.product_id=?
DEBUG org.hibernate.SQL : select r1_0.product_id,r1_0.id,r1_0.comment,r1_0.rating from reviews r1_0 where r1_0.product_id=?
DEBUG org.hibernate.SQL : select r1_0.product_id,r1_0.id,r1_0.comment,r1_0.rating from reviews r1_0 where r1_0.product_id=?

The pattern to look for is the same statement repeated with a different id=?, right after a query that returned a list.

6. Fetch Strategies That Fix the N+1 Problem

Each strategy below was measured on the same data (6 products, 3 categories, 8 reviews) with Hibernate's statistics.

6.1 FetchType.LAZY vs FetchType.EAGER

Switching a relationship to FetchType.EAGER looks like a fix, but it isn't. With an eager @ManyToOne, findAll() still runs a separate query per category. It does so immediately, even if your code never reads the category:

Console (@ManyToOne without fetch = LAZY, category never read) 
DEBUG org.hibernate.SQL : select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0
DEBUG org.hibernate.SQL : select c1_0.id,c1_0.name from categories c1_0 where c1_0.id=?
DEBUG org.hibernate.SQL : select c1_0.id,c1_0.name from categories c1_0 where c1_0.id=?
DEBUG org.hibernate.SQL : select c1_0.id,c1_0.name from categories c1_0 where c1_0.id=?

A JPQL query such as findAll() loads only the products, and Hibernate then fetches eager relationships with extra queries. So keep relationships LAZY, as in this tutorial's entities, and choose what to load per query with the strategies below. Note that @ManyToOne and @OneToOne are eager by default, so you must set fetch = FetchType.LAZY on them yourself.

6.2 JOIN FETCH in JPQL

A fetch join tells Hibernate to load the related entity in the same SQL query and fill in the relationship, instead of leaving a proxy:

ProductRepository.java 
@Query("select p from Product p join fetch p.category order by p.sku")
List<Product> findAllWithCategory();
Console 
DEBUG org.hibernate.SQL : select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,p1_0.name,p1_0.price,p1_0.sku from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id order by p1_0.sku

One query instead of four. Use join fetch for single-valued relationships (@ManyToOne, @OneToOne) freely. Fetch-joining collections needs more care (sections 9.1 and 9.5).

6.3 @EntityGraph

An entity graph lists the relationships to load with a query, without writing JPQL. Spring Data applies it to any repository method, including derived queries:

ProductRepository.java 
@EntityGraph(attributePaths = "category")
List<Product> findAllByOrderBySkuAsc();

@EntityGraph(attributePaths = {"category", "reviews"})
Optional<Product> findBySku(String sku);

The first method produces exactly the same single SQL statement as the fetch join above. The second loads a product, its category and its reviews in one query, which is ideal for a detail page. Use entity graphs when you want to keep using derived query methods, or to reuse one query with different fetch plans.

6.4 Batch Fetching with @BatchSize and default_batch_fetch_size

Batch fetching keeps lazy loading but loads many proxies or collections at once with an IN list. The first access loads the related data for up to batch size owners in one query. You can enable it globally or per mapping:

application.properties, Product.java, Category.java 
// Globally, in application.properties:
// spring.jpa.properties.hibernate.default_batch_fetch_size=25

// Or per mapping, with org.hibernate.annotations.BatchSize:
@OneToMany(mappedBy = "product")
@BatchSize(size = 25)
private List<Review> reviews = new ArrayList<>();

@Entity
@BatchSize(size = 25)   // batches loading of Category proxies
@Table(name = "categories")
public class Category { ... }
Console 
DEBUG org.hibernate.SQL : select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0
DEBUG org.hibernate.SQL : select c1_0.id,c1_0.name from categories c1_0 where c1_0.id in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

DEBUG org.hibernate.SQL : select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0
DEBUG org.hibernate.SQL : select r1_0.product_id,r1_0.id,r1_0.comment,r1_0.rating from reviews r1_0 where r1_0.product_id in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Loading the categories and the reviews for all 6 products now takes 2 queries each, whatever the loop does. Batch fetching doesn't reach 1 query, but it needs no query changes. That makes it a good safety net for code paths you haven't optimised yet.

6.5 FetchMode.SUBSELECT

For collections, Hibernate's @Fetch(FetchMode.SUBSELECT) loads the collections for all owners loaded by the previous query in one go, by repeating that query as a subquery:

Product.java 
@OneToMany(mappedBy = "product")
@Fetch(FetchMode.SUBSELECT)   // org.hibernate.annotations.Fetch and FetchMode
private List<Review> reviews = new ArrayList<>();
Console 
DEBUG org.hibernate.SQL : select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0
DEBUG org.hibernate.SQL : select r1_0.product_id,r1_0.id,r1_0.comment,r1_0.rating from reviews r1_0 where r1_0.product_id in (select p1_0.id from products p1_0)

It is always 2 queries, no matter how many products there are. Because it re-runs the original query, it works best when that query is cheap.

6.6 DTO Projections

When a page only displays data, you often don't need entities at all. A DTO projection selects just the columns the page shows, straight into a record. It even aggregates the reviews in the database:

ProductRepository.java 
@Query("""
        select new com.javabytechie.catalog.product.ProductSummary(
            p.sku, p.name, p.price, c.name, count(r), avg(r.rating))
        from Product p
        join p.category c
        left join p.reviews r
        group by p.sku, p.name, p.price, c.name
        order by p.sku
        """)
List<ProductSummary> findSummaries();
Console 
DEBUG org.hibernate.SQL : select p1_0.sku,p1_0.name,p1_0.price,c1_0.name,count(r1_0.id),avg(cast(r1_0.rating as float(53))) from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id left join reviews r1_0 on p1_0.id=r1_0.product_id group by p1_0.sku,p1_0.name,p1_0.price,c1_0.name order by p1_0.sku

One query, no proxies, and no lazy-loading surprises later. Projections are the best choice for read-only list pages and reports. Use entities when you need to modify the data.

6.7 Strategy Comparison

StrategyQueries for 6 productsBest for
Lazy loading in a loop4 (categories) / 7 (reviews)Single-entity screens only
FetchType.EAGER4, even when unusedAlmost never
JOIN FETCH1A known use case with single-valued relationships
@EntityGraph1Derived query methods, detail pages
Batch fetching (size 10 to 25)2A global safety net, large or varied graphs
FetchMode.SUBSELECT2Collections on a cheap parent query
DTO projection1Read-only lists, reports, aggregates

7. A Complete Example: Product Catalog API

The catalog API has two endpoints. GET /api/products lists products with their category, review count and average rating, and GET /api/products/{sku} shows one product with its reviews. Each endpoint runs exactly one SQL query. The code runs on Spring Boot 4.1 with Java 21.

7.1 The Entities

Every relationship is lazy. The fetch plan is chosen per query in the repository.

package com.javabytechie.catalog.product;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "categories")
public class Category {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true)
    private String name;

    protected Category() {
    }

    public Category(String name) {
        this.name = name;
    }

    public Long getId() { return id; }
    public String getName() { return name; }
}

7.2 Repository and DTOs

package com.javabytechie.catalog.product;

import java.util.List;
import java.util.Optional;

import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface ProductRepository extends JpaRepository<Product, Long> {

    // Fix 1: a fetch join loads each product's category in the same query
    @Query("select p from Product p join fetch p.category order by p.sku")
    List<Product> findAllWithCategory();

    // Fix 2: the same result, described with an entity graph instead of JPQL
    @EntityGraph(attributePaths = "category")
    List<Product> findAllByOrderBySkuAsc();

    // One product with its category and reviews, for the detail page
    @EntityGraph(attributePaths = {"category", "reviews"})
    Optional<Product> findBySku(String sku);

    // Fix 3: a DTO projection selects only the columns the list page needs
    @Query("""
            select new com.javabytechie.catalog.product.ProductSummary(
                p.sku, p.name, p.price, c.name, count(r), avg(r.rating))
            from Product p
            join p.category c
            left join p.reviews r
            group by p.sku, p.name, p.price, c.name
            order by p.sku
            """)
    List<ProductSummary> findSummaries();
}

7.3 Service, Controller and Sample Data

The service builds DTOs inside read-only transactions, so no lazy data is touched after the session closes. The transaction management tutorial explains @Transactional(readOnly = true).

package com.javabytechie.catalog.product;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class ProductCatalogService {

    private static final Logger log = LoggerFactory.getLogger(ProductCatalogService.class);

    private final ProductRepository productRepository;

    public ProductCatalogService(ProductRepository productRepository) {
        this.productRepository = productRepository;
    }

    // List page: one query, only the columns the page shows
    @Transactional(readOnly = true)
    public List<ProductSummary> listProducts() {
        List<ProductSummary> summaries = productRepository.findSummaries();
        log.info("Listed {} products", summaries.size());
        return summaries;
    }

    // Detail page: one query for the product, its category and its reviews
    @Transactional(readOnly = true)
    public ProductDetails getProduct(String sku) {
        return productRepository.findBySku(sku)
                .map(ProductDetails::from)
                .orElseThrow(() -> new ProductNotFoundException(sku));
    }
}

7.4 Run It

Start the app with ./mvnw spring-boot:run and call the list endpoint:

Terminal 
curl http://localhost:8080/api/products

[{"sku":"KB-100","name":"Mechanical Keyboard","price":89.00,"category":"Keyboards","reviewCount":2,"averageRating":4.5},
 {"sku":"KB-200","name":"Low-Profile Keyboard","price":59.00,"category":"Keyboards","reviewCount":1,"averageRating":4.0},
 {"sku":"MN-100","name":"24-inch Monitor","price":159.00,"category":"Monitors","reviewCount":1,"averageRating":5.0},
 {"sku":"MN-200","name":"27-inch Monitor","price":219.00,"category":"Monitors","reviewCount":2,"averageRating":4.5},
 {"sku":"MS-100","name":"Wireless Mouse","price":25.50,"category":"Mice","reviewCount":1,"averageRating":5.0},
 {"sku":"MS-200","name":"Gaming Mouse","price":49.00,"category":"Mice","reviewCount":1,"averageRating":3.0}]
Console 
DEBUG org.hibernate.SQL : select p1_0.sku,p1_0.name,p1_0.price,c1_0.name,count(r1_0.id),avg(cast(r1_0.rating as float(53))) from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id left join reviews r1_0 on p1_0.id=r1_0.product_id group by p1_0.sku,p1_0.name,p1_0.price,c1_0.name order by p1_0.sku
INFO  c.j.c.product.ProductCatalogService : Listed 6 products

The detail endpoint returns the product with its reviews, again from a single query:

Terminal 
curl http://localhost:8080/api/products/MN-200

{"sku":"MN-200","name":"27-inch Monitor","price":219.00,"category":"Monitors","reviews":[{"rating":4,"comment":"Good value"},{"rating":5,"comment":"Perfect for work"}]}
Console 
DEBUG org.hibernate.SQL : select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,p1_0.name,p1_0.price,r1_0.product_id,r1_0.id,r1_0.comment,r1_0.rating,p1_0.sku from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id left join reviews r1_0 on p1_0.id=r1_0.product_id where p1_0.sku=?

An unknown SKU returns 404 Not Found through ProductNotFoundException:

Terminal 
curl -i http://localhost:8080/api/products/XX-1

HTTP/1.1 404
{"timestamp":"2026-10-24T18:21:20.054Z","status":404,"error":"Not Found","path":"/api/products/XX-1"}

8. Configuration Options

PropertyDefaultWhat it does
logging.level.org.hibernate.SQL=debugOffLogs every SQL statement. Use it in development and tests
spring.jpa.properties.hibernate.generate_statisticsfalseCollects query counts and timings. Turn it on in tests, and in production only for short investigations
spring.jpa.properties.hibernate.default_batch_fetch_sizeNot set (no batching)Enables batch fetching for every lazy relationship and collection
spring.jpa.open-in-viewtrue (with a startup warning)Keeps the session open during the web request. Set it to false so lazy loading cannot hide in controllers and JSON serialization
spring.jpa.properties.hibernate.query.fail_on_pagination_over_collection_fetchfalseThrows an error instead of paging in memory when a query cannot page in SQL (section 9.2)

9. Common Pitfalls and How to Fix Them

9.1 MultipleBagFetchException

A List collection without an index column is a "bag" in Hibernate terms. Fetch-joining two bags in one query fails, because the combined rows can't be split back into two lists reliably. If Product also had a List<String> imageUrls collection:

ProductRepository.java 
@Query("select p from Product p left join fetch p.reviews left join fetch p.imageUrls")
List<Product> findAllWithReviewsAndImages();
Console 
org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags: [com.javabytechie.catalog.product.Product.imageUrls, com.javabytechie.catalog.product.Product.reviews]

How to fix it: fetch one collection with the query and let batch fetching or SUBSELECT load the other, or run two queries (one per collection) in the same transaction. Changing one collection to a Set also works, but see section 9.5 first.

9.2 Pagination with a Collection Fetch

Paging a query that fetches a collection is tricky, because a SQL LIMIT counts joined rows, not products. Hibernate's behaviour here changed between versions. With an entity graph on reviews and PageRequest.of(0, 2):

Console (Spring Boot 3.5, Hibernate 6.6) 
WARN  HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory
DEBUG org.hibernate.SQL : select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,r1_0.product_id,r1_0.id,r1_0.comment,r1_0.rating,p1_0.sku from products p1_0 left join reviews r1_0 on p1_0.id=r1_0.product_id
Console (Spring Boot 4.1, Hibernate 7.4) 
DEBUG org.hibernate.SQL : select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,r1_0.product_id,r1_0.id,r1_0.comment,r1_0.rating,p1_0.sku from (select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 offset ? rows fetch first ? rows only) p1_0(id,category_id,name,price,sku) left join reviews r1_0 on p1_0.id=r1_0.product_id

Hibernate 6 (Spring Boot 3.x) loads every row and pages in memory, which can exhaust memory on large tables. Hibernate 7 (Spring Boot 4.x) pushed the limit into a subquery in this test. How to fix it: page the parent entities first (or page a DTO projection), then fetch the collections for that page with batch fetching or a second query. Set hibernate.query.fail_on_pagination_over_collection_fetch=true so any in-memory paging fails loudly instead of slowing down silently. The pagination and sorting tutorial covers Pageable in general.

9.3 Switching Relationships to EAGER

As section 6.1 showed, EAGER moves the extra queries earlier but doesn't remove them, and they now run even when the data isn't needed. How to fix it: keep every relationship LAZY and fetch per use case.

9.4 Open Session in View Hides the Problem

With spring.jpa.open-in-view=true (the default), a controller or the JSON serializer can trigger lazy loading after your service returns. The N+1 queries then run where you don't look for them. How to fix it: set it to false, return DTOs from services, and treat a LazyInitializationException as a sign that a fetch plan is missing.

9.5 Fetching Large Collections with Joins

Fetch-joining a collection multiplies rows: each product row is repeated once per review. Fetching two collections at once (for example by turning both into Sets) multiplies them again, a Cartesian product that can return far more rows than entities. Hibernate 6 and later remove duplicate parent entities from the result automatically, so distinct is no longer needed for that, but the database still sends every row. How to fix it: fetch-join at most one collection per query, and use batch fetching or a second query for the others.

10. Testing: Count Queries to Catch N+1 Early

The most reliable guard against the N+1 problem is a test that asserts the number of SQL statements. Hibernate's Statistics object counts them once hibernate.generate_statistics is on. The test below measures the same work before and after the fix:

ProductQueryCountTest.java 
package com.javabytechie.catalog.product;

import static org.assertj.core.api.Assertions.assertThat;

import java.util.List;

import org.hibernate.SessionFactory;
import org.hibernate.stat.Statistics;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.data.jpa.test.autoconfigure.DataJpaTest;
import org.springframework.boot.jpa.test.autoconfigure.TestEntityManager;
import org.springframework.test.context.TestPropertySource;

import jakarta.persistence.EntityManagerFactory;

@DataJpaTest
@TestPropertySource(properties = "spring.jpa.properties.hibernate.generate_statistics=true")
class ProductQueryCountTest {

    @Autowired
    private ProductRepository productRepository;

    @Autowired
    private TestEntityManager entityManager;

    @Autowired
    private EntityManagerFactory entityManagerFactory;

    private Statistics statistics;

    @BeforeEach
    void resetCounters() {
        statistics = entityManagerFactory.unwrap(SessionFactory.class).getStatistics();
        entityManager.clear(); // start with an empty persistence context
        statistics.clear();
    }

    @Test
    void fetchJoinTurnsNPlusOneIntoOneQuery() {
        // Before: findAll() plus a lazy category per product
        List<String> naive = productRepository.findAll().stream()
                .map(p -> p.getCategory().getName())
                .toList();
        long naiveQueries = statistics.getPrepareStatementCount();

        entityManager.clear();
        statistics.clear();

        // After: the categories arrive with the products
        List<String> fetched = productRepository.findAllWithCategory().stream()
                .map(p -> p.getCategory().getName())
                .toList();
        long fetchedQueries = statistics.getPrepareStatementCount();

        assertThat(naive).hasSize(6);
        assertThat(naiveQueries).isEqualTo(4);   // 1 for products + 1 per distinct category (3)
        assertThat(fetched).containsExactlyInAnyOrderElementsOf(naive);
        assertThat(fetchedQueries).isEqualTo(1);
    }

    @Test
    void summaryProjectionNeedsOneQuery() {
        List<ProductSummary> summaries = productRepository.findSummaries();

        assertThat(statistics.getPrepareStatementCount()).isEqualTo(1);
        assertThat(summaries).hasSize(6);
        assertThat(summaries.getFirst().sku()).isEqualTo("KB-100");
        assertThat(summaries.getFirst().reviewCount()).isEqualTo(2);
        assertThat(summaries.getFirst().averageRating()).isEqualTo(4.5);
    }

    @Test
    void detailGraphLoadsReviewsInTheSameQuery() {
        Product monitor = productRepository.findBySku("MN-200").orElseThrow();
        int reviewCount = monitor.getReviews().size();

        assertThat(reviewCount).isEqualTo(2);
        assertThat(monitor.getCategory().getName()).isEqualTo("Monitors");
        assertThat(statistics.getPrepareStatementCount()).isEqualTo(1);
    }
}
  • entityManager.clear() empties the persistence context, so entities cached by an earlier step can't hide queries.
  • getPrepareStatementCount() counts every SQL statement Hibernate prepared since statistics.clear().
  • If someone later changes findAllWithCategory() or the detail graph, the count assertions fail immediately.

See the Spring Boot testing tutorial for more on test slices.

Version note: in Spring Boot 4.x, @DataJpaTest is in org.springframework.boot.data.jpa.test.autoconfigure and needs spring-boot-starter-data-jpa-test, and TestEntityManager is in org.springframework.boot.jpa.test.autoconfigure. In Spring Boot 3.x, both are in org.springframework.boot.test.autoconfigure.orm.jpa.

11. Choosing a Fetch Strategy

SituationGood choicePoor choice
Read-only list page or reportDTO projectionLoading full entities and their collections
Detail page for one entity@EntityGraph or JOIN FETCHLazy loading each part separately
List with a @ManyToOne shown per rowJOIN FETCH or @EntityGraphFetchType.EAGER
List that needs several collectionsBatch fetching or SUBSELECTFetch-joining all collections at once
Paged list with a collectionPage the parents, then batch-load the collectionCollection fetch join plus Pageable
Code paths you haven't tuned yetA global default_batch_fetch_sizeMaking relationships eager "just in case"

12. Best Practices

  • Make every relationship lazy, including @ManyToOne and @OneToOne, which are eager by default.
  • Decide the fetch plan per use case in the repository, with JOIN FETCH, @EntityGraph or a projection, instead of in the entity mapping.
  • Use DTO projections for read-only lists, so pages load only the columns they show.
  • Fetch-join at most one collection per query, and load other collections with batch fetching or a second query.
  • Set a global default_batch_fetch_size (for example 16 to 50) as a safety net for code paths you haven't tuned.
  • Turn off spring.jpa.open-in-view and build DTOs inside @Transactional(readOnly = true) service methods.
  • Watch the SQL log during development, and look for the same statement repeated with different ids.
  • Assert query counts in tests with Hibernate statistics, so a fixed N+1 problem cannot return unnoticed.
  • Page parents, not joined rows, and enable fail_on_pagination_over_collection_fetch to catch in-memory paging.

13. Summary

The JPA N+1 problem appears when lazy relationships are read in a loop: one query loads the list, then one more runs for each row's related data. FetchType.EAGER doesn't fix it. It only runs the same extra queries sooner. Keep every relationship lazy and choose a fetch strategy per use case: JOIN FETCH or @EntityGraph to load single-valued relationships and one collection in one query, batch fetching or SUBSELECT to load collections in a fixed number of queries, and DTO projections for read-only pages. Turn off open session in view, watch the SQL log, and assert query counts in tests. The main recommendation: use projections for lists and entity graphs for detail pages, and keep a global batch size as a safety net for everything else.