Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp
Hibernate version:3.0
N+1 problem- have issues in solving this when we have composite keys and 4 levels of joins.
I have four tables A,B,C,D.
The data model is as follows:
A has a unique primary key say A_KEY.
B has a unique primary key having bKey and it has column which is a foreign key say A_Key to Table A.
C has a composite Key having cCompKey = (cKey,bKey), where bKey is the foriegn key to Table B.
D has a composite key having dCompKey = (dKey,cCompKey), where cCompKey is the foreign key to Table c.
Table A has a set of B objects
Table B has a set of C objects
Table C has a set of D objects
i wanted to do a left outer join of all the 4 tables based on the primary key of Table A that is A_PKEY.
My query was as follows.
list = session.createQuery("from A as a left join fetch a.bSet as b " +
" left join fetch b.cSet as c " +
"left join fetch c.dSet as d " +
"where a.A_PKEY = a_p_key")
.setParameter("a_p_key", new Integer(2))
.list();
I expected a joined query of all the 4 tables.
Obsevations:
1. It generated a joined query with all the fields in 4 tables. Here it self it got all the data that is required.
2. Apart from that a seperate query was generated for each object in bset.
Note: I have set the hibernate.max_fetch_depth set to "7".
This problem occurs only if B and C tables have composite keys. It seems to be querying only one joined query if there are no composite keys in any of the four tables.
The generated joined query was:
select a.* ,b.*,c.*,d.* from
A a inner join b b on a.A_KEY=b.A_KEY
inner join C c on b.B_KEY=c.B_KEY
left outer join D d on c.B_KEY=d.B_KEY and c.C_KEY=d.C_KEY
where a.AKEY=?
And the individual query at the second level was executed N times:
select b.* from B b where b.B_KEY = ?
I wonder why hibernate is doing an individual query for each object at the second level for bSet.
Login
FAQ
Search