Hibernate N+1 problem occurs when you use for your entity associations. If you perform a query to select n-entities and if you try to call any access method of your entity's lazy association, Hibernate will perform n-additional queries to load lazily fetched objects. FetchType.LAZY For example, we have the following Author entity with one-to-many books collection: public class Author {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String fullName;
    @OneToMany(fetch = FetchType.LAZY)
    private Set<Book> books;
} Let’s try to load all authors and print each author’s name with his books collection size: entityManager.createQuery("select a from Author a", Author.class)
                .getResultList()
                .forEach(a -> System.out.printf("%s had written %d books\n", 
                                               a.getFullName(), a.getBooks().size())); The first query Hibernate will generate is to select all authors: SELECT author0_.id AS id1_0_,
       author0_.fullName AS fullname2_0_
FROM authors author0_; After that, when we call method on the books collection, this association needs to be initialized, so Hibernate will perform an additional query: size() SELECT books0_.author_id AS author_i4_1_0_,
       books0_.id AS id1_1_0_,
       books0_.id AS id1_1_1_,
       books0_.author_id AS author_i4_1_1_,
       books0_.title AS title2_1_1_,
       books0_.year AS year3_1_1_
FROM books books0_
WHERE books0_.author_id=?; This query will be called n-times for each author when we print the amount of books in addition to the first query. Thus the total number of queries will be equal to N+1. Hibernate provides a couple of ways to eliminate this issue: The first solution is to use join fetch: entityManager.createQuery("select a from Author a left join fetch a.books", 
                                                             Author.class); SELECT author0_.id AS id1_0_0_,
       books1_.id AS id1_1_1_,
       author0_.fullName AS fullname2_0_0_,
       books1_.author_id AS author_i4_1_1_,
       books1_.title AS title2_1_1_,
       books1_.year AS year3_1_1_,
       books1_.author_id AS author_i4_1_0__,
       books1_.id AS id1_1_0__
FROM authors author0_
LEFT OUTER JOIN books books1_ ON author0_.id=books1_.author_id; This query works fine, but it has one issue: it doesn’t allow us to use pagination because the limit will not be applied to the authors. If you specify , Hibernate will fetch all existing rows and do the pagination in the memory, significantly increasing memory consumption. query.setMaxResults(n) Another way is to use on the lazy association: @BatchSize public class Author {
    …
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "author")
    @BatchSize(size = 10)
    private Set<Book> books;
} Hibernate will create the first query to retrieve all authors: SELECT author0_.id AS id1_0_,
       author0_.fullName AS fullname2_0_
FROM authors author0_; In this case, we can easily perform the pagination on the authors. Then, when we call method on the books collection, Hibernate will perform this query: size() /* load one-to-many Author.books */
SELECT books0_.author_id AS author_i4_1_1_,
       books0_.id AS id1_1_1_,
       books0_.id AS id1_1_0_,
       books0_.author_id AS author_i4_1_0_,
       books0_.title AS title2_1_0_,
       books0_.year AS year3_1_0_
FROM books books0_
WHERE books0_.author_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ? /*batch size*/); This query will be called N/M times, where N is the amount of authors and M is the specified batch size. Totally we will call N/M+1 queries. The third way is to use a sub query returning a list of author identifiers Hibernate provides this opportunity by setting on the lazy association: @Fetch(FetchMode.SUBSELECT) public class Author {
    …
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "author")
    @Fetch(FetchMode.SUBSELECT)
    private Set<Book> books;
} The first query will load all authors: SELECT author0_.id AS id1_0_,
       author0_.fullName AS fullname2_0_
FROM authors author0_; The second query will fetch books by using authors sub query: SELECT books0_.author_id AS author_i4_1_1_,
       books0_.id AS id1_1_1_,
       books0_.id AS id1_1_0_,
       books0_.author_id AS author_i4_1_0_,
       books0_.title AS title2_1_0_,
       books0_.year AS year3_1_0_
FROM books books0_
WHERE books0_.author_id in
    (SELECT author0_.id
     FROM authors author0_); If you look closely into the IN condition, you’ll see that the code inside the sub query almost repeats the first query. It can slow the performance if we have to execute a very complex query twice.  To speed up this case we can filter and page authors retrieving their ids by the 1st query. Then we can pass these identifiers directly to the 2nd query’s sub query: List<Integer> authorIds = em.createQuery("select a.id from Author a", 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", Author.class)
                                       .setParameter("authorIds", authorIds)
                                       .getResultList();

Fetch

Initialized

2022 - HackerNoon Contributor of the Year - Algorithms

2022 - HackerNoon Contributor of the Year - Coding Skills

2022 - HackerNoon Contributor of the Year - Functional Programming

2022 - HackerNoon Contributor of the Year - Java

2022 - HackerNoon Contributor of the Year - Programming

2022 - HackerNoon Contributor of the Year - Sql

2022 - Rawr For Ror

Coding Guru of 2022

Nominated for 2022 - Rawr For Ror

Nominated for 2022 - HackerNoon Contributor of the Year - Sql

Nominated for Coding Guru of 2022

Nominated for 2022 - HackerNoon Contributor of the Year - Coding Skills

Nominated for 2022 - HackerNoon Contributor of the Year - Java

Nominated for 2022 - HackerNoon Contributor of the Year - Algorithms

Building Your Own Programming Language From Scratch

Parallel Merge Sort with Fork/Join Framework

3 Ways to Deal With Hibernate N+1 Problem

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

3 Ways to Deal With Hibernate N+1 Problem

Too Long; Didn't Read

Companies Mentioned

Alexander Makeev

About Author

TOPICS

Languages

THIS ARTICLE WAS FEATURED IN...

3 Ways to Deal With Hibernate N+1 Problem

Too Long; Didn't Read

Companies Mentioned

Alexander Makeev

About Author

TOPICS

Languages

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES