The N+1 Query Problem in Hibernate

← Back to the section

You query 50 orders and notice 51 SQL queries in the logs. That is the N+1 problem: one query for the list plus one query per related entity.

Where N+1 comes from

By default, Hibernate loads related collections lazily (FetchType.LAZY). When you iterate over the results and access a collection field, Hibernate goes to the database for each element separately.

List<Order> orders = em.createQuery("SELECT o FROM Order o", Order.class)
        .getResultList(); // 1 query: SELECT * FROM orders

for (Order order : orders) {
    // here Hibernate runs SELECT * FROM order_items WHERE order_id = ?
    // for each order — N queries in total
    System.out.println(order.getItems().size());
}

Total: 1 + N queries instead of one.

The same happens with @ManyToOne fields accessed inside a loop.

How to spot the problem

The first step is to enable SQL logging. In application.yml:

spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true

logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.orm.jdbc.bind: TRACE

After that all queries are visible in the console. If their number grows in proportion to the number of rows in the result set, you are looking at N+1.

For precise counting in tests, datasource-proxy or p6spy are handy — they intercept JDBC and count the queries.

Solution 1: JOIN FETCH in JPQL

The most straightforward way is to load the association in a single query via JOIN FETCH:

List<Order> orders = em.createQuery(
        "SELECT o FROM Order o JOIN FETCH o.items",
        Order.class
).getResultList();

JOIN FETCH tells Hibernate: "load the items collection with the same query." In SQL this turns into an INNER JOIN with the full data of both tables.

DISTINCT in older code is a leftover: SQL returns a row for each (order, item) pair, and in Hibernate 5 the list would contain several copies of the same Order without it. Starting with Hibernate 6 (that is Spring Boot 3) duplicates are removed automatically, while a written DISTINCT goes straight into SQL and makes the database do an extra sort.

Limitation: JOIN FETCH cannot be used with pagination (setFirstResult/setMaxResults) on @OneToMany/@ManyToMany collections. Hibernate is forced to load everything into memory and slice it there — with large data sets this is a serious problem. A warning will appear in the log:

HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory

Pagination requires different approaches (see below).

Solution 2: @EntityGraph

@EntityGraph is a declarative way to specify what should be loaded together with an entity. It is convenient in Spring Data repositories:

@EntityGraph(attributePaths = {"items", "items.product"})
List<Order> findByStatus(OrderStatus status);

In SQL this is also a JOIN FETCH, but the logic is specified at the repository-method level rather than in a JPQL string — easier to reuse and read.

Solution 3: batch fetching

If JOIN FETCH is inconvenient (or you need pagination), you can ask Hibernate to load lazy collections in batches. Then, instead of N separate queries, it issues one query per group with a list of identifiers:

live example

SELECT order_id, product_id, quantity, unit_price
FROM order_items
WHERE order_id IN ('ord-01', 'ord-02', 'ord-03', 'ord-04', 'ord-05')
ORDER BY order_id, id

Global setting — in application.yml:

spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 25

Annotation on a specific collection:

@OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
@BatchSize(size = 25)
private List<OrderItem> items;

With a result set of 50 and batch_fetch_size = 25, Hibernate makes 1 + 2 queries instead of 1 + 50. The arithmetic is easy to see on a round-trip counter — first one collection at a time, then in batches:

live example

import java.util.ArrayList;
import java.util.List;

public class NPlusOneDemo {

    static int queries = 0;

    static List<Long> loadOrderIds(int count) {
        queries++;
        List<Long> ids = new ArrayList<>();
        for (long id = 1; id <= count; id++) {
            ids.add(id);
        }
        return ids;
    }

    static void loadItems(List<Long> orderIds) {
        queries++; // one SELECT ... WHERE order_id IN (...)
    }

    public static void main(String[] args) {
        List<Long> ids = loadOrderIds(50);
        for (Long id : ids) {
            loadItems(List.of(id));
        }
        System.out.println("one collection at a time — queries: " + queries);

        queries = 0;
        ids = loadOrderIds(50);
        for (int from = 0; from < ids.size(); from += 25) {
            loadItems(ids.subList(from, Math.min(from + 25, ids.size())));
        }
        System.out.println("batches of 25 — queries: " + queries);
    }
}

@BatchSize works well with pagination: the page is requested via LIMIT/OFFSET, and collections are loaded in batches.

Solution 4: DTO projection

When the data is needed for reading only (a screen, an API response), you can avoid loading entities altogether — request the needed fields directly into a DTO via JPQL:

record OrderSummary(Long id, String customerName, long itemCount) {}  // COUNT returns long

List<OrderSummary> summaries = em.createQuery(
        """
        SELECT new com.example.dto.OrderSummary(
            o.id,
            o.customer.name,
            COUNT(i)
        )
        FROM Order o
        LEFT JOIN o.items i
        GROUP BY o.id, o.customer.name
        """,
        OrderSummary.class
).getResultList();

Hibernate runs a single query, the Persistence Context is not involved — the objects are not tracked, and no flush is needed. A good choice for screens that only read data.

JOIN FETCH and pagination: the right way

If you need both to avoid N+1 and to support pagination over the root entity, use a two-step approach:

1. The first query fetches the IDs for the page:

List<Long> ids = em.createQuery(
        "SELECT o.id FROM Order o WHERE o.status = :status ORDER BY o.createdAt DESC",
        Long.class
).setParameter("status", status)
 .setFirstResult(offset)
 .setMaxResults(pageSize)
 .getResultList();

2. The second one loads the full entities with JOIN FETCH by those IDs:

List<Order> orders = em.createQuery(
        "SELECT o FROM Order o JOIN FETCH o.items WHERE o.id IN :ids ORDER BY o.createdAt DESC",
        Order.class
).setParameter("ids", ids)
 .getResultList();

Two queries instead of N+1, and without loading everything into memory.

Which tool when

In short

• The N+1 problem: one query for the list + one query per lazy association during iteration.
• Diagnostics: spring.jpa.show-sql=true, and a query counter via datasource-proxy in tests.
• JOIN FETCH — the simplest way; does not work with pagination on @OneToMany, the log gets HHH90003004.
• @EntityGraph — the same thing, but declaratively at the repository-method level.
• @BatchSize / default_batch_fetch_size — reduces N queries to N/batch and gets along with pagination.
• A DTO projection avoids the problem for reads, and pagination with JOIN FETCH is done as a two-step query by IDs.

What to read next

• Lazy and Eager Loading — how Hibernate decides when to go to the database
• JPQL and Criteria API — query syntax, subqueries, projections
• Caching in Hibernate — the second-level cache as an additional tool for reducing load
• Spring Data JPA — repositories, @EntityGraph, and projections the Spring way