Mybatis-Plus的SQL注入器实现批量插入/修改，效率比较

Sql效率

mysql支持一条sql语句插入多条数据。但是Mybatis-Plus中默认提供的saveBatch、updateBatchById方法并不能算是真正的批量语句，而是遍历实体集合执行INSERT_ONE、UPDATE_BY_ID语句。

mybatis-plus虽然做了分批请求、一次提交的处理。但如果jdbc不启用配置rewriteBatchedStatements，那么批量提交的sql到了mysql就还是一条一条执行，mysql并不会将这些sql重写为insert多值插入，相比一条sql批量插入，性能上会差点。

rewriteBatchedStatements文档
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-performance-extensions.html#cj-conn-prop_rewriteBatchedStatements

插入10000条数据

1. mybatis-plus的saveBatch耗时3.6s

##sql格式(不止这些字段)
 <script>
 <foreach collection="list" item="item" separator=";">
 	insert into user (name, age) values ("张三",17)
 </foreach>
 </script>

2. insert values 耗时1.8s

##sql格式(不止这些字段)
insert into user (name, age) values ("张三", 17), ("李四", 18);

更新10000条数据

1. mybatis-plus的updateBatchById 耗时3.1s

##sql格式(不止这些字段)
 <script>
 <foreach collection="list" item="item" separator=";">
	update user set name = "张三", age = 17 where id = 1
 </foreach>
</script>

2. update case when

• 使用Update case when 是可以一条sql批量更新，一次提交10000条会卡死，得分批处理
• 最终耗时更久，需要5.8s。

##sql格式(不止这些字段)
update
        user
set
        name=
        (
        case id when 1 then '张三'
        when 2 then '李四'
        else name
end),
        age=
        (
        case id when 1 then 16
        when 2 then 26
        else age
end)
where
        id in ( 1 , 2 )

综合比较效率

10000条数据	mybatis-plus	自定义sql
批量插入	3.6s	1.8s
批量更新	3.1s	5.8s

自定义sql注入器

1.mybatis-plus给我们提供了自定义sql注入的功能，我们可以自己定义类似insert()、updateById()的方法。

2.首先mybatis-plus在com.baomidou.mybatisplus.extension，给我们提供了4个自带的注入方法。

1. AlwaysUpdateSomeColumnById 根据Id更新每一个字段，不忽略null字段，数据为null则更新为null。
2. InsertBatchSomeColumn 单条sql批量插入，通过单SQL的insert语句实现批量插入。
3. LogicDeleteByIdWithFill 带自动填充的逻辑删除，比如自动填充更新时间、操作人。
4. Upsert 更新or插入，根据唯一约束判断是执行更新还是删除，相当于提供insert on duplicate key update支持，但mysql应该没有此语法。

3. 类似com.baomidou.mybatisplus.core.enums.SqlMethod里的模板，我们可以继承com.baomidou.mybatisplus.core.injector.AbstractMethod方法来自行定义批量插入/修改的模板方法

import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import org.apache.ibatis.executor.keygen.NoKeyGenerator;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;

public class InsertBatchMethod extends AbstractMethod {
    /**
     * insert into user(id, name, age) values (2, "b", 24), (3, "c", 26);
     <script>
     insert into user(id, name, age) values
     <foreach collection="list" item="item" index="index" open="(" separator="),(" close=")">
     #{item.id}, #{item.name}, #{item.age}
     </foreach>
     </script>
     */
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        final String sql = "<script>insert into %s %s values %s</script>";
        final String fieldSql = prepareFieldSql(tableInfo);
        final String valueSql = prepareValuesSql(tableInfo);
        final String sqlResult = String.format(sql, tableInfo.getTableName(), fieldSql, valueSql);
        SqlSource sqlSource = languageDriver.createSqlSource(
        configuration, sqlResult, modelClass);
        
