What is the solution for the N+1 issue in JPA and Hibernate?

Tags: , , , ,



I understand that the N+1 problem is where one query is executed to fetch N records and N queries to fetch some relational records.

But how can it be avoided in Hibernate?

Answer

Suppose we have a class Manufacturer with a many-to-one relationship with Contact.

We solve this problem by making sure that the initial query fetches all the data needed to load the objects we need in their appropriately initialized state. One way of doing this is using an HQL fetch join. We use the HQL

"from Manufacturer manufacturer join fetch manufacturer.contact contact"

with the fetch statement. This results in an inner join:

select MANUFACTURER.id from manufacturer and contact ... from 
MANUFACTURER inner join CONTACT on MANUFACTURER.CONTACT_ID=CONTACT.id

Using a Criteria query we can get the same result from

Criteria criteria = session.createCriteria(Manufacturer.class);
criteria.setFetchMode("contact", FetchMode.EAGER);

which creates the SQL :

select MANUFACTURER.id from MANUFACTURER left outer join CONTACT on 
MANUFACTURER.CONTACT_ID=CONTACT.id where 1=1

in both cases, our query returns a list of Manufacturer objects with the contact initialized. Only one query needs to be run to return all the contact and manufacturer information required

for further information here is a link to the problem and the solution.



Source: stackoverflow