title: SQL statement conversion to hibernate criteria (having count clause)
body: <p>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 :</p>
  
  <pre><code>select last_name
    from member
    where
      member.end_date &gt; SYSDATE
    group by member.last_name
    having count(member.last_name) &gt;1;
  </code></pre>
  
  <p>I already try a lot a combinations, but there is no result that would be good for me ... This is my code :</p>
  
  <pre><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"))
  ;
  </code></pre>
  
  <p>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</p>
  
  <p>To develop this code, I am inspired by different post below:</p>
  
  <ul>
  <li><p><a href="https://stackoverflow.com/questions/3919445/hibernate-criteria-projecting-count-with-group-by-clause">Hibernate Criteria: Projecting Count with group by clause</a></p></li>
  <li><p><a href="https://stackoverflow.com/questions/8310516/hibernate-count-rows-with-some-criterias">Hibernate count rows with some criterias</a></p></li>
  <li><p><a href="https://stackoverflow.com/questions/1372317/how-do-we-count-rows-using-hibernate">How do we count rows using Hibernate?</a></p></li>
  <li><p><a href="https://stackoverflow.com/questions/8605708/how-to-use-having-count-with-hibernate">HOW to use HAVING COUNT(*) with hibernate</a></p></li>
  <li><p><a href="https://stackoverflow.com/questions/467172/hibernate-criteria-api-having-clause-work-arounds">Hibernate Criteria API - HAVING clause work arounds</a></p></li>
  </ul>
  
  <p>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...</p>