1、说明：实现批量插入的方式

1）可以继承IService, 使用saveBatch方法，此方法不在此说明

2）通过自定义sql注入器，InsertBatchSomeColumn 方法

2、通过InsertBatchSomeColumn方法实现

2.1 引包

   <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

  <dependency>
            <groupId>com.github.yulichang</groupId>
            <artifactId>mybatis-plus-join-boot-starter</artifactId>
            <version>1.4.10</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-annotation</artifactId>
            <version>3.5.1</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>3.5.1</version>
        </dependency>

2.2 自定义sql 注入

import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.extension.injector.methods.InsertBatchSomeColumn;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * @author zc
 * @date 2024/3/18 10:45
 * @desc
 */
@Component
public class InsertBatchSqlInjector extends DefaultSqlInjector {

    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass,  TableInfo tableInfo) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
        methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
        return methodList;
    }
}

2.3 mybatis plus 自定义配置

包括了分页，这里注意如果引用了mybatis-plus-join，如果把sql注入交给Spring管理，会造成冲突，所有把easySqlInjector注释掉了，网上有自定义sql注入和mybatis-plus-join 兼容的方法，可以自行查找；

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Component;

/**
 * @author zc
 * @date 2024/3/18 10:42
 * @desc mybatis plus 批量注入
 */
@Configuration
public class MybatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        //添加分页插件
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        //添加乐观锁插件
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return interceptor;
    }

//    @Bean
//    @Primary
//    public InsertBatchSqlInjector easySqlInjector(){
//        return new InsertBatchSqlInjector();
//    }

}

2.4  自定义mapper

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

import java.util.Collection;

/**
 * @author zc
 * @date 2024/3/18 15:22
 * @desc
 */
@Mapper
public interface EasyBaseMapper<T> extends BaseMapper<T> {
    /**
     * 批量插入 仅适用于mysql
     *
     * @param entityList 实体列表
     * @return 影响行数
     */
    Integer insertBatchSomeColumn(Collection<T> entityList);
}

实体类mapper 继承 EasyBaseMapper ，即可使用insertBatchSomeColumn 方法

import com.neusoft.bean.HostDiffBean;
import org.apache.ibatis.annotations.Mapper;

/**
 * @author zc
 * @date 2024/3/15 10:43
 * @desc
 */
@Mapper
public interface HostDiffMapper extends EasyBaseMapper<HostDiffBean> {
}

2.5  实体类

import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

/**
 * @author zc
 * @date 2024/3/14 17:16
 * @desc
 */
@Data
@TableName("cm_host_compare_diff")
public class HostDiffBean implements Serializable {

    /**
     * id
     */
    @TableId
    private String id;

    /**
     * 序列号
     */
    private String serialNumber;

    /**
     * ip
     */
    private String ip;

    /**
     * 设备型号
     */
    private String deviceNo;

    /**
     * 华为端ip
     */
    private String hIp;

    /**
     * 华为端设备型号
     */
    private String hDeviceNo;

    /**
     * 创建时间
     */
    private Date createTime;
}

2.6  service 使用

此service 是实现读取execl 文件内容 与数据库中的内容进行差异对比后差异数据批量保存：

hostDiffMapper.insertBatchSomeColumn(hostDiffBeans); 这个地方使用的insertBatchSomeColumn方法

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mysql.jdbc.StringUtils;
import com.neusoft.bean.*;
import com.neusoft.mapper.CmdbResourceMapper;
import com.neusoft.mapper.HostDiffMapper;
import com.neusoft.mapper.HostInfoMapper;
import com.neusoft.util.FileUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author zc
 * @date 2024/3/15 11:08
 * @desc
 */
@Slf4j
@Service
@Transactional(rollbackFor = Exception.class)
public class HostDataCompareService {

    @Autowired
    private HostDiffMapper hostDiffMapper;

    @Autowired
    private HostInfoMapper hostInfoMapper;

    @Autowired
    private CmdbResourceMapper cmdbResourceMapper;

