基于Mybatis-Plus扩展批量插入或更新InsertOrUpdateBath

前言： mybatis-plus 是一款很好用的crud基础框架，但是我在api中没有找到插入或者更新，那么我想着基于mybatis-plus 自定义一个方法出来用，因为插入或者更新在字段数量多的时候写xml是非常麻烦的事情。

传统写法：

INSERT INTO test(`id`,`name`,`address`)
VALUES('4','修改10','北京'),
    ( '1', '张三' ,1) 
ON DUPLICATE KEY UPDATE
name=VALUES(name),address=VALUES(address);
 

基于MP的写法，是需要用到MP里面的SQL注入器

MP SQL 注入器文档地址： SQL注入器 | MyBatis-Plus

 1. 新建自定义注解 DuplicateSql

说明：自定义注解是为了插入更新基于那些字段（自定更新字段使用）

@Target(ElementType.FIELD)@Retention(RetentionPolicy.RUNTIME)@Documentedpublic @interface DuplicateSql {        String columnName() default "";}

2. 自定义批量插入更新方法实现

public class MysqlInsertOrUpdateBath extends AbstractMethod {    @Override    public MappedStatement injectMappedStatement(Class mapperClass, Class modelClass, TableInfo tableInfo) {        final String sql = "";        final String tableName = tableInfo.getTableName();        final String filedSql = prepareFieldSql(tableInfo);        final String modelValuesSql = prepareModelValuesSql(tableInfo);        final String modelKeySql = prepareDuplicateKeySql(modelClass);        final String duplicateKeySql = prepareDuplicateKeySql(tableInfo);        final String sqlResult = String.format(sql, tableName, filedSql, modelValuesSql, StringUtils.isBlank(modelKeySql) ? duplicateKeySql : modelKeySql);        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);        return this.addInsertMappedStatement(mapperClass, modelClass, "mysqlInsertOrUpdateBath", sqlSource, new NoKeyGenerator(), null, null);    }        private String prepareDuplicateKeySql(Class modelClass) {        final StringBuilder duplicateKeySql = new StringBuilder();        // 获取所有的字段信息        Field[] declaredFields = modelClass.getDeclaredFields();        for (Field declaredField : declaredFields) {            DuplicateSql duplicateSql = declaredField.getAnnotation(DuplicateSql.class);            if (ObjectUtil.isNotEmpty(duplicateSql)) {                String columnName = duplicateSql.columnName();                duplicateKeySql.append(columnName).append("=values(").append(columnName).append("),");            }        }        if (StringUtils.isNotBlank(duplicateKeySql)) {            duplicateKeySql.delete(duplicateKeySql.length() - 1, duplicateKeySql.length());        }        return duplicateKeySql.toString();    }        private String prepareDuplicateKeySql(TableInfo tableInfo) {        final StringBuilder duplicateKeySql = new StringBuilder();        if (!StringUtils.isEmpty(tableInfo.getKeyColumn())) {            duplicateKeySql.append(tableInfo.getKeyColumn()).append("=values(").append(tableInfo.getKeyColumn()).append("),");        }        tableInfo.getFieldList().forEach(x -> {            duplicateKeySql.append(x.getColumn())                .append("=values(")                .append(x.getColumn())                .append("),");        });        duplicateKeySql.delete(duplicateKeySql.length() - 1, duplicateKeySql.length());        return duplicateKeySql.toString();    }        private String prepareFieldSql(TableInfo tableInfo) {        StringBuilder fieldSql = new StringBuilder();        fieldSql.append(tableInfo.getKeyColumn()).append(",");        tableInfo.getFieldList().forEach(x -> {            fieldSql.append(x.getColumn()).append(",");        });        fieldSql.delete(fieldSql.length() - 1, fieldSql.length());        fieldSql.insert(0, "(");        fieldSql.append(")");        return fieldSql.toString();    }    private String prepareModelValuesSql(TableInfo tableInfo) {        final StringBuilder valueSql = new StringBuilder();        valueSql.append("");        if (!StringUtils.isEmpty(tableInfo.getKeyProperty())) {            valueSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},");        }        tableInfo.getFieldList().forEach(x -> valueSql.append("#{item.").append(x.getProperty()).append("},"));        valueSql.delete(valueSql.length() - 1, valueSql.length());        valueSql.append("");        return valueSql.toString();    }}

3. 注册自定义方法SQL注入器

@Componentpublic class CustomizedSqlInjector extends DefaultSqlInjector {        @Override    public List getMethodList(Class mapperClass, TableInfo tableInfo) {        List methodList = super.getMethodList(mapperClass, tableInfo);        methodList.add(new MysqlInsertOrUpdateBath());        return methodList;    }}

4. 自定义Mapper RootMapper

public interface RootMapper extends BaseMapper {        Boolean mysqlInsertOrUpdateBath(@Param("list") List list);}

来源地址：https://blog.csdn.net/weixin_38982591/article/details/126600572