Hibernate Community • View topic - Incorrect joins on composite ID

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Incorrect joins on composite ID

Page 1 of 1

[ 4 posts ]

Previous topic | Next topic

Author

Message

tsar bomba

Post subject: Incorrect joins on composite ID

Posted: Sat Jan 21, 2006 2:07 am

Regular

Joined: Mon Nov 14, 2005 7:33 pm
Posts: 73

I've got a simple parent/child relationship between two tables/objects but both tables have composite IDs. I'm able to query them but the joins are not correct.

Here are the details...

parent mapping:

Code:

<hibernate-mapping>
   <class 
      name="PactsSalesHeader" 
      proxy="PactsSalesHeader"
      table="pa_invhdrp">
     <composite-id name="Id" class="PactsSalesHeaderId">
      <key-property name="InvoiceNumber" column="ihinvno" />
         <key-property name="CompanyCode" column="comcode" />
    </composite-id>
     <property name="BranchId" column="brid"/>
      <property name="DateCreated" column="ihcrtdte"/>
      <property name="InvoiceTotal" column="ihinvtot"/>
     <bag 
        name="Branches" 
        table="pa_branchp" 
         inverse="true"
        lazy="true" 
        fetch="join"
        cascade="all-delete-orphan">
        <key>
            <column name="brid" />
            <column name="comcode" />
        </key>
        <one-to-many class="PactsBranch" />
     </bag>
   </class>
</hibernate-mapping>

child mapping:

Code:

<hibernate-mapping>
   <class 
      name="PactsBranch" 
      proxy="PactsBranch"
      table="pa_branchp">
     <composite-id name="Id" class="PactsBranchId">
      <key-property name="BranchId" column="brid" />
         <key-property name="CompanyCode" column="comcode" />
    </composite-id>
     <property name="Name" column="brname"/>
     <many-to-one
        name="Header"
         insert="false" 
         update="false"
        class="PactsSalesHeader">
         <column name="brid" />
         <column name="comcode" />
      </many-to-one>
   </class>
</hibernate-mapping>

The query that is returned:

Code:

select 
   this_.ihinvno as ihinvno17_1_, 
   this_.comcode as comcode17_1_, 
   this_.brid as brid17_1_, 
   this_.ihcrtdte as ihcrtdte17_1_, 
   this_.ihinvtot as ihinvtot17_1_, 
   branches2_.brid as brid3_, 
   branches2_.comcode as comcode3_, 
   branches2_.brid as brid18_0_, 
   branches2_.comcode as comcode18_0_, 
   branches2_.brname as brname18_0_ 
from 
   pa_invhdrp this_ 
left outer join 
   pa_branchp branches2_ 
   on 
   [b]this_.ihinvno=branches2_.brid[/b] 
   and 
   this_.comcode=branches2_.comcode

Notice the join criteria...it's not joining the correct fields...I would have expected this query:

Code:

select 
   this_.ihinvno as ihinvno17_1_, 
   this_.comcode as comcode17_1_, 
   this_.brid as brid17_1_, 
   this_.ihcrtdte as ihcrtdte17_1_, 
   this_.ihinvtot as ihinvtot17_1_, 
   branches2_.brid as brid3_, 
   branches2_.comcode as comcode3_, 
   branches2_.brid as brid18_0_, 
   branches2_.comcode as comcode18_0_, 
   branches2_.brname as brname18_0_ 
from 
   pa_invhdrp this_ 
left outer join 
   pa_branchp branches2_ 
   on 
   [b]this_.brid=branches2_.brid[/b] 
   and 
   this_.comcode=branches2_.comcode

Even though I specified the key fields to join on...it's using the primary key(s) from the parent mapping (or so it appears.)

What am I doing wrong?

Thanks!

Hibernate version: 3.1

Name and version of the database you are using: MSSQL 2000

Top

rajasaur

Post subject: Re: Incorrect joins on composite ID

Posted: Sat Jan 21, 2006 8:42 am

Senior

Joined: Tue Aug 23, 2005 8:52 am
Posts: 181

tsar bomba wrote:

Code:

     <bag 
        name="Branches" 
        table="pa_branchp" 
         inverse="true"
        lazy="true" 
        fetch="join"
        cascade="all-delete-orphan">
        <key>
            <column name="brid" />
            <column name="comcode" />
        </key>
        <one-to-many class="PactsBranch" />
     </bag>

In your mapping above, why do you declare the "table" attribute". The one-to-many indicates which table contains the one-to-many value. Remove the table attribute from the mapping.

IIUC, what hibernate is doing is do a join between the table that contains the bag mapping and the pa_branchp table and hence the join columns are the ones of the primary key of the table. If you remove the table attribute, then it would do the mapping between the columns in the bag declaration and the pa_branchp's composite-key.

Top

tsar bomba

Post subject:

Posted: Sat Jan 21, 2006 6:59 pm

Regular

Joined: Mon Nov 14, 2005 7:33 pm
Posts: 73

That table mapping must have been in there when I copied it out from another mapping file.

Anyhow, even after removing it, the query stayed exactly the same...it made no difference so the table attribute was obviously being ignored.

Any other ideas?

Top

rajasaur

Post subject:

Posted: Sun Jan 22, 2006 3:17 am

Senior

Joined: Tue Aug 23, 2005 8:52 am
Posts: 181

Yeah sorry about that.

You can use the property-ref in your mapping to refer to any column thats not the primary key. So your "set" mapping should contain a property-ref that maps to the components(brid and comcode in your case). You can create a "properties" tag that contains the brid and comcode and ues the name of the properties as a property-ref but the one thing I wasnt able to find out was how to define the properties tag and use that also in the composite-key.

Read about the property-ref and properties tag in the Reference Docs. Sorry, cant be of more help :)

Top

Page 1 of 1

[ 4 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum