mybatis根据表名动态批量新增，批量更新的实现方法（附排坑过程+完整源码完全可用）
发布时间：2023-06-05 18:00
项目中迁移的需求用到了批量新增，批量更新来做迁移的需求，为了方便以后的迁移，这里写了一套完整的方法来实现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 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);
}
}
}
批量更新会做驼峰转下划线，为的是和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);
}
}
}
如果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;
}
其实就是走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<>();
}
随便选一个进去，sql很简单没啥好说的，数据量大的话要分页
/**
* 索引create_date取数据
*/
List<Map<String, Object>> queryByCreateDateList(@Param("map") Map<String,Object> map);
<select id="queryByCreateDateList" resultType="java.util.Map">
SELECT *
from ${map.tableName} where create_date > #{map.startTime} and create_date < #{map.endTime}
</select>
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;
}
}
}
/**
* 批量插入数据
* @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);
}
然后是dao，按照我的写法即可实现批量插入，一定要注意字段顺序和count一致，不然会报sql语法错误
int insertBatchTableListDynamic(@Param("map") Map<String,Object> map);
<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>
批量更新同理
@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;
}
}
}
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);
}
dao如下，按找我的写法即可，<choose>标签根据实际需求修改
int updateBatchTableListDynamic(@Param("map") Map<String,Object> map);
<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>
这里遇到了不少坑，这里做了下整理
批量update一直报语法错误，解决办法如下：
mybatis批量更新报错
还有时间问题，datetime类型的字段是不能!=''的
mybatis invalid comparison: java.sql.Timestamp and java.lang.String
我就比较直接所有进行比较了，不清楚mybatis是否可以判断该字段是否是datetime的语法，如果有更好的办法可以改进
其他问题：
解决mybatis #{}无法自动添加引号的错误
mybatis传入List实现批量更新的坑