How to avoid N+1 problem in hibernate

Hibernate n+1 problems only comes for one to many relationship.

N+1 problem can happen if the first query populates the primary object and the second query populates all the associated child objects. 

Let us say we have a collection of Retailer objects and each Retailer object has a collection of Product objects associated to it. In other words, Retailer and Products objects are having 1-to-many relationship.

Now assuming that you need to iterate through all the Retailers, and for each one Retailer object, print the list of products.

Using the naive Object Relational implementation, we can retrieve as follows,

SELECT * FROM Retailer;

SELECT * FROM Product WHERE RetailerId = ? // For each record from the above query, it has to execute this query to fetch associations

Which means, we have only one select query for the Retailer, and then N additional selects for Products, where N is the total number of Retailers. If the value of “N” increases, then it has huge impact on the performance.

We can solve this problem by making sure that the initial query fetches all the required data to load the objects which are needed in their initialized state. In the most cases lazy behavior is good and optimal.

Following are the various ways through which we can address the reported issue.
i.e.

(i) HQL fetch join

“from RETAILER retailer join fetch retailer.product Product”

(ii) Criteria query
Criteria criteria = session.createCriteria(Retailer.class);
criteria.setFetchMode(“product”, FetchMode.EAGER);

In both cases, the query returns a list of Retailer objects with the Product initialized. Only one query needs to be run to return all the Product and Retailer information required.