- 说明
- mybatis-plus in条件超过1000条时报错解决方案:
数据库中 IN 可以包含的选项数如下:
mysql数据库:in语句中参数个数是不限制的。不过对整段sql语句的长度有了限制, 8.0之前的版本默认4M,最大可设置为1G; 8.0版本之后默认64M,最大可设置1G (设置max_allowed_packet修改默认大小)。 Oracle数据库:9i(最多256条)、10g(最多1000条)、 11g(最多1000条)mybatis-plus in条件超过1000条时报错解决方案:
我们采取的是 截取in条件的方案,例如 select*from test where name in () or name in (),具体如下:
1.新建参数截取的工具类
package com.spsolution.hera.parent.jdbc.util;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.support.SFunction;
import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;
import com.baomidou.mybatisplus.extension.conditions.update.LambdaUpdateChainWrapper;
import org.apache.commons.lang3.ObjectUtils;
import java.util.ArrayList;
import java.util.List;
public class MybatisParameterUtils {
public static void cutInParameter(LambdaQueryWrapper wrapper, SFunction column, List coll) throws Exception {
List> newList = splitList(coll, 900);
if (ObjectUtils.isEmpty(newList)) {
throw new Exception("参数错误");
} else if (newList.size() == 1) {
wrapper.in(column, newList.get(0));
return;
}
wrapper.and(i -> {
i.in(column, newList.get(0));
newList.remove(0);
for (List objects : newList) {
i.or().in(column, objects);
}
});
}
public static void cutNotInParameter(LambdaQueryWrapper wrapper, SFunction column, List coll) throws Exception {
List> newList = splitList(coll, 900);
if (ObjectUtils.isEmpty(newList)) {
throw new Exception("参数错误");
} else if (newList.size() == 1) {
wrapper.notIn(column, newList.get(0));
return;
}
wrapper.and(i -> {
i.in(column, newList.get(0));
newList.remove(0);
for (List objects : newList) {
i.or().notIn(column, objects);
}
});
}
public static void cutInParameter(LambdaQueryChainWrapper wrapper, SFunction column, List coll) throws Exception {
List> newList = splitList(coll, 900);
if (ObjectUtils.isEmpty(newList)) {
throw new Exception("参数错误");
} else if (newList.size() == 1) {
wrapper.in(column, newList.get(0));
return;
}
wrapper.and(i -> {
i.in(column, newList.get(0));
newList.remove(0);
for (List objects : newList) {
i.or().in(column, objects);
}
});
}
public static void cutNotInParameter(LambdaQueryChainWrapper wrapper, SFunction column, List coll) throws Exception {
List> newList = splitList(coll, 900);
if (ObjectUtils.isEmpty(newList)) {
throw new Exception("参数错误");
} else if (newList.size() == 1) {
wrapper.notIn(column, newList.get(0));
return;
}
wrapper.and(i -> {
i.in(column, newList.get(0));
newList.remove(0);
for (List objects : newList) {
i.or().notIn(column, objects);
}
});
}
public static void cutInParameter(LambdaUpdateWrapper wrapper, SFunction column, List coll) throws Exception {
List> newList = splitList(coll, 900);
if (ObjectUtils.isEmpty(newList)) {
throw new Exception("参数错误");
} else if (newList.size() == 1) {
wrapper.in(column, newList.get(0));
return;
}
wrapper.and(i -> {
i.in(column, newList.get(0));
newList.remove(0);
for (List objects : newList) {
i.or().in(column, objects);
}
});
}
public static void cutNotInParameter(LambdaUpdateWrapper wrapper, SFunction column, List coll) throws Exception {
List> newList = splitList(coll, 900);
if (ObjectUtils.isEmpty(newList)) {
throw new Exception("参数错误");
} else if (newList.size() == 1) {
wrapper.notIn(column, newList.get(0));
return;
}
wrapper.and(i -> {
i.in(column, newList.get(0));
newList.remove(0);
for (List objects : newList) {
i.or().notIn(column, objects);
}
});
}
public static void cutInParameter(LambdaUpdateChainWrapper wrapper, SFunction column, List coll) throws Exception {
List> newList = splitList(coll, 900);
if (ObjectUtils.isEmpty(newList)) {
throw new Exception("参数错误");
} else if (newList.size() == 1) {
wrapper.in(column, newList.get(0));
return;
}
wrapper.and(i -> {
i.in(column, newList.get(0));
newList.remove(0);
for (List objects : newList) {
i.or().in(column, objects);
}
});
}
public static void cutNotInParameter(LambdaUpdateChainWrapper wrapper, SFunction column, List coll) throws Exception {
List> newList = splitList(coll, 900);
if (ObjectUtils.isEmpty(newList)) {
throw new Exception("参数错误");
} else if (newList.size() == 1) {
wrapper.notIn(column, newList.get(0));
return;
}
wrapper.and(i -> {
i.in(column, newList.get(0));
newList.remove(0);
for (List objects : newList) {
i.or().notIn(column, objects);
}
});
}
public static List> splitList(List list, int groupSize) {
int length = list.size();
// 计算可以分成多少组
int num = (length + groupSize - 1) / groupSize;
List> newList = new ArrayList<>(num);
for (int i = 0; i < num; i++) {
// 开始位置
int fromIndex = i * groupSize;
// 结束位置
int toIndex = Math.min((i + 1) * groupSize, length);
newList.add(list.subList(fromIndex, toIndex));
}
return newList;
}
}
2.方法中引用
//条件构造器 LambdaQueryWrapperqueryWrapper = new LambdaQueryWrapper<>(); //普通字段查询 queryWrapper.eq(!StringUtils.isEmpty(enterForm.getHylbdm()),TXxytEnterpriseInfo::getHylbdm,enterForm.getHylbdm()); //调用工具类重新拼装in条件 (条件wrapper, get方法的方法引用, 参数list) MybatisParameterUtils.cutInParameter(queryWrapper,TXxytEnterpriseInfo::getOrgId, orgIdList); List list = this.list(queryWrapper);
------------------------ 至此完美解决 ------------------------



