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)

1 2 3 4 5 6 7 8 9 10 11

// It takes Select fetch mode as a default Query query = session.createQuery( "from Product p"); List list = query.list(); // Retailer is being accessed displayProductsListWithRetailerName(results);   // It takes Select fetch mode as a default Query query = session.createQuery( "from Product p"); List list = query.list(); // Retailer is being accessed displayProductsListWithRetailerName(results);

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.