Hibernate Community • View topic - One-to-One formula challenge!

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

One-to-One formula challenge!

Page 1 of 1

[ 5 posts ]

Previous topic | Next topic

Author

Message

etienne

Post subject: One-to-One formula challenge!

Posted: Fri Jul 15, 2005 12:40 pm

Senior

Joined: Tue Jan 11, 2005 5:03 pm
Posts: 137
Location: Montreal, Quebec

Hi,

I think I have a real challenge here.

I have 2 links to do between 3 database tables (2 one-to-one relations).

My first table is AddressBook having a composite-key : AddressType and AddressKey.

I want to link it with theses 2 next tables using a one-to-one relation:

The second table is Composante having also a composite-key: Institution, Transit and ServiceCenter. The first 2 are Char(5) and ServiceCenter is a numeric(3)

The last Table is Branch having a single key : BranchId.

The relations i need to make between those 2 classes is not obvious.

The first one is between Branch and AddressBook.
It is done like this : AddressBook is a Branch Address if AddressBook.AddressType=='B' AND AddressBook.addressKey==Branch.BranchId.

The second link is more complicated:
AdressBook is a Composante Address if AddressBook.AddressType=='C' AND AddressBook.AddressKey == Composante.Institution+Composante.Transit+Composante.ServiceCenter!

I have check for a one-to-one formula, but I dont see a lot of documentation on this (even in the Hibernate in Action book).

I got this example (Person.hbm.xml) from the Hibernate 3.0.5 test section that seems similiar but I really dont know how it works. There is no comment.

Code:

<hibernate-mapping package="org.hibernate.test.onetooneformula">

   <class name="Person">
      <id name="name"/>
      <one-to-one name="address" cascade="all" constrained="false">
         <formula>name</formula>
         <formula>'HOME'</formula>
      </one-to-one>
      <one-to-one name="mailingAddress" constrained="false">
         <formula>name</formula>
         <formula>'MAILING'</formula>
      </one-to-one>
   </class>
   
   <class name="Address" batch-size="2" 
         check="addressType in ('MAILING', 'HOME', 'BUSINESS')">
      <composite-id>
         <key-many-to-one name="person" 
               column="personName"/>
         <key-property name="type" 
               column="addressType"/>
      </composite-id>
      <property name="street" type="text"/>
      <property name="state"/>
      <property name="zip"/>
   </class>

</hibernate-mapping>

Any idea?

Thanks

Etienne.

Top

gavin

Post subject:

Posted: Fri Jul 15, 2005 1:09 pm

Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia

http://www.hibernate.org/hib_docs/v3/re ... ed-onetone

Top

etienne

Post subject:

Posted: Fri Jul 15, 2005 1:43 pm

Senior

Joined: Tue Jan 11, 2005 5:03 pm
Posts: 137
Location: Montreal, Quebec

Thanks!

I will try to decipher the code and make an example work.

But as someone note in the page:

Code:

TODO: put words around this stuff

Top

etienne

Post subject:

Posted: Fri Jul 15, 2005 4:06 pm

Senior

Joined: Tue Jan 11, 2005 5:03 pm
Posts: 137
Location: Montreal, Quebec

Thank Gavin, it really rock for the first case!!

Code:

  
    <!-- This one-to-one relationship use 2 formula for each composite key of AddressBook.  It mean that the first composite key (AddressType) == 'S' and the second (AddressKey) equal the branchCode column of the Branch Table -->
    <one-to-one name="addressBook"
       class="AddressBook">
        <formula>'S'</formula>
        <formula>branchCode</formula>
    </one-to-one>

it generate a good SQL, and my test works!

Code:

select branch0_.branchCode as branchCode1_, 
   'C' as formula0_1_, 
   branch0_.branchCode as formula1_1_, 
   addressboo1_.addressType as addressT1_0_, 
   addressboo1_.addressKey as addressKey0_, 
   addressboo1_.streetFrench as streetFr3_5_0_ from as400_goodname.dbo.Branch branch0_ 
   left outer join as400_goodname.dbo.AddressBook addressboo1_ 
   on 'C'=addressboo1_.addressType 
   and branch0_.branchCode=addressboo1_.addressKey 
   where branch0_.branchCode=?

But I still have some problem with the second SQL generation:

Code:

    <one-to-one name="addressBook"        class="AddressBook">
        <formula>'C'</formula>
        <formula>(institution+transit+CONVERT(char(3), serviceCenter))</formula>
    </one-to-one>

It doesn't generate the left outer join...: I also try with a simple formula and I still have problem with the join... The only difference between Branch and Composante is that a composante have a Composite-id (using a composite-id component as recommended)...

This is the wrong SQL:

Code:

select composante0_.institution as institut1_, 
   composante0_.transit as transit, 
   composante0_.serviceCenter as serviceC3_, 
   composante0_.composanteType as composan4_7_, 
   composante0_.paymentMode as paymentM5_7_, 
   'C' as formula0_, 
   composante0_.institution as formula1_ 
   from as400_goodname.dbo.Composante composante0_

I dont see any reference to the Address table...

Thanks for helping me.

Etienne.

Top

etienne

Post subject:

Posted: Mon Jul 18, 2005 2:28 pm

Senior

Joined: Tue Jan 11, 2005 5:03 pm
Posts: 137
Location: Montreal, Quebec

HI,

I finally made it!

Here the mapping I need to put for the second case:

Code:

    <one-to-one name="addressBook" class="com.desjardins.vmd.persistence.bdcorpo.model.general.AddressBook">
        <formula>'C'</formula>
        <formula>(institution+transit+right(replicate('0',3)+convert(VARCHAR(3), serviceCenter), 3))</formula>
    </one-to-one>

The error I had was unrelated to the code... I was running another test case... pathetic!

Thank for everything!

Etienne.

Top

Page 1 of 1

[ 5 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