Saturday, August 18, 2007

Hibernate: Computed property value

Many times you want to have computed value from db as a property value of an entity. For simple computation, where you additional information from DB is not needed, you use java code which is fine. But if you need information from DB, it means additional queries. Hibernate provides a powerful feature known as as derived properties

For example consider there is are table called Order and OrderItem; an Order has multiple OrderItem(s) and OrderItem has a field called price. Now if you want orderPrice property in Order Entity such that
orderPrice = sum of all OrderItem.price*OrderItem.qty where OrderItem.orderId=<my order id>.
This can be done simply by defining a property in hbm file like this .

< property name="orderPrice"
formula="( SELECT SUM (OI.QTY*OI.PRICE) FROM ORDER_ITEM
OI OI.ORDER_ID = ID )"/>
These properties are by definition read-only, and the value is computed at load time. You declare the computation as a SQL expression, this translates to a SELECT clause subquery in the SQL query that loads an instance. Make sure you use SQL names (TABLE and column names) and not the Java names (Object and property names). also don't forget to put the formula inside "(" and ")".

2 comments:

Ashish said...

But doesn't this thing undermine the very essence of hibernate(database independence).

This way we are actually injecting a loophole because the query could use functions that are database specific and thus when you deploy the same on a different database , it might not work.

As is the cliche "with great power, comes great responsibility" although this is a nice to have feature but it should be used with prudence.

Anurag Jain said...

Yeh I agree with you, this is a useful feature but should be used judiciously. Use plain standard SQL in here, and you are safe.