Java and Hibernate - HQL aggregate alias

I am trying to write a simple HQL query with an aggregate function and getting an error. Please help.

I have 3 entities Order, OrderItem and Product with the following relationships:

Order --> OrderItem (One to many bi-directional)
OrderItem --> Product (Many to one Uni-directional)

At a high level, a Customer can place an Order that has multiple Order line items with each line item referring to a product and its quantity. Product is like a master table representing the Product catalog.

I am trying to find the total quantity ordered for each product across all Order Items. 开发者_如何学PythonI am using the following query:

select oi.product.name, sum(oi.quantity) as s1 from OrderItem oi
group by oi.product.name order by s1 desc

I am aliasing the sum() function with s1 so that I can sort by the same. But this alias s1 seems to create a problem. Here is the error in the hibernate debug log:

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

---

If you look at the SQL that Hibernate has generated, you'll see it has ignored your alias and used its own.

Instead, simply group by the same aggregate you're using in the select

select oi.product.name, sum(oi.quantity) from OrderItem oi group by oi.product.name order by sum(oi.quantity) desc

---

Aggregation functions doesn't work with alias