Hibernate Criteria Restrictions: comparing two properties as simple expression

Question

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

`Restrictions.eqProperty("x", "y")` : x and y should be property names. Your question is not clear. What is your objective? — Nandkumar Tekale, Jan 07 '13 at 12:11
Wow. I did put effort into making the question clear. Not sure what I missed. Instead of 'where x = y', I want 'where x >= (y - 1)'. — Stewart, Jan 07 '13 at 12:29

score 6 · Accepted Answer · answered Jan 07 '13 at 14:11

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

Agreed. I was frustrated by this too, so ended up opening a pull request for it: https://github.com/hibernate/hibernate-orm/pull/533/files. Something like this works in the meantime, though :). Thanks for posting your approach. — jonc, May 14 '13 at 20:18

score 2 · Answer 2 · answered Jan 12 '18 at 10:58

You can implement your own Criterion implementation for this:

public static class YourCriterion implements Criterion{

    @Override
    public TypedValue[] getTypedValues(Criteria arg0, CriteriaQuery arg1) throws HibernateException {
        return new TypedValue[0];
    }

    @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 query = new StringBuilder();
         query.append(x);
         query.append(">= (");
         query.append(y);
         query.append(" - 1)");
         return query.toString();
    }

}
 criteria.add(new YourCriterion());

For reference: PropertyExpression

Hibernate Criteria Restrictions: comparing two properties as simple expression

2 Answers2