Java criteria restrictions sum of columns lower than value

Question

Hi I've a question about converting a simple SQL statement into a java hibernate restriction.

SQL:

SELECT a + b FROM col WHERE a + b < 10

When I try to convert this to a criteria I get:

Criteria criteria = createCriteria();
criteria.createAlias("col","col").add(Restrictions.lt("a+b",10));

But it seems like the string "a+b" is not accepted. How do I have to write my restriction?

I have my own database. But when I execute the script above I get this exeption:Exception in thread "main" org.hibernate.QueryException: could not resolve property: a+b of: col — karen_22, Aug 16 '12 at 11:49
I think Joe was asking what flavour of database server you are using. MySQL? postgresql? Oracle? MS SQLServer? — theon, Aug 16 '12 at 12:41

score 0 · Answer 1 · edited Jan 05 '16 at 16:08

It's likely because Hibernate is trying to resolve a+b as your entity's property, and you get

org.hibernate.QueryException: could not resolve property: a+b

There are few possible solutions:

1st: sql restriction:

//Hibernate 4.x
criteria.add(Restrictions.sqlRestriction("col_a+col_b < ?", Integer.valueOf(10), org.hibernate.type.StandardBasicTypes.INTEGER);

// Hibernate 3.x
criteria.add(Restrictions.sqlRestriction("col_a+col_b < ?", Integer.valueOf(10),       org.hibernate.Hibernate.INTEGER);

There is one issue that you must use column names instead of property names.

2nd: Formula:

Add to your entity a virtual column using @Formula annotation:

@org.hibernate.annotations.Formula("a + b")
private int aSumB;

and use it as a standard property:

criteria.add(Restrictions.lt("aSumB", Integer.valueOf(10));

3rd Custom Criteria:

If you are using it a lot consider to create custom criteria by implementing org.hibernate.criterion.Criterion

Java criteria restrictions sum of columns lower than value

1 Answers1

Linked