mybatis-plus 根据任意字段saveOrUpdateBatch
- 场景
- MP源码 （v3.3）
- 改造一 表中有id
- 改造二 表中无id
- 补充
- 更新规则：
场景
- mybatisplus Iservice接口下的saveOrUpdateBatch方法默认是根据主键来决定是要更新还是插入的。很多情况下我们的条件字段不止一个，可能是组合建。
- 下面是以MP V3.3 的版本为例
MP源码 （v3.3）
@Transactional(rollbackFor = Exception.class)
@Override
public boolean saveOrUpdateBatch(Collection<T> entityList, int batchSize) {
Assert.notEmpty(entityList, "error: entityList must not be empty");
Class<?> cls = currentModelClass();
TableInfo tableInfo = TableInfoHelper.getTableInfo(cls);
Assert.notNull(tableInfo, "error: can not execute. because can not find cache of TableInfo for entity!");
String keyProperty = tableInfo.getKeyProperty();
Assert.notEmpty(keyProperty, "error: can not execute. because can not find column for id from entity!");
int size = entityList.size();
executeBatch(sqlSession -> {
int i = 1;
for (T entity : entityList) {
Object idVal = ReflectionKit.getMethodValue(cls, entity, keyProperty);
if (StringUtils.checkValNull(idVal) || Objects.isNull(getById((Serializable) idVal))) {
sqlSession.insert(sqlStatement(SqlMethod.INSERT_ONE), entity);
} else {
MapperMethod.ParamMap<T> param = new MapperMethod.ParamMap<>();
param.put(Constants.ENTITY, entity);
sqlSession.update(sqlStatement(SqlMethod.UPDATE_BY_ID), param);
}
// 不知道以后会不会有人说更新失败了还要执行插入 ????????????
if ((i % batchSize == 0) || i == size) {
sqlSession.flushStatements();
}
i++;
}
});
return true;
}
- 从代码可以看出MP判断是否insert或者update 有两个条件
- 一：判断是否有id (idVal)
- 二：当前id是否能查到值 (getById((Serializable) idVal))
if (StringUtils.checkValNull(idVal) || Objects.isNull(getById((Serializable) idVal))) {
sqlSession.insert(sqlStatement(SqlMethod.INSERT_ONE), entity);
} else {
MapperMethod.ParamMap<T> param = new MapperMethod.ParamMap<>();
param.put(Constants.ENTITY, entity);
sqlSession.update(sqlStatement(SqlMethod.UPDATE_BY_ID), param);
}
改造一 表中有id
假设当前表有id且唯一自增，并且 字段：z1, 字段：z2 , 字段: z3组合键也是唯一的
xxxService:
void saveOrUpdateBatchByZ1AndZ2AndZ3(List<Entity> entityList, int batchSize); // batchSize ：批量更新大小
xxxServiceImpl:
@Override
public void saveOrUpdateBatchByZ1AndZ2AndZ3(List<Entity> entityList, int batchSize) {
Assert.notEmpty(entityList, "error: entityList must not be empty");
Class<?> cls = currentModelClass();
TableInfo tableInfo = TableInfoHelper.getTableInfo(cls);
Assert.notNull(tableInfo, "error: can not execute. because can not find cache of TableInfo for entity!");
String keyProperty = tableInfo.getKeyProperty();
Assert.notEmpty(keyProperty, "error: can not execute. because can not find column for id from entity!");
int size = entityList.size();
executeBatch(sqlSession -> {
int i = 1;
for (Entity entity : entityList) {
LambdaQueryWrapper<Entity> eq = new LambdaQueryWrapper<Entity>()
.eq(Entity::getZ1, entity.getZ1())
.eq(Entity::getZ2, entity.getZ2())
.eq(Entity::getZ3, entity.getZ3());
// z1 + z2 + z3 是唯一组合键， 如果这里报错了，那说明当前键重复。
// 无法避免可以换个查询方法，只要能判断当前组合键能在数据库是否有值就可以了
Entity oneEntity= getOne(eq);
boolean aNull = Objects.isNull(oneEntity);
if (aNull) {
sqlSession.insert(sqlStatement(SqlMethod.INSERT_ONE), entity);
} else {
// 添加主键
entity.setId(oneEntity.getId());
MapperMethod.ParamMap<CgJcjclb> param = new MapperMethod.ParamMap<>();
param.put(Constants.ENTITY, entity);
sqlSession.update(sqlStatement(SqlMethod.UPDATE_BY_ID), param);
}
if ((i % batchSize == 0) || i == size) {
sqlSession.flushStatements();
}
i++;
}
});
}
相较于源码只改变了判断依据，最后在update的时候将id set进去使他可以更具id更新。如果你的表里没有id字段，且唯一条件就是这个三个字段的组合时，你需要更改 SqlMethod
源码中他的 SqlMethod 是 UPDATE_BY_ID
UPDATE_BY_ID("updateById", "根据ID 选择修改数据", "<script>\nUPDATE %s %s WHERE %s=#{%s} %s\n</script>"),
改造二 表中无id
public void saveOrUpdateBatchByZ1AndZ2AndZ3(List<Entity> entityList, int batchSize) {
Assert.notEmpty(entityList, "error: entityList must not be empty");
Class<?> cls = currentModelClass();
TableInfo tableInfo = TableInfoHelper.getTableInfo(cls);
Assert.notNull(tableInfo, "error: can not execute. because can not find cache of TableInfo for entity!");
String keyProperty = tableInfo.getKeyProperty();
Assert.notEmpty(keyProperty, "error: can not execute. because can not find column for id from entity!");
int size = entityList.size();
executeBatch(sqlSession -> {
int i = 1;
for (Entity entity : entityList) {
LambdaQueryWrapper<Entity> eq = new LambdaQueryWrapper<Entity>()
.eq(Entity::getZ1, entity.getZ1())
.eq(Entity::getZ2, entity.getZ2())
.eq(Entity::getZ3, entity.getZ3());
// z1 + z2 + z3 是唯一组合键， 如果这里报错了，那说明当前键重复。
// 无法避免可以换个查询方法，只要能判断当前组合键能在数据库是否有值就可以了
Entity oneEntity= getOne(eq);
boolean aNull = Objects.isNull(oneEntity);
if (aNull) {
sqlSession.insert(sqlStatement(SqlMethod.INSERT_ONE), entity);
} else {
// 修改他的跟新策略
LambdaUpdateWrapper<Entity> updateWrapper = new LambdaUpdateWrapper<Entity>()
.eq(Entity::getZ1, entity.getZ1())
.eq(Entity::getZ2, entity.getZ2())
.eq(Entity::getZ3, entity.getZ3());
Map<String, Object> map = new HashMap<>(2);
map.put(Constants.ENTITY, Entity);
map.put(Constants.WRAPPER, updateWrapper);
sqlSession.update(sqlStatement(SqlMethod.UPDATE), map);
}
if ((i % batchSize == 0) || i == size) {
sqlSession.flushStatements();
}
i++;
}
});
补充
需要修改更新规则
更新规则：
你可以点进SqlMethod里查看，具体规则如下：
INSERT_ONE("insert", "插入一条数据（选择字段插入）", "<script>\nINSERT INTO %s %s VALUES %s\n</script>"),
DELETE_BY_ID("deleteById", "根据ID 删除一条数据", "<script>\nDELETE FROM %s WHERE %s=#{%s}\n</script>"),
DELETE_BY_MAP("deleteByMap", "根据columnMap 条件删除记录", "<script>\nDELETE FROM %s %s\n</script>"),
DELETE("delete", "根据 entity 条件删除记录", "<script>\nDELETE FROM %s %s %s\n</script>"),
DELETE_BATCH_BY_IDS("deleteBatchIds", "根据ID集合，批量删除数据", "<script>\nDELETE FROM %s WHERE %s IN (%s)\n</script>"),
LOGIC_DELETE_BY_ID("deleteById", "根据ID 逻辑删除一条数据", "<script>\nUPDATE %s %s WHERE %s=#{%s} %s\n</script>"),
LOGIC_DELETE_BY_MAP("deleteByMap", "根据columnMap 条件逻辑删除记录", "<script>\nUPDATE %s %s %s\n</script>"),
LOGIC_DELETE("delete", "根据 entity 条件逻辑删除记录", "<script>\nUPDATE %s %s %s %s\n</script>"),
LOGIC_DELETE_BATCH_BY_IDS("deleteBatchIds", "根据ID集合，批量逻辑删除数据", "<script>\nUPDATE %s %s WHERE %s IN (%s) %s\n</script>"),
UPDATE_BY_ID("updateById", "根据ID 选择修改数据", "<script>\nUPDATE %s %s WHERE %s=#{%s} %s\n</script>"),
UPDATE("update", "根据 whereEntity 条件，更新记录", "<script>\nUPDATE %s %s %s %s\n</script>"),
LOGIC_UPDATE_BY_ID("updateById", "根据ID 修改数据", "<script>\nUPDATE %s %s WHERE %s=#{%s} %s\n</script>"),
SELECT_BY_ID("selectById", "根据ID 查询一条数据", "SELECT %s FROM %s WHERE %s=#{%s}"),
SELECT_BY_MAP("selectByMap", "根据columnMap 查询一条数据", "<script>\nSELECT %s FROM %s %s\n</script>"),
SELECT_BATCH_BY_IDS("selectBatchIds", "根据ID集合，批量查询数据", "<script>\nSELECT %s FROM %s WHERE %s IN (%s)\n</script>"),
SELECT_ONE("selectOne", "查询满足条件一条数据", "<script>\nSELECT %s FROM %s %s %s\n</script>"),
SELECT_COUNT("selectCount", "查询满足条件总记录数", "<script>\nSELECT COUNT(%s) FROM %s %s %s\n</script>"),
SELECT_LIST("selectList", "查询满足条件所有数据", "<script>\nSELECT %s FROM %s %s %s\n</script>"),
SELECT_PAGE("selectPage", "查询满足条件所有数据（并翻页）", "<script>\nSELECT %s FROM %s %s %s\n</script>"),
SELECT_MAPS("selectMaps", "查询满足条件所有数据", "<script>\nSELECT %s FROM %s %s %s\n</script>"),
SELECT_MAPS_PAGE("selectMapsPage", "查询满足条件所有数据（并翻页）", "<script>\nSELECT %s FROM %s %s %s\n</script>"),
SELECT_OBJS("selectObjs", "查询满足条件所有数据", "<script>\nSELECT %s FROM %s %s %s\n</script>"),
LOGIC_SELECT_BY_ID("selectById", "根据ID 查询一条数据", "SELECT %s FROM %s WHERE %s=#{%s} %s"),
LOGIC_SELECT_BATCH_BY_IDS("selectBatchIds", "根据ID集合，批量查询数据", "<script>\nSELECT %s FROM %s WHERE %s IN (%s) %s\n</script>");