文章目录
- 前言
- 一、mp的批量插入是假的
- 二、真正的批量插入
- 1.利用sql注入器处理
- 2.采用自编码,编写xml批量执行
- 生成内容如下:
- 三 问题
- 问题描述
- 问题原因
- 问题解决
- 粘贴一份,兼容集合
- 替换原有文件
- 总结
- 自增与非自增区别:
前言
mybatis/mp 在实际开发中是常用的优秀持久层框架,但是在非自增主键的时候,单条数据插入式可以的,当批量插入的时候,如何做到填充主键呢?
这里的批量插入是指执行真正的批量插入!
一、mp的批量插入是假的
mp中细心的小伙伴会发现,批量插入是假的,以下是mybatis-plus的源码
public boolean saveBatch(Collection<T> entityList, int batchSize) {String sqlStatement = this.getSqlStatement(SqlMethod.INSERT_ONE);return this.executeBatch(entityList, batchSize, (sqlSession, entity) -> {sqlSession.insert(sqlStatement, entity);});}
问题就出在这里: 循环调用的insert方法,不是insert into values
我想做的是做一个真正的批量执行,然后插入,但是问题是,当插入的时候,非自增主键没有被填充!!!
二、真正的批量插入
1.利用sql注入器处理
可以参考我的另一篇文章实现mybatis-plus 批量插入修改
2.采用自编码,编写xml批量执行
- 可以采用easycode生成器,选择最新修复版本,会自动生成xml批量插入
- 直接手写
生成内容如下:
- mapper/dao
/*** 批量新增数据（MyBatis原生foreach方法）** @param entities List<Goods> 实例对象列表* @return 影响行数*/int insertBatch(@Param("entities") List<Goods> entities);
- xml
<!-- 批量插入 --><insert id="insertBatch" keyProperty="id">insert into dbo.goods(id,name, code, price)values<foreach collection="entities" item="entity" separator=",">(#{entity.id},#{entity.name}, #{entity.code}, #{entity.price})</foreach></insert>
这就能直接调用生成的 insertBatch 去执行真正的批量执行了!
三 问题
问题描述
虽然如上两种实现了批量插入,但是都有问题, 批量插入无法生成id,导致插入失败,因为主键不能为空
问题原因
经过不断 断点 跟踪mybatis执行,发现在 MybatisParameterHandler 中,如下代码有问题:
private void process(Object parameter) {if (parameter != null) {TableInfo tableInfo = null;Object entity = parameter;if (parameter instanceof Map) {Map<?, ?> map = (Map)parameter;if (map.containsKey("et")) {Object et = map.get("et");if (et != null) {entity = et;tableInfo = TableInfoHelper.getTableInfo(et.getClass());}}} else {tableInfo = TableInfoHelper.getTableInfo(parameter.getClass());}if (tableInfo != null) {MetaObject metaObject = this.configuration.newMetaObject(entity);if (SqlCommandType.INSERT == this.sqlCommandType) {this.populateKeys(tableInfo, metaObject, entity);this.insertFill(metaObject, tableInfo);} else {this.updateFill(metaObject, tableInfo);}}}}
其中问题为:
if (parameter instanceof Map) {Map<?, ?> map = (Map)parameter;if (map.containsKey("et")) {Object et = map.get("et");if (et != null) {entity = et;tableInfo = TableInfoHelper.getTableInfo(et.getClass());}}} else {tableInfo = TableInfoHelper.getTableInfo(parameter.getClass());}
此处
map.containsKey("et")不对,因为我这里是批量插入,传入的不是et,就算我把参数名称改为et也不行
因为我是批量执行,这里获取到的应该是一个集合,list
但是这里是按照一个对象处理的TableInfoHelper.getTableInfo(et.getClass())
所以一直获取不到tableInfo ,导致后续无法执行填充主键逻辑
问题解决
思路:
- 将MybatisParameterHandler 重新粘贴一份,然后修改上述的问题,增加判断逻辑,处理集合;
- 将当前的文件替换原有文件,使得mybatis执行的时候,走我这份文件即可(覆盖之前不兼容集合的文件)
粘贴一份,兼容集合
package com.baomidou.mybatisplus.core;import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.core.incrementer.IdentifierGenerator;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import com.baomidou.mybatisplus.core.toolkit.*;
import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeException;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;/*** @author fulin* @since 2023/9/20 17:38*/
public class MybatisParameterHandler implements ParameterHandler {private final TypeHandlerRegistry typeHandlerRegistry;private final MappedStatement mappedStatement;private final Object parameterObject;private final BoundSql boundSql;private final Configuration configuration;private final SqlCommandType sqlCommandType;public MybatisParameterHandler(MappedStatement mappedStatement, Object parameter, BoundSql boundSql) {this.typeHandlerRegistry = mappedStatement.getConfiguration().getTypeHandlerRegistry();this.mappedStatement = mappedStatement;this.boundSql = boundSql;this.configuration = mappedStatement.getConfiguration();this.sqlCommandType = mappedStatement.getSqlCommandType();this.parameterObject = processParameter(parameter);}public Object processParameter(Object parameter) {/* 只处理插入或更新操作 */if (parameter != null&& (SqlCommandType.INSERT == this.sqlCommandType || SqlCommandType.UPDATE == this.sqlCommandType)) {//检查 parameterObjectif (ReflectionKit.isPrimitiveOrWrapper(parameter.getClass())|| parameter.getClass() == String.class) {return parameter;}Collection<Object> parameters = getParameters(parameter);if (null != parameters) {// 感觉这里可以稍微优化一下，理论上都是同一个.parameters.forEach(this::process);} else {process(parameter);}}return parameter;}@Overridepublic Object getParameterObject() {return this.parameterObject;}private void process(Object parameter) {if (parameter != null) {TableInfo tableInfo = null;Object entity = parameter;if (parameter instanceof Map) {Map<?, ?> map = (Map<?, ?>) parameter;if (map.containsKey(Constants.ENTITY)) {Object et = map.get(Constants.ENTITY);if (et != null) {entity = et;tableInfo = TableInfoHelper.getTableInfo(entity.getClass());}}if (map.containsKey("entities")) {List list = (List<Object>) map.get("entities");if (CollectionUtils.isEmpty(list)) {return;}Optional first = list.stream().findFirst();if (!first.isPresent()) {return;}entity = first.get();tableInfo = TableInfoHelper.getTableInfo(entity.getClass());}} else {tableInfo = TableInfoHelper.getTableInfo(parameter.getClass());}if (tableInfo != null) {//到这里就应该转换到实体参数对象了,因为填充和ID处理都是争对实体对象处理的,不用传递原参数对象下去.MetaObject metaObject = this.configuration.newMetaObject(entity);if (SqlCommandType.INSERT == this.sqlCommandType) {populateKeys(tableInfo, metaObject, entity);insertFill(metaObject, tableInfo);} else {updateFill(metaObject, tableInfo);}}}}protected void populateKeys(TableInfo tableInfo, MetaObject metaObject, Object entity) {final IdType idType = tableInfo.getIdType();final String keyProperty = tableInfo.getKeyProperty();if (StringUtils.isNotBlank(keyProperty) && null != idType && idType.getKey() >= 3) {final IdentifierGenerator identifierGenerator = GlobalConfigUtils.getGlobalConfig(this.configuration).getIdentifierGenerator();Object idValue = metaObject.getValue(keyProperty);if (StringUtils.checkValNull(idValue)) {if (idType.getKey() == IdType.ASSIGN_ID.getKey()) {if (Number.class.isAssignableFrom(tableInfo.getKeyType())) {metaObject.setValue(keyProperty, identifierGenerator.nextId(entity));} else {metaObject.setValue(keyProperty, identifierGenerator.nextId(entity).toString());}} else if (idType.getKey() == IdType.ASSIGN_UUID.getKey()) {metaObject.setValue(keyProperty, identifierGenerator.nextUUID(entity));}}}}protected void insertFill(MetaObject metaObject, TableInfo tableInfo) {GlobalConfigUtils.getMetaObjectHandler(this.configuration).ifPresent(metaObjectHandler -> {if (metaObjectHandler.openInsertFill()) {if (tableInfo.isWithInsertFill()) {metaObjectHandler.insertFill(metaObject);} else {// 兼容旧操作 id类型为input或none的要用填充器处理一下if (metaObjectHandler.compatibleFillId()) {String keyProperty = tableInfo.getKeyProperty();if (StringUtils.isNotBlank(keyProperty)) {Object value = metaObject.getValue(keyProperty);if (value == null && (IdType.NONE == tableInfo.getIdType() || IdType.INPUT == tableInfo.getIdType())) {metaObjectHandler.insertFill(metaObject);}}}}}});}protected void updateFill(MetaObject metaObject, TableInfo tableInfo) {GlobalConfigUtils.getMetaObjectHandler(this.configuration).ifPresent(metaObjectHandler -> {if (metaObjectHandler.openUpdateFill() && tableInfo.isWithUpdateFill()) {metaObjectHandler.updateFill(metaObject);}});}/*** 处理正常批量插入逻辑* <p>* org.apache.ibatis.session.defaults.DefaultSqlSession$StrictMap 该类方法* wrapCollection 实现 StrictMap 封装逻辑* </p>** @return 集合参数*/@SuppressWarnings({"rawtypes", "unchecked"})protected Collection<Object> getParameters(Object parameterObject) {Collection<Object> parameters = null;if (parameterObject instanceof Collection) {parameters = (Collection) parameterObject;} else if (parameterObject instanceof Map) {Map parameterMap = (Map) parameterObject;if (parameterMap.containsKey("collection")) {parameters = (Collection) parameterMap.get("collection");} else if (parameterMap.containsKey("list")) {parameters = (List) parameterMap.get("list");} else if (parameterMap.containsKey("array")) {parameters = Arrays.asList((Object[]) parameterMap.get("array"));}}return parameters;}@Override@SuppressWarnings("unchecked")public void setParameters(PreparedStatement ps) {ErrorContext.instance().activity("setting parameters").object(this.mappedStatement.getParameterMap().getId());List<ParameterMapping> parameterMappings = this.boundSql.getParameterMappings();if (parameterMappings != null) {for (int i = 0; i < parameterMappings.size(); i++) {ParameterMapping parameterMapping = parameterMappings.get(i);if (parameterMapping.getMode() != ParameterMode.OUT) {Object value;String propertyName = parameterMapping.getProperty();if (this.boundSql.hasAdditionalParameter(propertyName)) { // issue #448 ask first for additional paramsvalue = this.boundSql.getAdditionalParameter(propertyName);} else if (this.parameterObject == null) {value = null;} else if (this.typeHandlerRegistry.hasTypeHandler(this.parameterObject.getClass())) {value = parameterObject;} else {MetaObject metaObject = this.configuration.newMetaObject(this.parameterObject);value = metaObject.getValue(propertyName);}TypeHandler typeHandler = parameterMapping.getTypeHandler();JdbcType jdbcType = parameterMapping.getJdbcType();if (value == null && jdbcType == null) {jdbcType = this.configuration.getJdbcTypeForNull();}try {typeHandler.setParameter(ps, i + 1, value, jdbcType);} catch (TypeException | SQLException e) {throw new TypeException("Could not set parameters for mapping: " + parameterMapping + ". Cause: " + e, e);}}}}}
}这里面仅仅加了 如下这一段,其他完全复制
if (map.containsKey("entities")) {List list = (List<Object>) map.get("entities");if (CollectionUtils.isEmpty(list)) {return;}Optional first = list.stream().findFirst();if (!first.isPresent()) {return;}entity = first.get();tableInfo = TableInfoHelper.getTableInfo(entity.getClass());}
替换原有文件
在本地项目中创建于mybatis中MybatisParameterHandler 同包目录,再将同名文件放入即可
这样就可以通过本地文件,覆盖jar包中的文件了
总结
对于此次的问题,是因为之前一直用的是自增主键,今天改为非自增主键,导致该问题;
自增与非自增区别:
- 自增: 数据库层面的ID填充
- 非自增: 代码层面的数据ID填充,需要在插入之前就获取到ID,并填充到实体中
此次问题解决,对于mybatis执行流程也有了更加清晰的认知,与君共勉~
文中涉及所有代码: 代码地址
