Java和休眠 - HQL总别名

我试图写一个简单的HQL查询与聚合函数,并得到一个错误。 请帮忙。

我有3个实体订单,订单项和产品具有以下关系:

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

聚合功能不能与别名工作

分类:过冬 时间:2015-03-16 人气:6
本文关键词: 过冬
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 0.293 (s). 10 q(s)