Search This Blog

Friday, 30 December 2011

Composite Identifiers And Associations-1

As seen in an earlier post, Composite Identifiers are used when you have natural primary keys as opposed to surrogate keys.In this case the primary keys are assigned by the user. There exists the case when the primary key used is actually the primary key of some other table. i.e. it is a foreign key. In this case how do we manage the identifier and also create an association for directional navigation ??

Version 1

Consider a simple User class that uses the user name as the primary key.
public class User {
    private String name;
    private Integer age;
//setter-getter methods
}
The hibernate mapping for the same would be as below:
<?xml version="1.0"?>
<!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_primary">
    <class name="User" table="USER">
        <id name="name" type="string">
            <column name="USER_NAME" />
            <generator class="assigned" />
        </id>
        <property name="age" type="integer">
            <column name="AGE" />
        </property>
    </class>
</hibernate-mapping>
Now consider a new entity - Student. This Student is uniquely identified by the user name and the student code. The user name indicates that there is as an association between the Student and the User table(one-to-one in this case).
public class Student implements Serializable {

    private String userName;
    private String studentCode;
    private User user;
        //setter getters
}
The hibernate file would 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.composite_primary">
    <class name="Student" table="STUDENT">
        <composite-id>
            <key-property name="userName" column="USER_NAME" />
            <key-property name="studentCode" column="STUDENT_CODE" />
        </composite-id>
        <many-to-one name="user" class="User" foreign-key="STUDENT_FK1"
            unique="true" cascade="save">
            <column name="USER_NAME" />
        </many-to-one>
    </class>
</hibernate-mapping>
The user_name column is now a part of the identifier as well as the element.On start up the logs show

1594 [main] 
DEBUG org.hibernate.cfg.Configuration  - resolving reference to clas
s: com.ec.composite_primary.User
Exception in thread "main" org.hibernate.MappingException: Repeated column in ma
pping for entity: com.ec.composite_primary.Student column: USER_NAME (should be 
mapped with insert="false" update="false")
    at org.hibernate.mapping.PersistentClass.checkColumnDuplication(PersistentClass
.java:652)
    at org.hibernate.mapping.PersistentClass.checkPropertyColumnDuplication(Persist
entClass.java:674)
To resolve the error, it is needed that the association is mapped as read-only. The change association would now be
<many-to-one name="user" class="User" foreign-key="STUDENT_FK1" cascade="save"
    unique="true" insert="false" update="false">
    <column name="USER_NAME" />
</many-to-one>
Now the code runs to generate the following DDL:
create table STUDENT (
        USER_NAME varchar(255) not null,
        STUDENT_CODE varchar(255) not null,
        primary key (USER_NAME, STUDENT_CODE)
    )
    create table USER (
        USER_NAME varchar(255) not null,
        AGE integer,
        primary key (USER_NAME)
    )
    alter table STUDENT 
        add index STUDENT_FK1 (USER_NAME), 
        add constraint STUDENT_FK1 
        foreign key (USER_NAME) 
        references USER (USER_NAME)
The code to insert a record would be:
static void testCreate() {
    User user = new User();
    user.setName("Ronald");
    user.setAge(14);
        
    Student student = new Student();
    student.setUser(user);
    student.setStudentCode("#2145");
    student.setUserName(user.getName());

    Session session = sessionFactory.openSession();
    Transaction t = session.beginTransaction();        
    session.save(student);
    t.commit();
}
On executing the logs indicate the below scripts fired:
3765 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        USER
        (AGE, USER_NAME) 
    values
        (?, ?)
...
3843 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        STUDENT
        (USER_NAME, STUDENT_CODE) 
    values
        (?, ?)
In the above code the two highligted lines play an important role. Calling the the setUserName() method causes the part of the primary key for the student record to be set. If this line is not executed then the save fails.
Caused by: java.sql.BatchUpdateException: Column 'USER_NAME' cannot be null
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java
:1269)
The setUser() call is also needed. If not set it in the above code it produces the an exception.
Caused by: java.sql.BatchUpdateException: Cannot add or update a child row: a fo
reign key constraint fails (`ec`.`student`, CONSTRAINT `STUDENT_FK1` FOREIGN KEY
 (`USER_NAME`) REFERENCES `user` (`USER_NAME`))
We have used the cascade property in Student to mange the associated user record. If we do not call student.setUser() in the above code, the user record will not exist in the database . Hence the foreign key constraint fails.
If we were working with an existing user record, then the setUser() call can be avoided. 
The setUser() does not make any difference to the student object state. Its is the setUserName() that creates the association.

Version 2

An alternative approach to achieve the above relation would be to combine the two fields. The new mapping file for Student is :
<?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_primary.merged">
    <class name="Student" table="STUDENT">
        <composite-id>
            <key-many-to-one name="user" column="USER_NAME" />
            <key-property name="studentCode" column="STUDENT_CODE" />
        </composite-id>
    </class>
</hibernate-mapping>
The Student class is now one property short.
public class Student implements Serializable {
    private String studentCode;
    private User user;
//setter-getters
} 
The user_name field now occurs only once and the setUser() call determines both Student record creation() and is used for student to user navigation.
The code to create the records now is:
static void testCreateV2() {
    User user = new User();
    user.setName("Ronald");
    user.setAge(14);

    Student student = new Student();
    student.setUser(user);
    student.setStudentCode("#2145");

    Session session = sessionFactory.openSession();
    Transaction t = session.beginTransaction();
    session.save(user);
    session.save(student);
    t.commit();
    session.close();
}
 As we do not have any cascade settings here we had to manually save the two records. 
However this approach faces limitations in HQL and Criteria queries and hence is to be used only in exceptional circumstances. The first technique should be given preference wherever possible.

1 comment:

  1. Thanks a bunch for sharing this with all people you actually recognize what you are talking about! Bookmarked. Kindly also discuss with my website. Progressive Property Network (PPN)

    ReplyDelete