MybatisPlus更新为null的字段
MybatisPlus更新为null的字段
mybatis-plus在执行更新操作,当更新字段为 空字符串 或者 null 的则不会执行更新。如果要将指定字段更新null,可以通过以下四种方式实现
1、全局配置(不推荐)
可以在 application.yml 配置文件中注入配置 GlobalConfiguration 属性 update-strategy,
将 update-strategy 策略调整为 IGNORED,即忽略判断策略。即可调整全局的验证策略。
# yml 配置:
mybatis-plus:
global-config:
db-config:
update-strategy: IGNORED
2、添加updateStrategy 注解(不推荐)
在实体类中将需要更新为null的字段上添加updateStrategy 注解,并将更新策略设置为IGNORED。
@TableField(value = "name", updateStrategy=FieldStrategy.IGNORED)
private String name;
3、使用UpdateWrapper更新(当属性很多写起来很麻烦)
通过baseMapper的UpdateWrapper将实体类的指定字段set为null。
baseMapper.update(sysUser, Wrappers.<SysUser>lambdaUpdate()
.set(SysUser::getOrgId, null)
.set(SysUser::getOrgName, null)
.eq(SysUser::getId, sysUser.getId())
);
4、Mybatis Plus 自定义SqlInjector sql注入器(推荐)
(1)、自定义sql注入器MySqlInjector
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import java.util.List;
public class MySqlInjector extends DefaultSqlInjector {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
// 根据id更新所有数据
methodList.add(new UpdateWithNullMethod());
return methodList;
}
}
(2)、方法对应的实现类UpdateWithNullMethod
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.FieldStrategy;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
import java.lang.reflect.Field;
import java.util.List;
import static java.util.stream.Collectors.joining;
/**
* @author :zzt
*/
@Slf4j
public class UpdateWithNullMethod extends AbstractMethod {
protected UpdateWithNullMethod() {
super("updateWithNull");
}
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
String sql = "<script>\nupdate %s %s where %s=#{%s}\n</script>";
final List<TableFieldInfo> fieldList = tableInfo.getFieldList();
for (TableFieldInfo tableFieldInfo : fieldList) {
final Class<? extends TableFieldInfo> aClass = tableFieldInfo.getClass();
try {
final Field fieldFill = aClass.getDeclaredField("fieldFill");
fieldFill.setAccessible(true);
fieldFill.set(tableFieldInfo, FieldFill.UPDATE);
} catch (NoSuchFieldException | IllegalAccessException e) {
log.error("获取fieldFill失败", e);
}
}
String setSql = this.sqlSet(tableInfo);
String sqlResult = String.format(sql, tableInfo.getTableName(), setSql, tableInfo.getKeyColumn(), tableInfo.getKeyProperty());
log.debug("sqlResult----->{}", sqlResult);
SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
// 第三个参数必须和rootMapper的自定义方法名一致
return this.addUpdateMappedStatement(mapperClass, modelClass, "updateWithNull", sqlSource);
}
protected String sqlSet(TableInfo table) {
String sqlScript = table.getFieldList()
.stream().map(i -> this.getSqlSet(i, StringPool.EMPTY)).collect(joining(StringPool.NEWLINE));
sqlScript = SqlScriptUtils.convertTrim(sqlScript, "SET", null, null, ",");
return sqlScript;
}
public String getSqlSet(TableFieldInfo i, String prefix) {
String newPrefix = prefix == null ? StringPool.EMPTY : prefix;
String column = i.getColumn();
String update = i.getUpdate();
FieldFill fieldFill = i.getFieldFill();
String el = i.getEl();
// 默认:column=
String sqlSet = column + StringPool.EQUALS;
if (StringUtils.isNotEmpty(update)) {
sqlSet += String.format(update, column);
} else {
sqlSet += SqlScriptUtils.safeParam(newPrefix +el);
}
sqlSet += StringPool.COMMA;
if (fieldFill == FieldFill.UPDATE || fieldFill == FieldFill.INSERT_UPDATE) {
// 不进行if包裹
return sqlSet;
}
return convertIf(sqlSet, convertIfProperty(newPrefix, column), i.getUpdateStrategy(),
i.getPropertyType().isPrimitive(), StringUtils.isCharSequence(i.getPropertyType()));
}
private String convertIfProperty(String prefix, String property) {
return StringUtils.isNotBlank(prefix)
? prefix.substring(0, prefix.length() - 1) + "['" + property + "']" : property;
}
private String convertIf(final String sqlScript, final String property, final FieldStrategy fieldStrategy,
boolean isPrimitive, boolean isCharSequence) {
if (fieldStrategy == FieldStrategy.NEVER) {
return null;
}
if (isPrimitive || fieldStrategy == FieldStrategy.IGNORED) {
return sqlScript;
}
if (fieldStrategy == FieldStrategy.NOT_EMPTY && isCharSequence) {
return SqlScriptUtils.convertIf(sqlScript, String.format("%s != null and %s !=''", property, property), false);
}
return SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", property), false);
}
}
(2)、自定义基础Mapper继承BaseMapper
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
/**
* @description: 自定义mapper借口,mybatisPlus
* @author: zzt
*/
public interface MyBaseMapper<T> extends BaseMapper<T> {
/**
* 全字段更新,更新为null的字段
* @param obj 待更新对象
* @return 是否成功
*/
boolean updateWithNull(T obj);
}
最后直接调用即可。