One-to-many relations with composite natural keys

simon.brunning · **Posted:** Thu May 12, 2005 4:52 am

I'm having trouble building some mappings to my legacy database. I Can't work out how to build one-to-many mappings where both tables have natural composite keys. I have a lot of these to build, but once I've got one working, the rest should be easy.

A simple example. I have two tables; currency and exchange rate. The essential parts of the DDL looks like this:

Code:

Create table NIADATA/MACPFCC0
(
CCCMCC      DECIMAL     (    3, 0)     NOT NULL WITH DEFAULT    ,
CCISCD      CHARACTER   (    3)        NOT NULL WITH DEFAULT    ,
);

Create table NIADATA/MACPFCR0
(
CRCMCC      DECIMAL     (    3, 0)     NOT NULL WITH DEFAULT    ,
CRISCD      CHARACTER   (    3)        NOT NULL WITH DEFAULT    ,
CREXDT      DECIMAL     (    7, 0)     NOT NULL WITH DEFAULT    ,
);

(I'm showing only the key columns here. Both tables also have a bunch of other non-key columns, but I've stripped those out for the purposes of this explanation.) Note that the column names have different prefixes, though the last four characters are the same. I'm not in a position to make any changes to the existing database.

Now, building the mapping for the first of these tables alone was simple enough. Once Max helped me out with a separate issue with user types, that is. ;-)

Code:

<hibernate-mapping>

    <class name="uk.co.trisystems.morph.currency.CurrencyDAO" table="MACPFCC0">
        <meta attribute="class-description">
      Details of a currency.
    </meta>

        <composite-id name="CurrencyPK" class="uk.co.trisystems.morph.currency.CurrencyPK"> 

            <key-property name="company" type="integer" column="CCCMCC">
                <meta attribute="field-description">Currency Company</meta>

            <key-property name="iso" type="string" length="3" column="CCISCD">
                <meta attribute="field-description">ISO Currency Code</meta>
            </key-property>

        </composite-id>
    </class>
</hibernate-mapping>

Now I'm trying to add a mepping to the 2nd table, and to make the two tables refer to one another.

I think I need to add something like this to the currency mapping:

Code:

        <list name="exchangeRates" table="MACPFCR0">
            <key column="??????"/>
            <index column="CREXDT"/>
            <one-to-many class="uk.co.trisystems.morph.currency.ExchangeRateDAO"/>
        </list>

But I don't know what to put into the ?????? bit.

As for the exchange rate mapping, I tried this:

Code:

<hibernate-mapping>

    <class name="uk.co.trisystems.morph.currency.ExchangeRateDAO" table="MACPFCR0">
        <meta attribute="class-description">
      Currency exchange rate.
    </meta>

        <composite-id name="ExchangeRatePK" class="uk.co.trisystems.morph.currency.ExchangeRatePK"> 

            <key-property name="currency" type="uk.co.trisystems.morph.currency.CurrencyPK">
                <meta attribute="field-description">Currency</meta>
                <column name="CRCMCD"/>
                <column name="CRISCD"/>
            </key-property>

            <key-property name="date" type="uk.co.trisystems.morph.user_type.ImacsDateType" column="CREXDT">
                <meta attribute="field-description">Exchange Rate Date</meta>
            </key-property>
    </class>
</hibernate-mapping>

This generates OK, but when I try to retrieve data through it I get:

Code:

Exception in thread "main" net.sf.hibernate.MappingException: identifier mapping has wrong number of columns: uk.co.trisystems.morph.currency.ExchangeRateDAO type: uk.co.trisystems.morph.currency.ExchangeRatePK
   at net.sf.hibernate.mapping.RootClass.validate(RootClass.java:171)
   at net.sf.hibernate.cfg.Configuration.validate(Configuration.java:633)
   at net.sf.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:799)
   at uk.co.trisystems.morph.currency.ExchangeRateDAODemo.main(ExchangeRateDAODemo.java:39)

I'm running Hibernate 2.1.8.

simon.brunning · **Posted:** Thu May 12, 2005 9:18 am

I've looked at the possibility that I need to do something like a

Code:

<list>
    <composite-element>

in the currency (patent) mapping, but the DTD won't allow me to put key properties under the composite-element.

simon.brunning · **Posted:** Thu May 12, 2005 10:12 am

I'm making, uh, well, I'm not sure if I'm making progress, but I'm cvertainly making changes. ;-)

