I have been having some troubles with some of my queries that are using the Criteria object since I changed the fetchType (from EAGER to LAZY) of the model objects in the project I am part with.
the following code works perfectly fine when the model objects are still eagerly fetched:
Code:
private void addActivityUserCriteria(Criteria criteria, List<Long> userIdList){
criteria.createAlias("workAssignment", "workAssignment", Criteria.LEFT_JOIN);
criteria.createAlias("activityOwner", "activityOwner", Criteria.LEFT_JOIN);
criteria.createAlias("workAssignment.handledBy", "handledBy", Criteria.LEFT_JOIN);
criteria.createAlias("workAssignment.handledBy2", "handledBy2", Criteria.LEFT_JOIN);
criteria.createAlias("workAssignment.handledBy3", "handledBy3", Criteria.LEFT_JOIN);
criteria.add(
Restrictions.or(
Restrictions.and(
Restrictions.and(
Restrictions.isNull("activityOwner"),
Restrictions.isNotNull("workAssignment")),
Restrictions.or(
Restrictions.or(
Restrictions.in("handledBy.userID", userIdList),
Restrictions.in("handledBy2.userID", userIdList)),
Restrictions.in("handledBy3.userID", userIdList))),
Restrictions.and(
Restrictions.isNotNull("activityOwner"),
Restrictions.in("activityOwner.userID", userIdList))));
}
but now I am having some problems with the query being generated by Hibernate:
Code:
select
this_.activity_id as activity1_1_0_,
this_.work_assignment_id as work23_1_0_,
this_.user_id as user20_1_0_,
this_.bru as bru1_0_,
this_.activity_date as activity3_1_0_,
this_.start_time as start4_1_0_,
this_.end_time as end5_1_0_,
this_.activity_type as activity6_1_0_,
this_.description as descript7_1_0_,
this_.referring_activity as referring22_1_0_,
this_.remarks as remarks1_0_,
this_.status as status1_0_,
this_.activity_locked as activity10_1_0_,
this_.date_submitted as date11_1_0_,
this_.activity_type_code as activity12_1_0_,
this_.status_code as status13_1_0_,
this_.activity_venue as activity14_1_0_,
this_.creator as creator1_0_,
this_.date_updated as date15_1_0_,
this_.updated_by as updated16_1_0_,
this_.document_id as document24_1_0_,
this_.activity_status_description as activity17_1_0_,
this_.has_notes as has18_1_0_,
this_.is_decision_pending as is19_1_0_
from
tbl_activities this_
where
this_.activity_date between ? and ?
and (((this_.user_id is null and this_.work_assignment_id is not null)
and ((handledby1_.user_id in (?) or handledby2x2_.user_id in (?)) or handledby3x3_.user_id in (?))) or (this_.user_id is not null and this_.user_id in (?)))
order by
this_.activity_date asc,
this_.start_time asc
with the following oracle error:
Code:
2009-08-12 16:43:41,078 ERROR [org.hibernate.util.JDBCExceptionReporter] - ORA-00904: "HANDLEDBY3X3_"."USER_ID": invalid identifier
the error stack trace is:
Code:
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
Caused by: 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:2216)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at com.sz.lcms.dao.hibernate.ActivityDaoImpl$1.doInHibernate(ActivityDaoImpl.java:136)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:372)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:338)
at com.sz.lcms.dao.hibernate.ActivityDaoImpl.getActivities(ActivityDaoImpl.java:79)
at com.sz.lcms.service.AgendaManager.getActivityDetails(AgendaManager.java:273)
at com.sz.lcms.service.AgendaManager$$FastClassByCGLIB$$e0a9b0d1.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:695)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:144)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:166)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:630)
at com.sz.lcms.service.AgendaManager$$EnhancerByCGLIB$$6f1a230.getActivityDetails(<generated>)
at com.sz.lcms.actions.AgendaAction.list(AgendaAction.java:183)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:269)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:170)
at org.springframework.web.struts.DelegatingActionProxy.execute(DelegatingActionProxy.java:110)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:228)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:75)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.ajaxanywhere.AAFilter.doFilter(AAFilter.java:46)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: ORA-00904: "HANDLEDBY3X3_"."USER_ID": invalid identifier
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)
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:2213)
... 51 more
The database the project is using is Oracle10g and we are using Hibernate 3.2.6. The models are using Annotations.
I have been reading some JIRA entries about some related issues (others about using Projections) and tried some of the solutions provided for some of related issues I have found on this forum but I can't still seem to make the query work.
Thanks.
Login
FAQ
Search