MybatisPlus自定义批量插入更新问题及解决

说明

MybtaisPlus自带有批量插入和插入更新，但是其实自带的是有问题的，它是根据单条数据逐个处理的，所以说虽然是批量，实际上也是一条一条数据插入的，对于大量的数据插入来说，无意是不能接收的。

验证自带的插入更新

批量插入

其余的引入等就不多说了，这里直接插入一组数据

/**
 * 测试批量插入
 */
@Test
public void insertBatch(){
    List<MeetingRoom> roomList = Arrays.asList(
            new MeetingRoom().setName("第一1个会议室").setPlace("第一个地点").setUserCount(11).setIsPhone(1).setIsProjector(1).setIsVideo(1),
            new MeetingRoom().setName("第二1个会议室").setPlace("第二个地点").setUserCount(12).setIsPhone(1).setIsProjector(1).setIsVideo(1),
            new MeetingRoom().setName("第三1个会议室").setPlace("第三个地点").setUserCount(13).setIsPhone(1).setIsProjector(1).setIsVideo(1)
    );
    boolean b = meetingRoomMpService.saveBatch(roomList);
    System.out.println(roomList);
}

使用了p6spy打印日志可以看到执行的SQL

JDBC Connection [HikariProxyConnection@636953520 wrapping com.p6spy.engine.wrapper.ConnectionWrapper@4aa11206] will be managed by Spring
==>  Preparing: INSERT INTO d104_meeting_room ( name, user_count, place, is_video, is_projector, is_phone ) VALUES ( ?, ?, ?, ?, ?, ? )
==> Parameters: 第一1个会议室(String), 11(Integer), 第一个地点(String), 1(Integer), 1(Integer), 1(Integer)
 Consume Time：1 ms 2024-08-07 17:08:39
 Execute SQL：INSERT INTO d104_meeting_room ( name, user_count, place, is_video, is_projector, is_phone ) VALUES ( '第一1个会议室', 11, '第一个地点', 1, 1, 1 )

==> Parameters: 第二1个会议室(String), 12(Integer), 第二个地点(String), 1(Integer), 1(Integer), 1(Integer)
 Consume Time：0 ms 2024-08-07 17:08:39
 Execute SQL：INSERT INTO d104_meeting_room ( name, user_count, place, is_video, is_projector, is_phone ) VALUES ( '第二1个会议室', 12, '第二个地点', 1, 1, 1 )

==> Parameters: 第三1个会议室(String), 13(Integer), 第三个地点(String), 1(Integer), 1(Integer), 1(Integer)
 Consume Time：0 ms 2024-08-07 17:08:39
 Execute SQL：INSERT INTO d104_meeting_room ( name, user_count, place, is_video, is_projector, is_phone ) VALUES ( '第三1个会议室', 13, '第三个地点', 1, 1, 1 )

从上可以看出，其实这里是单个插入的

批量插入更新

/**
 * 测试批量插入
 */
@Test
public void saveBatch(){
    List<MeetingRoom> roomList = Arrays.asList(
            new MeetingRoom().setId(1L).setName("第一个会议室").setPlace("第一个地点").setUserCount(11).setIsPhone(1).setIsProjector(1).setIsVideo(1),
            new MeetingRoom().setId(2L).setName("第二个会议室").setPlace("第二个地点").setUserCount(12).setIsPhone(1).setIsProjector(1).setIsVideo(1),
            new MeetingRoom().setName("第三2个会议室").setPlace("第三个地点").setUserCount(13).setIsPhone(1).setIsProjector(1).setIsVideo(1)
    );
    boolean b = meetingRoomMpService.saveOrUpdateBatch(roomList);
    System.out.println(roomList);
}
``

使用了`p6spy`打印日志可以看到执行的SQL如下：

