I have bi-directional many-to-many relationship between two entities, let's say Department and Employee. In the database this is realized through the two main tables DEPARTMENT and EMPLOYEE and the intermediary table DEP_EMP. In the Java classes this relationship is defined through collections (Set) and is mapped as follows:

-- from Department.hbm.xml --
<set name="employees" table="DEP_EMP" cascade="save-update" fetch="join">
<key>
<column name="DEP_ID" not-null="true" />
</key>
<many-to-many entity-name="domain.Employee">
<column name="EMP_ID" not-null="true" />
</many-to-many>
</set>

-- from Employee.hbm.xml --
<set name="departments" table="DEP_EMP" inverse="true" cascade="save-update">
<key>
<column name="EMP_ID" not-null="true" />
</key>
<many-to-many entity-name="domain.Department">
<column name="DEP_ID" not-null="true" />
</many-to-many>
</set>

Now, I want to eagerly load the 'employees' collection for the Department instance without sending additional 'select' queries to the database. This is why I added fetch="join" to this collection mapping (see above).

This works perfectly if I load one Deparment instance from the database. However, if I load multiple departments like this:
List<Department> = session.createQuery("from Department").list();
- then 'employees' collections are not loaded into the Department instances, and extra selects are needed. I guess, this is what they call the 'n+1' problem.

How should I do this properly? What is the query (HQL or SQL)?

I figure I have to do createSQLQuery() including 'left join fetch'. Also, addEntity() and addJoin() should be called. I just cannot figure out details fast enough. Please help!

Hi

May be it would help



Are you really sure in your cascade attributes?

This DID work. Thank you!