Posts Tagged ‘hibernate join on fields other than primary key’

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


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


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

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