one-to-one mapping and redundant queries

Nilf · **Joined:** Tue Jul 01, 2008 4:56 am **Posts:** 2

I have simple one-to-one mapping for Blank and Test entities

Mapping documents:
<class name="TSTTicket" table="TST_TICKET" >
<id name="Id" column="TCK_ID" type="long" unsaved-value="-1"
access="field.camelcase-underscore">
<generator class="native" />
</id>
<property name="Name" column="TCK_NAME" type="string" length="5"
not-null="true" access="field.camelcase-underscore"/>
<many-to-one name="Blank" class="TSTBlank" column="BLA_ID" unique="true"
not-null="true" access="field.camelcase-underscore" />
</class>

<class name="TSTBlank" table="TST_BLANK">
<id name="Id" column="BLA_ID" type="long" unsaved-value="-1"
access="field.camelcase-underscore">
<generator class="native" />
</id>
<property name="Name" column="BLA_NAME" type="string" length="5"
not-null="true" access="field.camelcase-underscore"/>

<one-to-one name="Ticket" class="TSTTicket" property-ref="Blank" />
</class>

I want to fetch all in one query:

Code:

List<TSTBlank> result = null;
            result = (List<TSTBlank>)Session.CreateQuery(
                    "from TSTBlank b  left join fetch b.Ticket t")
                       .List<TSTBlank>(); 

But redundant query is executing for each TSTBlank in result list
The generated SQL (show_sql=true):

Code:

NHibernate: select tstblank0_.BLA_ID as BLA1_24_0_, tstticket1_.TCK_ID as TCK1_25_1_, tstblank0_.BLA_NAME as BLA2_24_0_, tstticket1_.TCK_NAME as TCK2_25_1_, tstticket1_.BLA_ID as BLA3_25_1_ from TST_BLANK tstblank0_ left outer join TST_TICKET tstticket1_ on tstblank0_.BLA_ID=tstticket1_.BLA_ID
NHibernate: SELECT tstticket0_.TCK_ID as TCK1_25_0_, tstticket0_.TCK_NAME as TCK2_25_0_, tstticket0_.BLA_ID as BLA3_25_0_ FROM TST_TICKET tstticket0_ WHERE tstticket0_.BLA_ID=?; p0 = '1'
NHibernate: SELECT tstticket0_.TCK_ID as TCK1_25_0_, tstticket0_.TCK_NAME as TCK2_25_0_, tstticket0_.BLA_ID as BLA3_25_0_ FROM TST_TICKET tstticket0_ WHERE tstticket0_.BLA_ID=?; p0 = '

Hibernate version: 1.2.1
Name and version of the database you are using:DB2Dialect, DB2 9.5-C

I found same problem in bugreports for older versions, but in 0.8 it must be fixed...

wolli · **Posted:** Wed Jul 02, 2008 3:00 am

I'm not sure, but I think there was a similar posting about this a few month ago. The solution was to use the non-generic version of List():

Code:

List<TSTBlank> result = new List<TSTBlank>();

result = Session.CreateQuery(
             "from TSTBlank b  left join fetch b.Ticket t")
            .List(result);

Try this, maybe it helps.

Nilf · **Joined:** Tue Jul 01, 2008 4:56 am **Posts:** 2

Thanks for suggestion, but it didn't help :( It seems that there is NH bug for bidirectional one-to-one mapping with foreign key assosiation.

wolli · **Posted:** Thu Jul 03, 2008 3:28 am

Try and set lazy="false" on the "Ticket" association on TSTBlank and check if that changes the behavior.