DeVaCart logo
Published on

Efficient Hibernate queries by avoiding the N+1 problem

Authors

1. The N+1 problem. What is it exactly?

By default, in Hibernate (in relational db ORMs in general), collection type fields such as the ones which correspond to One-to-Many and Many-to-Many relationships are lazily populated / fetched from the database.

This default behavior, as useful as it is, could lead to the N+1 problem:
This is a scalability problem where for example your code leads to a single database query to select all parent records (whose number is say N). And then for all N records your code lazily populates collection property out of related database table by issuing separate select db query, leading to N additional select queries, hence N+1 queries would be issued in total.

The default lazy fetch behavior prevents you from overuse of resources. Especially the memory resource. This is so, because each parent record could potentially be related to a huge number of child records. If the default behavior was to fetch eagerly, then fetching a large number of such records could easily lead to dramatic consumption of the memory. Not to mention the CPU time that would be needed to fetch and build this dense collection of related objects. Moreover, as you will see in this article, the eager fetch does not always work as expected.

Actually, statistically speaking, most of the times you wouldn't even need to pre-populate those collection fields for all parent objects. Most of the times you would be perfectly ok to lazily populate them (if you delve within them at all). So, this is why the default behavior is to lazy fetch the *-to-Many properties.

With that said, let's look at the following simplified example:

er-diagram

And build small project to demonstrate the problem:

Product Entity:

package bg.devasoft.multibag.eagerfetch.entity;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;

    @OneToMany(mappedBy = "product", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Attribute> attributes = new ArrayList<>();

    // constructors, getters, setters, hashCode, 
    // equals and toString methods are omitted for brevity
}

Attribute Entity:

package bg.devasoft.multibag.eagerfetch.entity;


import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

@Entity
public class Attribute {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;

    @OneToMany(mappedBy = "attribute", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<AttributeValue> attributeValues = new ArrayList<>();

    @ManyToOne()
    private Product product;

    // constructors, getters, setters, hashCode, 
    // equals and toString methods are omitted for brevity
}

AttributeValue Entity:

package bg.devasoft.multibag.eagerfetch.entity;


import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

@Entity
public class Attribute {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;

