Spring Boot JPA N+1 Problem: Fetch Joins, Entity Graphs, Batching

What the N+1 problem is and how lazy loading triggers it

An endpoint that lists recent orders runs one query to load the orders. It then runs one more query for each order the moment code touches a lazy association. Fifty orders produce fifty-one round trips. That is the N+1 pattern.

The model used throughout this article has three entities. Order has a @ManyToOne(fetch = FetchType.LAZY) reference to Customer and a @OneToMany(mappedBy = "order") collection of OrderLine. Collections are lazy by default in JPA. To-one associations default to eager, so the LAZY on customer must be declared explicitly. Hibernate fills lazy references with uninitialized proxies or collection wrappers, and the first access to one of them issues a select through the persistence context.

CopyService              Persistence context            Database
  |-- findRecent() ------->|-- select orders ------------>|  (1)
  |<-- 50 Order proxies ---|<-----------------------------|
  |  loop: order.getCustomer().getName()
  |------------------------>|-- select customer id=? ---->|  (N)
  |------------------------>|-- select customer id=? ---->|
  |          ... repeated per order ...                   |

The trigger is rarely an obvious loop. It is often a mapper that copies customer.name into a DTO, or a JSON serializer walking every getter. Neither looks like data access in a code review.

The fix does not belong in the mapping. One use case needs customers but not lines, and another needs lines but not customers. Each query needs its own fetch plan. Backend and platform engineers who own list endpoints, exports or reports should treat fetch planning as part of designing each endpoint. Latency there grows with result size, and connection pools fill up under load.

Copy// Order.java: "order" is a reserved SQL word, so map to "orders"
@Entity
@Table(name = "orders")
public class Order {
    @Id @GeneratedValue
    private Long id;

    // To-one defaults to EAGER in JPA, so LAZY must be explicit.
    // Hibernate puts an uninitialized Customer proxy here.
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "customer_id")
    private Customer customer;

    // Collections are LAZY by default. Hibernate puts a collection wrapper here.
    @OneToMany(mappedBy = "order")
    private List<OrderLine> lines = new ArrayList<>();

    public Customer getCustomer() { return customer; }
    public List<OrderLine> getLines() { return lines; }
}

// OrderLine.java: the owning side of the one-to-many association
@Entity
public class OrderLine {
    @Id @GeneratedValue
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "order_id")
    private Order order;
}

// OrderSummaryMapper.java: looks harmless in review, but runs 1 + N selects
List<OrderSummary> toSummaries(List<Order> orders) {   // orders came from 1 select
    return orders.stream()
        .map(o -> new OrderSummary(o.getId(), o.getCustomer().getName())) // proxy init: 1 select per distinct customer
        .toList();
}

The Order and OrderLine mappings with explicitly lazy to-one and default-lazy collection associations, plus a DTO mapper whose access to customer.getName() initializes a proxy and fires one extra select for each distinct customer not already in the persistence context.

Why FetchType.EAGER and open-in-view do not fix it

Switching an association to FetchType.EAGER looks like a fix because the collection is always populated. It moves the problem rather than removing it. EAGER is declared on the mapping, so it applies to every code path that loads the entity: a list endpoint, a background job that only needs one column, an existsBy check. You cannot reliably turn it back to lazy for a single query.

EAGER also does not guarantee a join. When you run a JPQL or derived query, Hibernate executes the statement as written and then loads each EAGER association it did not fetch. Often that means one extra select per parent row, which is the same N+1 pattern, now with no opt-out.

spring.jpa.open-in-view hides the problem in a different way. Spring Boot enables it by default and logs a startup warning about it. It keeps the persistence context open until the web response is written. Lazy associations touched by a controller, template or JSON serializer therefore load silently. Each one is a separate query, run outside the service transaction, and the database connection is held for the whole request.

With open-in-view disabled, the same access throws LazyInitializationException. That exception means a caller needed data the query never planned to load. It points at the endpoint whose fetch plan is incomplete, so the N+1 shows up during development instead of in production latency.

