Hibernate's Formula

In a reporting application we were building, we had a need to display different text descriptions based on the value in a column.

The resultset we were expecting to get back was so large that it was not an option to loop through the result set and set the description programmatically.

We were using Hibernate's Criteria API for generating the report queries. What we ended up doing is writing a FORMULA with a CASE statement in the hibernate mapping files that would return the description we wanted based on the code.

<property name="errorReason" type="string"
formula="CASE WHEN err_status = -1 THEN 'INACTIVE'
WHEN err_status = 1 THEN 'NOT FOUND'
WHEN err_status = 2 THEN 'DUPLICATE'
WHEN err_status = 3 THEN 'DUPLICATE (ACTIVE)'
WHEN err_status = 4 THEN 'DUPLICATE (INACTIVE)'
ELSE ' 'END" />

When we are creating the Criteria Query, if we just add the name of the property 'errorReason', this CASE statement will be tagged along behind the scenes with the query and we will get the description we need.In the above example based on the err_status different descriptions are returned.