Hibernate ignores the fetch="join" for associations when there is a restiction on the associated criteria !!!,
In the example code below, when I have created Employee criteria and specified restrictions on Language associations which results in N+1 query pattern even if fetch="join" is specified.
It fires only one query to fetch employees and thier associated languages if I only specify the restriction on Employee criteria e.g. surname.
Hibernate version:
3.2.3
languges Mapping
<set name="languages" table="EMPLOYEE_SPEAKS_LANGUAGE" lazy="false" fetch="join">
<key column="emp_id"/>
<many-to-many column="lan_id" class="Language"/>
</set>
Code between sessionFactory.openSession() and session.close():
criteria.add(Restrictions.like("surname", "FOR",MatchMode.START));
Criteria lanCriteria = criteria.createCriteria("languages");
lanCriteria.add(Restrictions.eq("code",language));
criteria.setFetchMode("languages", FetchMode.JOIN);
criteria.list();
database used
Oracle 10g
The generated SQL
1 query to fetch all matching employees and their associated languages
select this_.emp_id as emp1_1_1_,
this_.emp_surname as emp2_1_1_,
this_.emp_firstname as emp3_1_1_,
this_.cn_id as cn4_1_1_,
languages3_.emp_id as emp1_,
language1_.lan_id as lan2_,
language1_.lan_id as lan1_3_0_,
language1_.lan_code as lan2_3_0_,
language1_.lan_name as lan3_3_0_
from EMPLOYEE this_ inner join EMPLOYEE_SPEAKS_LANGUAGE languages3_ on this_.emp_id = languages3_.emp_id inner join SPOKEN_LANGUAGE language1_ on languages3_.lan_id = language1_.lan_id
where this_.emp_surname like ? and language1_.lan_code = ?
N queries for languages (unnecessary)
select languages0_.emp_id as emp1_1_,
languages0_.lan_id as lan2_1_,
language1_.lan_id as lan1_3_0_,
language1_.lan_code as lan2_3_0_,
language1_.lan_name as lan3_3_0_
from EMPLOYEE_SPEAKS_LANGUAGE languages0_ left outer join SPOKEN_LANGUAGE language1_ on languages0_.lan_id = language1_.lan_id
where languages0_.emp_id = ?
This works as expected (single join fetch) if I only apply the restrictions on Employee attributes
|