Posts Tagged ‘Hiberate sqlRestriction on alias’

Restrictions on table alias in Hibernate Criteria sqlRestriction

Hibernate documentation on SQLRestriction on Crietria

*******************
sqlRestriction public static Criterion sqlRestriction(String sql)
Apply a constraint expressed in SQL.
Any occurrences of {alias} will be replaced by the table alias.
Parameters: sql
Returns: Criterion
*******************

The catch here is that the only {alias} sqlRestriction understands is the alias name of the table on which the criteria call is constructed. If there is more criterias added  , sqlRestriction does not understand the alias of any other tables joined.  In order to get around this problem in our project, i had to look at the SQL generated by hibernate to find the table alias and use this in the SQL passed to sqlRestriction.

Given below  is the criteria i had to use to make the sqlRetriction work to meet our requirements.

Criteria c = Session.createCriteria(ParentTable.class, “parent”);
c.createCriteria(“parent.movements”, “movements”)
         .add(Restrictions.sqlRestriction(“this_.movement_initial={alias}.movement_init and this_.movement_number={alias}.movement_nbr “));
 
 
Hibernate generated an alias of this_ on the root criteria table ‘ParentTable’ and i had to use this alias on the sqlRestriction.

Given below is our table structure

ParentTable

movement_id int(9) (unique)
movement_initial varchar2(5)
movement_number int(3)

ChildTable

child_id int(9) (unique)
movement_id int(9) (non unique)
movement_init varchar2(5)
movement_nbr int(3)
ParentTable.hbm.xml
<hibernate-mapping>
    <class name=”com.dj.ParentTable” table=”ParentTable” schema=”XXX”>
        <id name=”movementid” type=”java.lang.Long”>
            <column name=”movement_id” precision=”9″ scale=”0″ />
            <generator>
                <param name=”sequence”>XX_Q1</param>
            </generator>
        </id>

 <property name=”movementInitial” type=”java.lang.String”>
             <column name=”movement_initial” length=”5″ />
 </property>
 <property name=”movementNumber” type=”java.lang.Long”>
             <column name=”movement_number” precision=”6″ scale=”0″ />
        </property>
 <set name=”movements” inverse=”true” cascade=”save-update”>
            <key>
                <column name=”movement_id” precision=”9″ scale=”0″ not-null=”true” />
            </key>
            <one-to-many class=”com.dj.ChildTable”/>
</set>
</class>
</hibernate-mapping>