        //加入Configuration中的Map<String, MappedStatement> mappedStatements,
        //这个mappedStatements保存我们xml中写的各种标签<select>、<insert>、<update>等语句的信息
        //key为全限定类名方法名、value为对应元数据信息
        return this.addInsertMappedStatement(mapperClass, modelClass, "insertBatch", sqlSource, new NoKeyGenerator(), null, null);
    }

    private String prepareFieldSql(TableInfo tableInfo) {
        StringBuilder fieldSql = new StringBuilder();
        fieldSql.append(tableInfo.getKeyColumn()).append(",");
        tableInfo.getFieldList().forEach(x -> {
            fieldSql.append(x.getColumn()).append(",");
        });
        fieldSql.delete(fieldSql.length() - 1, fieldSql.length());
        fieldSql.insert(0, "(");
        fieldSql.append(")");
        return fieldSql.toString();
    }

    private String prepareValuesSql(TableInfo tableInfo) {
        final StringBuilder valueSql = new StringBuilder();
        valueSql.append("<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" separator=\"),(\" close=\")\">");
        valueSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},");
        tableInfo.getFieldList().forEach(x -> valueSql.append("#{item.").append(x.getProperty()).append("},"));
        valueSql.delete(valueSql.length() - 1, valueSql.length());
        valueSql.append("</foreach>");
        return valueSql.toString();
    }
}

public class UpdateBatchMethod extends AbstractMethod {
    /**
      update user set
        name=
        (CASE
                id WHEN 1 THEN '张三'
                WHEN 2 THEN '李四'
        end),
        age =
        (CASE
                id WHEN 1 THEN '2'
                WHEN 2 THEN '2'
        end) where id in (1,2);
	   <script>
	   update user 
	   <trim prefix="set" suffixOverrides=",">
		<trim prefix="name =(case id" suffix="end),">
		  <foreach collection="list" item="item" >
		    <if test="item.name!=null">
		      when #{item.id} then #{item.name}
		    </if>
		  </foreach>
		  else name
		</trim>
		<trim prefix="age =(case id" suffix="end),">
		  <foreach collection="list" item="item" >
		    <if test="item.age!=null">
		      when #{item.id} then #{item.age}
		    </if>
		  </foreach>
		  else age</trim>
		</trim>
		where id in 
		 <foreach collection="list" item="item" separator="," open="(" close=")">
		 	#{item.id} 
		 </foreach> 
	   </script>
     */
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        final String sql = "<script>\n update %s %s \n where id in \n <foreach collection=\"list\" item=\"item\" separator=\",\" open=\"(\" close=\")\">\n #{item.id} </foreach> \n </script>";
        final String valueSql = prepareValuesSql(tableInfo);
        final String sqlResult = String.format(sql, tableInfo.getTableName(), valueSql);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
        return this.addUpdateMappedStatement(mapperClass, modelClass, "updateBatch", sqlSource);
    }

    private String prepareValuesSql(TableInfo tableInfo) {
        final StringBuilder valueSql = new StringBuilder();
        valueSql.append("<trim prefix=\"set\" suffixOverrides=\",\">\n");
        tableInfo.getFieldList().forEach(x -> {
            valueSql.append("<trim prefix=\"").append(x.getColumn()).append(" =(case id\" suffix=\"end),\">\n");
            valueSql.append("<foreach collection=\"list\" item=\"item\" >\n");
            valueSql.append("<if test=\"item.").append(x.getProperty()).append("!=null\">\n");
            valueSql.append("when #{item.id} then #{item.").append(x.getProperty()).append("}\n");
            valueSql.append("</if>\n");
            valueSql.append("</foreach>\n");
            valueSql.append("else ").append(x.getColumn());
            valueSql.append("</trim>\n");
        });
        valueSql.append("</trim>\n");
        return valueSql.toString();
    }
}

4.配置自定义方法SQL注入器

import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import com.baomidou.mybatisplus.extension.injector.methods.InsertBatchSomeColumn;
import org.springframework.context.annotation.Configuration;

import java.util.List;

/**
 * 自定义方法SQL注入器
 */
@Configuration
public class CustomizedSqlInjector extends DefaultSqlInjector {

    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass);
        methodList.add(new InsertBatchSomeColumn());
        methodList.add(new InsertBatchMethod());
        methodList.add(new UpdateBatchMethod());
        return methodList;
    }
}

5.自定义BaseEntityMapper集成BaseMapper,5. 在BaseEntityMapper中定义对应的抽象方法，方法名要与injectMappedStatement中的addMappedStatement一致

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

import java.util.List;

/**
 * 业务实体基础Mapper接口
 */
public interface BaseEntityMapper<T> extends BaseMapper<T> {

    int insertBatch(@Param("list") List<T> list);
    
    Integer insertBatchSomeColumn(List<T> list);
    
    int updateBatch(@Param("list") List<T> list);
}

6. 使用，和Myabtis-plus的insert、updateById等方法一样直接使用即可

import com.test.demo.model.User;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserMapper extends BaseEntityMapper<User> {
}

...
userMapper.insertBatch(userList);
...