    /**
     * 宿主机对比
     * @param filePath
     */
    public void hostCompare(String filePath, String targetPath) {
        log.info("宿主机对比操作开始: {}", filePath);
        if (!StringUtils.isNullOrEmpty(filePath)) {
            if (Files.exists(Paths.get(filePath))) {
                List<HostInfoBean> list = new ArrayList<>();
                try (FileInputStream file = new FileInputStream(filePath); Workbook workbook = new XSSFWorkbook(file)) {
                    Sheet sheet = workbook.getSheetAt(0);
                    Iterator<Row> rowIterator = sheet.iterator();
                    while (rowIterator.hasNext()) {
                        Row row = rowIterator.next();
                        if (row.getRowNum() != 0) {
                            HostInfoBean hostInfoBean = new HostInfoBean();
                            hostInfoBean.setName(row.getCell(0).toString());
                            hostInfoBean.setIp(row.getCell(9).toString());
                            hostInfoBean.setDeviceNo(row.getCell(11).toString());
                            hostInfoBean.setSerialNumber(row.getCell(10).toString());
                            hostInfoBean.setId(UUID.randomUUID().toString());
                            hostInfoBean.setCreateTime(new Date());
                            list.add(hostInfoBean);
                        }
                    }
                } catch (IOException e) {
                    log.error("读取宿主机Execl数据异常：", e);
                }
                if (!CollectionUtils.isEmpty(list)) {
                    List<HostInfoBean> filterList = list.stream().filter(e-> !StringUtils.isNullOrEmpty(e.getSerialNumber())).collect(Collectors.toList());
                    if(!CollectionUtils.isEmpty(filterList)){
                        log.info("过滤后华为宿主机数量：{}", filterList.size());
                        List<HostInfoBean> beans = cmdbResourceMapper.getHostInfos(new Date());
                        if (!CollectionUtils.isEmpty(beans)) {
                            List<HostInfoBean> filterBeans = beans.stream().filter(e-> !StringUtils.isNullOrEmpty(e.getSerialNumber())).collect(Collectors.toList());
                            if (!CollectionUtils.isEmpty(filterBeans)) {
                                log.info("过滤后数据库宿主机数量：{}", filterBeans.size());
                                List<HostDiffBean> hostDiffBeans = new ArrayList<>();
                                List<HostInfoBean> hostInfoBeans = new ArrayList<>();
                                for (HostInfoBean hostInfoBean : filterList) {
                                    boolean isHave = false;
                                    for (HostInfoBean bean : filterBeans) {
                                        if (hostInfoBean.getSerialNumber().equals(bean.getSerialNumber())) {
                                            if (!hostInfoBean.getIp().equals(bean.getIp()) || !hostInfoBean.getDeviceNo().equals(bean.getDeviceNo())) {
                                                HostDiffBean hostDiffBean = new HostDiffBean();
                                                hostDiffBean.setId(UUID.randomUUID().toString());
                                                hostDiffBean.setSerialNumber(hostInfoBean.getSerialNumber());
                                                hostDiffBean.setHIp(hostInfoBean.getIp());
                                                hostDiffBean.setIp(bean.getIp());
                                                hostDiffBean.setHDeviceNo(hostInfoBean.getDeviceNo());
                                                hostDiffBean.setDeviceNo(bean.getDeviceNo());
                                                hostDiffBean.setCreateTime(new Date());
                                                hostDiffBeans.add(hostDiffBean);
                                            }
                                            isHave = true;
                                        }
                                    }
                                    if (!isHave) {
                                        hostInfoBean.setType(1);
                                        hostInfoBeans.add(hostInfoBean);
                                    }
                                }

                                for (HostInfoBean bean : filterBeans) {
                                    boolean isHave = false;
                                    for (HostInfoBean hostInfoBean : filterList) {
                                        if (hostInfoBean.getSerialNumber().equals(bean.getSerialNumber())) {
                                            isHave = true;
                                        }
                                    }
                                    if (!isHave) {
                                        bean.setType(0);
                                        hostInfoBeans.add(bean);
                                    }
                                }

                                if (!CollectionUtils.isEmpty(hostDiffBeans)) {
                                    log.info("宿主机内容差异数量：{}", hostDiffBeans.size());
                                    hostDiffMapper.insertBatchSomeColumn(hostDiffBeans);
                                }
                                if (!CollectionUtils.isEmpty(hostInfoBeans)) {
                                    log.info("宿主机数量差异数量：{}", hostInfoBeans.size());
                                    hostInfoMapper.insertBatchSomeColumn(hostInfoBeans);
                                }

                                try {
                                    FileUtil.moveFile(filePath, targetPath);
                                    log.error("宿主机移动文件夹完成");
                                } catch (IOException e) {
                                    log.error("宿主机移动文件夹异常：", e);
                                }
                            }else{
                                log.error("过滤后宿主机数据库数据为空");
                            }
                        } else {
                            log.error("读取宿主机数据库数据为空");
                        }
                    }else{
                        log.error("过滤后华为宿主机数据为空");
                    }
                } else {
                    log.error("读取宿主机Execl数据为空");
                }
            } else {
                log.error("宿主机execl文件不存在");
            }
        } else {
            log.error("execl文件路径为空, 对比失败");
        }
        log.info("宿主机对比操作完成");
    }
}

3、最后

以上内容通过实际开发操作可用，如未引用mybatis-plus-join， 可把sql 注入交给Spring处理；

在引用mybatis-plus-join 下 打开  MybatisPlusConfig 下easySqlInjector 注释启动会报这个错误