On an established application, disabling the setting is not a one-line change. Before you change the flag, audit the controllers, serializers and view models that rely on it. Keep associations lazy by default, and state what each use case loads in the query that serves it.

Copy// application.properties: spring.jpa.open-in-view=false
// Lazy access outside a transaction now throws LazyInitializationException
// during development instead of issuing silent queries during rendering.

@Entity
public class PurchaseOrder {
    @Id @GeneratedValue
    private Long id;

    private String status;

    // @ManyToOne defaults to EAGER, so declare LAZY explicitly.
    @ManyToOne(fetch = FetchType.LAZY)
    private Customer customer;

    // @OneToMany is already LAZY by default. Do not switch it to EAGER,
    // because that would affect every query that loads PurchaseOrder.
    @OneToMany(mappedBy = "order")
    private List<OrderLine> lines = new ArrayList<>();
}

public interface PurchaseOrderRepository extends JpaRepository<PurchaseOrder, Long> {

    // The list endpoint needs only order columns. No associations load.
    List<PurchaseOrder> findByStatus(String status);

    // The detail use case declares what it loads in the query that serves it.
    @Query("select distinct o from PurchaseOrder o "
         + "join fetch o.customer "
         + "join fetch o.lines "
         + "where o.id = :id")
    Optional<PurchaseOrder> findDetailById(@Param("id") Long id);
}

With open-in-view disabled and associations kept lazy, each use case states its own fetch plan: a plain derived query for the list view and a JOIN FETCH query for the detail view.

How to detect N+1 queries in development and tests

The fastest feedback loop is making the SQL visible. In development, set logging.level.org.hibernate.SQL=DEBUG to log every statement Hibernate sends. Prefer this over spring.jpa.show-sql, which writes to standard out and bypasses your logging configuration. Add spring.jpa.properties.hibernate.format_sql=true for readability. Then call the endpoint you care about and read the log. The N+1 signature is one select against the parent table followed by a run of near-identical selects against a child table, each filtered on a single foreign key value. If the run grows with the size of the result set, you have found it.

Setting spring.jpa.properties.hibernate.generate_statistics=true makes Hibernate log a per-session summary, including how many JDBC statements were prepared. That count is what you can assert on in tests. In an integration test, unwrap SessionFactory from the EntityManagerFactory and call getStatistics(). Call clear() immediately before the operation under test, invoke it, then assert on getPrepareStatementCount(). A regression, such as someone adding a lazily loaded field to a response DTO, now fails the build. The alternative is a latency alert in production followed by an afternoon of log archaeology.

These assertions break easily if they count incidental work, such as fixture inserts, sequence lookups or security queries. Scope each assertion to a single service or repository call, and flush and clear the persistence context before measuring so the first-level cache does not hide queries. Use fixtures with several parents, each with several children. With a single row, N+1 and a fetch join can issue the same number of statements, so the test cannot tell them apart.

Copyimport jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import org.hibernate.SessionFactory;
import org.hibernate.stat.Statistics;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.transaction.annotation.Transactional;
import static org.assertj.core.api.Assertions.assertThat;

// Statistics are off by default, so enable them only for this test context
@SpringBootTest(properties = "spring.jpa.properties.hibernate.generate_statistics=true")
@Transactional // roll back fixtures after the test
class AuthorServiceQueryCountTest {
  @Autowired EntityManagerFactory emf;
  @Autowired EntityManager em;
  @Autowired AuthorService authorService;

  @Test
  void listingAuthorsWithBooksIssuesOneSelect() {
    // 5 authors x 3 books: an N+1 regression would show up as 1 + 5 selects
    for (int i = 0; i < 5; i++) {
      Author author = new Author("Author " + i);
      for (int j = 0; j < 3; j++) author.addBook(new Book("Book " + i + "-" + j));
      em.persist(author); // assumes cascade persist from Author to Book
    }
    em.flush(); // send fixture inserts now so they are not counted
    em.clear(); // empty the first-level cache so loads must hit the database

    Statistics stats = emf.unwrap(SessionFactory.class).getStatistics();
    stats.clear(); // measure only the call below

    var authors = authorService.findAllWithBooks(); // maps entities to DTOs

    assertThat(authors).hasSize(5);
    assertThat(stats.getPrepareStatementCount()).isEqualTo(1);
  }
}

