Search This Blog

Sunday, 8 January 2012

Composite Foreign Key referencing Nonprimary Keys

In the previous post we had a scenario wherein the foreign key was not actually referring to the primary key but instead to another natural key. Let us take this not -so normal scenario a step further into the weird. What if the natural key is formed by a group of columns as opposed to the single column natural key we saw earlier ?
In the previous example of student and vehicles, STUDENT_NAME acted as a unique (and therefore natural) key for Student. The Vehicle entity referred to this column in student table instead of the primary key id.
Let us modify the Student entity to add a new column SCHOOL_NAME. The natural key now would be a combination of student name and school name.
public class Student {
    private Integer id;
    private String name;
    private String schoolName;
    private Set<Vehicle> vehicles = new HashSet<Vehicle>();
//setter-getter methods
}
The Vehicle entity remains the same as before
public class Vehicle {
    private Integer id;
    private String model;
    private Student student;
//setter-getter methods
}
The difference is in how hibernate maps the new constraint to continue to maintain the association as before. The hbm for Student is as below:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.ec.composite_foreign_non_primary">
    <class name="Student" table="STUDENT">
        <id name="id" type="integer" column="ID">
            <generator class="native" />
        </id>
        <properties name="StudentKey" unique="true">
            <property name="name" column="STUDENT_NAME" />
            <property name="schoolName" column="SCHOOL_NAME" />
        </properties>
        <set name="vehicles" cascade="all" inverse="true">
            <key property-ref="StudentKey" >
                <column name="STUDENT_NAME"/>
                <column name="SCHOOL_NAME"/>
            </key>
            <one-to-many class="Vehicle" />
        </set>
    </class>
</hibernate-mapping>
As can be seen a new properties element has been added. The element allows us to give a group of properties a single name. Now it is possible to refer these properties in the property-ref attribute. Additional benefits include moving common properties for the column to the properties element. For e.g. rather than specifying the unique property on each element the property has been added to the "StudentKey" element. The same concept applied for the set of vehicles.
The mapping for vehicle entity would now be as below:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.ec.foreign_non_primary">
    <class name="Vehicle" table="VEHICLE">
        <id name="id" type="integer" column="ID">
            <generator class="native" />
        </id>
        <property name="model" column="MODEL" />
        <many-to-one name="student" class="Student" property-ref="StudentKey">
            <column name="STUDENT_NAME" />
            <column name="SCHOOL_NAME" />
        </many-to-one>
    </class>
</hibernate-mapping>
As can be seen the many-to-one element is now associated with two columns and not one.I executed code similar to the earlier post to create records.
static void testCreate() {
    Student student = new Student();
    student.setName("Rajesh");
    student.setSchoolName("St Josephs Boys High School Khirkee");
        
    Vehicle vehicle1 = new Vehicle();
    vehicle1.setModel("Honda Unicorn");
    vehicle1.setStudent(student);
    student.getVehicles().add(vehicle1);

    Session session = sessionFactory.openSession();
        
    Transaction t = session.beginTransaction();
    session.save(student);
    t.commit();
}
On execution I received the error:
Exception in thread "main" java.lang.ClassCastException: com.ec.composite_foreig
n_non_primary.Student cannot be cast to java.io.Serializable
    at org.hibernate.type.CollectionType.getKeyOfOwner(CollectionType.java:355)
    at org.hibernate.engine.Collections.processReachableCollection(Collections.java
:173)
I fixed the same by marking the Student class serializable.
public class Student implements Serializable{
On running the code agian the records were created successfully. The resultant data is as seen in the screen shot:
Schema Diagram of Database

2 comments:

  1. Hi,

    thanks for post, very usefull.
    My scenario is a bit different; the vehicle table have a composite primary id:
    - student_id
    - vehicle_id

    But when I save the student object Hibernate don't set student id into vehicle table, Why?

    Thanks in advance

    ReplyDelete