Java 关于批量插入遇到的问题 -sqlserver

序言：

        我们在做项目的时候，经常会遇到，对数据的新增动作，如果数据量很少的情况下，单个新增对性能还好，但是一旦涉及到 大数据量，如十万，百万，千万，这个时候如果采用单个新增，那么整个系统都得瘫痪，响应时间 小时级别了，就可以出门左转，28路公交，火车站，回老家了。

吐槽：

        对于项目而言，性能是最重要的，性能的快慢，对客户的体验和公司的名誉有着直接关系，要想代码写得好，首先围着性能跑。这几天一直在搞大数据插入的功能，自己总结了一些经验，供于各位大佬参考：

1、定时实体

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("Dictionary_Info")
@ApiModel(value="DictionaryInfo对象", description="")
public class DictionaryInfo implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "主键ID")
    @TableId(value = "ID", type = IdType.AUTO)
    private Integer id;

    @ApiModelProperty(value = "编码")
    @TableField("DI_Code")
    private String diCode;

    @ApiModelProperty(value = "名称")
    @TableField("DI_Name")
    private String diName;

    @ApiModelProperty(value = "描述")
    @TableField("DI_Desc")
    private String diDesc;

    @ApiModelProperty(value = "类型")
    @TableField("DI_Type")
    private String diType;

    @ApiModelProperty(value = "是否删除")
    @TableField("IsDelete")
    private Boolean isdelete;

    @TableField("TS")
    private Date ts;


}

2、定义mapper.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.walmart.java.sams.api.mapper.OrderplanConfirmMapper">
 <insert id="insertBs" parameterType="保存数据库的参数实体">
INSERT INTO Dictionary_Info (DI_Code, DI_Name, DI_Desc, DI_Type, IsDelete, TS) VALUES(#{diCode}, #{diName}, #{diDesc}, #{diType}, #{isDelete, #{ts};
 </insert>
</mapper>

3、定时一个接口

//服务类
public interface IDictionaryInfoService extends IService<DictionaryInfo> {
    void insertBs(List<DictionaryInfo> list);
}

一、MyBatis 集合方式批量新增

1、定义Mapper

@Mapper
public interface DictionaryInfoMapper extends BaseMapper<DictionaryInfo> {

    //MyBatis以集合方式批量新增
    void insertBs(DictionaryInfomodel);
}

2、实现批量插入方法

@Service
//服务实现类
public class DictionaryInfoServiceImpl extends ServiceImpl<DictionaryInfoMapper, DictionaryInfo> implements IDictionaryInfoService {
    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    //MyBatis以集合方式批量新增
    public void insertBs(List<DictionaryInfo> list) {
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        list.stream().forEach(p -> baseMapper.insertBs(p));
        sqlSession.commit();
        sqlSession.clearCache();
    }

    public void TestInsert(){
        long start = System.currentTimeMillis();
        List<DictionaryInfo> dicList = new ArrayList<>();
        DictionaryInfo dInfo;
        for(int i = 0 ;i < 10000; i++) {
            dInfo.setDiCode("1"+i);
            dInfo.setDiDesc("2");
            dInfo.setDiType("c");
            dInfo.setIsdelete(false);
            dInfo.setDiName("test");
            dicList .add(dInfo);
        }
        //批量插入
        insertBs(dicList);
    }

}

二、MyBatis-Plus提供的SaveBatch方法

1、实现批量插入方法

@Service
//服务实现类
public class DictionaryInfoServiceImpl extends ServiceImpl<DictionaryInfoMapper, DictionaryInfo> implements IDictionaryInfoService {
    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    //MyBatis以集合方式批量新增
    public void insertBs(List<DictionaryInfo> list) {
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        list.stream().forEach(p -> baseMapper.insertBs(p));
        sqlSession.commit();
        sqlSession.clearCache();
    }

    public void TestInsert(){
        long start = System.currentTimeMillis();
        List<DictionaryInfo> dicList = new ArrayList<>();
        DictionaryInfo dInfo;
        for(int i = 0 ;i < 10000; i++) {
            dInfo.setDiCode("1"+i);
            dInfo.setDiDesc("2");
            dInfo.setDiType("c");
            dInfo.setIsdelete(false);
            dInfo.setDiName("test");
            dicList .add(dInfo);
        }
        //批量插入
        saveBatch(dicList);
    }

}

//服务类
public interface IDictionaryInfoService extends IService<DictionaryInfo> {
    void insertBs(List<DictionaryInfo> list);
}

三、MyBatis-Plus  的 InsertBatchSomeColumn 

1、自定义 BatchPlusSqlInjector 类

public class BatchPlusSqlInjector 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、定时项目启动，加载的核心类

@Configuration
public class MybatisPlusConfig {

    public BatchPlusSqlInjector sqlInjector() {
        return new BatchPlusSqlInjector();
    }
}

3、自定义一个Mapper接口

public interface BatchPlusBaseMapper<T> extends BaseMapper<T> {

    Integer insertBatchSomeColumn(Collection<T> entityList);
}

4、定义Mapper继承 BatchPlusBaseMapper

@Mapper
public interface DictionaryInfoMapper<T> extends EasyBaseMapper<DictionaryInfo> {
    
}

实现批量插入方法

@Service
//服务实现类
public class DictionaryInfoServiceImpl extends ServiceImpl<DictionaryInfoMapper, DictionaryInfo> implements IDictionaryInfoService {

    public void TestInsert(){
        long start = System.currentTimeMillis();
        List<DictionaryInfo> dicList = new ArrayList<>();
        DictionaryInfo dInfo;
        for(int i = 0 ;i < 10000; i++) {
            dInfo.setDiCode("1"+i);
            dInfo.setDiDesc("2");
            dInfo.setDiType("c");
            dInfo.setIsdelete(false);
            dInfo.setDiName("test");
            dicList .add(dInfo);
        }
        //批量插入
        baseMapper.insertBatchSomeColumn(dicList )
    }

}