3

I need the equivalent of

SELECT m.id, count(i.id)
FROM master m LEFT JOIN item i on m.id = i.master_id
GROUP BY m.id, m.size
HAVING m.size <> count(i.id);

in Hibernate Criteria. Thanks to this question, I know how to get the grouped result as a list of Object[]:

ProjectionList projList = Projections.projectionList();
projList.add(Projections.groupProperty("master"));
projList.add(Projections.count("id"));

session
.createCriteria(Item.class)
.join("master")
.setProjection(projList)
.addRestriction(???) // <- my HAVING clause
.list();

I have no clue how to add the HAVING clause. I guess, it's something like Restrictions.eqProperty, but how can I refer to the count?

Is there a way how to refer to the resulting tuple elements in the query?

maaartinus
  • 44,714
  • 32
  • 161
  • 320
  • how can m.size be in your having clause without being in the group by clause ? – jpprade Jul 25 '17 at 09:00
  • @jpprade It's MySQL. :D MySQL is rather sloppy in this respect and I forgot to switch the strict mode on. Another thing is that with `id` being the PK, mentioning the `size` in GROUP BY is redundant (logically, not according to SQL). – maaartinus Jul 25 '17 at 13:56

2 Answers2

3

Hibernate Criteria API does not support HAVING clauses. Since it is deprecated anyway in newer Hibernate versions, I suggest you move to JPA Criteria API, or use HQL/JPQL or more advanced wrappers like Querydsl JPA.

Dragan Bozanovic
  • 23,102
  • 5
  • 43
  • 110
1

You can use a sqlRestriction how workaround, something like:

Restrictions.sqlRestriction("1=1 group by this_.id, this_.size HAVING this_.size <> count(i_.id));

Here is a example:

ct.setProjection(Projections.sqlProjection(
                    "cobr_.client_id as clientID"
                    , new String[] {"clientID" }
                    , new Type[] { new LongType()}));
ct.add(Restrictions.sqlRestriction("1=1 group by cobr_.vlr_total,clientID having (sum(this_.vlr_net)-cobr_.vlr_total) < -20"));
Renato Barros
  • 177
  • 3
  • 14