```txt
==>  Preparing: SELECT id,name,user_count,place,remark,is_video,is_projector,is_phone,create_time,update_time FROM d104_meeting_room WHERE id=?
==> Parameters: 1(Long)
 Consume Time：52 ms 2024-08-07 17:14:54
 Execute SQL：SELECT id,name,user_count,place,remark,is_video,is_projector,is_phone,create_time,update_time FROM d104_meeting_room WHERE id=1 

<==    Columns: id, name, user_count, place, remark, is_video, is_projector, is_phone, create_time, update_time
<==        Row: 1, 第一个会议室, 11, 第一个地点, null, 1, 1, 1, null, null
<==      Total: 1
==>  Preparing: UPDATE d104_meeting_room SET name=?, user_count=?, place=?, is_video=?, is_projector=?, is_phone=? WHERE id=?
==> Parameters: 第一个会议室(String), 11(Integer), 第一个地点(String), 1(Integer), 1(Integer), 1(Integer), 1(Long)
 Consume Time：0 ms 2024-08-07 17:14:54
 Execute SQL：UPDATE d104_meeting_room SET name='第一个会议室', user_count=11, place='第一个地点', is_video=1, is_projector=1, is_phone=1 WHERE id=1

==>  Preparing: SELECT id,name,user_count,place,remark,is_video,is_projector,is_phone,create_time,update_time FROM d104_meeting_room WHERE id=?
==> Parameters: 2(Long)
 Consume Time：43 ms 2024-08-07 17:14:55
 Execute SQL：SELECT id,name,user_count,place,remark,is_video,is_projector,is_phone,create_time,update_time FROM d104_meeting_room WHERE id=2 

<==    Columns: id, name, user_count, place, remark, is_video, is_projector, is_phone, create_time, update_time
<==        Row: 2, 第二个会议室, 12, 第二个地点, null, 1, 1, 1, null, null
<==      Total: 1
==>  Preparing: UPDATE d104_meeting_room SET name=?, user_count=?, place=?, is_video=?, is_projector=?, is_phone=? WHERE id=?
==> Parameters: 第二个会议室(String), 12(Integer), 第二个地点(String), 1(Integer), 1(Integer), 1(Integer), 2(Long)
 Consume Time：0 ms 2024-08-07 17:14:55
 Execute SQL：UPDATE d104_meeting_room SET name='第二个会议室', user_count=12, place='第二个地点', is_video=1, is_projector=1, is_phone=1 WHERE id=2

==>  Preparing: INSERT INTO d104_meeting_room ( name, user_count, place, is_video, is_projector, is_phone ) VALUES ( ?, ?, ?, ?, ?, ? )
==> Parameters: 第三2个会议室(String), 13(Integer), 第三个地点(String), 1(Integer), 1(Integer), 1(Integer)
 Consume Time：0 ms 2024-08-07 17:14:55
 Execute SQL：INSERT INTO d104_meeting_room ( name, user_count, place, is_video, is_projector, is_phone ) VALUES ( '第三2个会议室', 13, '第三个地点', 1, 1, 1 )

批量插入更新更是凄惨，同样是分开单条数据执行，但是首先会去查询是否有数据了，没有则插入，有则更新，这样执行效率更低

分析

至于MybatisPlus为啥要这样处理，猜想原因可能是要满足多种数据库的关系，各个数据库批量的语法可能不同导致了不方便做到统一。

MySql中自定义方案解决

在MySQL中本身就支持批量的插入和批量更新语法，因此，我们可以扩展MybatisPlus的通用Mapper来实现真正意义上的批量插入更新

MySQL中批量插入的语法是

insert into t_a (id,name,age) values (#{value_a},#{value_b},#{value_c}),(#{value_a},#{value_b},#{value_c})

批量插入更新的语法为

insert into t_a (id,name,age) values (#{value_a},#{value_b},#{value_c}),(#{value_a},#{value_b},#{value_c})
on DUPLICATE KEY UPDATE id=values(id),name=values(name),age=values(age)

我們就可以根据此来实现一个通用的批量更新修改的方法
在MybatisPlus中增加全局自定义方法中也有说道，主要是细化编写SqlSource逻辑

首先定义一个Mapper继承BaseMapper

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

    /**
     * 批量插入更新
     * @param entityList
     * @return
     */
    int insertOrUpdateBatch(@Param("list") List<T> entityList);
}

然后编写一个批量方法

/**
 * 批量插入或更新 
 * 如有一个表为：
 * CREATE TABLE `meeting_room` (
 *   `id` bigint NOT NULL AUTO_INCREMENT,
 *   `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '会议室名字',
 *   `user_count` int DEFAULT NULL COMMENT ' 会议室容纳人数',
 *   `place` varchar(255) DEFAULT NULL COMMENT '位置',
 *   `remark` varchar(255) DEFAULT NULL COMMENT '备注',
 *   `is_video` tinyint DEFAULT NULL COMMENT '是否有电视屏幕',
 *   `is_projector` tinyint DEFAULT NULL COMMENT '是否有投影仪',
 *   `is_phone` tinyint DEFAULT NULL COMMENT '是否有座机电话',
 *   `create_time` datetime DEFAULT NULL,
 *   `update_time` datetime DEFAULT NULL,
 *   PRIMARY KEY (`id`),
 *   UNIQUE KEY `index_meeting_room_name` (`name`) USING BTREE
 * ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
 * 
 * 插入的数据语法则应该为：
 * insert into d104_meeting_room ( id,name,user_count,place,remark,is_video,is_projector,is_phone,create_time,update_time )
 * values   ( 1,'第一个会议室',11,'第一个地点',NULL,1,1,1,NULL,NULL ),
 *          ( 2,'第二个会议室',12,'第二个地点',NULL,1,1,1,NULL,NULL ),
 *          ( 3,'第三个会议室',13,'第三个地点',NULL,1,1,1,NULL,NULL )
 * on DUPLICATE KEY UPDATE  id=values(id),name=values(name),user_count=values(user_count),place=values(place),remark=values(remark),
 *                          is_video=values(is_video),is_projector=values(is_projector),is_phone=values(is_phone),
 *                          create_time=values(create_time),update_time=values(update_time)
 */
