ORA-01795: maximum number of expressions in a list is 1000问题解决

最新推荐文章于 2024-07-25 17:08:21 发布
最新推荐文章于 2024-07-25 17:08:21 发布
阅读量959 收藏
点赞数 1
分类专栏： 异常问题解决 文章标签： list java spring oracle mybatis
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41814311/article/details/130418998
版权

项目场景：

线上项目查询接口报错

问题描述

最近线上接口调用报错，通过查询error.log日志查询发现，SQL执行报错了。
报错信息显示 条件参数字段超过 1000条；
报错信息如下：

### Cause: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
	at com.sun.proxy.$Proxy132.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:173)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:78)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
	at com.sun.proxy.$Proxy236.getPeriodData(Unknown Source)
	at sun.reflect.GeneratedMethodAccessor606.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:282)
	at sun.reflect.GeneratedMethodAccessor124.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:634)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:93)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
	at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at sun.reflect.GeneratedMethodAccessor122.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
	at com.sun.proxy.$Proxy322.query(Unknown Source)
	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:69)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:165)
	at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:92)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	at sun.reflect.GeneratedMethodAccessor197.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
	... 66 more
Caused by: Error : 1795, Position : 0, Sql = select t.code
              from tour_info t
            where t.create_time >= :1 
              and t.status in ('1','2')
              and t.code IN
             (xxxxxx,.......)

原因分析：

原因是因为这接口 调用第三方接口，将返回数据作为参数到数据库查询。
第三方返回数据条数超过1000+
造成SQL in 操作参数长度超出。
oracle in中参数最多可包含1000个，超出则报错。

解决方案：

解决方法有两种：
第一种 --通过修改SQL语句：

select 
    t.code
from   tour_info t
where t.create_time >= :1 
       and t.status in ('1','2')
<if test="ids!= null and ids.size > 0">
 and t.code IN
    <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
       <if test="(index % 999) == 998">  NULL ) OR t.code IN (</if>#{item}
    </foreach>
</if>

第二种 --代码控制：

List<String> param = new ArrayList<String>();
//使用hutool工具类
List<List<String>> lists =ListUtil.split(param ,998);
List<String> results = new ArrayList<String>();
for(List<String> list : lists  ){
  results .addAll(query(list));
}
return results ;
清风-云烟
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mybatis脚本sql in 1000限制
疏影横斜水清浅
06-15 1163
今天测试人员发现了一个bug，看了一下日志，大概如下： Cause: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000 ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list
ORA-01795的原因及解决办法
02-24 805
系统报出一SQL异常，内容如下： java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000 找出抛异常时执行的SQL语句，貌不惊人啊，很平常一SQL语句，内容类似：SELECT * FROM DUAL T WHERE T.DUMMY IN ('1', '2', '3', ...)...
ORA-01795：列表中的最大表达式数为1000
技术分享
11-09 2062
ORA-01795：列表中的最大表达式数为1000
Oracle：ORA-01795: maximum number of expressions in a list is 1000
赵大土的博客
07-01 3947
ORA-01795: maximum number of expressions in a list is 1000SQLMyBatis优化思考 最近测试老师反映一个页面加载异常，查看日志发现返回Cause: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000，意思是说Oracle的in查询中数据不能超过1000，解决方案也不复杂，就是拆分in查询语句 SQL 原句sele
ORA-01795: maximum number of expressions in a list is 1000解决方案
辰兮要努力
06-16 2107
ORA-01795: maximum number of expressions in a list is 1000解决方案！
错误：maximum number of expressions in a list is 1000
weixin_30379531的博客
09-23 8160
某一日发现这么如下这么一个错误 -->maximum number of expressions in a list is 1000 原因：因为SQL语句中用到了IN字句，而IN中的元素个数超过了1000个而导致。 方法： 　　1.在程序中将一个IN改成多个IN； 　　2.把IN List 改成一个SELECT语句，把IN List中的元素放到一个Nested Tab...
ORA-01795: maximum number of expressions in a list is 1000
weixin_45606111的博客
06-13 3089
ORA-01795: maximum number of expressions in a list is 1000完美解决
ORA-01795 Oracle in中超过1000个值处理方法汇总
Hehuyi_In的博客
06-03 8352
最近又又又遇到了开发同事问Oracle 中in超过1000个值遇到ORA-01795: maximum number of expressions in a list is 1000怎么处理，之前也陆陆续续查过一些方法，汇总整理一把。当然，其中的一些方法只是保证它不报错，性能可能堪忧，尽量少用。
调整参数后引起，ORA-00838: Specified value of MEMORY_TARGET is too small
03-30
调整参数后引起，ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3 2768M 的解决方案
连接Oracle数据库时报ORA-12541:TNS:无监听程序的图文解决教程
09-10
主要介绍了连接Oracle数据库时报ORA-12541:TNS:无监听程序的图文解决教程,非常不错，具有参考借鉴价值，需要的朋友可以参考下
Oracle dbca时报：ORA-12547: TNS:lost contact错误的解决
12-16
最近在工作中遇到了一个问题，错误是Oracle dbca时报错：ORA-12547: TNS:lost contact，通过查找相关的资料终于找到了解决的方法，下面分享给大家，话不多说了，来一起看看详细的介绍吧。 环境： OS:RHEL6.5 x86-...
解决ORA-12170:TNS connect timeout occurred问题
12-15
报错如下 报错： ORA-12170:TNS connect timeout occurred 第一反应是监听没开： 1. 测试了一下， 连接超时没用ping 通 C:\Users\Administrator>tnsping brorcl TNS Ping Utility for 64-bit Windows: Version 11.2...
ORA-01795: maximum number of expressions in a list is 1000 【Java代码解决】
m0_51079637的博客
05-27 632
该错误的出现原因就是在sql语句中 in 了超过一千条数据。 但有些复杂的sql不能修改，那么我们可以通过最笨的办法去处理。 只要我保证每次in的不超一千个即可。 在这里使用的是list的subList方法 public static void main(String[] args) { List<String> list = new ArrayList<>(); for (int i= 0; i<10000;i++){
oracle-01795,Oracle教程：ORA-01795的原因及解决办法
weixin_32954401的博客
04-03 847
系统报出一SQL异常，内容如下：java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000找出抛异常时执行的SQL语句，貌不惊人啊，很平常一SQL语句，内容类似：SELECT * FROM DUAL T WHERE T.DUMMY IN ('1', '2', '3', ...)，只是IN后括号里...
oracle-01795,oracle数据库错误：ORA-01795:maximumnumberofexpressionsinalistis1000SQL异常
weixin_33355385的博客
04-03 615
org.springframework.jdbc.BadSqlGrammarException:### Error querying database. Cause: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000### Cause: java.sql.SQL...
LinkedList 实现 LRU 缓存
最新发布
m0_62056231的博客
07-25 292
先定义了大小为3的缓存，然后存1,2,3，此时的访问顺序1-2-3，list头部是最早访问的，尾部是最晚访问的，此时缓存已满，然后访问了1，则现在的顺序是2-3-1，可见，2是那个最久没被访问的，我再添加新元素4时，需要删除的是2，顺序变成3-1-4。LRU（Least Recently Used，最近最少使用）缓存是一种缓存淘汰策略，用于在缓存满时淘汰最久未使用的元素。
oracle ORA-01795: 列表中最大表达式为1000解决办法
07-13
当你在 Oracle 数据库中遇到 ORA-01795 错误时，这意味着你的查询中的表达式列表超过了 Oracle 的限制。Oracle 限制了一个查询中的表达式列表的最大数量为 1000。 为了解决这个问题，你可以尝试以下方法： 1. 利用...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值