Search This Blog

Saturday 24 September 2011

Composite Identifiers

In the previous discussions of identifiers the focus was on implementing surrogate keys as means to uniquely identify the records.
The wikipedia definition of surrogate key states that
"A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data."
These values are system generated, not manipulable by the user or application and contain no semantic meaning.
Most of our implementations involved either generating them through the database or via hibernate itself. The generated id did not give any hints to the nature of data it represented in the database.
The alternate (and once upon a time) popular approach to identifiers involved primary keys that was a part of the Entity's domain. For e.g., the PAN Card would be the primary key for a Person. Or it could be a combination of the columns that formed the primary key for the table. e.g. The User Id and Address Id would uniquely identify a Person (crooked use case I know :( , but you get the point right! ).
Such combination columns that represent a Row Identifier are mapped in Hibernate using the <composite-id> attribute.
Consider a database of all residents of the cities. Each Member would be identified uniquely by a combination of Person_Id and City_Id). The SQL scripts for such a table would be as below:
create table  CITY_MEMBER_INFORMATION (
    PERSON_ID INT(2) NOT NULL ,
    CITY_ID INT(11) NOT NULL ,
    MEMBER_ID VARCHAR(100) NOT NULL ,
    CODE VARCHAR(100) NOT NULL ,
    NO_OF_ACTIVE_YEARS INT(11) NOT NULL
);

alter table CITY_MEMBER_INFORMATION add constraint CITY_MEMBER_INFORMATION_PK  
primary key(PERSON_ID, CITY_ID);
alter table CITY_MEMBER_INFORMATION add constraint 
CITY_MEMBER_INFORMATION_FK1 foreign key(CITY_ID) references CITY(ID);
alter table CITY_MEMBER_INFORMATION add constraint 
CITY_MEMBER_INFORMATION_FK2 foreign key(PERSON_ID) references PERSON(ID); 
The Java model class is as follows:
public class CityMemberInformation {
    private CityMemberInformationId cityMemberInformationId;
    private String memberId;
    private String code;
    private Integer noOfActiveYears;

    public CityMemberInformationId getCityMemberInformationId() {
        return cityMemberInformationId;
    }
    
    public void setCityMemberInformationId(
            CityMemberInformationId cityMemberInformationId) {
        this.cityMemberInformationId = cityMemberInformationId;
    }

   //other setter getters
}
Rather than have all the fields that form the Primary key dumped in the model class, a common and very sensible practise is to group these fields together in a single class. In this case the class CityMemberInformationId acts as the Primary key. The class is as below
public class CityMemberInformationId implements Serializable {
//Implementing serializable is a must here
    private Integer cityId;
    private Integer personId;

   //other setter getters
}
The mapping file is 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.model">
    <class name="CityMemberInformation" table="CITY_MEMBER_INFORMATION">
        <composite-id name="cityMemberInformationId" 
              class="CityMemberInformationId">
            <key-property name="cityId" type="integer">
                <column name="CITY_ID" />
            </key-property>
            <key-property name="personId" type="integer">
                <column name="PERSON_ID" />
            </key-property>
        </composite-id>
        <property name="memberId" type="string">
            <column name="MEMBER_ID" length="100" not-null="true" />
        </property>
        <property name="code" type="string">
            <column name="CODE" />
        </property>
        <property name="noOfActiveYears" type="integer">
            <column name="NO_OF_ACTIVE_YEARS" />
        </property>
    </class>
</hibernate-mapping>
The primary key has been specified with the fields being specified as a part of the <key-property> element. The nested <column> element indicates the column name. The code to create a database entry is as follows:
Session session = sessionFactory.openSession();
        CityMemberInformationId id = new CityMemberInformationId();
        id.setCityId(1);
        id.setPersonId(1);        
        CityMemberInformation cityMemberInformation = new CityMemberInformation();
        cityMemberInformation.setCityMemberInformationId(id);
        cityMemberInformation.setCode("MN");
        cityMemberInformation.setMemberId("#4356");
        cityMemberInformation.setNoOfActiveYears(12);
        Transaction transaction = session.beginTransaction();
        session.save(cityMemberInformation);
        transaction.commit();
The logs are as below
2547 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister  - Stat
ic SQL for entity: com.model.CityMemberInformation
2547 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister  -  Ver
sion select: select CITY_ID, PERSON_ID from CITY_MEMBER_INFORMATION where CITY_I
D =? and PERSON_ID =?
2547 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister  -  Sna
pshot select: select citymember_.CITY_ID, citymember_.PERSON_ID, citymember_.MEM
BER_ID as MEMBER3_0_, citymember_.CODE as CODE0_, citymember_.NO_OF_ACTIVE_YEARS
 as NO5_0_ from CITY_MEMBER_INFORMATION citymember_ where citymember_.CITY_ID=? 
and citymember_.PERSON_ID=?
2563 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister  -  Ins
ert 0: insert into CITY_MEMBER_INFORMATION (MEMBER_ID, CODE, NO_OF_ACTIVE_YEARS,
 CITY_ID, PERSON_ID) values (?, ?, ?, ?, ?)
2563 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister  -  Upd
ate 0: update CITY_MEMBER_INFORMATION set MEMBER_ID=?, CODE=?, NO_OF_ACTIVE_YEAR
S=? where CITY_ID=? and PERSON_ID=?
...
2781 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener  - generated
 identifier: component[cityId,personId]{cityId=1, personId=1}, using strategy: o
rg.hibernate.id.Assigned
2781 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener  - saving [
com.model.CityMemberInformation#component[cityId,personId]{cityId=1, personId=1}]
2844 [main] DEBUG org.hibernate.transaction.JDBCTransaction  - commit
...
2859 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister  - Inse
rting entity: [com.model.CityMemberInformation#component[cityId,personId]{cityId
=1, personId=1}]
2859 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to open PreparedSt
atement (open PreparedStatements: 0, globally: 0)
2875 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        CITY_MEMBER_INFORMATION
        (MEMBER_ID, CODE, NO_OF_ACTIVE_YEARS, CITY_ID, PERSON_ID) 
    values
        (?, ?, ?, ?, ?)
2875 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - preparing statement
As can be seen from the logs, Hibernate used the assigned generator for primary keys. The record was created in the database based on passed values. All SQL scripts (CRUD) are based on the combination of columns that form the composite ids. Similarly the code to load an object would be
public static void testLoad() {
Session session = sessionFactory.openSession();
CityMemberInformationId id = new CityMemberInformationId();
id.setCityId(1);
id.setPersonId(1);
session.load(CityMemberInformation.class, id);
}
The Hibernate documentation advises that composite keys should be used for legacy systems and new applications must prefer the surrogate key approach wherever possible. The link gives reasons for the growing preference to surrogate keys. However if a use case arises or we need to work with an already existing database, then the <composite-id> is available for use.

No comments:

Post a Comment