Clever Geek Handbook

Sleep Criteria with Count Condition

I need an equivalent

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 the criteria for hibernation. Thanks to this question , I know how to get the grouped result as a list 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 don't know how to add the HAVING clause. I think it is something like Restrictions.eqProperty

, but how can I refer to the invoice?

Is there a way to refer to the retrieved elements of a tuple in a query?

+3


source to share


2 answers


The Hibernate Criteria API does not support suggestions HAVING

. Since it is deprecated anyway in newer versions of Hibernate, I suggest you switch to the JPA API, or use HQL / JPQL or more advanced wrappers like Querydsl JPA .



+3


source


You can use sqlRestriction as a workaround, for example:

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


Here's an 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"));
+1


source






More articles:


All Articles