使用 mybatis-plus 来进行批量新增和更新时，你会发现其实是一条条 sql 执行, 下面进行优化。
- 添加 InsertBatchMethod 和 UpdateBatchMethod 类
package com.liboshuai.mall.tiny.compone.handler.mybatisPlus;
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 lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
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: liboshuai
* @Date: 2022-11-28 13:55
* @Description: 自定义mybatisPlus批量插入方法
*/
@Slf4j
public class InsertBatchMethod extends AbstractMethod {
/**
* 字段筛选条件
*/
@Setter
@Accessors(chain = true)
private Predicate<TableFieldInfo> predicate;
@SuppressWarnings("Duplicates")
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
KeyGenerator keyGenerator = new NoKeyGenerator();
SqlMethod sqlMethod = SqlMethod.INSERT_ONE;
List<TableFieldInfo> fieldList = tableInfo.getFieldList();
String insertSqlColumn = tableInfo.getKeyInsertSqlColumn(false) +
this.filterTableFieldInfo(fieldList, predicate, TableFieldInfo::getInsertSqlColumn, EMPTY);
String columnScript = LEFT_BRACKET + insertSqlColumn.substring(0, insertSqlColumn.length() - 1) + RIGHT_BRACKET;
String insertSqlProperty = tableInfo.getKeyInsertSqlProperty(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 (StringUtils.isNotBlank(tableInfo.getKeyProperty())) {
if (tableInfo.getIdType() == IdType.AUTO) {
/* 自增主键 */
keyGenerator = new Jdbc3KeyGenerator();
keyProperty = tableInfo.getKeyProperty();
keyColumn = tableInfo.getKeyColumn();
} else {
if (null != tableInfo.getKeySequence()) {
keyGenerator = TableInfoHelper.genKeyGenerator(getMethod(sqlMethod), 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);
return this.addInsertMappedStatement(mapperClass, modelClass, getMethod(sqlMethod), sqlSource, keyGenerator, keyProperty, keyColumn);
}
@Override
public String getMethod(SqlMethod sqlMethod) {
// 自定义 mapper 方法名
return "insertBatch";
}
}
package com.liboshuai.mall.tiny.compone.handler.mybatisPlus;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
/**
* @Author: liboshuai
* @Date: 2022-11-28 13:56
* @Description: 自定义mybatisPlus批量更新方法
*/
@Slf4j
public class UpdateBatchMethod extends AbstractMethod {
/**
* update user set name = "a", age = 17 where id = 1;
* update user set name = "b", age = 18 where id = 2;
<script>
<foreach collection="list" item="item" separator=";">
update user
<set>
<if test="item.name != null and item.name != ''">
name = #{item.name,jdbcType=VARCHAR},
</if>
<if test="item.age != null">
age = #{item.age,jdbcType=INTEGER},
</if>
</set>
where id = #{item.id,jdbcType=INTEGER}
</foreach>
</script>
*/
@Override
public 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);
log.debug("sqlResult----->{}", sqlResult);
SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
// 第三个参数必须和RootMapper的自定义方法名一致
return this.addUpdateMappedStatement(mapperClass, modelClass, "updateBatch", sqlSource);
}
}
- 添加自定义方法 SQL 注入器
package com.liboshuai.mall.tiny.compone.handler.mybatisPlus;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import java.util.List;
/**
* @Author: liboshuai
* @Date: 2022-11-28 13:58
* @Description: mybatisPlus自定义sql注入器
*/
public class CustomizedSqlInjector extends DefaultSqlInjector {
/**
* 如果只需增加方法，保留mybatis plus自带方法，
* 可以先获取super.getMethodList()，再添加add
*/
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass);
methodList.add(new InsertBatchMethod());
methodList.add(new UpdateBatchMethod());
return methodList;
}
}
- 注入配置
package com.liboshuai.mall.tiny.compone.config;
import com.liboshuai.mall.tiny.compone.handler.mybatisPlus.CustomizedSqlInjector;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* @Author: liboshuai
* @Date: 2022-11-28 14:17
* @Description: 自定义mybatisPlus配置类
*/
// 如果启动类上已经有@MapperScan这个注解了, 那么这里就不需要了(推荐在启动类上添加)
@MapperScan("com.xxx.mapper")
@Configuration
public class MybatisPlusConfig {
@Bean
public CustomizedSqlInjector customizedSqlInjector() {
return new CustomizedSqlInjector();
}
}
- 添加通用 mapper
package com.liboshuai.mall.tiny.common.base;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.springframework.data.repository.query.Param;
import java.util.List;
/**
* @Author: liboshuai
* @Date: 2022-11-28 13:59
* @Description: 自定义myabtisPlusMapper
*/
public interface RootMapper<T> extends BaseMapper<T> {
/**
* 自定义批量插入
* 如果要自动填充，@Param(xx) xx参数名必须是 list/collection/array 3个的其中之一
*/
int insertBatch(@Param("list") List<T> list);
/**
* 自定义批量更新，条件为主键
* 如果要自动填充，@Param(xx) xx参数名必须是 list/collection/array 3个的其中之一
*/
int updateBatch(@Param("list") List<T> list);
}
- 如何使用
@Repository
public interface UserInfoMapper extends RootMapper<UserInfo> {
}
public interface UserInfoService extends IService<UserInfo> {
int saveAll();
int updateAll();
}
@Service
public class UserInfoServiceImpl extends ServiceImpl<UserInfoMapper, UserInfo> implements UserInfoService{
@Override
public int saveAll() {
List<UserInfo> list = new ArrayList<>();
for (int i = 0; i < 20; i++) {
UserInfo userInfo = new UserInfo();
userInfo.setUserName("厉害" + i);
userInfo.setSalt(RandomStringUtils.randomAlphabetic(6));
userInfo.setPassword(SecureUtil.sha256("123456" + userInfo.getSalt()));
userInfo.setSex(0);
userInfo.setAvatar(LoginServiceImpl.AVATAR);
list.add(userInfo);
}
return baseMapper.insertBatch(list);
}
@Override
public int updateAll() {
List<UserInfo> userInfos = baseMapper.selectList(Wrappers.<UserInfo>lambdaQuery().between(BaseEntity::getId, 43, 62));
userInfos.forEach(userInfo -> {
userInfo.setUserName("更新了" + IdUtil.simpleUUID());
});
return baseMapper.updateBatch(userInfos);
}
}
- 最后最重要的一点: jdbc链接Url要追加
allowMultiQueries=true参数 (这一点坑死我了, 一直报错).