    @OneToMany(mappedBy = "attribute", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<AttributeValue> attributeValues = new ArrayList<>();

    @ManyToOne()
    private Product product;

    // constructors, getters, setters, hashCode, 
    // equals and toString methods are omitted for brevity
}

ProductRepository JpaRepository:

package bg.devasoft.multibag.eagerfetch.entity.repository;

import bg.devasoft.multibag.eagerfetch.entity.Product;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ProductRepository extends JpaRepository<Product, Long> {
}

And now let's write a code which produces N+1 queries:

@Test
@Transactional
void nPlusOneExample() {
    logger.debug("1 query to select all products");
    List<Product> allProducts = productRepository.findAll();
    logger.debug("Number of product records: " + allProducts.size());
    allProducts.forEach(product -> {
        logger.debug("Product " + product.getName());
        product.getAttributes().forEach(productAttribute -> {
            logger.debug("\tAttribute " + productAttribute.getName());
        });
    });
}

Line #5 above issues single query for selecting all products. If we log the hibernate queries output, then the log is going to look similarly to this:

2023-07-18 19:00:30.457 DEBUG 490246 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 1 query to select all products
2023-07-18 19:00:30.482 DEBUG 490246 --- [    Test worker] org.hibernate.SQL                        : select product0_.id as id1_2_, product0_.name as name2_2_ from product product0_

In our case, line #6 is logging out that we have 2 product records in total:

2023-07-18 19:00:30.490 DEBUG 490246 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : Number of product records: 2

The lazy fetch that is triggered on line #9 is going to issue a separate query for each separate product record:
And we can see that clearly within the logs. In our case that is 2 additional sql queries (one for each product we have)

2023-07-18 19:00:30.491 DEBUG 490246 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : Product Product1
2023-07-18 19:00:30.492 DEBUG 490246 --- [    Test worker] org.hibernate.SQL                        : select attributes0_.product_id as product_3_0_0_, attributes0_.id as id1_0_0_, attributes0_.id as id1_0_1_, attributes0_.name as name2_0_1_, attributes0_.product_id as product_3_0_1_ from attribute attributes0_ where attributes0_.product_id=?
2023-07-18 19:00:30.492 TRACE 490246 --- [    Test worker] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [1]
2023-07-18 19:00:30.499 DEBUG 490246 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute1-1
2023-07-18 19:00:30.499 DEBUG 490246 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute1-2
2023-07-18 19:00:30.499 DEBUG 490246 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute1-3
2023-07-18 19:00:30.499 DEBUG 490246 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute1-4
2023-07-18 19:00:30.499 DEBUG 490246 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : Product Product2
2023-07-18 19:00:30.499 DEBUG 490246 --- [    Test worker] org.hibernate.SQL                        : select attributes0_.product_id as product_3_0_0_, attributes0_.id as id1_0_0_, attributes0_.id as id1_0_1_, attributes0_.name as name2_0_1_, attributes0_.product_id as product_3_0_1_ from attribute attributes0_ where attributes0_.product_id=?
2023-07-18 19:00:30.499 TRACE 490246 --- [    Test worker] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [2]
2023-07-18 19:00:30.501 DEBUG 490246 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute2-1
2023-07-18 19:00:30.501 DEBUG 490246 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute2-2
2023-07-18 19:00:30.501 DEBUG 490246 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute2-3
2023-07-18 19:00:30.501 DEBUG 490246 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute2-4

In our case we have just 2 products and we can somehow "swallow" the 2 extra requests. But what is going to happen if we are doing the same for say thousand products? What if it is tens of thousands or even more?
Clearly the N+1 problem in this case has the potential to cripple and hurt the performance, so let's see how we can solve this.

2. Let's Eager fetch

Let's modify the ProductRepository and create repository method which selects all Products and fetches eagerly the attributes list for each product.

package bg.devasoft.multibag.eagerfetch.entity.repository;

import bg.devasoft.multibag.eagerfetch.entity.Product;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.QueryHints;

import javax.persistence.QueryHint;
import java.util.List;

import static org.hibernate.jpa.QueryHints.HINT_PASS_DISTINCT_THROUGH;

public interface ProductRepository extends JpaRepository<Product, Long> {
    @Query("select distinct p from Product p left join fetch p.attributes as attributes")
    @QueryHints(@QueryHint(name = HINT_PASS_DISTINCT_THROUGH, value = "false"))
    List<Product> findAllProductsAndAttributes();
}

select distinct is required in order to remove the duplicate Product objects out from the Cartesian product that is produced out from the join clause.

left join fetch does an eager fetch, based left join

The HINT_PASS_DISTINCT_THROUGH QueryHint with value set to false instructs Hibernate to issue sql query which is missing the distinct keyword. In this case, the distinct keyword is only interpret from Hibernate, but it doesn't reach the underlying db. Which might help improve the performance at least from the DB perspective.

now, after the left join fetch modification is done, let's use the new method to execute the test scenario and see what the logs are looking like:

2023-07-18 19:04:47.309 DEBUG 490809 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 1 query to select all products
2023-07-18 19:04:47.320 DEBUG 490809 --- [    Test worker] org.hibernate.SQL                        : select product0_.id as id1_2_0_, attributes1_.id as id1_0_1_, product0_.name as name2_2_0_, attributes1_.name as name2_0_1_, attributes1_.product_id as product_3_0_1_, attributes1_.product_id as product_3_0_0__, attributes1_.id as id1_0_0__ from product product0_ left outer join attribute attributes1_ on product0_.id=attributes1_.product_id
2023-07-18 19:04:47.339 DEBUG 490809 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : Number of product records: 2
2023-07-18 19:04:47.340 DEBUG 490809 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : Product Product1
2023-07-18 19:04:47.340 DEBUG 490809 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute1-1
2023-07-18 19:04:47.341 DEBUG 490809 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute1-2
2023-07-18 19:04:47.341 DEBUG 490809 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute1-3
2023-07-18 19:04:47.341 DEBUG 490809 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute1-4
2023-07-18 19:04:47.341 DEBUG 490809 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : Product Product2
2023-07-18 19:04:47.341 DEBUG 490809 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute2-1
2023-07-18 19:04:47.341 DEBUG 490809 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute2-2
2023-07-18 19:04:47.341 DEBUG 490809 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute2-3
2023-07-18 19:04:47.341 DEBUG 490809 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute2-4

This is so much better. We now have just a single select query for the products. The query does left outer join on the attributes. And with that we have reduced the N+1 queries to just a single query. An Awesome result, one might say, right?

The result looks good indeed. But can we use the same approach in more complex queries?

Let's introduce one more ProductRepository Query method.
In addition to what we have done before, we also left join fetch the attributeValues out from the attributes:

package bg.devasoft.multibag.eagerfetch.entity.repository;

import bg.devasoft.multibag.eagerfetch.entity.Product;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.QueryHints;

import javax.persistence.QueryHint;
import java.util.List;

import static org.hibernate.jpa.QueryHints.HINT_PASS_DISTINCT_THROUGH;

public interface ProductRepository extends JpaRepository<Product, Long> {
    @Query("select distinct p from Product p left join fetch p.attributes as attributes")
    @QueryHints(@QueryHint(name = HINT_PASS_DISTINCT_THROUGH, value = "false"))
    List<Product> findAllProductsAndAttributes();

