Hibernate SQLRestriction using alias of the join Table.

Problem:

We Faced an issue which using SQLRestricion in the subcriteria and we use an table aliasCriteria c1 = dao.getSession().createCriteria(Parent.class,"p")

      .createCriteria("p.child", "c", Criteria.LEFT_JOIN)
      .add(Restrictions.sqlRestriction("c.address ~* 'Address%'"));

print(dao, c1);

The SQL that gets generated is as follows and failed

select this_.id as id3_0_, this_.address as address3_0_, this_.phone as phone3_0_, this_.firstName as firstName3_0_
from
   Parent this_
   left outer join Child this_1_
on
   this_.id=this_1_.id
where
   c.address ilike 'Address%'

Expected sql that uses the alias in my where clause.

select this_.id as id3_0_, this_.address as address3_0_, this_.phone as phone3_0_, this_.firstName as firstName3_0_
from
   Parent this_
   left outer join Child c
on
   this_.id=c.id
where
   c.address ilike 'Address%'

Root Cause:

We identified that the problem was with SQLRestriction implementation in hibernate, which does not care about Alias names. So we took an approach, to not use SQLRestriction, instead extended SimpleExpression to accomodate Regex 

Solution:

Instead of using SQLRestriction, we resolved it by extending SimpleExpression, which solved the problem public class CustomSimpleExpression extends SimpleExpression{     public CustomSimpleExpression(String propertyName, Object value, String op){        super(propertyName, value, op);     } } public class CustomRestrictions{      public static SimpleExpression regex(String propertyName, Object value){          return new CustomSimpleExpression(propertyName, value, " ~* ");       } } Criteria c1 = dao.getSession().createCriteria(Parent.class,"p")       .createCriteria("p.child", "c", Criteria.LEFT_JOIN)       .add(CustomRestrictions.regex(c.address,"Address%")); print(dao, c1);