HQL和Hibernate条件中的HQL和GROUP BY子句

Question

执行以下Hibernate HQL查询时出现问题：

String hql = "from ProcessInstanceHistory where processInstance.processInstanceId=122 group by newStatusId having max(processModifiedDate)";
Query query = session.createQuery(hql);

List qResult = query.list();
ProcessInstanceHistory processInstanceHistory = (ProcessInstanceHistory) qResult.get(0);

当我在没有HAVING子句的情况下运行上面的脚本时，它给了我以下错误：

org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.loader.Loader.doList(Loader.java:2223)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
    at org.hibernate.loader.Loader.list(Loader.java:2099)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
    at Test.main(Test.java:140)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 'tne_travel_7_4_1.processins0_.PI_HISTORY_ID' isn't in GROUP BY
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.Util.getInstance(Util.java:381)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1912)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
    at org.hibernate.loader.Loader.doQuery(Loader.java:674)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
    at org.hibernate.loader.Loader.doList(Loader.java:2220)
    ... 8 more

当我使用HAVING子句时，它给出：

org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: max near line 1, column 117 [from tne.nexstep.dao.ProcessInstanceHistory where processInstance.processInstanceId=122 group by newStatusId having max(processModifiedDate)]
    at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
    at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
    at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
    at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:235)
    at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
    at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
    at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
    at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
    at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
    at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
    at Test.main(Test.java:138)

我也尝试过使用Hibernate标准，但它无法工作。

他们还有其他方法来完成同样的任务吗？

提前谢谢

Answer 1

您的having子句不完整。它必须是一种条件，例如having max(processModifiedDate) > 1。此外，您还需要使用聚合函数进行项目，如果您需要对某些列进行项目，则它们需要在组中。当前，ProcessInstanceHistory类中的所有列(字段)都在投影中。例如，

String hql = "Select newStatusId, max(processModifiedDate) from ProcessInstanceHistory where processInstance.processInstanceId=122 group by newStatusId having max(processModifiedDate) > 1";

有关有子句的更多信息可以在having.asp上找到。