CODEWITHSUNDEEP
hibernate
Chaps
Chaps

Reputation: 131

How to sum two columns with Hibernate

I would like to do :

SELECT * FROM Table where (FieldA + FieldB > 90)

using hibernate.

I know I could get all the objects and then do the operation in Java, but there is a lot of data, and I would like to be able to do it in a simple request. Thank you!

Upvotes: 0

Views: 1883

Answers (4)

Chaps
Chaps

Reputation: 131

It seems that it can be done using HSQL:

select cat.weight + sum(kitten.weight) 
from Cat cat 
    join cat.kittens kitten
group by cat.id, cat.weight

BUT! if on of the number is null, result is null... when I would need null to be considered as 0, so this does not work

Upvotes: 0

Chaps
Chaps

Reputation: 131

Thank you all for your answers. Solution was indeed 

from Table as t where (t.FieldA + t.FieldB) > 90

Except that in order to consider null as 0 I had to use COALESCE (or ifnull)

from Table as t where (COALESCE(t.FieldA,0) + COALESCE(t.FieldB,0)) > 90

Now it's working :)

Upvotes: 0

hmrojas.p
hmrojas.p

Reputation: 582

I think that you can use HQL or Criteria.

If you use HQL, you have to do something like this:

from Table as t where (t.FieldA + t.FieldB) > 90

If you use Criteria, you should use Restrictions.sqlRestriction method:

Criteria c = session.createCriteria(Table.class);
c.add(Restrictions.sqlRestriction("(FieldA + FieldB) > 90"));

I hope this information helps you.

Good Luck.

Upvotes: 1

cнŝdk
cнŝdk

Reputation: 32145

You can simply use it as an SQL Query:

SELECT * FROM Table where (FieldA + FieldB) > 90;

And it works perfectly.

Upvotes: 1

Related Questions