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. FetchType.LAZY For example, we have an Author entity with One-to-many books collection marked as FetchType.LAZY: FetchType.LAZY public class Author { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; private String fullName; @OneToMany(fetch = FetchType.LAZY) private Set<Book> books; } 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())); 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_; 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: size() SELECT books0_.author_id, books0_.id, books0_.title, books0_.year FROM books books0_ WHERE books0_.author_id=?; 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. N + 1 Hibernate provides a few ways to handle this issue: Hibernate provides a few ways to handle this issue: Use JOIN FETCH: Use JOIN FETCH: JOIN FETCH entityManager.createQuery("SELECT a FROM Author a LEFT JOIN FETCH a.books", Author.class); 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; 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. TypedQuery#setMaxResults(n) OutOfMemoryException Set @BatchSize for one-to-many association: Set @BatchSize for one-to-many association: @BatchSize 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) ... 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; 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: size() /* 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*/); /* 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. N / M N M N / M + 1 Use FetchMode.SUBSELECT Use FetchMode.SUBSELECT 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) ... 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; 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 '' ); 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(); 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();
