Thursday, December 07, 2006

Hibernate mapping legacy data

I recently encountered this while working with Hibernate. Mapping legacy data where foreign key does not refer to a primary key of the associated table is complex and is not generated correctly using many of the IDE's out there. You basically have to do some tweakings to make it work.

Two cases arise:
1) when foreign key refers to a unique key of the associated table
2) when foreign key refers to a non-primary/non-unique key

You will get an error of similar sort when mapping without the tweakings in the above cases:

[java] org.hibernate.MappingException: Foreign key (FK6771BFAA1845E8B:CardField [])) must have same number of columns as the referenced primary key (Card [project_id])
> [java] at org.hibernate.mapping.ForeignKey.alignColumns(
> [java] at org.hibernate.mapping.ForeignKey.alignColumns(
> [java] at org.hibernate.cfg.Configuration.secondPassCompileForeignKeys(
> [java] at org.hibernate.cfg.Configuration.secondPassCompile(
> [java] at org.hibernate.cfg.AnnotationConfiguration.secondPassCompile(
> [java] at org.hibernate.cfg.Configuration.buildMappings(

The trick is to utilize the "property-ref" attribute to solve the above mapping issue.

Consider you have two tables:
Employee (emp_ID, emp_SSN, emp_Name)
Manager (mgr_SSN, mgr_Id)

In employee table empID is the primary key and empSSN is unique. Manager table has a many-to-one relation with the employee table with one manager can be assigned to multiple departments.

In Employee.hbm.xml file the empSSN would be defined like this:

<property name="empSSN" type="java.lang.Integer">
     <column name="emp_SSN" not-null="true" unique="true" />

<set name="managers" inverse="true">
     <key property-ref="empSSN">
          <column name="mgr_SSN" not-null="true" />
     <one-to-many class="com.hibernate.mapping.Manager"/>

In manager.hbm.xml the property will be defined like this

<property name="mgrSSN" type="java.lang.Integer">
     <column name="mgr_SSN" not-null="true" />

<many-to-one name="employee" class="com.hibernate.mapping.Employee" update="false" insert="false" fetch="select" property-ref="empSSN" column="mgr_SSN" />

This should make it work!!!

Some other resources:

Post on Serverside
Hibernate Docs