[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Aggregate function in where clause (Criteria API)

[shouab]

I am using Criteria API. I want to use the Aggregate Functions like 
Avg(),count() etc in the where clause i know its available through Projections but projections used as the slect part of the query i want to use these functions in where clause like 
criteria.add(Restriction.eq(Projections.count("emp"),5));

Is there any way to do that.

if the problem is not clear lets say i have two tables Employee and Address and the relation between is one to many Employee have multiple Addresses so i want to retrieve the employee who have 5 or more Addresses. 

Code is like that.

Criteria criteria = session.createCriteria(Employee.class,"emp");
  | 		criteria.setFetchMode("addresses",FetchMode.EAGER);
  | ProjectionList projList = Projections.projectionList();
  | projList.add(Projections.count("emp.addresses"),"NoOfOpp");
  | criteria.setProjection(projList);
  | criteria.add((Restrictions.ge("NoOfOpp",6)));
  | List list=criteria.list();
  | 
  | 

When i execute this it gives me NoOfOpp invalid identifier is any other way round to accomplish the task.

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3969195#3969195

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3969195