具有Haveing和GROUP BY子句的HQL和Hibernate条件

问题描述

我在执行以下Hibernate HQL查询时遇到问题:

I have Problem when I executing the following Hibernate HQL Query:

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子句的脚本上运行时，它给我以下错误:

It gives me Following error when I run above script without HAVING clause:

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子句时，它给出:

And when I use HAVING clause it gives:

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)

我也尝试过休眠标准，但是它不起作用.

I had also try with Hibernate Criteria but it won't work.

他们还有其他方法可以完成相同的任务吗?

Is their any other way to acheive the same task ?

预先感谢

推荐答案

您的having子句不完整.它必须是一个条件，例如having max(processModifiedDate) > 1.另外，您需要使用聚合函数进行投影，并且如果需要在某些列上进行投影，则它们必须在分组依据中.当前，ProcessInstanceHistory类中的所有列(字段)都在投影中.例如，

Your having clause is incomplete. It needs to be a condition e.g. having max(processModifiedDate) > 1. Also, you need to project using aggregate functions and if you need to project on certain columns, they need to be in the group by. Currently, all columns(fields) in the ProcessInstanceHistory class are in the projection.e.g.,

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

有关 HAVING 子句的更多信息，请访问 http://www.w3schools.com/sql/sql_having.asp .

More information on the HAVING clause can be found at http://www.w3schools.com/sql/sql_having.asp.

这篇关于具有Haveing和GROUP BY子句的HQL和Hibernate条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！