Hibernate N+1 Problems And Solution

Hibernate N+1 SELECT’s Problems And Solution

Hibernate n+1 problems only comes for one to many relationship.
Let say one example – Retailer with a one-to-many relationship with Product. One Retailer has many Products.

***** Table: Retailer *****
+—–+——————-+
| ID | NAME |
+—–+——————-+
| 1 | Retailer Name 1 |
| 2 | Retailer Name 2 |
| 3 | Retailer Name 3 |
| 4 | Retailer Name 4 |
+—–+——————-+

***** Table: Product *****
+—–+———–+——————–+——-+————+
| ID | NAME | DESCRIPTION | PRICE | RETAILERID |
+—–+———–+——————–+——-+————+
|1 | Product 1 | Name for Product 1 | 82.0 | 4 |
|2 | Product 2 | Name for Product 2 | 20.0 | 2 |
|3 | Product 3 | Name for Product 3 | 10.0 | 2 |
|4 | Product 4 | Name for Product 4 | 77.0 | 3 |
+—–+———–+——————–+——-+————+

Factors:

Lazy mode for Retailer set to “true” (default)

Fetch mode used for querying on Product is Select

Fetch mode (default): Retailer information is accessed

Caching does not play a role for the first time the Retailer is accessed

Fetch mode is Select Fetch (default)

select * from PRODUCT
select * from RETAILER where RETAILER.id=?
select * from RETAILER where RETAILER.id=?
select * from RETAILER where RETAILER.id=?

Result:

1 select statement for Product
N select statements for RETAILER

This is N+1 select problem!


Solution to N+1 SELECTs problem

(i) HQL fetch join

“from RETAILER retailer join fetch retailer.product Product”

Corresponding SQL would be –

SELECT * FROM RETAILER retailer LEFT OUTER JOIN PRODUCT product ON product.product_id=retailer.product_id

(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.

Gopal Das
Follow me

Gopal Das

Founder at GopalDas.Org
He is a technology evangelist, Salesforce trainer, blogger, and working as a Salesforce Technical Lead. After working in Java based project implementation, he jumped to the Salesforce system on a whim and never looked back. He fell in love with Salesforce’s flexibility, scalability, and power. He expanded his knowledge of the platform and became a Certified App Builder, Administrator, Platform Developer I, SalesCloud Consultant while leading the Salesforce implementation and technology needs. He has worked in a wide variety of applications/services like desktop, web and mobile applications.
Gopal Das
Follow me

Leave a Reply

Your email address will not be published. Required fields are marked *