Having clause in criteria

anirudhvyas010 · **Joined:** Thu Dec 13, 2007 2:32 pm **Posts:** 26

I have a query like this :

Code:

      /*
       *select  this_.department,
       *        sum(this_.salary)
       *    from Employee this_
       *       group by department
       *     having sum(salary) > 100
       * 
       */

how do i execute this using criteria : ( i have this in mind , but it uses where clause instead of having )

Code:

Criteria critera = session.createCriteria(Employee.class).setProjection(Projections.projectionList()
                                             .add(Projections.sum("salary").as("sum_salary"))
                                             .add(Projections.groupProperty("department")));
      List<Object[]> emmployeeListWithHaving = critera.add(Restrictions.eq("sum_salary", 4000000)).list();

Sql generated :

Code:

    select
        sum(this_.SALARY) as y0_,
        this_.DEPARTMENT as y1_ 
    from
        EMPLOYEE this_ 
    where
        y0_=? 
    group by
        this_.DEPARTMENT

Any thoughts / suggestions ?

anirudhvyas010 · **Joined:** Thu Dec 13, 2007 2:32 pm **Posts:** 26

I was going through JP with Hibernate and i found out that Having clause isnt available with Criteria API; was this added ? ( I checked JIRA issue thingie and it seems to be in "fixed" status ... if so, can someone point me to "new" syntax? )