环境
语言: java
项目框架:springMVC+mybatis+shiro
数据库:oracle
问题描述
项目已经在测试当中,业务人员发现选择多个部门后查询信息出现报错,导致无法查询。问题本地复现,发现是代码中一个方法使用的sql语句用到in,因为数据库限定,所以报ORA-01795:列表中的最大表达式数为1000,也就是in后面跟的列表最大数不得超过1000。
问题解决思路
既然是数据库的问题,最先想到应该是去修改sql语句,既然in后面的列表不能太长,那么就让in后面的列表不要太长即可。用or可以解决,大概就是两种方法:
- 拼接成类似
select * from CARS_CARDRE_V c where 1=1 and (c.JGBH in 'xxx' or c.JGBh in 'xxx'or..)
但是这种有个问题,就是效率比较低,数据量过大,可能查询会有点慢。
2. 拼接成类似
select * from CARS_CARDRE_V c where 1=1 and (c.JGBH in (1,2,3,...,999) or c.JGBH in (1,2,3,...,999) or ..)
大概就是这样,in后面的列表不超过1000即可。
结合mybatis实现
这块涉及到代码,主要包括xml和java,因为用到mybatis,所以我们在其中会用到foreach在mybatis中的部分用法
- 上述第一种方法
这里我传过来的是字符串,所以在collection的时候又把字符串转成数组进行操作,然后使用foreach,拼接成上述第一种格式,代码展示这块就放出xml的语句,因为jgbh这是用逗号隔开的字符串,java代码就不放了。当然也可以是其他格式:list,array,map等,不过用法可能会有点改变需要自己去查
select * from CARS_CARDRE_V c where 1=1
<if test="jgbh!=null and jgbh!=''">
and
<foreach item="item" index="index" collection="jgbh.split(',')" open="(" separator="or" close=")"> c.JGBH in #{item}</foreach>
</if>
- 上述第二种方法
该方法就需要改变一下传过来的格式,因为我最后要将拼成上述第二种格式,我们需要一种数据格式List<List>,这样我们就能在xml 中进行遍历拼凑我们需要的sql语句。
java中需要使用下面方法,将数组拼成List<List>
/**
* 按指定大小,分隔集合,将集合按规定个数分为n个部分
* @param list
* @param len
* @return
*/
private static List<List<String>> splitList(List<String> list, int len) {
if (list == null || list.size() == 0 || len < 1) {
return null;
}
List<List<String>> result = new ArrayList();
int size = list.size();
int count = (size + len - 1) / len;
for (int i = 0; i < count; i++) {
List<String> subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));
result.add(subList);
}
return result;
}
xml中,用到上述的格式数据,进行两次便利,得出你要的格式
select * from CARS_CARDRE_V c where 1=1
<if test="dw!=null and dw.size()>0">
and
<foreach collection="dw" item="items" open="(" separator="or" close=")" index="idx">
<if test="items!=null and items.size()>0">c.JGBH in<foreach collection="items" item="it" index="num" open="(" separator="," close=")">#{it}</foreach></if></foreach>
</if>
坑
1.xml中的语句拼写,一不小心会被占位符搞到你崩溃,提醒一下
我不清楚是编辑器还是什么的问题,第一个方法就那样写没有问题,但是第二个的xml的写法要注意,如果你把我这段代码考过去,然后格式化了,出现了ORA-00911:无效字符的错误,注意有可能是占位符在坑你(前提是你的sql中么有;之类的其他字符)你打出语句可以看到语句中有很多?,那么你最好还是像我这样写,我就是这样,搞了我一下午,好了,暂时就这样吧