Problem with Hibernate composite key mapping

Hi,

I'm trying the Hibernate mapping between two tables - AGENCY and OFFICER_AGENCY.

The AGENCY table has the composite primary key which consists of agency_code and sub_agency_code. This is defined as a composite Java object AgencyPK.

The table AGENCY holds a one-to-many relationship with OFFICER_AGENCY, which has int_ofcr2agcy_id as the primary key and has foreign key reference to the agency_code and sub_agency_code fields of table AGENCY.

The following exception is thrown when I try to map the relationships:

Foreign key (FKBB3E234E2C0B82A3 OFFICER_AGENCY [INT_OFCR2AGCY_ID])) must have same number of columns as the referenced primary key (AGENCY [AGENCY_CODE,SUB_AGENCY_CODE])
org.hibernate.MappingException: Foreign key (FKBB3E234E2C0B82A3 O FFICER_AGENCY [INT_OFCR2AGCY_ID])) must have same number of columns as the referenced primary key (AGENCY [AGENCY_CODE,SUB_AGENCY_CODE])
at org.hibernate.mapping.ForeignKey.alignColumns(ForeignKey.java:90)
at org.hibernate.mapping.ForeignKey.alignColumns(ForeignKey.java:73)
at org.hibernate.cfg.Configuration.secondPassCompileForeignKeys(Configuration.java:1145)
at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:1052)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1168)
at FirstExample.main(FirstExample.java:23)

This is the mapping used:

// Agency.hbm.xml

<hibernate-mapping>
<class name="test.bean.Agency" schema="ECITATION" table="AGENCY">

<composite-id name="agencyPK" class="test.bean.AgencyPK">
<key-property column="AGENCY_CODE" length="4" name="agencyCode" type="java.lang.String"/>
<key-property column="SUB_AGENCY_CODE" length="4" name="subAgencyCode" type="java.lang.String"/>
</composite-id>

<property column="AGENCY_NAME" length="50" name="agencyName" type="java.lang.String"/>
<property column="AGENCY_TYPE" length="50" name="agencyType" type="java.lang.String"/>

<set name="officerAgency" lazy="true" inverse="true">
<key column="INT_OFCR2AGCY_ID"></key>
<one-to-many class="test.bean.OfficerAgency"/>
</set>
</class>
</hibernate-mapping>

----------------------------------------------------------------------------
// OfficerAgency.hbm.xml

<hibernate-mapping>
<class name="test.bean.OfficerAgency" schema="ECITATION" table="OFFICER_AGENCY">

<id column="INT_OFCR2AGCY_ID" name="intOfcr2agcyId" type="java.lang.Long">
<generator class="sequence"/>
</id>

<property column="AGENCY_CODE" length="4" name="agencyCode" not-null="true" type="java.lang.String"/>
<property column="SUB_AGENCY_CODE" length="4" name="subAgencyCode" not-null="true" type="java.lang.String"/>
<property column="START_DATE" length="7" name="startDate" type="java.util.Date"/>
<property column="END_DATE" length="7" name="endDate" type="java.util.Date"/>

<many-to-one name="agencyPK" class="us.md.state.courts.bean.AgencyPK" insert="false" update="false" not-null="true">
</many-to-one>

</class>
</hibernate-mapping>

-------------------------------------------------------------------------

How can define the relationship in Hibernate? Could someone please help?

Thanks in advance,
Jenny
[ February 15, 2007: Message edited by: Jenny George ]

<property column="AGENCY_CODE" length="4" name="agencyCode" not-null="true" type="java.lang.String"/>
<property column="SUB_AGENCY_CODE" length="4" name="subAgencyCode" not-null="true" type="java.lang.String"/>

<many-to-one name="agencyPK" class="us.md.state.courts.bean.AgencyPK" insert="false" update="false" not-null="true">
</many-to-one>

Don't you need to put those two properties, in the many-to-one instead of a seperate property?

like

Mark

Thank you, Mark. The problem was solved when a property reference to the composite object was given for the mapping:

Agency.hbm.xml
--------------
<hibernate-mapping>
<class name="us.md.state.courts.bean.Agency" schema="ECITATION" table="AGENCY">
<composite-id name="agencyPK" class="us.md.state.courts.bean.AgencyPK">
<key-property column="AGENCY_CODE" length="4" name="agencyCode" type="java.lang.String"/>
<key-property column="SUB_AGENCY_CODE" length="4" name="subAgencyCode" type="java.lang.String"/>
</composite-id>

<property column="AGENCY_NAME" length="50" name="agencyName" type="java.lang.String"/>
<property column="AGENCY_TYPE" length="50" name="agencyType" type="java.lang.String"/>

<property name="agencyPK" type="us.md.state.courts.bean.AgencyPK" insert="false" update="false" access="field" />

<set name="officerAgency" table="OFFICER_AGENCY" >
<key property-ref="agencyPK" />
<one-to-many class="us.md.state.courts.bean.OfficerAgency" />
</set>

</class>
</hibernate-mapping>

OfficerAgency.hbm.xml
---------------------
<hibernate-mapping>
<class name="us.md.state.courts.bean.OfficerAgency" schema="ECITATION" table="OFFICER_AGENCY">

<id column="INT_OFCR2AGCY_ID" name="intOfcr2agcyId" type="java.lang.Long">
<generator class="sequence"/>
</id>

<property column="AGENCY_CODE" length="4" name="agencyCode" not-null="true" type="java.lang.String"/>
<property column="SUB_AGENCY_CODE" length="4" name="subAgencyCode" not-null="true" type="java.lang.String"/>
<property column="OFFICER_ID" length="4" name="officerId" not-null="true" type="java.lang.String"/>
<property column="START_DATE" length="7" name="startDate" type="java.util.Date"/>
<property column="END_DATE" length="7" name="endDate" type="java.util.Date"/>

<many-to-one name="officer" class="us.md.state.courts.bean.Officer" column="OFFICER_ID" insert="false" update="false" not-null="true">
</many-to-one>

</class>
</hibernate-mapping>

Thank you very much for your help.

Jenny