Java和Hibernate - HQL集合别名
问题描述：
我想用集合函数编写一个简单的HQL查询并获取错误。请帮忙。Java和Hibernate - HQL集合别名
我有3个实体秩序，OrderItem的与产品有如下关系：
Order --> OrderItem (One to many bi-directional)
OrderItem --> Product (Many to one Uni-directional)
在较高水平，客户可以将具有与每个行项目的多个订单行项目指的是产品的订购和它的数量。产品就像代表产品目录的主表一样。
我正在尝试查找所有订购商品中为每件商品订购的总数量。我使用下面的查询：
select oi.product.name, sum(oi.quantity) as s1 from OrderItem oi
group by oi.product.name order by s1 desc
我走样的s1的总和（）函数，这样我可以用相同的排序。但是这个别名s1似乎造成了一个问题。下面是在休眠调试日志中的错误：
15:39:56,350 DEBUG SQL:111 - select product1_.PRODUCT_NAME as col_0_0_, sum(orderitem0_.ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_.PRODUCT_ID=product1_.PRODUCT_ID group by product1_.PRODUCT_NAME order by s1 desc
Hibernate: select product1_.PRODUCT_NAME as col_0_0_, sum(orderitem0_.ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_.PRODUCT_ID=product1_.PRODUCT_ID group by product1_.PRODUCT_NAME order by s1 desc
15:39:56,356 DEBUG JDBCExceptionReporter:92 - could not execute query [select product1_.PRODUCT_NAME as col_0_0_, sum(orderitem0_.ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_.PRODUCT_ID=product1_.PRODUCT_ID group by product1_.PRODUCT_NAME order by s1 desc]
java.sql.SQLException: Column not found: S1 in statement [select product1_.PRODUCT_NAME as col_0_0_, sum(orderitem0_.ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_.PRODUCT_ID=product1_.PRODUCT_ID group by product1_.PRODUCT_NAME order by s1 desc]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1577)
at org.hibernate.loader.Loader.doQuery(Loader.java:696)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2232)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
at org.hibernate.loader.Loader.list(Loader.java:2124)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at com.bpp.cm.ProductRepository.getTopFiveProducts(ProductRepository.java:136)
at com.bpp.cm.Main.testGetTop5Products(Main.java:315)
at com.bpp.cm.Main.testProduct(Main.java:233)
at com.bpp.cm.Main.main(Main.java:40)
15:39:56,377 WARN JDBCExceptionReporter:100 - SQL Error: -28, SQLState: S0022
15:39:56,378 ERROR JDBCExceptionReporter:101 - Column not found: S1 in statement [select product1_.PRODUCT_NAME as col_0_0_, sum(orderitem0_.ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_.PRODUCT_ID=product1_.PRODUCT_ID group by product1_.PRODUCT_NAME order by s1 desc]
15:39:56,379 DEBUG JDBCTransaction:186 - rollback
15:39:56,379 DEBUG JDBCTransaction:197 - rolled back JDBC Connection
15:39:56,379 DEBUG ConnectionManager:427 - aggressively releasing JDBC connection
15:39:56,380 DEBUG ConnectionManager:464 - releasing JDBC connection [ (open PreparedStatements: 1, globally: 1) (open ResultSets: 0, globally: 0)]
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2235)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
at org.hibernate.loader.Loader.list(Loader.java:2124)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at com.bpp.cm.ProductRepository.getTopFiveProducts(ProductRepository.java:136)
at com.bpp.cm.Main.testGetTop5Products(Main.java:315)
at com.bpp.cm.Main.testProduct(Main.java:233)
at com.bpp.cm.Main.main(Main.java:40)
Caused by: java.sql.SQLException: Column not found: S1 in statement [select product1_.PRODUCT_NAME as col_0_0_, sum(orderitem0_.ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_.PRODUCT_ID=product1_.PRODUCT_ID group by product1_.PRODUCT_NAME order by s1 desc]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1577)
at org.hibernate.loader.Loader.doQuery(Loader.java:696)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2232)
... 11 more
答
如果你看一下，Hibernate对已经生成的SQL，你会看到它忽略了你的别名，并使用它自己的。
相反，只需你使用在选择
select oi.product.name, sum(oi.quantity) from OrderItem oi group by oi.product.name order by sum(oi.quantity) desc
答
聚合函数相同的聚合团不与别名
工作