Search code examples
javamysqlhibernatecriteria

Java Hibernate sqlGroupProjection mysql Syntax error

i need to translate this SQL expressioninto Hibernate using Criteria 

select id,field1,field2,count(*) from my_table 
group by field1,field2 having count(*)>1;

here is my code so far.

    final Session session = ......
    ProjectionList projectionList = (ProjectionList)emptyProjection();
    projectionList.add(Projections.property("id"),"id"); 
    projectionList.add(Projections.groupProperty("codeI"));
    projectionList.add(Projections.groupProperty("codeII"));
    projectionList.add(Projections.sqlGroupProjection("count(*)","having count(*)>1",new String[]{},new Type[]{StandardBasicTypes.INTEGER}));     
    final Criteria criteria = session.createCriteria(MyClass.class).setProjection(projectionList).setResultTransformer(transformer(MyClass.class));
    return new ArrayList<MyClass>(criteria.list());

my problem is

the criteria is generating this SQL.

group by
    this_.FIELD1,
    this_.FIELD2,

having
    count(*)>1

as you can see the second group by is followed by a COMMA this_.FIELD2, and mySQL is throwing SyntaxError

UPDATE

here is my hibernate.cfg

<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property> 
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

and we are using mysql 5.5.29 and HIBERNATE 4.2.3

i hope somebody give a tip.

best regards..

Solution

  • Hibernate does not support a having clause in the Criteria API. Your code is tantalizing close to the SQL you want, but what you've actually specified is just another group by condition, hence the comma. Here is a feature request from forever ago: https://hibernate.atlassian.net/browse/HHH-1043.

    If you must use a criteria, then you can use a subquery which will end up being slow. HQL does support the clause so you could do it that way.

    More discussion and examples: Hibernate Criteria API - HAVING clause work arounds.