Edit Question - KnowledgeHub

Title Body

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!

Tags (comma-separated) Cancel