many-to-many mapping with composite keys on both sides

piccione99 · **Joined:** Mon Jan 07, 2008 11:18 am **Posts:** 2

In our database tables, we have many instances of many to many relationships with composite keys on both sides. These types of composite key relationships are not unusual and have existed in every database I have ever worked with for the last 15 years.

Here is an example relationship from our DB:

Alert_subscription Table - primary key (ian, alert_subscription_type_id)

Alert_subscription_Email_Map Table - primary key (ian, alert_subscription_type_id, email_address_id)

Email_Address Table - primary key (ian, email_address_id)

I have tried many, many different syntaxes in the AlertSubscription.hbm.xml mapping file to represent emails as a Set. While the syntax is fine as far as the hibernate mapping is concerned, I always end up getting the same exact exception later in JBOSS startup, no matter what I have tried:

collection foreign key mapping has wrong number of columns: com.ingrid.ibp.common.entity.AlertSubscription.emails type: component[ingridAccountNumber,alertSubscriptionTypeId]

In a similiar example, the mapping DOES work because the first table (Reminder table as opposed to alert_subscription table) does NOT have a composite key and simply uses a generator key.

As a result, there is not much point in our using hiberate - we have to map each of our tables individually and might as well be using jdbc.

Can someone please confirm or deny that hibernate just does not handle many-to-many relationships with (for example) a 2-column to 3-column to 2-column key mapping?

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Piccione,

Yes, you can achieve a many-to-many relationship using composite primary keys instead of the single-column, meaningless ID.

What you do is to mix the recommendations of Chapter 7 of the reference, where they teach you about "Association Mappings", with Chapter 8 of the reference, where they teach you about Component Mapping with multiple keys.

Here's a working example: Suppose you have 2 main tables, A and C.
You want to create a many-to-many relationship between them, so you use a relation table B.

Both main tables A and C have composite primary keys. In order to represent that, you create 2 small classes, PKA and PKC. This is how your code would look like (I suppress the getters and setters for brevity, and assume it to be unidirectional only)

Code:

public class A {
   private APK idA;
   private Set<C> cs=new HashSet<C>();
}

Code:

public class C {
      private CPK idC;
}

Code:

public class APK implements Serializable{
    String ak1;
    String ak2;
}

Code:

public class CPK implements Serializable{
    private String ck1;
    private String ck2;
}

This would be a mapping file that achieves what you want

Code:

<hibernate-mapping package="test5" >
  <class name="A" table="A">
    <composite-id name="idA" class="APK">
      <key-property name="ak1"/>
      <key-property name="ak2"/>
    </composite-id>
    
    <set name="cs" table="B">
      <key>
        <column name="ak1"/>
        <column name="ak2"/>
      </key> 
      <many-to-many class="C">
        <column name="ck1"/>
        <column name="ck2"/>
      </many-to-many>
    </set>
  </class>
    
      
  <class name="C" table="C">
    <composite-id name="idC" class="CPK">
      <key-property name="ck1"/>
      <key-property name="ck2"/>
    </composite-id>
  </class>
</hibernate-mapping>

And these would be the corresponding tables (DDL):
create table A (ak1 varchar(255) not null, ak2 varchar(255) not null, primary key (ak1, ak2))
create table B (ak1 varchar(255) not null, ak2 varchar(255) not null, ck1 varchar(255) not null, ck2 varchar(255) not null, primary key (ak1, ak2, ck1, ck2))
create table C (ck1 varchar(255) not null, ck2 varchar(255) not null, primary key (ck1, ck2))
alter table B add constraint FK42AB395985 foreign key (ak1, ak2) references A
alter table B add constraint FK42AB39688B foreign key (ck1, ck2) references C