public class SaveOrUpdateBatchMethod extends AbstractMethod {


    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        
        String tableName = tableInfo.getTableName();

        String fileds = parseFileds(tableInfo);

        String valuesSql = parseForeachSql(tableInfo);

        String duplicateSql = parseDuplicateSql(tableInfo);
        
        //SQL需要用<script></script> 标签包裹
        String sql = String.format("<script>insert into %s ( %s ) values %s on DUPLICATE KEY UPDATE %s</script>",
                tableName,
                fileds,
                valuesSql,
                duplicateSql
        );

        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);

        return addInsertMappedStatement(mapperClass,modelClass,"insertOrUpdateBatch",sqlSource,new NoKeyGenerator(),null,null);
    }

    /**
     * 解析所有字段值
     */
    private String parseFileds(TableInfo tableInfo) {
        return tableInfo.getAllSqlSelect();
    }

    /**
     * 组装values中的语句，结构如下
     * <foreach collection="list" open="(" close=")" item="item" separator="),(">
     *     #{item.auth}
     * </foreach>
     * @param tableInfo
     * @return
     */
    private String parseForeachSql(TableInfo tableInfo) {
        StringBuilder builder = new StringBuilder();
        // <foreach collection="list" open="(" close=")" item="item" separator="),(">
        builder.append("<foreach collection=\"list\" open=\"(\" close=\")\" item=\"item\"  separator=\"),(\" >");

        //      #{item.auth}
        builder.append("#{item.").append(tableInfo.getKeyProperty()).append("},");
        for (TableFieldInfo fieldInfo : tableInfo.getFieldList()) {
            builder.append("#{item.").append(fieldInfo.getProperty()).append("},");
        }
        builder.deleteCharAt(builder.length() - 1);

        // </foreach>
        builder.append("</foreach>");

        return builder.toString();
    }

    /**
     *  组装 on DUPLICATE KEY UPDATE 后面的语句 ，结构如下
     *  id=values(id),name=values(name),user_count=values(user_count),place=values(place),remark=values(remark)
     */
    private String parseDuplicateSql(TableInfo tableInfo) {
        StringBuilder builder = new StringBuilder();
        String keyColumn = tableInfo.getKeyColumn();
        // id=values(id),name=values(name),
        builder.append(keyColumn).append("=values(").append(keyColumn).append("),");
        for (TableFieldInfo fieldInfo : tableInfo.getFieldList()) {
            String column = fieldInfo.getColumn();
            builder.append(column).append("=values(").append(column).append("),");
        }
        builder.deleteCharAt(builder.length() - 1);
        return builder.toString();
    }
}

编写一个注册Bean注册方法

重新定义一个注入器，替换默认的DefaultSqlInjector，但需要注意执行父方法加入默认的一些方法，否则默认方法不可用
然后注入到容器中

/**
 * 方法注入器
 */
@Component
public class AdviceSqlInjector extends DefaultSqlInjector {

    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
        methodList.add(new SaveOrUpdateBatchMethod());
        return methodList;
    }
}

这样就完成了一个真正的批量插入修改

若只要批量插入的话，也类似的构造，只是构造出来的