Hibernate version:3.1

I have a table "Routing" that has a column "state" and a column "order_num", among others. The same state can be listed multiple times, and I am trying to return a collection of objects that have the highest order_num for each state. Here is the code i have so far. It works in TOAD, but Hibernate doesn't like it:




I'm new to Hibernate and HQL, and would appreciate any help. Thank you!

Hi ln260, can u provide your error stacktrace and info about your environment?

Error stack trace:

2006-12-07 13:23:27,214 ERROR [com.att.ivr.drm.business.ExceptionResolver] - <org.springframework.orm.hibernate3.HibernateQueryException: could not resolve property: state of: com.att.ivr.drm.model.easl.v31.RoutingRulesSbrPriority [select rr from com.att.ivr.drm.model.easl.v31.RoutingRulesSbrPriority rr where (rr.state,rr.orderNum) in (select rrsp.state, max(rrsp.orderNum) from com.att.ivr.drm.model.easl.v31.RoutingRulesSbrPriority rrsp group by rrsp.state)]; nested exception is org.hibernate.QueryException: could not resolve property: state of: com.att.ivr.drm.model.easl.v31.RoutingRulesSbrPriority [select rr from com.att.ivr.drm.model.easl.v31.RoutingRulesSbrPriority rr where (rr.state,rr.orderNum) in (select rrsp.state, max(rrsp.orderNum) from com.att.ivr.drm.model.easl.v31.RoutingRulesSbrPriority rrsp group by rrsp.state)]>
org.springframework.orm.hibernate3.HibernateQueryException: could not resolve property: state of: com.att.ivr.drm.model.easl.v31.RoutingRulesSbrPriority [select rr from com.att.ivr.drm.model.easl.v31.RoutingRulesSbrPriority rr where (rr.state,rr.orderNum) in (select rrsp.state, max(rrsp.orderNum) from com.att.ivr.drm.model.easl.v31.RoutingRulesSbrPriority rrsp group by rrsp.state)]; nested exception is org.hibernate.QueryException: could not resolve property: state of: com.att.ivr.drm.model.easl.v31.RoutingRulesSbrPriority [select rr from com.att.ivr.drm.model.easl.v31.RoutingRulesSbrPriority rr where (rr.state,rr.orderNum) in (select rrsp.state, max(rrsp.orderNum) from com.att.ivr.drm.model.easl.v31.RoutingRulesSbrPriority rrsp group by rrsp.state)]
org.hibernate.QueryException: could not resolve property: state of: com.att.ivr.drm.model.easl.v31.RoutingRulesSbrPriority [select rr from com.att.ivr.drm.model.easl.v31.RoutingRulesSbrPriority rr where (rr.state,rr.orderNum) in (select rrsp.state, max(rrsp.orderNum) from com.att.ivr.drm.model.easl.v31.RoutingRulesSbrPriority rrsp group by rrsp.state)]
at org.hibernate.persister.entity.AbstractPropertyMapping.throwPropertyException(AbstractPropertyMapping.java:43)
at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:37)
at org.hibernate.persister.entity.AbstractEntityPersister.toType(AbstractEntityPersister.java:1265)
at org.hibernate.hql.ast.tree.FromElementType.getPropertyType(FromElementType.java:279)
at org.hibernate.hql.ast.tree.FromElement.getPropertyType(FromElement.java:372)
at org.hibernate.hql.ast.tree.DotNode.getDataType(DotNode.java:539)
at org.hibernate.hql.ast.tree.DotNode.prepareLhs(DotNode.java:221)
at org.hibernate.hql.ast.tree.DotNode.resolve(DotNode.java:172)
at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:94)
at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:90)
at org.hibernate.hql.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:725)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1215)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1237)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4032)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:3832)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1758)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:776)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:577)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:218)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
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:1583)
at org.springframework.orm.hibernate3.HibernateTemplate$31.doInHibernate(HibernateTemplate.java:846)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:366)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:844)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:836)
at com.att.ivr.drm.dao.impl.EaslDaoImpl.findHighestOrderNum(EaslDaoImpl.java:370)
at com.att.ivr.drm.business.impl.EaslFindServiceImpl.findHighestOrderNum(EaslFindServiceImpl.java:268)
at com.att.ivr.drm.ui.datamod.routingrules.RoutingRulesSbrPriorityCreateController.onSubmit(RoutingRulesSbrPriorityCreateController.java:55)
at org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:258)
at org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:249)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:44)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:723)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:663)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:394)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:358)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:264)
at com.att.ivr.drm.ui.session.SessionContainerFilter.doFilter(SessionContainerFilter.java:67)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107)
at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:217)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:191)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148)
at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:90)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
Hibernate: select release0_.ID as ID0_, release0_.NAME as NAME0_, release0_.TYPE as TYPE0_, release0_.STATUS as STATUS0_, release0_.DESCRIPTION as DESCRIPT5_0_, release0_.DEPLOYMENT_DATE as DEPLOYMENT6_0_, release0_.DEPLOYMENT_TYPE as DEPLOYMENT7_0_, release0_.LAST_MODIFIED_ON as LAST8_0_, release0_.RELEASE_MANAGER_UUID as RELEASE9_0_, release0_.LAST_MODIFIED_UUID as LAST10_0_, release0_.DATA_APPLY_DOC_ID as DATA11_0_ from RELEASE release0_ where (release0_.STATUS in (? , ?)) and release0_.DEPLOYMENT_DATE<? order by release0_.DEPLOYMENT_DATE asc
Hibernate: select release0_.ID as ID0_, release0_.NAME as NAME0_, release0_.TYPE as TYPE0_, release0_.STATUS as STATUS0_, release0_.DESCRIPTION as DESCRIPT5_0_, release0_.DEPLOYMENT_DATE as DEPLOYMENT6_0_, release0_.DEPLOYMENT_TYPE as DEPLOYMENT7_0_, release0_.LAST_MODIFIED_ON as LAST8_0_, release0_.RELEASE_MANAGER_UUID as RELEASE9_0_, release0_.LAST_MODIFIED_UUID as LAST10_0_, release0_.DATA_APPLY_DOC_ID as DATA11_0_ from RELEASE release0_ where (release0_.STATUS in (? , ?)) and release0_.DEPLOYMENT_DATE<? order by release0_.DEPLOYMENT_DATE asc
Hibernate: select servergrou0_.ID as ID10_, servergrou0_.SERVER_GROUP_ID as SERVER2_10_, servergrou0_.PERFORMED_ON as PERFORMED3_10_, servergrou0_.DETAIL as DETAIL10_ from SERVER_GROUP_ACTIVITY servergrou0_ where servergrou0_.PERFORMED_ON<? order by servergrou0_.PERFORMED_ON
Hibernate: select serveracti0_.ID as ID12_, serveracti0_.SERVER_ID as SERVER2_12_, serveracti0_.SERVER_GROUP_ACTIVITY_ID as SERVER3_12_, serveracti0_.PERFORMED_ON as PERFORMED4_12_, serveracti0_.DETAIL as DETAIL12_ from SERVER_ACTIVITY serveracti0_ where serveracti0_.PERFORMED_ON<? order by serveracti0_.PERFORMED_ON

Environment:

Hibernate 3.1
Spring IDE 1.3
JDK 1.4
Eclipse 3.2

please let me know if you need any more info. Thank you!

Ln

I am not sure why your selecting rr, its the alias to the table, do you mean select * ?, lastly I am not sure you can specify two conditions with a "in" function...

I could be wrong, however I have not seen it

No, it works in another query that I have "select o from OfficeHours as o where"...



It works in SQL, but its possible that it doesn't work in Hibernate.


Maybe I'm going about it the wrong way. Can any one think of a way to write a Hibernate query that returns a collection of objects that have the highest ordernum for each state? For example if this is my table, I want to return the objects that have a * by them

Although I didn't find any state field in HQL generated, maybe you are using 2 fields with same name without differenciate it by its containing POJO.

I'm confused... but could you revise it?

Thanks

hi again, sorry for the previous reply... my browser didn't refresh...
Maybe I'm ignoring some You know, but I believe the solution is in group by - having clauses.

I hope this helps