Blue icons showing parts of a system working together.

SQLRestriction on Table Alias in Hibernate Criteria

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 project, we looked at the SQL generated by hibernate to find the table alias and used this in the SQL passed to sqlRestriction.

Below is 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.