An integration test that flushes and clears fixtures, resets Hibernate statistics, and asserts that a single service call over several parents with several children prepares exactly one JDBC statement, so an N+1 regression fails the build.

Fetch joins and entity graphs: loading associations in one query

A JPQL join fetch tells Hibernate to populate an association from the same result set instead of leaving a proxy. For select o from Order o join fetch o.customer, the SQL is one inner join from orders to customers, and each row maps to exactly one Order. Fetching o.lines changes the shape. The result has one row per order line, so the parent columns repeat. Older Hibernate versions return a duplicated Order for every line unless you add DISTINCT or deduplicate the results yourself. Newer versions deduplicate entity results automatically. Check which behavior your version has before relying on either.

@EntityGraph declares the same fetch plan per repository method without rewriting the query. Use attributePaths = {"customer", "lines"} on a derived finder, or reference a @NamedEntityGraph defined on the entity. Under fetch-graph semantics, the default, listed attributes are loaded eagerly and unlisted ones are treated as lazy. Load-graph semantics loads the listed attributes eagerly and leaves the others at their mapped fetch type.

There are three cases where you should avoid these techniques:

• Pagination with a collection fetch. SQL LIMIT would cut through child rows, so Hibernate fetches everything and pages in memory. Instead, page over order IDs first, then fetch-join the lines for those IDs.
• Fetching two or more collections at once. This produces a Cartesian product. With List bags it throws MultipleBagFetchException. Switching to Set removes the exception but still returns lines × payments rows per order.
• Many near-duplicate methods. Repositories accumulate variants like findWithLines and findWithLinesAndCustomer. Their fetch plans drift apart, and callers end up picking the wrong one.

Copy// On the entity: @NamedEntityGraph(name = "Order.withCustomer", attributeNodes = @NamedAttributeNode("customer"))
public interface OrderRepository extends JpaRepository<Order, Long> {

  // To-one: SQL is one inner join orders -> customers, one row per Order.
  @Query("select o from Order o join fetch o.customer where o.status = :status")
  List<Order> findWithCustomer(@Param("status") OrderStatus status);

  // To-many: SQL returns one row per order line, so parent columns repeat.
  // DISTINCT dedups Order instances on older Hibernate; Hibernate 6 dedups automatically.
  @Query("select distinct o from Order o join fetch o.lines where o.id in :ids order by o.id")
  List<Order> findWithLinesByIdIn(@Param("ids") Collection<Long> ids);

  // Same fetch plan without writing JPQL. Default FETCH semantics: unlisted attributes are lazy.
  @EntityGraph(attributePaths = {"customer", "lines"})
  List<Order> findByStatus(OrderStatus status);

  // Named graph with LOAD semantics: unlisted attributes keep their mapped fetch type.
  @EntityGraph(value = "Order.withCustomer", type = EntityGraph.EntityGraphType.LOAD)
  Optional<Order> findById(Long id);

  // Step 1 of safe pagination: page over parent IDs only, so LIMIT applies to orders, not lines.
  @Query(value = "select o.id from Order o where o.status = :status",
         countQuery = "select count(o) from Order o where o.status = :status")
  Page<Long> findIdsByStatus(@Param("status") OrderStatus status, Pageable pageable);
}

// Service usage: page IDs in SQL, then fetch-join lines for just that page.
// Page<Long> ids = repo.findIdsByStatus(OrderStatus.OPEN, PageRequest.of(0, 20, Sort.by("id")));
// List<Order> orders = repo.findWithLinesByIdIn(ids.getContent());
// Avoid: join fetch o.lines join fetch o.payments (two List bags -> MultipleBagFetchException;
// as Sets -> no exception, but lines x payments rows per order).

