栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

mybatis-plus in查询条件超过1000条报错

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

mybatis-plus in查询条件超过1000条报错

mybatis-plus in查询条件超过1000条报错
  • 说明
  • 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.方法中引用

		//条件构造器
 		LambdaQueryWrapper queryWrapper = 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);

------------------------ 至此完美解决 ------------------------

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/979353.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号