    @Query("select distinct p from Product p " +
            "left join fetch p.attributes as at " +
            "left join fetch at.attributeValues")
    @QueryHints(@QueryHint(name = HINT_PASS_DISTINCT_THROUGH, value = "false"))
    List<Product> findAllProductsAndAttributesAndAttributeValues();
}

Now, if we try to compile and run our code, to see if Spring Data JPA is going to create implementation for this new findAllProductsAndAttributesAndAttributeValues method, then we are going to receive the following exception

Caused by: org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags: [bg.devasoft.multibag.eagerfetch.entity.Product.attributes, bg.devasoft.multibag.eagerfetch.entity.Attribute.attributeValues]
	at org.hibernate.loader.BasicLoader.postInstantiate(BasicLoader.java:76) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.loader.hql.QueryLoader.<init>(QueryLoader.java:110) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.createQueryLoader(QueryTranslatorImpl.java:249) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:213) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:112) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:636) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:748) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	... 131 common frames omitted

It fails so early, so obviously Hibernate is trying to warn us for something important and prevent the problem before it even happened. So, clearly left join fetch does not work for more complex queries, but why?

3. MultipleBagFetchException. Why do we receive it and what does it mean?

If we have queries with more than 1 left join fetch (or more than 1 join fetch of any kind) then the query leads to MultipleBagFetchException, saying cannot simultaneously fetch multiple bags

First and foremost, let's clear out what are those multiple bags:
In our case we have 2 bags and those are:

  • List<Attribute> attributes; from the Product entity
  • List<AttributeValue> attributeValues; from the Attribute entity

Te reason for receiving this exception is because Hibernate knows that selecting objects within those "Multiple Bags" is going to produce a wrong result, one that might have duplicates.
And yes, this is indeed the case, because this select operation with the additional 2 left join clauses is producing a Cartesian product out from the matching records. So, if we have more than 1 record within the corresponding tables, then there will be duplicates within the result and since the List allow duplicate values, then those duplicate records are going to result in duplicate objects which will be completely wrong.

One way to circumvent the multiple bags select issue is to convert the List instances to Set collections. Unlike List, Set does not allow duplicates, so the Set(s) are going to remove the duplicates out from the resultant Cartesian product. The tradeoff is that you loose the the order that List provides and also you would lose any duplicate values if they are indeed part of the records.

But there is even better solution, so keep reading 😀

4. Even better solution: Separate selects, one for each Bag:

Instead of dealing with Cartesian product results and the Set workaround, we can instead issue separate query which selects all the records within each of the bags. So, we can have the following queries, executed in the same order:

  1. single left join fetch query to select all Products and eagerly fetch their belonging Attributes.
  2. single left join fetch query to select all Attributes and eagerly fetch their belonging AttributeValues

In fact we can arrange multiple different set of queries, which are going to lead to the same result, like for instance, another one would be:

  1. single left join fetch query to select all Products and eagerly fetch their belonging Attributes.
  2. single query to obtain all AttributeValues for the specific attribute ids (leveraging the IN clause)

If we execute the first set of the above queries within the same Hibernate session, then our code will be able to obtain and access the entire hierarchy of objects without any lazy fetches whatsoever.

So, with just 2 queries we can browse the hierarchy of thousands of products, their attributes and the attribute values for each of the individual attribute.

Let's see this in action:

First, let's get our JpaRepository method which does left join fetch all attributes and their belonging attribute values:

package bg.devasoft.multibag.eagerfetch.entity.repository;

import bg.devasoft.multibag.eagerfetch.entity.Attribute;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.QueryHints;

import javax.persistence.QueryHint;
import java.util.List;

import static org.hibernate.jpa.QueryHints.HINT_PASS_DISTINCT_THROUGH;

public interface AttributeRepository extends JpaRepository<Attribute, Long> {
    @Query("select distinct a from Attribute a left join fetch a.attributeValues as attributeValues")
    @QueryHints(@QueryHint(name = HINT_PASS_DISTINCT_THROUGH, value = "false"))
    List<Attribute> findAllAttributesAndAttributeValues();
}

