I have a Hibernate Criteria, for which the following works:
criteria.add(Restrictions.eqProperty("x", "tbl.y"));
but I now want to modify the criteria to check a range of possible values ('x' and 'y' are both integers), for example, this doesn't work:
criteria.add(Restrictions.geProperty("x", "tbl.y - 1"));
I've tried using an sqlRestriction, such as:
criteria.add(Restrictions.sqlRestriction("{alias}.x >= (tbl1_.y - 1)"));
but how to know the actual alias name ('tbl1_') ?
Am I going to have to use a subselect to make this work? (Using HQL is not a practical option right now.)
This is how I solved it:
criteria.add(new PropertyExpression(null, null, null) {
@Override
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
String x = criteriaQuery.getColumnsUsingProjection(criteria, "x")[0];
String y = criteriaQuery.getColumnsUsingProjection(criteria, "tbl.y")[0];
StringBuilder result = new StringBuilder();
result.append(x);
result.append(">= (");
result.append(y);
result.append(" - 1)");
return result.toString();
}
});
shame there's no way, using Hibernate Criteria API, to do this (alias a joined table):
criteria.add(Restrictions.sqlRestriction("{alias}.x >= ({tbl}.y - 1)"));