hibernatecriteriarestrictions

Hibernate Criteria Restrictions: comparing two properties as simple expression


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.)


Solution

  • 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)"));