Now, let's execute the two queries and browse the data:

@Test
@Transactional
void eagerFetchWithSeparateQueries() {
    logger.debug("Query to select all products and their attributes");
    List<Product> allProducts = productRepository.findAllProductsAndAttributes();
    logger.debug("Query to select all attributes and their attribute values");
    List<Attribute> allAttributes = attributeRepository.findAllAttributesAndAttributeValues();
    logger.debug("Number of product records: " + allProducts.size());
    allProducts.forEach(product -> {
        logger.debug("Product " + product.getName());
        product.getAttributes().forEach(productAttribute -> {
            logger.debug("\tAttribute " + productAttribute.getName());
            productAttribute.getAttributeValues().forEach(attributeValue -> {
                logger.debug("\t\tAttributeValue " + attributeValue.getVal());
            });
        });
    });
}

as expected, we have just 2 queries and all the data is available:

2023-07-18 17:39:28.136 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : Query to select all products and their attributes
2023-07-18 17:39:28.146 DEBUG 482517 --- [    Test worker] org.hibernate.SQL                        : select product0_.id as id1_2_0_, attributes1_.id as id1_0_1_, product0_.name as name2_2_0_, attributes1_.name as name2_0_1_, attributes1_.product_id as product_3_0_1_, attributes1_.product_id as product_3_0_0__, attributes1_.id as id1_0_0__ from product product0_ left outer join attribute attributes1_ on product0_.id=attributes1_.product_id
2023-07-18 17:39:28.159 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : Query to select all attributes and their attribute values
2023-07-18 17:39:28.161 DEBUG 482517 --- [    Test worker] org.hibernate.SQL                        : select attribute0_.id as id1_0_0_, attributev1_.id as id1_1_1_, attribute0_.name as name2_0_0_, attribute0_.product_id as product_3_0_0_, attributev1_.attribute_id as attribut3_1_1_, attributev1_.val as val2_1_1_, attributev1_.attribute_id as attribut3_1_0__, attributev1_.id as id1_1_0__ from attribute attribute0_ left outer join attribute_value attributev1_ on attribute0_.id=attributev1_.attribute_id
2023-07-18 17:39:28.165 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : Number of product records: 2
2023-07-18 17:39:28.165 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : Product Product1
2023-07-18 17:39:28.165 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute1-1
2023-07-18 17:39:28.166 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 		AttributeValue attribute-value-1-1-1
2023-07-18 17:39:28.166 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 		AttributeValue attribute-value-1-1-2
2023-07-18 17:39:28.166 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 		AttributeValue attribute-value-1-1-2
2023-07-18 17:39:28.166 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute1-2
2023-07-18 17:39:28.166 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute1-3
2023-07-18 17:39:28.166 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute1-4
2023-07-18 17:39:28.166 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : Product Product2
2023-07-18 17:39:28.166 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute2-1
2023-07-18 17:39:28.166 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 		AttributeValue attribute-value-2-1-1
2023-07-18 17:39:28.166 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 		AttributeValue attribute-value-2-1-2
2023-07-18 17:39:28.166 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 		AttributeValue attribute-value-2-1-2
2023-07-18 17:39:28.166 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute2-2
2023-07-18 17:39:28.166 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute2-3
2023-07-18 17:39:28.167 DEBUG 482517 --- [    Test worker] b.d.m.eagerfetch.ApplicationTests        : 	Attribute attribute2-4

If you are curious on why the two separate queries somehow combine all the data into a single browseable objects hierarchy, then the key thing to have in mind is that those two queries are done within the same Hibernate session, so they populate the same hibernate session memory with objects which reference each other.

We guarantee that the 2 queries are executed within the same session by marking the test method above as @Transactional.

Now, the line numbers 9 to 12 are reading data out from the first query,
but then line number 13 instead of triggering lazy fetch on the attributeValues, it actually obtains them right from the Hibernate session memory because the respective objects are already placed there from the second query (line #7)

A word of caution

You need to be mindful if you decide to use this solution:
It allows you to operate over this huge hierarchy of objects without issuing tremendous amount of unnecessary queries, but it pre-populates the memory with all the objects, so you need to be careful with your memory usage.

You should optimize your queries, so that you populate the Hibernate session memory with objects which are really needed. In general, if you can, you should prefer selection of specific objects rather than selecting all records within a table.

5. Source code

The following GitHub repo contains an example project, demonstrating the N+1 problem along with the solutions discussed above: https://github.com/mrbeanbg/hibernate-mutibag-fetch

Discuss on Twitter