Mybatis Plus用自定义新增，来实现真正的批量新增方式-尧图网站建设

编写自定义insert和update的SQL 批量处理方法
insert

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.core.enums.SqlMethod;
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.metadata.TableInfoHelper;
import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
import lombok.Setter;
import lombok.experimental.Accessors;
import org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator;
import org.apache.ibatis.executor.keygen.KeyGenerator;
import org.apache.ibatis.executor.keygen.NoKeyGenerator;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;import java.util.List;
import java.util.function.Predicate;/*** @author aaa* @date 2023-09-27 18:27* @description*/
public class InsertBatchColumn extends AbstractMethod {/*** 字段筛选条件*/@Setter@Accessors(chain = true)private Predicate<TableFieldInfo> predicate;public InsertBatchColumn() {super("insertBatch");}public InsertBatchColumn(Predicate<TableFieldInfo> predicate) {// 此处的名称必须与后续的RootMapper的新增方法名称一致super("insertBatch");this.predicate = predicate;}@SuppressWarnings("Duplicates")@Overridepublic MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {KeyGenerator keyGenerator = NoKeyGenerator.INSTANCE;SqlMethod sqlMethod = SqlMethod.INSERT_ONE;List<TableFieldInfo> fieldList = tableInfo.getFieldList();String insertSqlColumn = tableInfo.getKeyInsertSqlColumn(true,false) +this.filterTableFieldInfo(fieldList, predicate, TableFieldInfo::getInsertSqlColumn, EMPTY);String columnScript = LEFT_BRACKET + insertSqlColumn.substring(0, insertSqlColumn.length() - 1) + RIGHT_BRACKET;String insertSqlProperty = tableInfo.getKeyInsertSqlProperty(true,ENTITY_DOT, false) +this.filterTableFieldInfo(fieldList, predicate, i -> i.getInsertSqlProperty(ENTITY_DOT), EMPTY);insertSqlProperty = LEFT_BRACKET + insertSqlProperty.substring(0, insertSqlProperty.length() - 1) + RIGHT_BRACKET;String valuesScript = SqlScriptUtils.convertForeach(insertSqlProperty, "list", null, ENTITY, COMMA);String keyProperty = null;String keyColumn = null;// 表包含主键处理逻辑,如果不包含主键当普通字段处理if (tableInfo.havePK()) {if (tableInfo.getIdType() == IdType.AUTO) {/* 自增主键 */keyGenerator = Jdbc3KeyGenerator.INSTANCE;keyProperty = tableInfo.getKeyProperty();keyColumn = tableInfo.getKeyColumn();} else {if (null != tableInfo.getKeySequence()) {keyGenerator = TableInfoHelper.genKeyGenerator(modelClass.getName(), tableInfo, builderAssistant);keyProperty = tableInfo.getKeyProperty();keyColumn = tableInfo.getKeyColumn();}}}String sql = String.format(sqlMethod.getSql(), tableInfo.getTableName(), columnScript, valuesScript);SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);// 注意第三个参数，需要与后续的RootMapper里面新增方法名称要一致，不然会报无法绑定异常return this.addInsertMappedStatement(mapperClass, modelClass, "insertBatch", sqlSource, keyGenerator, keyProperty, keyColumn);}
}

update

import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;/*** @author aaa* @date 2023-09* @description*/
public class UpdateBatchColumn extends AbstractMethod {/*** @param methodName 方法名* @since 3.5.0*/public UpdateBatchColumn(String methodName) {super(methodName);}@SuppressWarnings("Duplicates")@Overridepublic MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {String sql = "<script>\n<foreach collection=\"list\" item=\"item\" separator=\";\">\nupdate %s %s where %s=#{%s} %s\n</foreach>\n</script>";String additional = tableInfo.isWithVersion() ? tableInfo.getVersionFieldInfo().getVersionOli("item", "item.") : "" + tableInfo.getLogicDeleteSql(true, true);String setSql = sqlSet(tableInfo.isWithLogicDelete(), false, tableInfo, false, "item", "item.");String sqlResult = String.format(sql, tableInfo.getTableName(), setSql, tableInfo.getKeyColumn(), "item." + tableInfo.getKeyProperty(), additional);SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);// 第三个参数必须和RootMapper的自定义方法名一致return this.addUpdateMappedStatement(mapperClass, modelClass, "updateBatch", sqlSource);}}

2,创建一个 SQL 注入器 InsertBatchSqlInjector

import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.example.ftserver.plugin.InsertBatchColumn;
import com.example.ftserver.plugin.UpdateBatchColumn;import java.util.List;/*** @author aaa* @description*/
public class CustomizedSqlInjector extends DefaultSqlInjector {public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {List<AbstractMethod> methods = super.getMethodList(mapperClass,tableInfo);// 自定义的insert SQL注入器methods.add(new InsertBatchColumn());// 自定义的update SQL注入器，参数需要与RootMapper的批量update名称一致methods.add(new UpdateBatchColumn("updateBatch")); return methods;}}

3,将SQL 注入器放入配置类

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;/*** @author aaa* @description*/
@Configuration
@MapperScan(basePackages = "com.example.ftserver.mapper") // 不用配置.*
public class MybatisPlusConfig {@Beanpublic CustomizedSqlInjector sqlInjector(){return new CustomizedSqlInjector();}/*** 添加分页插件*/@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));//如果配置多个插件,切记分页最后添加return interceptor;}
}

4,新建一个在 config 包下创建 RootMapper 接口，让其继承自 Mybatis Plus 提供的 BaseMapper, 并定义批量插入方法

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;import java.util.Collection;/*** @author aaa* @date 2023-09-28 16:11* @description*/
public interface RootMapper<T> extends BaseMapper<T> {// 新增方法int insertBatch(@Param("list")Collection<T> batchList);// 修改方法int updateBatch(@Param("list")Collection<T> batchList);
}

5,新建自己的Mapper，注意继承刚刚自定义的 RootMapper, 而不是Mybatis plus的 BaseMapper :

import com.test.ft.common.entity.PersonEntity;
import com.example.ftserver.plugin.RootMapper;
import org.apache.ibatis.annotations.Mapper;/*** <p>*  Mapper 接口* </p>** @author aaa* @since 2023-10-19 16:32:39*/
public interface PersonMapper extends RootMapper<PersonEntity> {}

批量修改会报错，Mybatis映射文件中的sql语句默认是不支持以" ; " 结尾，也就是不支持多条sql语句的执行。需要在连接mysql的url上加 &allowMultiQueries=true 运行批量操作才可以执行。

如果数据库设置了默认值，用批量添加汇会存null，修改如下：

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.core.enums.SqlMethod;
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.metadata.TableInfoHelper;
import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
import lombok.Setter;
import lombok.experimental.Accessors;
import org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator;
import org.apache.ibatis.executor.keygen.KeyGenerator;
import org.apache.ibatis.executor.keygen.NoKeyGenerator;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;import java.util.List;
import java.util.function.Predicate;/*** @author aaa* @date 2023-09-27 18:27* @description*/
public class InsertBatchColumn extends AbstractMethod {/*** 字段筛选条件*/@Setter@Accessors(chain = true)private Predicate<TableFieldInfo> predicate;public InsertBatchColumn() {super("insertBatch");}public InsertBatchColumn(Predicate<TableFieldInfo> predicate) {// 此处的名称必须与后续的RootMapper的新增方法名称一致super("insertBatch");this.predicate = predicate;}@SuppressWarnings("Duplicates")@Overridepublic MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {KeyGenerator keyGenerator = NoKeyGenerator.INSTANCE;SqlMethod sqlMethod = SqlMethod.INSERT_ONE;List<TableFieldInfo> fieldList = tableInfo.getFieldList();String insertSqlColumn = tableInfo.getKeyInsertSqlColumn(true, false) +this.filterTableFieldInfo(fieldList, predicate, TableFieldInfo::getInsertSqlColumn, EMPTY);String columnScript = LEFT_BRACKET + insertSqlColumn.substring(0, insertSqlColumn.length() - 1) + RIGHT_BRACKET;
/*        String insertSqlProperty = tableInfo.getKeyInsertSqlProperty(true, ENTITY_DOT, false) +this.filterTableFieldInfo(fieldList, predicate, i -> i.getInsertSqlProperty(ENTITY_DOT), EMPTY);*/String insertSqlProperty = tableInfo.getKeyInsertSqlProperty(true, ENTITY_DOT, false) +this.filterTableFieldInfo(fieldList, predicate, i -> getInsertSqlProperty(i, ENTITY_DOT), EMPTY);insertSqlProperty = LEFT_BRACKET + insertSqlProperty.substring(0, insertSqlProperty.length() - 1) + RIGHT_BRACKET;String valuesScript = SqlScriptUtils.convertForeach(insertSqlProperty, "list", null, ENTITY, COMMA);String keyProperty = null;String keyColumn = null;// 表包含主键处理逻辑,如果不包含主键当普通字段处理if (tableInfo.havePK()) {if (tableInfo.getIdType() == IdType.AUTO) {/* 自增主键 */keyGenerator = Jdbc3KeyGenerator.INSTANCE;keyProperty = tableInfo.getKeyProperty();keyColumn = tableInfo.getKeyColumn();} else {if (null != tableInfo.getKeySequence()) {keyGenerator = TableInfoHelper.genKeyGenerator(modelClass.getName(), tableInfo, builderAssistant);keyProperty = tableInfo.getKeyProperty();keyColumn = tableInfo.getKeyColumn();}}}String sql = String.format(sqlMethod.getSql(), tableInfo.getTableName(), columnScript, valuesScript);SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);// 注意第三个参数，需要与后续的RootMapper里面新增方法名称要一致，不然会报无法绑定异常return this.addInsertMappedStatement(mapperClass, modelClass, "insertBatch", sqlSource, keyGenerator, keyProperty, keyColumn);}private String getInsertSqlProperty(TableFieldInfo tableFieldInfo, final String prefix) {String newPrefix = prefix == null ? "" : prefix;String elPart = SqlScriptUtils.safeParam(newPrefix + tableFieldInfo.getEl());//属性为空时使用默认值String result = SqlScriptUtils.convertIf(elPart,String.format("%s != null", newPrefix + tableFieldInfo.getEl()), false)+ SqlScriptUtils.convertIf("default",String.format("%s == null", newPrefix + tableFieldInfo.getEl()), false);return result + ",";}}

总结
以上就是MyBatis plus对MySQL实现批量新增，批量修改的内容，才疏学浅，如有错误，希望多多的指正。需要注意的是：**该方法只对MySQL测试过，其他的数据库支持度不一致，不一定适用

资讯详情

Mybatis Plus用自定义新增，来实现真正的批量新增方式