SQL statement conversion to hibernate criteria (having count clause)

Question

I have an SQL query that I would like to convert into a criteria (Hibernate 3.3.2) to use it into my persistancy layer. That's the query :

select last_name
  from member
  where
    member.end_date > SYSDATE
  group by member.last_name
  having count(member.last_name) >1;

I already try a lot a combinations, but there is no result that would be good for me ... This is my code :

Criteria criteria = getSession(false).createCriteria(Member.ENTITY_NAME);
criteria.setProjection(Projections.projectionList()
            .add(Projections.groupProperty("lastName"))
            .add(Projections.count("lastName").as("cptLastName"))
            );
criteria.add(Restrictions.ge("endDate", now))
            .add(Restrictions.le("startDate", now))
            //.add(Restrictions.gt("cptLastName", new Integer(1)))
            .addOrder(Order.asc("lastName"))
;

With writing it with the comment line, I have an objects list that contains the name and the number of times that the name appears in the database. But when i would like to decomment it, this the error : java.sql.SQLException: ORA-00904: "Y1_": invalid identifier

To develop this code, I am inspired by different post below:

I think I'm not far of the solution but is there anybody to help me? If you have questions, don't hesitate to ask me it. And if you found another workaround for me to paste to this query, don't hesitate to propose it...

score 1 · Accepted Answer · answered Sep 04 '12 at 12:29

Hi if i am right you cant use the having clause count directly in hibernate criteria as of now, Please find below the modified query. If it doesnt works, please post the entire stack trace

DetachedCriteria innerQuery = DetachedCriteria.forClass(Member.ENTITY_NAME, "inner");
  innerQuery.setProjection(Projections.rowCount());
  innerQuery.add(Restrictions.eqProperty("lastName", "outer.lastName"));

  Criteria c = s.createCriteria(Member.ENTITY_NAME, "outer");
  c.setProjection(Projections.property("lastName"));
  c.add(Restrictions.gt("endDate", now))   
  c.add(Subqueries.eq(new Integer(1), innerQuery));

Thanks for your answer... The query works but it returns a lot of record ... and it should return only 8. Furthermore, it works for a very long time ... — user1645271, Sep 04 '12 at 14:47

score 0 · Answer 2 · answered Sep 05 '12 at 09:20

I adapted my code with your proposition, and this is what I have :

DetachedCriteria innerQuery = DetachedCriteria.forEntityName(Member.ENTITY_NAME,"inner")
    .setProjection(Projections.rowCount())
    .add(Restrictions.eqProperty("lastName", "outer.lastName"));

Criteria criteria = getSession(false).createCriteria(Member.ENTITY_NAME, "outer")
    .setProjection(Projections.property("lastName"))
    .add(Restrictions.g("endDate", now))
    .add(Subqueries.gt(new Integer(1), innerQuery));

So, like I said in the above comment, it works but it takes a long time... for giving me the wrong result (it should give me eight records).

I activated the option that display the hibernate query (hibernate.show_sql=true). And if I format it in sql query format, this is that it able to execute :

select this.last_name
from member this 
where this.END_DATE>=SYSDATE 
and 1 > 
    (select count(*) 
    from member inner  
    where inner.last_name = this.last_name);

So, what it be possible to do with this query to give me result what I would like ?

score 0 · Answer 3 · answered Sep 12 '12 at 13:02

In fact, I succeed partially with your answer... because I've display only a part of my big request. It is like this :

select member.last_name, member.first_name, 
from member
where  
  member.end_date > SYSDATE
and
  member.last_name in 
  (select member.last_name
  from member 
  where
    member.end_date > SYSDATE
  group by member.last_name
  having count(member.last_name) >1)
order by member.last_name;

And it display all member that have an homonym (same name) in the database. So, for your information, this is my final criteria :

Calendar now = Calendar.getInstance();
DetachedCriteria innerQuery = DetachedCriteria.forEntityName(Member.ENTITY_NAME,"inner")
        .setProjection(Projections.rowCount())
        .add(Restrictions.eqProperty("lastName", "outer.lastName"))
        .add(Restrictions.ge("endDate", now));

Criteria criteria = getSession(false).createCriteria(Member.ENTITY_NAME, "outer")
        .setProjection(Projections.property("lastName"))                
        .add(Restrictions.ge("endDate", now))
        .add(Subqueries.lt(new Integer(1), innerQuery));

To explain a little, with the projection, I count the number of rows and I compare the last name (of the select) with the last name of the primary query. Also, the second line of the primary criteria is always needed. If you use a subquery (DetachedCriteria), you must use a Projection. But, finally, I use an HQL query to run my code, because the criteria is more heavy to work ...

So, Thanks for you answer...

SQL statement conversion to hibernate criteria (having count clause)

3 Answers3