mysql - How Do I use Having and group By clause in hibernate? -

mysql - How Do I use Having and group By clause in hibernate? -

i have sql query this.

select version_id viewtest ( item_id='i001' , value ='v001') or ( item_id='i002' , value ='v002') or ( item_id= 'i003'and value ='v003') grouping version_id having count(1) = 3

my viewtest entity this.

@column(name = "version_id") private string versionid; @column(name = "item_id") private string itemid; @column(name = "value") private string value;

here crieteria query written me.

criteriabuilder criteriabuilder = getentitymanager().getcriteriabuilder(); criteriaquery<string> query = criteriabuilder.createquery(string.class); root<viewtest> testroot = query.from(viewtest.class); list<predicate> predicates = new arraylist<predicate>(); predicate p1 = criteriabuilder.equal(testroot.get("itemid"), "i001"); predicate p2 = criteriabuilder.equal(testroot.get("itemid"), "i002"); predicate p3 = criteriabuilder.equal(testroot.get("itemid"), "i003"); predicate v1 = criteriabuilder.equal(testroot.get("value"), "v001"); predicate v2 = criteriabuilder.equal(testroot.get("value"), "v002"); predicate v3 = criteriabuilder.equal(testroot.get("value"), "v003"); predicates.add(criteriabuilder.and(p1 , v1)); predicates.add(criteriabuilder.and(p2 , v2)); predicates.add(criteriabuilder.and(p3 , v3));

now dont know how add together grouping , having clause criteria.

can please help me out?

found improve way it.

just added below code.

query.groupby(testroot.<string> get("versionid")); query.having(criteriabuilder.in(criteriabuilder.count(testroot.get("versionid"))).value( querycount));

this did trick.

have looked @ projections? hibernate developer guide: projections

example:

list results = session.createcriteria(cat.class) .setprojection( projections.projectionlist() .add( projections.rowcount() ) .add( projections.avg("weight") ) .add( projections.max("weight") ) .add( projections.groupproperty("color") ) ) .list();

mysql sql hibernate criteria