Let’s assume that you’re writing code that’d track the price of mobile phones. Now, let’s say you have a collection of objects representing different Mobile phone vendors (MobileVendor), and each vendor has a collection of objects representing the PhoneModels they offer.
To put it simple, there’s exists a one-to-many relationship between MobileVendor:PhoneModel.
Okay, so you want to print out all the details of phone models. A naive O/R implementation would SELECT all mobile vendors and then do N additional SELECTs for getting the information of PhoneModel for each vendor.
— Get all Mobile Vendors
SELECT * FROM MobileVendor;
— For each MobileVendor, get PhoneModel details
SELECT * FROM PhoneModel WHERE MobileVendor.vendorId=?
As you see, the N+1 problem can happen if the first query populates the primary object and the second query populates all the child objects for each of the unique primary objects returned.
Resolve N+1 SELECTs problem
(i) HQL fetch join
“from MobileVendor mobileVendor join fetch mobileVendor.phoneModel PhoneModels”
Corresponding SQL would be (assuming tables as follows: t_mobile_vendor for MobileVendor and t_phone_model for PhoneModel)
SELECT * FROM t_mobile_vendor vendor LEFT OUTER JOIN t_phone_model model ON model.vendor_id=vendor.vendor_id
(ii) Criteria query
Criteria criteria = session.createCriteria(MobileVendor.class);
In both cases, our query returns a list of MobileVendor objects with the phoneModels initialized. Only one query needs to be run to return all the PhoneModel and MobileVendor information required.