Session load with composite keys

anjithalb · **Posted:** Mon Aug 29, 2005 3:16 am

Hi,

I have following composite key assigned to MerchantOperator POJO class.

<composite-id>
<key-property name="stringId" column="UserId" length="50" />
<key-many-to-one name="marchant" column="MarchantId" />
</composite-id>

When I call the Session's load method i. e....

public Object load(Class theClass,
Serializable id)
throws HibernateException

then, what will be the value of id? How can I specify composite Id there?

Thank you.
Thilina.

sven · **Posted:** Mon Aug 29, 2005 3:41 am

Use a serializable class for your composite-id.
Note that equals and hashCode have to be overwritten.

Then you can use: load(Object, Serializable).

There is more information in the documentation.

See for an example:
http://cvs.sourceforge.net/viewcvs.py/h ... Order.java
http://cvs.sourceforge.net/viewcvs.py/h ... dTest.java

Alternatively, you can of course use HQL query.

Best regards
Sven

thilinaa · **Joined:** Tue Aug 30, 2005 1:05 am **Posts:** 11

I have many-to-many bidirectinal relationship with 2 classes(Group and Customer) Both the classes having Id as composite key. So how can I specify this in the hbm.xml mapping file.

I tried as follows;

hbm.xml mapping file.........

<class name="Marchant" table="Marchants">
<id name="stringId" column="MarchantId" length="50"/>
<property name="marchantName" column="MarchantName" length="200" not-null="true" />
<property name="email" column="Email" length="100" />
<property name="msisdn" column="Msisdn" length="20" />
<property name="lastModifiedDateTime" column="LastModifiedDateTime" type="timestamp"/>
<property name="status" column="Status" length="10" />
<property name="shortCode" column="ShortCode" length="20" />
</class>

<class name="Customer" table="Customers">
<composite-id >
<key-property name="customerId" type="long" column="CustomerId" length="50" />
<key-many-to-one name="marchant" column="MarchantId" />
</composite-id>
<property name="msisdn" column="MSISDN" length="20" unique="true"/>
<property name="shortName" column="ShortName" length="200"/>
<property name="fullName" column="FullName" length="200"/>
<property name="email" column="Email" length="100" unique="true"/>
<set name="groups" table="CustomerGroups">
<key column="CustomerId" />
<many-to-many column="GroupId" class="Group"/>
</set>
</class>

<class name="Group" table="Groups">
<composite-id >
<key-property name="stringId" column="GroupId" length="50" />
<key-many-to-one name="marchant" column="MarchantId" />
</composite-id>
<property name="groupName" column="GroupName" length="100"/>
<property name="description" column="Description"/>
<property name="lastModifiedDateTime" column="LastModifiedDateTime" type="timestamp"/>
<set name="customers" table="CustomerGroups" inverse="true">
<key column="GroupId" />
<many-to-many column="CustomerId" class="Customer"/>
</set>
</class>

It gives following exceptions.....

org.hibernate.MappingException: Foreign key (FK22573372E1840434:CustomerGroups [CustomerId])) must have same number of columns as the referenced primary key (Customers [CustomerId,MarchantId])
at org.hibernate.mapping.ForeignKey.alignColumns(ForeignKey.java:86)
at org.hibernate.mapping.ForeignKey.setReferencedTable(ForeignKey.java:51)
at org.hibernate.cfg.Configuration.secondPassCompileForeignKeys(Configuration.java:976)
at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:921)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:999)
...........
...................
............................

I really appreciate for any help.
Thank you.
thilinaa

ardal · **Posted:** Tue Aug 30, 2005 3:55 am

I never tend to use the many-to-many relationship, but break it into composite elements (many-to-one & one-to-many).

In any case you can use the key element, with multiple column child elements to fix your error with your composite keys

The Map for the Group can look something like this:

Code:

<class name="Group" table="Groups"> 
:
:
 <set name="customers" table="CustomerGroups" inverse="true"> 

    <key> 
        <column name="GroupId"/>
        <column name="MarchantId "/>
    </key>
     <many-to-many class="Customer"/> 
  </set> 
</class>

Hopefully this will fix your foreign key problem

thilinaa · **Joined:** Tue Aug 30, 2005 1:05 am **Posts:** 11

I have changed my hbm.xml as follows,

Code:

        <class name="Customer" table="Customers">
        <composite-id >
            <key-property name="customerId" type="long" column="CustomerId" length="50" />
            <key-many-to-one name="marchant" column="MarchantId"  />
        </composite-id>
        <property name="msisdn" column="MSISDN" length="20" unique="true"/>
        <property name="shortName" column="ShortName" length="200"/>
        <property name="fullName" column="FullName" length="200"/>
        <property name="email" column="Email" length="100" unique="true"/>
        <set name="groups" table="CustomerGroups">
            <key>
                <column name="CustomerId"/>
                <column name="MarchantId"/>
            </key>
            <many-to-many column="GroupId" class="Group"/>
        </set>
    </class>

    <class name="Group" table="Groups">
        <composite-id >
            <key-property name="stringId" column="GroupId" length="50"  />
            <key-many-to-one name="marchant" column="MarchantId"  />
        </composite-id>
        <property name="groupName" column="GroupName" length="100"/>
        <property name="description" column="Description"/>
        <property name="lastModifiedDateTime" column="LastModifiedDateTime" type="timestamp"/>
        <set name="customers" table="CustomerGroups" inverse="true">
            <key>
                <column name="GroupId"/>
                <column name="MarchantId"/>
            </key>
            <many-to-many column="CustomerId" class="Customer"/>
        </set>
    </class>

