SQLRestriction on Table Alias in Hibernate Criteria

Tagged with: Case Studies, Software Development, Technical Tips & FAQs

Hibernate documentation on SQLRestriction on Criteria:

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 only {alias} sqlRestriction understands is the alias name of the table on which the criteria call is constructed. If there are more criteria added, sqlRestriction does not understand the alias of any other joined tables. To get around this problem in a development project, we looked at the SQL generated by hibernate to find the table alias and used this in the SQL passed to sqlRestriction.

Below are the criteria used 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 we used 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>

Need help with a custom development project? Contact Unidev today.