Hibernate Criteria sqlRestriction alias variable limitation

The API Documenataion of Hibernate's Criteria API's sqlRestriction method, defines as follows:


/**
* Apply a constraint expressed in SQL, with the given JDBC
* parameter. Any occurrences of {alias} will be replaced
* by the table alias. */


Make note of the {alias} placeholder in the above comments.


The catch is the only {alias} this method understands is the alias name of the root table on which the Criteria call is constructed. If more than one table is joined in the Criteria query either using the createCriteria or the createAlias method, the alias of these 'joined' tables are not understood by the Criteria API.


In order to get over this problem in our project, we had to look at the SQL generated by the Criteria object, figure out the alias name generated by hibernate and then code that in the SQL that is passed in as an argument to the sqlRestriction method.


For example in the below snippet,

Criteria c = session.createCriteria(BaseTable.class, "base");
c.createAlias(SecondTable.class, "second", CriteriaSpecification.LEFT_JOIN);


sqlRestriction method can interpret only "base" as the alias.

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.