java - JPQL (Hibernate 4.3.6) subselect query not working -
i have problem can perform 2 separate queries; first set of ids, , second use ids in "in" clause; when try combine 2 1 query subselect, not work.
this works:
public list<lead> findleadsbysupplierandstatus(list<integer> supplierids, statuscode status){ query query = entitymanager.createquery("select ss.id statesupplier ss ss.supplier.id in :supplierids"); query.setparameter("supplierids", supplierids); @suppresswarnings("unchecked") list<integer> result = (list<integer>)query.getresultlist(); query query2 = entitymanager.createquery("from lead l l.statesupplier.id in :ids , l.leadstatus.id= :statusid"); query2.setparameter("ids", result); query2.setparameter("statusid", status.getdbid()); @suppresswarnings("unchecked") list<lead> result2 = (list<lead>)query2.getresultlist(); return result2; } but not:
public list<lead> findleadsbysupplierandstatus(list<integer> supplierids, statuscode status){ query query = entitymanager.createquery("select l lead l l.statesupplier.id in (select ss.id statesupplier ss ss.supplier.id in :supplierids) , l.leadstatus.id= :statusid"); query.setparameter("supplierids", supplierids); query.setparameter("statusid", status.getdbid()); @suppresswarnings("unchecked") list<lead> result2 = (list<lead>)query.getresultlist(); return result2; } i following exception:
[20 mar 2015 17:26:28] [org.springframework.scheduling.quartz.schedulerfactorybean#0_worker-1] 72277 error (org.hibernate.hql.internal.ast.errorcounter:73) :: line 1:183: unexpected token: , antlr.noviablealtexception: unexpected token: , @ org.hibernate.hql.internal.antlr.hqlbaseparser.queryrule(hqlbaseparser.java:772) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.antlr.hqlbaseparser.union(hqlbaseparser.java:1021) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.antlr.hqlbaseparser.subquery(hqlbaseparser.java:3857) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.antlr.hqlbaseparser.compoundexpr(hqlbaseparser.java:3226) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.antlr.hqlbaseparser.inlist(hqlbaseparser.java:3020) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.antlr.hqlbaseparser.relationalexpression(hqlbaseparser.java:2866) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.antlr.hqlbaseparser.equalityexpression(hqlbaseparser.java:2591) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.antlr.hqlbaseparser.negatedexpression(hqlbaseparser.java:2555) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.antlr.hqlbaseparser.logicalandexpression(hqlbaseparser.java:2471) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.antlr.hqlbaseparser.logicalorexpression(hqlbaseparser.java:2436) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.antlr.hqlbaseparser.expression(hqlbaseparser.java:2146) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.antlr.hqlbaseparser.logicalexpression(hqlbaseparser.java:1922) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.antlr.hqlbaseparser.whereclause(hqlbaseparser.java:481) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.antlr.hqlbaseparser.queryrule(hqlbaseparser.java:737) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.antlr.hqlbaseparser.selectstatement(hqlbaseparser.java:323) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.antlr.hqlbaseparser.statement(hqlbaseparser.java:186) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.ast.querytranslatorimpl.parse(querytranslatorimpl.java:295) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.ast.querytranslatorimpl.docompile(querytranslatorimpl.java:203) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.hql.internal.ast.querytranslatorimpl.compile(querytranslatorimpl.java:158) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.engine.query.spi.hqlqueryplan.<init>(hqlqueryplan.java:126) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.engine.query.spi.hqlqueryplan.<init>(hqlqueryplan.java:88) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.engine.query.spi.queryplancache.gethqlqueryplan(queryplancache.java:167) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.internal.abstractsessionimpl.gethqlqueryplan(abstractsessionimpl.java:301) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.internal.sessionimpl.list(sessionimpl.java:1254) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.internal.queryimpl.list(queryimpl.java:103) [hibernate-core-4.3.6.final.jar:4.3.6.final] @ org.hibernate.jpa.internal.queryimpl.list(queryimpl.java:573) [hibernate-entitymanager-4.3.6.final.jar:4.3.6.final] @ org.hibernate.jpa.internal.queryimpl.getresultlist(queryimpl.java:449) [hibernate-entitymanager-4.3.6.final.jar:4.3.6.final] @ com.chooseenergy.core.repository.leadrepository.findleadsbysupplierandstatus(leadrepository.java:158) [leadrepository.class:?] @ com.chooseenergy.core.repository.leadrepository$$fastclassbyspringcglib$$6d88acfd.invoke(<generated>) [spring-core-4.0.6.release.jar:?] @ org.springframework.cglib.proxy.methodproxy.invoke(methodproxy.java:204) [spring-core-4.0.6.release.jar:4.0.6.release] @ org.springframework.aop.framework.cglibaopproxy$dynamicadvisedinterceptor.intercept(cglibaopproxy.java:640) [spring-aop-4.0.6.release.jar:4.0.6.release] @ com.chooseenergy.core.repository.leadrepository$$enhancerbyspringcglib$$74954aee.findleadsbysupplierandstatus(<generated>) [spring-core-4.0.6.release.jar:?] @ com.chooseenergy.hermes.quartz.supplierftpdispatchjob.dispatch(supplierftpdispatchjob.java:71) [supplierftpdispatchjob.class:?] @ com.chooseenergy.hermes.quartz.supplierftpdispatchjob$$fastclassbyspringcglib$$c96ba89a.invoke(<generated>) [spring-core-4.0.6.release.jar:?] @ org.springframework.cglib.proxy.methodproxy.invoke(methodproxy.java:204) [spring-core-4.0.6.release.jar:4.0.6.release] @ org.springframework.aop.framework.cglibaopproxy$cglibmethodinvocation.invokejoinpoint(cglibaopproxy.java:708) [spring-aop-4.0.6.release.jar:4.0.6.release] @ org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:157) [spring-aop-4.0.6.release.jar:4.0.6.release] @ org.springframework.transaction.interceptor.transactioninterceptor$1.proceedwithinvocation(transactioninterceptor.java:98) [spring-tx-4.0.6.release.jar:4.0.6.release] @ org.springframework.transaction.interceptor.transactionaspectsupport.invokewithintransaction(transactionaspectsupport.java:262) [spring-tx-4.0.6.release.jar:4.0.6.release] @ org.springframework.transaction.interceptor.transactioninterceptor.invoke(transactioninterceptor.java:95) [spring-tx-4.0.6.release.jar:4.0.6.release] @ org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:179) [spring-aop-4.0.6.release.jar:4.0.6.release] @ org.springframework.aop.framework.cglibaopproxy$dynamicadvisedinterceptor.intercept(cglibaopproxy.java:644) [spring-aop-4.0.6.release.jar:4.0.6.release] @ com.chooseenergy.hermes.quartz.supplierftpdispatchjob$$enhancerbyspringcglib$$383c0773.dispatch(<generated>) [spring-core-4.0.6.release.jar:?] @ sun.reflect.nativemethodaccessorimpl.invoke0(native method) ~[?:1.7.0_51] @ sun.reflect.nativemethodaccessorimpl.invoke(nativemethodaccessorimpl.java:57) ~[?:1.7.0_51] @ sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43) ~[?:1.7.0_51] @ java.lang.reflect.method.invoke(method.java:606) ~[?:1.7.0_51] @ org.springframework.util.methodinvoker.invoke(methodinvoker.java:269) [spring-core-4.0.6.release.jar:4.0.6.release] @ org.springframework.scheduling.quartz.methodinvokingjobdetailfactorybean$methodinvokingjob.executeinternal(methodinvokingjobdetailfactorybean.java:322) [spring-context-support-4.0.6.release.jar:4.0.6.release] @ org.springframework.scheduling.quartz.quartzjobbean.execute(quartzjobbean.java:112) [spring-context-support-4.0.6.release.jar:4.0.6.release] @ org.quartz.core.jobrunshell.run(jobrunshell.java:213) [quartz-2.1.6.jar:?] @ org.quartz.simpl.simplethreadpool$workerthread.run(simplethreadpool.java:557) [quartz-2.1.6.jar:?] is there way make single query work jpa classes (entitymanager, query, etc.)?
each lead has fk (@manytoone) statesupplier (statesupplier) , fk (@manytoone) leadstatus (leadstatus), , each statesupplier has fk (@manytoone) supplier (supplier). think model simple enough not require source code entities, problem combine queries, work separately.
why don't use join (explicit or implicit) in query instead of subquery
select l lead l inner join l.statesupplier ss ss.supplier.id in :suppllierids , l.leadstatus.id =:statusid
Comments
Post a Comment