But still having same problem....
org.hibernate.MappingException: Foreign key (FK22573372E1840434:CustomerGroups [CustomerId])) must have same number of columns as the referenced primary key (Customers [CustomerId,MarchantId])

Help!!!!!!!!

thilinaa · **Joined:** Tue Aug 30, 2005 1:05 am **Posts:** 11

I have solve the problem by changing the hbm.xml mapping file as follows;

Code:

    <class name="Marchant" table="Marchants">
        <id name="stringId" column="MarchantId" length="50"/>
        <property name="marchantName" column="MarchantName" length="200" not-null="true" />
        <property name="email" column="Email" length="100" />
        <property name="msisdn" column="Msisdn" length="20" />
        <property name="lastModifiedDateTime" column="LastModifiedDateTime" type="timestamp"/>
        <property name="status" column="Status" length="10" />
        <property name="shortCode" column="ShortCode" length="20" />
    </class>
    <class name="Customer" table="Customers">
        <composite-id >
            <key-property name="stringId" column="CustomerId" length="50"  />
            <key-many-to-one name="marchant" column="MarchantIdCustomer"  />
        </composite-id>
        <property name="msisdn" column="MSISDN" length="20" unique="true"/>
        <property name="shortName" column="ShortName" length="200"/>
        <property name="fullName" column="FullName" length="200"/>
        <property name="email" column="Email" length="100" unique="true"/>
        <set name="groups" table="CustomerGroups">
            <key>
                <column name="CustomerId"/>
                <column name="MarchantIdCustomer" />
            </key>
            <many-to-many class="Group" >
                <column name="GroupId"/>
                <column name="MarchantIdGroup"/>
            </many-to-many>
        </set>
    </class>

    <class name="Group" table="Groups">
        <composite-id >
            <key-property name="stringId" column="GroupId" length="50"  />
            <key-many-to-one name="marchant" column="MarchantIdGroup"  />
        </composite-id>
        <property name="groupName" column="GroupName" length="100"/>
        <property name="description" column="Description"/>
        <property name="lastModifiedDateTime" column="LastModifiedDateTime" type="timestamp"/>
        <set name="customers" table="CustomerGroups" inverse="true">
            <key>
                <column name="GroupId"/>
                <column name="MarchantIdGroup" />
            </key>
            <many-to-many class="Customer" >
                <column name="CustomerId"/>
                <column name="MarchantIdCustomer"/>
            </many-to-many>
        </set>
    </class>

I can see the created database tables as follows;

Code:

CREATE TABLE Marchants (
  MarchantId varchar(50) NOT NULL default '',
  MarchantName varchar(200) NOT NULL default '',
  Email varchar(100) default NULL,
  Msisdn varchar(20) default NULL,
  LastModifiedDateTime datetime default NULL,
  Status varchar(10) default NULL,
  ShortCode varchar(20) default NULL,
  PRIMARY KEY  (MarchantId)
) TYPE=InnoDB;

CREATE TABLE Groups (
  GroupId varchar(50) NOT NULL default '',
  MarchantIdGroup varchar(50) NOT NULL default '',
  GroupName varchar(100) default NULL,
  Description varchar(255) default NULL,
  LastModifiedDateTime datetime default NULL,
  PRIMARY KEY  (GroupId,MarchantIdGroup),
  KEY FK7FA2C5F4688C34A1 (MarchantIdGroup),
  FOREIGN KEY (`MarchantIdGroup`) REFERENCES `Marchants` (`MarchantId`)
) TYPE=InnoDB;

CREATE TABLE Customers (
  CustomerId varchar(50) NOT NULL default '',
  MarchantIdCustomer varchar(50) NOT NULL default '',
  MSISDN varchar(20) default NULL,
  ShortName varchar(200) default NULL,
  FullName varchar(200) default NULL,
  Email varchar(100) default NULL,
  PRIMARY KEY  (CustomerId,MarchantIdCustomer),
  UNIQUE KEY MSISDN (MSISDN),
  UNIQUE KEY Email (Email),
  KEY FKD7809C35C7E54D5E (MarchantIdCustomer),
  FOREIGN KEY (`MarchantIdCustomer`) REFERENCES `Marchants` (`MarchantId`)
) TYPE=InnoDB;

CREATE TABLE CustomerGroups (
  CustomerId varchar(50) NOT NULL default '',
  MarchantIdCustomer varchar(50) NOT NULL default '',
  GroupId varchar(50) NOT NULL default '',
  MarchantIdGroup varchar(50) NOT NULL default '',
  PRIMARY KEY  (CustomerId,MarchantIdCustomer,GroupId,MarchantIdGroup),
  KEY FK225733727F7EFDF1 (CustomerId,MarchantIdCustomer),
  KEY FK22573372B72C759C (GroupId,MarchantIdGroup),
  FOREIGN KEY (`GroupId`, `MarchantIdGroup`) REFERENCES `Groups` (`GroupId`, `MarchantIdGroup`),
  FOREIGN KEY (`CustomerId`, `MarchantIdCustomer`) REFERENCES `Customers` (`CustomerId`, `MarchantIdCustomer`)
) TYPE=InnoDB;

But the problem is CustomerGroups table have 2 colums (MarchantIdCustomer and MarchantIdGroup) refers to same data, because both MarchantIdCustomer and MarchantIdGroup refers to Merchant class. This results duplicate data.

But I want to have a CustomerGroups table with 3 columns (i.e. CustomerId, MarchantId, GroupId) . MarchantId represents both MarchantIdCustomer and MarchantIdGroup columns in the previous table. (Since both reperesents same thing, I want to make it one)

Is this possible with Hibernate. If so.. how can we change the hbm.xml mapping file?

I really tired with searching this. Sorry for the long comment...

Thank you.
thilinaa