首页

解决"..SQLGrammarException: could not execute query..java.sql.SQLException: ORA-01795: 列表中的最大表达式数为 1000.."异常

标签:ORA-01795,oracle,数据库异常,SQLGrammarException,SQLException,DBError     发布时间:2017-05-14   

一、异常描述

通过Hibernate的org.hibernate.Query的查询语句Hql语句“from PublishVo where  id  in(9320,9901,9001...)”报出“java.sql.SQLException: ORA-01795: 列表中的最大表达式数为 1000”异常造成“org.hibernate.exception.SQLGrammarException: could not execute query”问题无法查询结果,具体如下所示

org.hibernate.exception.SQLGrammarException: could not execute query@b@    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)@b@    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)@b@    at org.hibernate.loader.Loader.doList(Loader.java:2220)@b@    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2108)@b@    at org.hibernate.loader.Loader.list(Loader.java:2103)@b@    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)@b@    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:340)@b@    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)@b@    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1122)@b@    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)@b@    at com.xwood.pms.dao.impl.PublishDao.findByIds(PublishDao.java:210)@b@    at com.xwood.pms.dao.impl.PublishDao$$FastClassByCGLIB$$df7c652b.invoke(<generated>)@b@    at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)@b@    at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:696)@b@    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)@b@    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)@b@    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)@b@    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)@b@    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)@b@    at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:631)@b@    at com.xwood.pms.dao.impl.PublishDao$$EnhancerByCGLIB$$64c365e3.findByIds(<generated>)@b@    at com.xwood.pms.service.impl.PublishServer.rePublish(PublishServer.java:157)@b@    at com.xwood.pms.web.action.PublisherAction.rePublishCons(PublisherAction.java:283)@b@    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)@b@    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)@b@    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)@b@    at java.lang.reflect.Method.invoke(Method.java:597)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:404)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:267)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:229)@b@    at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:221)@b@    at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:86)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:150)@b@    at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:48)@b@    at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:86)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:123)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:184)@b@    at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:86)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:105)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:83)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:207)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:74)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:127)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at org.apache.struts2.interceptor.ProfilingActivationInterceptor.intercept(ProfilingActivationInterceptor.java:107)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:206)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:115)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:143)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:121)@b@    at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:86)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:170)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:123)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:176)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)@b@    at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)@b@    at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)@b@    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)@b@    at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:50)@b@    at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:504)@b@    at org.apache.struts2.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:422)@b@    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)@b@    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)@b@    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)@b@    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)@b@    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)@b@    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)@b@    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)@b@    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)@b@    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)@b@    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)@b@    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)@b@    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)@b@    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1720)@b@    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1679)@b@    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)@b@    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)@b@    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)@b@    at java.lang.Thread.run(Thread.java:662)@b@Caused by: java.sql.SQLException: ORA-01795: 列表中的最大表达式数为 1000@b@@b@    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)@b@    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)@b@    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)@b@    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)@b@    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)@b@    at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)@b@    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)@b@    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)@b@    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)@b@    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)@b@    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:187)@b@    at org.hibernate.loader.Loader.getResultSet(Loader.java:1791)@b@    at org.hibernate.loader.Loader.doQuery(Loader.java:674)@b@    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)@b@    at org.hibernate.loader.Loader.doList(Loader.java:2217)@b@    ... 138 more

二、解决方法

通过“..列表中的最大表达式数为 1000..”和“from PublishVo where  id  in(9320,9901,9001...)”判断因为id在的枚举值只超过最大长度限制1000造成的异常,解决方法如下

方法一:分批查询确保每次提交in(..)表达式最大数量小于1000

方法二:在in(..)改用嵌套查询语句,如下“.. in ( select  pid  from  table2 )”

<<推荐下载>>
  • (1) 阿里巴巴Java开发手册8种不同版本
  • (2) Web前端开发视频教程
  • (3) 30+明星讲师PPT课件分享一线大厂架构实战经验
  • (4) java开发_架构篇_视频资源分享_v2208
  • (5) java开发_高级篇_视频资源分享_v2208
  • (6) java开发_进阶篇(中级)_视频资源分享_v2208
  • (7) java开发_入门篇_视频资源分享_v2208
  • (8) 微信小程序开发视频1+167源码+实战demo等下载
  • (9) easy-shopping电子商务java源码(附脚本和安装文档说明)下载
  • (10) java常用的72份知名实用的电子书下载
  • (11) java开发性能优化资料整理大全(8份电子文档+3份实战优化)下载
  • (12) 9个常用的算法设计资料和100以上视频课件内容下载
  • (13) vue开发必备常用手册16件下载
  • (14) 21种不同技术集群方案(es、flink、redis、nginx、zk、lvs、kafka、mysql、k8s等)参考资料下载
  • (15) 20种技术代码规范(js/java/dba/阿里/华为/oracle/mysql等)参考资料下载
  • (16) 微服务五套资料(0-1,架构设计,springcloud,nacos等)下载
  • (17) 架构师(28知识图谱+3套简历模板+6套架构实战文档等)完整资料整理下载
  • (18) 大数据18套实战基础知识+8套简历模板下载
  • (19) 并发编程全套(7套+阿里巴巴+亿级实战等)实战资料下载
  • (20) Kafka九套学习整理知识点全套(面试+笔记+代码api+命令+容备等)资料下载
  • (21) java全套9个不同方向类型的面试题(基础+核心+大厂+架构师+近万套题库等)下载
  • (22) JAVA开发常用API帮助文档大全(超52种以上技术资料,高手必备)下载
  • (23) springcloud超详细139件全套学习实战资料( 视频课件+源码demo+文档资料等)下载
  • 更多推荐>>
  • <<热门文章>>