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.

A similar situation arises with @ManyToOne fields when they are 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 will be 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, the datasource-proxy tool or the p6spy library 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 DISTINCT 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 is needed here to remove duplicates at the Java-object level: SQL returns a row for each (order, item) pair, and without DISTINCT the list would contain several copies of the same Order.

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.

More about Spring Data repositories in the article Spring Data JPA.

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 SELECT ... WHERE order_id IN (?, ?, ?, ...) per group.

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. For most cases this is a good balance between code simplicity and database load.

@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, int itemCount) {}

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 read-heavy endpoints.

More about JPQL and the Criteria API in the article JPQL and Criteria API.

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 DISTINCT 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 or datasource-proxy in tests.
• JOIN FETCH — the simplest way; does not work with pagination on @OneToMany.
• @EntityGraph — the same thing, but declaratively at the repository-method level.
• @BatchSize / default_batch_fetch_size — reduces N queries to N/batch, works with pagination.
• DTO projection — fully avoids the problem for read-only queries.
• Pagination + JOIN FETCH — 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