My exchange rate (child) mapping now looks like this:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>

    <class name="uk.co.trisystems.morph.currency.ExchangeRateDAO" table="MACPFCR0">
        <composite-id name="ExchangeRatePK" class="uk.co.trisystems.morph.currency.ExchangeRatePK"> 
            <key-many-to-one name="currency" class="uk.co.trisystems.morph.currency.CurrencyDAO">
                <meta attribute="field-description">Currency</meta>
                <column name="CRCMCD"/>
                <column name="CRISCD"/>
            </key-many-to-one>

            <key-property name="date" type="uk.co.trisystems.morph.user_type.ImacsDateType" column="CREXDT">
                <meta attribute="field-description">Exchange Rate Date</meta>
            </key-property>

        </composite-id>
        
    </class>
    
</hibernate-mapping>

Querying via this mapping, I get:

Code:

Exception in thread "main" net.sf.hibernate.MappingException: An association from the table MACPFCR0 refers to an unmapped class: uk.co.trisystems.morph.currency.CurrencyDAO
   at net.sf.hibernate.cfg.Configuration.secondPassCompileForeignKeys(Configuration.java:696)
   at net.sf.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:680)
   at net.sf.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:798)
   at uk.co.trisystems.morph.currency.ExchangeRateDAODemo.main(ExchangeRateDAODemo.java:39)

CurrencyDAO is mapped, though - see the mapping in my forst post. So, I don't understand the message.

simon.brunning · **Posted:** Tue May 24, 2005 4:33 am

FYI, it's working now. My mappings look like this:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
          "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>

    <class name="foo.bar.Parent" table="MACPFCC0">

        <composite-id name="ParentPK" class="foo.bar.ParentPK">

            <key-property name="company" type="integer">
                <meta attribute="field-description">Company</meta>
                <meta attribute="use-in-tostring">true</meta>
                <column name="CCCMCC"/>
            </key-property>

            <key-property name="iso" type="string" length="3">
                <meta attribute="field-description">ISO currency code</meta>
                <meta attribute="use-in-tostring">true</meta>
                <column name="CCISCD"/>
            </key-property>

        </composite-id>

        <property name="name" column="CCCYNM" type="string" length="30">
            <meta attribute="field-description">Currency name</meta>
            <meta attribute="use-in-tostring">true</meta>
        </property>

        <list name="children" lazy="true" inverse="true" cascade="all-delete-orphan">
            <key>
                <column name="CRCMCC"/>
                <column name="CRISCD"/>
            </key>
            <index column="CREXRT"/>
            <one-to-many class="foo.bar.Child"/>
        </list>
    </class>

</hibernate-mapping>

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
          "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>

    <class name="foo.bar.Child" table="MACPFCR0">

        <composite-id name="ChildPK" class="foo.bar.ChildPK">

            <key-many-to-one name="parent" class="foo.bar.Parent">
                <meta attribute="field-description">Parent</meta>
                <meta attribute="use-in-tostring">true</meta>
                <column name="CRCMCC"/>
                <column name="CRISCD"/>
            </key-many-to-one>

            <key-property name="key1" type="date">
                <meta attribute="field-description">Rate date</meta>
                <meta attribute="use-in-tostring">true</meta>
                <column name="CREXRT"/>
            </key-property>

        </composite-id>

        <property name="rate" column="CREXRT" type="big_decimal">
            <meta attribute="field-description">Child Property</meta>
            <meta attribute="use-in-tostring">true</meta>
        </property>

    </class>

</hibernate-mapping>

isandeeppatil · **Joined:** Wed Dec 07, 2011 1:35 pm **Posts:** 2

hi

The situtaion you were into looks almost simmilar to mine. I am trying out your solution , can you please post foo.bar.ChildPK java class.

For my my problem you can see the post at viewtopic.php?f=1&t=1014433

Thanks for your help !!!

simon.brunning · **Posted:** Fri Feb 17, 2012 7:15 am

Sorry, but I posted that some 7 years ago. What do you think are the chances that I can track down that source? :-)

yogendra · **Joined:** Wed Apr 18, 2012 11:26 am **Posts:** 1

isandeeppatil wrote:

hi

The situtaion you were into looks almost simmilar to mine. I am trying out your solution , can you please post foo.bar.ChildPK java class.

For my my problem you can see the post at viewtopic.php?f=1&t=1014433

Thanks for your help !!!

Code:

// Please ignore syntax errors as this is written right here right now :P

package foo.bar;

public class ChildPK implements Serializable{
  
  private Parent parent;
  private Date key1;
  public Parent getParent(){return parent;}
  public void setParent(Parent parent){ this.parent = parent;}
  public Date getKey1(){return key1;}
  public void setKey1(Date key1){this.key1 = key1;}
}