one-to-one bidirectional results in redundant queries

jicidre · **Joined:** Fri Jan 28, 2005 12:28 pm **Posts:** 3

Hibernate 2.1.8

Hi, I have a Parent-Child relationship where the parent has one child and the child has one parent (no less, no more). In DB, the child is the one that has the FK to the parent id.
I've mapped the parent ot have a <one-to-one> and the child to have a <many-to-one>
When I'm loading all the parents I'm falling in a n+1 problem. I want it to be eager. I've set that in the mappings.
Don't understand why, but hibernate is fetching the parents joining correctly with the childs but next it is fetching the childs one by one again joining with the parent.
I've tried several mappings configurations, HQL with join fetch, Criterias with FetchModes. Always the same. If it is bidirectional, there is a N+1.
I've read Hibernate in Action several times and, IMHO, the mappings are ok. Searched the Web and Forum without luck.

Follows my mappings and classes.

ParentClass.hbm.xml

Code:

   <class name="com.inceptor.gtk.beenz.ParentClass"
      table="Parentclass">

      <id name="id" type="long" unsaved-value="null">
         <column name="ID" sql-type="NUMBER(10)" not-null="true" />
         <generator class="sequence">
            <param name="sequence">GTK_SEAccountsInc</param>
         </generator>
      </id>
      <property name="data" />

      <one-to-one name="child" class="com.inceptor.gtk.beenz.ChildClass" property-ref="parent" constrained="true" outer-join="true"/>
  </class>

ChildClass.hbm.xml

Code:

   <class name="com.inceptor.gtk.beenz.ChildClass" 
      table="childClass">

      <id name="id" type="long" unsaved-value="null">
         <column name="ID" sql-type="NUMBER(10)" not-null="true" />
         <generator class="sequence">
            <param name="sequence">GTK_SEAccountsInc</param>
         </generator>
      </id>
      <property name="data" />
      
      <many-to-one name="parent" update="false" insert="false"  column="PARENTID" class="com.inceptor.gtk.beenz.ParentClass" />
            
  </class>

TABLES
Parent : ID, DATA
Child : ID, DATA, PARENTID (FK)

SQL:

Code:

--this returns all the data
Hibernate: select this.ID as ID1_, this.data as data1_, childclass1_.ID as ID0_, childclass1_.data as data0_, childclass1_.PARENTID as PARENTID0_ from Parentclass this left outer join childClass childclass1_ on this.ID=childclass1_.PARENTID where 1=1
--Follows the N+1 problem
Hibernate: select childclass0_.ID as ID1_, childclass0_.data as data1_, childclass0_.PARENTID as PARENTID1_, parentclas1_.ID as ID0_, parentclas1_.data as data0_ from childClass childclass0_ left outer join Parentclass parentclas1_ on childclass0_.PARENTID=parentclas1_.ID where childclass0_.PARENTID=?
Hibernate: select childclass0_.ID as ID1_, childclass0_.data as data1_, childclass0_.PARENTID as PARENTID1_, parentclas1_.ID as ID0_, parentclas1_.data as data0_ from childClass childclass0_ left outer join Parentclass parentclas1_ on childclass0_.PARENTID=parentclas1_.ID where childclass0_.PARENTID=?
Hibernate: select childclass0_.ID as ID1_, childclass0_.data as data1_, childclass0_.PARENTID as PARENTID1_, parentclas1_.ID as ID0_, parentclas1_.data as data0_ from childClass childclass0_ left outer join Parentclass parentclas1_ on childclass0_.PARENTID=parentclas1_.ID where childclass0_.PARENTID=?
Hibernate: select childclass0_.ID as ID1_, childclass0_.data as data1_, childclass0_.PARENTID as PARENTID1_, parentclas1_.ID as ID0_, parentclas1_.data as data0_ from childClass childclass0_ left outer join Parentclass parentclas1_ on childclass0_.PARENTID=parentclas1_.ID where childclass0_.PARENTID=?
Hibernate: select childclass0_.ID as ID1_, childclass0_.data as data1_, childclass0_.PARENTID as PARENTID1_, parentclas1_.ID as ID0_, parentclas1_.data as data0_ from childClass childclass0_ left outer join Parentclass parentclas1_ on childclass0_.PARENTID=parentclas1_.ID where childclass0_.PARENTID=?
Hibernate: select childclass0_.ID as ID1_, childclass0_.data as data1_, childclass0_.PARENTID as PARENTID1_, parentclas1_.ID as ID0_, parentclas1_.data as data0_ from childClass childclass0_ left outer join Parentclass parentclas1_ on childclass0_.PARENTID=parentclas1_.ID where childclass0_.PARENTID=?

Thank you so much
Examples of code

Code:

         Criteria nullCriteria = htx.createCriteria(ParentClass.class);
         nullCriteria.setFetchMode("child",FetchMode.EAGER);
         results = nullCriteria.list();

         Criteria nullCriteria = htx.createCriteria(ParentClass.class);
         //nullCriteria.setFetchMode("child",FetchMode.EAGER);
         results = nullCriteria.list();

         Query q = htx.createQuery("from com.inceptor.gtk.beenz.ParentClass as p left join fetch p.child");

Thank you so much

Woyzeck · **Joined:** Tue Aug 17, 2004 5:06 am **Posts:** 46

Has anyone solved this issue ?

I have exactly the same problem here...

jicidre · **Joined:** Fri Jan 28, 2005 12:28 pm **Posts:** 3

Nobody gave ME an answer, that for sure.
I solved my perf problem converting the one to one in 2 many to one.
That forced me to add a second FK from the parent class to the child one.
Awefull? Yes.
Solved the problem? Also yes.

Note: I had that problem with Hiber 2. Couldn't migrate to Hiber 3 yet. Perhaps is solved in there. Also, I've integrated IBatis to my project which deals perfectly well with complex queries. Not saying to replace Hibernate since it deals with dirtyness and other stuff, but to make both work together.

jt_1000 · **Posted:** Tue Feb 28, 2006 8:55 pm

did you try on the <one-to-one> side specifing:
lazy="false"
and
fetch="join"

Woyzeck · **Joined:** Tue Aug 17, 2004 5:06 am **Posts:** 46

jt_1000 wrote:

did you try on the <one-to-one> side specifing:
lazy="false"
and
fetch="join"

There's no such thing in the one-to-one element.
Applying this to the one-to-one side class did not change anything.

I think this is a Hibernate bug in version 2. It just ignores the constrained="true" setting.

jt_1000 · **Posted:** Wed Mar 01, 2006 12:46 pm

yes, that is a V3 solution.
Just throwing out another suggestion found in the V2 reference.pdf:
Have you tried a M-1 from Child
<many-to-one with the unique="true" attribute> - they seemed indicated that this would act as a one-to-one relationship... I think. See page 37 of v2 reference.pdf.

Woyzeck · **Joined:** Tue Aug 17, 2004 5:06 am **Posts:** 46

Thx for the response, but I've tried virtually all possible combinations. No chance to get it working without n+1 selects.

I'll now try the stupid way with two many-to-one associations.