10

I've written a query using Hibernate Criteria API to grab a summation of a particular value, now I need to be able to restrict the result to rows where that sum is greater or equal to a particular value.

Normally I would use a HAVING clause in my SQL to do this, but the Criteria API doesn't seem to support that at this moment.

In raw SQL this is what I need it to do:

SELECT user_pk, sum(amount) as amountSum
FROM transaction
GROUP BY user_pk
HAVING amountSum >=50;

One work-around that I thought of is to use a subquery in the FROM clause that grabs this summation value and use an outer query to restrict it using a WHERE clause.

So, in raw SQL it will look something like:

SELECT user_pk, amountSum
FROM (SELECT user_pk, sum(amount) as amountSum
      FROM transaction
      GROUP BY user_pk)
WHERE amountSum > 50;

Can anyone point me in the right direction as to how I could write this using Criteria API, or any other suggestions/work-arounds I can use to solve the HAVING issue?

This is the Criteria API code I have for the above example

DetachedCriteria criteria = DetachedCriteria.forClass(Transaction.class,"transaction");
criteria.setProjection(criteria.setProjection(Projections.projectionList().add(
        Projections.groupProperty("user.userPK").as("user_pk")).add(
            Projections.sum("transaction.amount").as("amountSum")));

Thanks!

user57701
  • 213
  • 2
  • 4
  • 7

3 Answers3

8

I don't know of a way for Hibernate/NHibernate to use a subquery in the FROM clause but you can use them in the WHERE clause. Apologies for any Java/Hibernate code mistakes, I am more familiar with C#/NHibernate.


DetachedCriteria subQuery = DetachedCriteria.forClass(Transaction.class);
subQuery.setProjection(Projections.sum("amount"));
subQuery.add(Expression.eqProperty("userPk", "tOuter.userPk"));

DetachedCriteria outerQuery = DetachedCriteria.forClass(Transaction.class, "tOuter");
outerQuery.setProjection(Projections.projectionList()
    .Add(Projections.sum("amount").as("sumAmount"))
    .Add(Projections.groupProperty("userPk").as("user_pk"));
outerQuery.add(Subqueries.le(50, subQuery));

This code should result in SQL similar to:


SELECT tOuter.userPk as user_pk, sum(tOuter.amount) as sumAmount
FROM transaction tOuter
WHERE 50 <= (SELECT sum(amount) FROM transaction WHERE userPk = tOuter.userPk)
GROUP BY tOuter.userPk

The disadvantage of this approach is that it calculates each of the sums twice, this might have a detrimental effect on performance depending on the amount of data involved - in which case you will want to use an HQL query which does support the HAVING clause.

Aidan Boyle
  • 1,431
  • 1
  • 8
  • 7
2

HHH-1700 was marked as a duplicate of HHH-1043, and therefore won't be fixed. You'll find my solution and workaround, as well as other peoples', on HHH-1043.

Community
  • 1
  • 1
  • this was 7 years ago and still not patched inside hibernate, I need to apply the patch for version 4.3.6 or newer but the code had been changed so much that I'm not clear how to do it. Anyone has this patch updated? – Daniel Ardison Feb 10 '17 at 14:48
1

http://opensource.atlassian.com/projects/hibernate/browse/HHH-1700