Monday, April 15, 2013

Derived properties in Hibernate

It's not a question you get asked on the bus on your way to work. But on that fateful day when you do, your answer is likely to be followed by a scratch of the head. How would you represent derived properties in Hibernate.Derived properties are calculated at fetch time, usually read-only, well they are read only period, but how they are represented in Hibernate, not everyone knows, understandably so too. 

I vaguely recall learning about derived properties back in one of the SQL sessions, but nothing much afterwards really.So why this write up, well a question about derived properties popped up in one of the interviews I had recently. I mess around with Hibernate a lot, I am not a guru but I know my way around it, but I can't say I have come across a scenario that mandated the usage of derived properties so my answer was, let's just say, not satisfactory.

Well, enough about that. Let's see how it's done then. You got two options; option number one ; use the formula attribute of the property element in your hibernate mapping file (for those who prefer xml configuration over annotations), options number two; use the @Formula annotation. 

I' ll just jump straight to code and show you how it's done.

First the xml approach,
<property name="fullName" formula="CONCAT(fName,' ',lName)" />
  <property name="idMul" formula="id * 12.0" />
 
And now how to do it with annotations,
import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.annotations.Formula;

@Entity
@Table(name = "person")
public class Person {
 @Id
 @GeneratedValue(strategy = GenerationType.AUTO)
 private Long id;
 private String fName;
 private String lName;
 private Date dob;
 
 @Formula("id * 12.0")
 private float idMul;
 
 @Formula("CONCAT(fName,' ',lName)")
 private String fullName;

And you can now write your queries based around your derived properties in the following fashion 
List<Person> person2 = (List<Person>) session.createQuery("from Person p where p.idMul > 5 and p.id=2").list();

And if you inspect your console you will see the following output,
Hibernate: 
    select
        person0_.id as id0_,
        person0_.dob as dob0_,
        person0_.fName as fName0_,
        person0_.lName as lName0_,
        CONCAT(person0_.fName,
        ' ',
        person0_.lName) as formula0_,
        person0_.id * 12.0 as formula1_ 
    from
        person person0_ 
    where
        person0_.id * 12.0>5 
        and person0_.id=2

Now before you close this window and down vote this post, I have to tell you I did this seemingly odd multiplication operation with the 'id' field to demonstrate what can be achieved within a formula annotation. 

Although its not clear from the code above, you need to understand that whatever you put inside  this annotation or the  formula attribute, does not undergo any form of translation, if you are wondering what the heck I am on about, It means Hibernate regards this as plain SQL. Questions ; Yes you can put a whole plain SQL query there also yes this violates Hibernate's database independence since you can put vendor specific SQL there, and yes you have to use actual table column names instead of entity field names. But imagine the possibilities, used correctly this could be a very powerful feature also and this is from my personal experience, you hardly switch database vendors, I am not trying to justify using vendor specific SQL but you catch my drift.

Now everywhere you use this derived property in your HQL, Hibernate replaces it with what you have defined. Now imagine  using a whole plain SQL query there to do some serious calculations (possibly time intensive) and using the derive property in your HQL query, Hibernate would blindly replace the derived property with your SQL. Say for example you want to execute the following query.
List<Person> person2 = (List<Person>) session.createQuery("from Person p where (p.idMul > 5 or p.fullName like '%chi%' ) and p.id=2").list();

And if you take a moment to inspect your console you will see the following query being generated.
Hibernate: 
    select
        person0_.id as id0_,
        person0_.dob as dob0_,
        person0_.fName as fName0_,
        person0_.lName as lName0_,
        CONCAT(person0_.fName,
        ' ',
        person0_.lName) as formula0_,
        person0_.id * 12.0 as formula1_ 
    from
        person person0_ 
    where
        (
            person0_.id * 12.0>5 
            or CONCAT(person0_.fName,' ',person0_.lName) like '%chi%'
        ) 
        and person0_.id=2

So you have to be careful about using derived properties in Hibernate.

Another point to note is that @Formula annotation is not a JSR annotation, so if you don't want to mix hibernate annotation with persistence annotations and want a cleaner and an optimized solution for using derived properties in Hibernate then you have no other choice but to do what this guy has done here. It's a pretty good post covering most aspects you are likely to be concerned about when using derived properties.



4 comments:

  1. Interesting post. Did not know of its existenc until now.

    ReplyDelete
  2. HI ,

    Your Post is So Nice and Very good Explanation.
    I have a Doubt....


    Can i place dynamic values in that "@Formula" query? If Yes How?

    i need to calculate using values coming from client and table values,

    Kindly reply.


    Thanks & Regards
    raju

    ReplyDelete
  3. Hello Raju,

    I believe you can use positional or named parameters.

    ReplyDelete
  4. I am using hibernate template to load the data from DB. I get "invalid column name" error referring to CONCAT keyword. my concat expression look like CONCAT(a,CONCAT(','b)).

    ReplyDelete