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 PhoneModel
s they offer.
To put it simple, there’s exists a one-to-many relationship between MobileVendor
:PhoneModel
.
MobileVendor
Class
Class MobileVendor{
long vendor_id;
PhoneModel[] phoneModels;
...
}
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);
criteria.setFetchMode("phoneModels", FetchMode.EAGER);
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.