A Spring Data JPA repository showing join fetch for to-one and to-many associations, @EntityGraph with attributePaths and a named LOAD graph, and ID-first pagination to avoid in-memory paging of collection fetches.

Batch fetching with @BatchSize and the default batch fetch size

Batch fetching takes a different approach. It does not widen the original query. Associations stay lazy, but when Hibernate initializes one uninitialized proxy or collection, it also loads others of the same type that are already in the persistence context.

You can enable it per association by putting @BatchSize(size = 25) on a collection field such as Order.lines, or on an entity class so that proxies of that entity are batched. You can also set it globally with spring.jpa.properties.hibernate.default_batch_fetch_size=25. In both cases, iterating 100 orders and touching getLines() no longer issues 100 selects. Hibernate instead runs a few queries of the form select … from order_line where order_id in (?, ?, …), each covering up to 25 parent IDs. That is roughly N divided by the batch size.

This is the right tool for several cases:

• Multiple collections. Each collection is loaded separately, so there is no Cartesian product.
• Deep graphs. Each level is batched on its own.
• Paginated parents. The page query stays a plain LIMIT/OFFSET over orders, with no in-memory pagination.

The trade-offs are real:

• Query counts are harder to predict than a single join. They depend on how many uninitialized proxies are in the persistence context when the first access happens, and on the order in which your code touches them. A refactor that changes iteration order or session scope can change the count.
• Large batch sizes produce long IN lists. These can fragment the statement and plan caches and can run into database limits on bind parameters or list length.

Treat the batch size as a value to tune. Start moderate, measure query counts and latency against realistic data, and adjust from there. Do not simply set it as high as possible.

Choosing a strategy by query shape, including DTO projections

Pick the technique by the shape of the result you need, not by habit.

• To-one associations and at most one collection: use a fetch join or an entity graph. One query returns everything. Entity graphs are easier to reuse on derived repository methods. Fetch joins give you explicit control in JPQL.
• Multiple collections, deep graphs, or paginated lists: use batch fetching. Fetch-joining two List collections throws a MultipleBagFetchException. Fetch-joining two Set collections avoids the exception but returns a cartesian product. Paginating a collection fetch join makes Hibernate load every row and page in memory. Batch fetching keeps the root query paginated and loads children in a few IN queries.
• Read-only endpoints needing a few columns: use a DTO projection. An interface projection works when the repository method returns something like OrderSummary with getters such as getId() and getCustomerName(). A constructor expression works too: select new com.example.OrderSummary(o.id, c.name) from Order o join o.customer c.

The trade-off is about what you keep in memory. Loading full entity graphs for a read-only view materializes every column. It also registers each entity in the persistence context and pays for dirty checking at flush, even though nothing will be written. DTOs avoid all of that, but they are not managed: you cannot modify and save them, and you cannot navigate lazily to anything you did not select.

Worked example and impact on cost and engineering time

Illustrative estimate (assumed numbers, not a benchmark)

Assume one page of N = 50 orders. Each order has a lazy customer (many-to-one, all 50 customers distinct) and a lazy lines collection with L = 4 order lines. The batch size is S = 25. The service touches both associations for every order.

The single-query rows are misleading for paging. A collection fetch join multiplies rows, so Hibernate cannot apply the limit in SQL and paginates in memory. That makes the hybrid row the safer default for this page. The query count also understates the cost of the baseline. Each of the 100 extra statements adds a network round trip and holds a connection for longer, and both costs grow linearly with N.

Where the engineering time goes

The fixes cost effort up front. Someone has to plan fetches for each use case, build fixtures with realistic association counts, and audit the endpoints that relied on open-in-view before it is disabled. That work is bounded and is done once per endpoint.

The alternative costs time without a limit. Database load grows quietly, and engineers end up diagnosing latency regressions in production from symptoms. SQL logging in development, together with tests that assert the number of statements per use case, turns a regression into a failing build that names the endpoint. Engineers then fix it in minutes instead of reconstructing it from production traces.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to spring boot jpa n+1 problem, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.