mybatis根据表名动态批量新增，批量更新的实现方法（附排坑过程+完整源码完全可用）

项目中迁移的需求用到了批量新增，批量更新来做迁移的需求，为了方便以后的迁移，这里写了一套完整的方法来实现insert和update

首先因为是老项目的表，索引列都层次不齐，因此还是要手动列出相关索引的表，建立arr方便之后遍历

这里传入tableArr，后面三个参数是根据业务需求制定的，我这里是要实现补录startTime和endTime期间旧表内的数据，queryType指的是索引类型，我这里的话queryType设置如下

注意这里新增和更新的两个方法的一些细微不同：
批量新增会remove掉map中的id，为了不起自增id冲突

private void migrationCreateTable(String[] tableArr, String startTime, String endTime, int queryType) {
        for (int i = 0; i < tableArr.length; i++) {
            //旧方法一个个mapper赋值传
            //List<XXXEntity> list = xxxService.getList(createTime);

            //insert补数据，改成动态传值迁移,
            List<Map<String, Object>> list = dataFromService.getListMap(tableArr[i], new HashMap<>(), startTime, endTime, queryType);
            Map<String, Object> fieldsMap;
            if (list.size() > 0) {
                //去除ID过滤，防止插入自增问题
                for (Map<String, Object> e :
                        list) {
                    e.remove("id");
                }
                fieldsMap = list.get(0);
                //插入字段去除ID过滤
                //fieldsMap.remove("id");
                dataToService.pageInsertDataByTableName(tableArr[i], humpToUnderline(fieldsMap), list);
            }
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

批量更新会做驼峰转下划线，为的是和mysql表中的字段一一对应

 private void migrationUpdateTable(String[] tableArr, String startTime, String endTime, int queryType) {
        for (int i = 0; i < tableArr.length; i++) {
            List<Map<String, Object>> list = dataFromService.getListMap(tableArr[i], new HashMap<>(), startTime, endTime, queryType);
            Map<String, Object> fieldsMap;
            if (list.size() > 0) {
                for (Map<String, Object> e:
                     list) {
                    humpToUnderline(e);
                }
                fieldsMap = list.get(0);
                dataToService.pageUpdateDataByTableName(tableArr[i], humpToUnderline(fieldsMap), list);
            }
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

如果mybatis-config没有配置，可以手动使用如下驼峰转下划线的方法（注意这里一定要用LinkedHashMap，之前用HashMap做插入SQL报错了，其实很好理解，就是字段没有一一对应，所以要确保字段顺序一致性）StrUtil用的是hutool

/**
     * 把 map 中的 key 由驼峰命名转为下划线，使用LinkedHashMap确保字段顺序一致性
     */
    private LinkedHashMap<String, Object> humpToUnderline(Map<String, Object> map) {
        //使用LinkedHashMap确保字段顺序一致性
        LinkedHashMap<String, Object> transitionMap = new LinkedHashMap<>(16);
        map.forEach((k, v) -> transitionMap.put(StrUtil.toUnderlineCase(k), v));
        return transitionMap;
    }
1
2
3
4
5
6
7
8
9

其实就是走dataFromService和dataToService的两个方法，分别做查旧表和批量插入/更新新表

1.dataFromService，分装成map后进入相应的Dao方法，@TargetDataSource是我自定义切换数据源的注解，看我上一篇博客即可

 /**
     * 批量插入数据
     * @author ziqiang.li
     * @param tableName 表名
     * @param queryType query类型，0 createDate,1 createTime,3 modify_date,4 update_time
     */
    @TargetDataSource(connName = "dbTwo")
    public List<Map<String, Object>> getListMap(String tableName, Map<String, Object> fieldsMap, String startTime,String endTime,int queryType){
        Map<String, Object> map = new HashMap<>();
        map.put("tableName", tableName);
        map.put("fieldsMap", fieldsMap);
        map.put("startTime", startTime);
        map.put("endTime", endTime);
        if (queryType==0){
            return dataMigrationDao.queryByCreateDateList(map);
        }else if (queryType==1){
            return dataMigrationDao.queryByCreateTimeList(map);
        }else if (queryType==3){
            return dataMigrationDao.queryByModifyDateList(map);
        }else if (queryType==4){
            return dataMigrationDao.queryByUpdateTimeList(map);
        }
        return new ArrayList<>();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

随便选一个进去，sql很简单没啥好说的，数据量大的话要分页

 	/**
     * 索引create_date取数据
     */
    List<Map<String, Object>> queryByCreateDateList(@Param("map") Map<String,Object> map);
1
2
3
4

<select id="queryByCreateDateList" resultType="java.util.Map">
        SELECT *
        from ${map.tableName} where create_date > #{map.startTime} and create_date &lt; #{map.endTime}
</select>
1
2
3
4

2.dataToService，重点来了，一共两个方法：pageInsertDataByTableName和pageUpdateDataByTableName，且都是分页操作

批量插入

/**
     * 大批量插入,数据分页处理
     * @author ziqiang.li
     * @param tableName 临时表名称
     * @param fieldsMap   临时表属性 Map,key = 临时表字段属性, value = 具体值。此处 value 没有使用
     * @param mapList   临时表的大批量数据
     */
    @Transactional
    public void pageInsertDataByTableName(String tableName, Map<String, Object> fieldsMap, List<Map<String, Object>> mapList) {
        //测试（大批量插入）：条数-耗时：6300条-2s,16800条-5s，24000条-7s 67300条-18s
        for (int i = 0, j = 0, amount = 1000; i < mapList.size(); i += amount) {
            j = i + amount;
            //amount可取值3000/5000/10000/。。。测看大批量效果。
            if (j < mapList.size()) {
                insertBatchList(tableName, fieldsMap, mapList.subList(i, mapList.size()));
            } else {
                insertBatchList(tableName, fieldsMap, mapList.subList(i, mapList.size()));
                break;
            }
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

/**
     * 批量插入数据
     * @author ziqiang.li
     * @param tableName 临时表名称
     * @param fieldsMap   临时表属性 Map,key = 临时表字段属性, value = 具体值。此处 value 没有使用
     * @param list      临时表分页后的批量数据
     */
    private void insertBatchList(String tableName, Map<String, Object> fieldsMap, List<Map<String, Object>> list) {
        Map<String, Object> map = new HashMap<>();
        map.put("tableName", tableName);
        map.put("fieldsMap", fieldsMap);
        map.put("list", list);
        dataMigrationDao.insertBatchTableListDynamic(map);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

然后是dao，按照我的写法即可实现批量插入，一定要注意字段顺序和count一致，不然会报sql语法错误

int insertBatchTableListDynamic(@Param("map") Map<String,Object> map);
1

 <insert id="insertBatchTableListDynamic" parameterType="java.util.HashMap">
        insert into
        ${map.tableName}
        (
        <foreach collection="map.fieldsMap" index="key" item="value"
                 separator=",">
            `${key}`
        </foreach>
        )
        values
        <foreach collection="map.list" item="line" separator=",">
            (
            <foreach collection="line" index="key" item="value"
                     separator=",">
                #{value}
            </foreach>
            )
        </foreach>
</insert>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

批量更新同理

 	@Transactional
    public void pageUpdateDataByTableName(String tableName, Map<String, Object> fieldsMap, List<Map<String, Object>> mapList) {
        for (int i = 0, j = 0, amount = 1000; i < mapList.size(); i += amount) {
            j = i + amount;
            //amount可取值3000/5000/10000/。。。测看大批量效果。
            if (j < mapList.size()) {
                updateBatchList(tableName, fieldsMap, mapList.subList(i, mapList.size()));
            } else {
                updateBatchList(tableName, fieldsMap, mapList.subList(i, mapList.size()));
                break;
            }
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13

private void updateBatchList(String tableName, Map<String, Object> fieldsMap, List<Map<String, Object>> list) {
        Map<String, Object> map = new HashMap<>();
        map.put("tableName", tableName);
        map.put("fieldsMap", fieldsMap);
        map.put("list", list);
        dataMigrationDao.updateBatchTableListDynamic(map);
}
1
2
3
4
5
6
7

dao如下，按找我的写法即可，<choose>标签根据实际需求修改

int updateBatchTableListDynamic(@Param("map") Map<String,Object> map);
1

<update id="updateBatchTableListDynamic" parameterType="java.util.HashMap">
        <foreach collection="map.list" item="line" index="index" open="" close="" separator=";">
            update ${map.tableName}
            <trim prefix="set" suffixOverrides=",">
                <foreach collection="line.entrySet()" item="value" index="key" separator=",">
                    <choose>
                        <when test="key != 'complaint_date' and key != 'close_date' and key != 'create_date' and key != 'modify_date' and key != 'handle_date'
                                and key != 'create_time' and key != 'update_time'">
                            <if test="value != null and value !=''">
                                ${key}= #{value}
                            </if>
                        </when>
                        <otherwise>
                            <if test="value!= null ">
                                ${key}= #{value}
                            </if>
                        </otherwise>
                    </choose>
                </foreach>
            </trim>
            where id = #{line.id}
        </foreach>
</update>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

这里遇到了不少坑，这里做了下整理

批量update一直报语法错误，解决办法如下：
mybatis批量更新报错

还有时间问题，datetime类型的字段是不能<if>标签里直接!=''的
mybatis invalid comparison: java.sql.Timestamp and java.lang.String

我就比较直接所有进行比较了，不清楚mybatis是否可以判断该字段是否是datetime的语法，如果有更好的办法可以改进

其他问题：
解决mybatis #{}无法自动添加引号的错误
mybatis传入List实现批量更新的坑