3 Ways to Deal With Hibernate N+1 Problem

N+1 problem in Hibernate may occur if you use FetchType.LAZY for your entity association. When you fetch N-entities with and then invoke any access method of your entity's LAZY association, Hibernate executes an additional query to load the association from database for each entity, making in total N selects.

For example, we have an Author entity with One-to-many books collection marked as FetchType.LAZY:

public class Author {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String fullName;
    @OneToMany(fetch = FetchType.LAZY)
    private Set<Book> books;
}

Let’s load all authors and print each author’s name with its number of books:

entityManager.createQuery("SELECT a FROM Author a", Author.class)
                .getResultList()
                .forEach(a -> System.out.printf("%s has %d books%n", a.getFullName(), a.getBooks().size()));

The first query Hibernate will generate is to select all authors:

SELECT author0_.id,
       author0_.fullName
FROM authors author0_;

Next, when we invoke size() method on each author’s books, in order for the books association to be initialized, Hibernate runs an additional select:

SELECT books0_.author_id,
       books0_.id,
       books0_.title,
       books0_.year
FROM books books0_
WHERE books0_.author_id=?;

The 2nd query will be called for each author when we print the books size. Taking into account the 1st query, the total number of performed selects is equal to N + 1, where N is the number of authors.

Hibernate provides a few ways to handle this issue:

1. Use JOIN FETCH:

entityManager.createQuery("SELECT a FROM Author a LEFT JOIN FETCH a.books", Author.class);

SELECT author0_.id,
       books1_.id,
       author0_.fullName,
       books1_.author_id,
       books1_.title,
       books1_.year
FROM authors author0_
LEFT OUTER JOIN books books1_ ON author0_.id=books1_.author_id;

This query works fine, but it has one significant drawback: having One-to-many JOIN producing multiple book records per author we can’t apply pagination. If you specify TypedQuery#setMaxResults(n), Hibernate will fetch all existing rows and do the pagination in the memory, significantly increasing heap consumption, which may lead to OutOfMemoryException.

2. Set @BatchSize for one-to-many association:

public class Author {
    …
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "author")
    @BatchSize(size = 10)
    private Set<Book> books;
}

entityManager.createQuery("SELECT a FROM Author a ORDER BY a.fullName DESC", Author.class)
             .setMaxResults(5)
             ...

Hibernate will generate the first query retrieving all authors with applied pagination:

SELECT
  a1_0.id,
  a1_0.full_name
FROM
  authors a1_0
ORDER BY
  a1_0.full_name DESC
FETCH FIRST
  ? ROWS ONLY;

Next, when we call size() method on the books collection, the following query will be executed:

/* load one-to-many Author.books */
SELECT books0_.author_id,
       books0_.id,
       books0_.id,
       books0_.author_id,
       books0_.title,
       books0_.year
FROM books books0_
WHERE books0_.author_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ? /*batch size*/);

The 2nd query is called N / M times, where N is the number of authors and M is the specified batch size. In total, we’ll have N / M + 1 queries.

3. Use FetchMode.SUBSELECT

public class Author {
    …
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "author")
    @Fetch(FetchMode.SUBSELECT)
    private Set<Book> books;
}

entityManager.createQuery("""
                          SELECT a
                             FROM Author a
                           WHERE LOWER(a.fullName) LIKE '%name%'
                          ORDER BY a.fullName DESC
             """, Author.class)
             .setMaxResults(5)
             ...

The first query will load all authors:

SELECT
  a1_0.id,
  a1_0.full_name
FROM
  authors a1_0
WHERE
  lower(a1_0.full_name) LIKE '%name%' escape ''
ORDER BY
  a1_0.full_name DESC
FETCH FIRST
  ? ROWS ONLY;

The second query will fetch all the books using authors subquery:

SELECT
  b1_0.author_id,
  b1_0.id,
  b1_0.title,
  b1_0.year
FROM
  books b1_0
WHERE
  b1_0.author_id IN (
    SELECT
      a1_0.id
    FROM
      authors a1_0
    WHERE
      lower(a1_0.full_name) LIKE '%name%' escape ''
  );

Finally, with this solution we execute only 2 selects to load full objects with pagination! There is only one issue you can notice: filtering for authors is applied two times, in the first query and in the 2nd query’s subquery.

3*. As an alternative approach you can compose the following separate queries. The first query applies pagination and filtering, retrieving only authors' identifiers. The second query filters authors by identifiers and loads full objects fetching the attached collection:

List<Integer> authorIds = entityManager.createQuery("""
                                SELECT a.id
                                    FROM Author a
                                 WHERE LOWER(a.fullName) LIKE '%name%'
                                ORDER BY a.fullName DESC
                                """, Integer.class)
                            .setFirstResult(5)
                            .setMaxResults(10)
                            .getResultList();

List<Author> resultList = entityManager.createQuery("""
                                SELECT a
                                    FROM Author a
                                LEFT JOIN FETCH a.books
                                 WHERE a.id IN :authorIds
                                ORDER BY a.fullName DESC
                                """, Author.class)
                                       .setParameter("authorIds", authorIds)
                                       .getResultList();