Search This Blog

Saturday, 10 September 2011

Using Derived properties

Hibernate provides us with the ability to include properties that do not map to any column in the database. These are fields that are calculated based on formula applied on other columns/other tables/ SQL functions/ custom procedures etc. The function is evaluated every time the entity is retrieved from the database. Consider the case where we have a person whose date of birth is saved in the database. Then the age of this person can be represented in the domain object as a derived property
The java class for the same is :
package com.other.domain.entity;

import java.util.Date;

public class Person {
    private Integer id;
    private String name;
    private Date dob;
    private Integer age;// this value is a derived property 
    //setter- getter for above columns
The hbm mapping for the above class is :
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    <class name="com.other.domain.entity.Person" table="PERSON">

        <id name="id" type="integer" access="property" unsaved-value="null">
            <column name="ID" />
            <generator class="native" />

        <property name="name" type="string" column="NAME" />
        <property name="dob" type="timestamp" column="DATE_OF_BIRTH" />
        <property name="age" type="integer" column="AGE"
            formula="( SELECT DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(p.date_of_birth, '%Y')
                     -(DATE_FORMAT(NOW(), '00-%m-%d') lt; 
                     DATE_FORMAT(p.date_of_birth, '00-%m-%d'))
                     from PERSON p where = ID)" /> 
As can be seen from the above code, an SQL script has been placed in the formula attribute for age property. The SQL executes when the record is retrieved to calculate and return the age based on the date_of_birth property which is a column in the PERSON table.
The code use to test it is as follows:
public class TestPerson {
    static SessionFactory sessionFactory;
    public static void main(String[] args) {
        Configuration configuration = new Configuration();
        configuration = configuration.configure();
        sessionFactory = configuration.buildSessionFactory();
//      createPerson();
    private static void createPerson() {
        Person person = new Person();
        person.setAge(12);//No Use :this won't be a part of the insert query 

        //static method to convert a dd-mm-yy string to date 
        Session session = sessionFactory.openSession();
        Transaction t = session.beginTransaction();;
    private static void loadPerson() {
        Session session = sessionFactory.openSession();
        Person person = (Person) session.load(Person.class, 1);
        System.out.println("Person : name " + person.getName() 
                + ", dob: " + person.getDob() + ", age : " + person.getAge());
The hibernate start up logs are as below. (Cleaned for readability.)
688  [main] INFO  org.hibernate.cfg.HbmBinder  - Mapping class: com.other.domain
.entity.Person -> PERSON
688  [main] DEBUG org.hibernate.cfg.HbmBinder  - Mapped property: id -> ID
703  [main] DEBUG org.hibernate.cfg.HbmBinder  - Mapped property: name -> NAME
703  [main] DEBUG org.hibernate.cfg.HbmBinder  - Mapped property: dob -> DATE_OF
703  [main] DEBUG org.hibernate.cfg.HbmBinder  - Mapped property: age -> ( SELEC
T DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(p.date_of_birth, '%Y') - (DATE_FORMAT(NO
W(), '00-%m-%d')     < DATE_FORMAT(p.date_of_birth, '00-%m-%d')) from PERSON p w
here = ID)

1797 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister  -  Ins
ert 0: insert into PERSON (NAME, DATE_OF_BIRTH, ID) values (?, ?, ?)
1797 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister  -  Upd
ate 0: update PERSON set NAME=?, DATE_OF_BIRTH=? where ID=?

1828 [main] DEBUG org.hibernate.loader.entity.EntityLoader  - Static select for 
entity com.other.domain.entity.Person: select person0_.ID as ID0_0_, person0_.NA
ME as NAME0_0_, person0_.DATE_OF_BIRTH as DATE3_0_0_, ( SELECT DATE_FORMAT(NOW()
,'%Y') - DATE_FORMAT(p.date_of_birth, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d')   
  < DATE_FORMAT(p.date_of_birth,'00-%m-%d')) from PERSON p where = person0
_.ID) as formula0_0_ from PERSON person0_ where person0_.ID=?
As can be seen from the start up logs the age property of the domain object is not mapped to any column, instead it is mapped to the SQL query. Also the insert and update statements do not include this column. On calling the create person method :
2203 [main] DEBUG org.hibernate.SQL  - 
        (NAME, DATE_OF_BIRTH) 
        (?, ?)
The query does not include the age field here, even though a value has been set for the property. (ID is auto-generated by db, hence not included) On executing the load call for the same object (created with id 1):
        person0_.ID as ID0_0_,
        person0_.NAME as NAME0_0_,
        person0_.DATE_OF_BIRTH as DATE3_0_0_,
        ( SELECT
            '%Y') - DATE_FORMAT(p.date_of_birth,
            '%Y') - (DATE_FORMAT(NOW(),
            '00-%m-%d')     < DATE_FORMAT(p.date_of_birth,
            PERSON p 
   = person0_.ID) as formula0_0_ 
        PERSON person0_ 
The SQL script is fired to return the value for the age field.
Person : name Manoj, dob: 1984-12-21 00:00:00.0, age : 26
The executed query could be on a different table too. Or it could involve simple addition of other columns of the same row to find a total value.
The given formula is however evaluated only when the entity is loaded. If a user changes the dob to some other value, the age field will continue to hold the value 26.
Personally I would prefer age to be not mapped to any formula. In fact I would prefer to have only a getAge() method in the class that would at all time calculate the age based on the date value. However if there exists certain immutable fields in the code that are only set when the object is created. (After all date of birth never changes in the real world :)), then it makes perfect sense to use the formula field. The setter method for age should be removed and hibernate should be allowed field access for this property. (Even dob being immutable could be treated in the same manner).

No comments:

Post a Comment