Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp
Hibernate 3.1.2
Mapping documents:
Code:
<class name="DocumentRecord" table="DOCUMENTS"
>
<id name="id" column="DOCUMENT_ID">
<generator class="native"/>
</id>
<property name="title" type="string" length="100"
column="TITLE" />
<property name="description" type="string" length="500"
column="DESCRIPTION" />
<property name="keywords" type="string" length="255"
column="KEYWORDS" />
<property name="url" type="string" length="255"
column = "URL" />
<property name="expiryDate" type="java.util.Date"
column= "EXPIRY_DATE" />
<property name="publicationDate" type="java.util.Date"
column="PUBLICATION_DATE" />
<property name="dateStamp" type="java.util.Date"
column="DATE_STAMP" />
<property name="fileSize" type="long" column="FILE_SIZE" />
<property name="fileType" type="string" column="FILE_TYPE" length="50" />
<many-to-one class="Category" column="CATEGORY_ID" name="category"
lazy="false" outer-join="true" >
</many-to-one>
</class>
<class name="Category" table="HIERARCHY_LKP"
>
<id name="id" column="HIERARCHY_LKP_ID">
<generator class="native"/>
</id>
<property name="name" type="string" column="NAME" length="100"/>
<set
name="children"
lazy="true">
<key column="PARENT_ID" />
<one-to-many class="Category" />
</set>
<many-to-one name="parent" class="Category"
column="PARENT_ID" lazy="true" >
</many-to-one>
</class>
Code:
Query q = getSession().createQuery("Select d from DocumentRecord as d left outer join fetch d.category where d.id=:id");
q.setInteger("id", id);
List results = q.list();
Name and version of the database you are using:Oracle 9i
The generated SQL (show_sql=true):Code:
select documentre0_.DOCUMENT_ID as DOCUMENT1_1_, documentre0_.TITLE as TITLE0_1_, documentre0_.DESCRIPTION as DESCRIPT3_0_1_, documentre0_.KEYWORDS as KEYWORDS0_1_, documentre0_.URL as URL0_1_, documentre0_.EXPIRY_DATE as EXPIRY6_0_1_, documentre0_.PUBLICATION_DATE as PUBLICAT7_0_1_, documentre0_.DATE_STAMP as DATE8_0_1_, documentre0_.FILE_SIZE as FILE9_0_1_, documentre0_.FILE_TYPE as FILE10_0_1_, documentre0_.CATEGORY_ID as CATEGORY11_0_1_, category1_.HIERARCHY_LKP_ID as HIERARCHY1_0_, category1_.NAME as NAME1_0_, category1_.PARENT_ID as PARENT3_1_0_ from WIS.DOCUMENTS documentre0_ left outer join WIS.HIERARCHY_LKP category1_ on documentre0_.CATEGORY_ID=category1_.HIERARCHY_LKP_ID where documentre0_.DOCUMENT_ID=?
Hi there,
I'm having the classic parent-child N+1 selects problem and nothing I try seems to fix it. I have a DocumentRecord class that has a <many-to-one> association with a Category class. The Category class has a parent Category and a children collection, both of which are mapped as lazy="true".
Now when I do a session.get() for my DocumentRecord hibernate generates the above sql, which is what I would expect, just the DocumentRecord and its Category hydrated. BUT hibernate then goes on to execute the following sql three times:
Code:
select category0_.HIERARCHY_LKP_ID as HIERARCHY1_0_, category0_.NAME as NAME1_0_, category0_.PARENT_ID as PARENT3_1_0_ from WIS.HIERARCHY_LKP category0_ where category0_.HIERARCHY_LKP_ID=?
which is hibernate hydrating all the children and parent Categories of the DocumentRecords Category. Why does hibernate do this when I've mapped both the children and parent properties of Category as lazy?
I've also tried to retrieve my DocumentRecord using an HQL join fetch, which i understand overrides the lazy initailaisation settings in the mapping docs, but this doesn't seem to make any difference (it generates the same 4 sql statements)
Anyone have any ideas about this? How can I stop hibernate executing the 3 extra sql statements?
Thanks
Tom
Login
FAQ
Search