一、批量插入数据SQL
- MySQL批量插入数据SQL
INSERT INTO TABLE_NAME(COLUMN1,COLUMN2...,COLUMNN)VALUES(VALUE1,VALUE2...,VALUEN),(VALUE1,VALUE2...,VALUEN),(VALUE1,VALUE2...,VALUEN);
- Oracle批量插入数据SQL
INSERT ALL
INTO TABLE_NAME(COLUMN1,COLUMN2...,COLUMNN)VALUES(VALUE1,VALUE2...,VALUEN)
INTO TABLE_NAME(COLUMN1,COLUMN2...,COLUMNN)VALUES(VALUE1,VALUE2...,VALUEN)
INTO TABLE_NAME(COLUMN1,COLUMN2...,COLUMNN)VALUES(VALUE1,VALUE2...,VALUEN)
SELECT * FROM DUAL
二、MybatisPlus批量插入实现方式
2.1 通过实现MybatisPlus IService接口，获取saveBatch，底层其实是单条插入
@Transactional(
rollbackFor = {Exception.class}
)
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);
});
}
2.2 通过XML手动拼接SQL实现批量插入
缺点是每个表都要手动编写xml，优点是效率较高
- MySQL
<insert id="batchInsert" parameterType="java.util.List">
insert into user (id, name, age)values
<foreach collection="list" item="user" separator=",">
(#{user.id}, #{user.name}, #{user.age})
</foreach>
</insert>
- Oracle
// mapper.xml
<insert id="batchInsert" parameterType="java.util.List">
insert all
<foreach collection="list" item="user" separator=",">
into user (id, name, age) values(#{user.id}, #{user.name}, #{user.age})
</foreach>
select * from dual
</insert>
2.3 通过使用InsertBatchSomeColumn方法批量插入
底层也是拼接sql，但无需手动编写sql语句，效率同第二种，本文重点介绍这种方式，使用步骤：
2.3.1. 自定义SQL注入器实现DefaultSqlInjector，添加InsertBatchSomeColumn方法
MySQL版
public class MySqlInjector extends DefaultSqlInjector {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
return methodList;
}
}
Oracle版
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import java.util.List;
public class OracleInjector extends DefaultSqlInjector {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass);
//这里改成我们自己的实现MyInsertBatchSomeColumn
methodList.add(new OracleInsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
return methodList;
}
}
2.3.2 编写配置类，把自定义注入器放入spring容器
@Configuration
public class MyBatisConfig {
@Bean
public OracleInjector sqlInjector() {
return new OracleInjector();
}
}
2.3.2 编写自定义BaseMapper,加入InsertBatchSomeColumn方法
public interface MyBaseMapper<T> extends BaseMapper<T> {
/**
* 以下定义的 4个 method 其中 3 个是内置的选装件
*/
int insertBatchSomeColumn(List<T> entityList);
}
2.3.4 需要批量插入的Mapper继承自定义BaseMapper
@Mapper
public interface UserMapper extends MyBaseMapper<Student> {
}
2.3.5 修改适配Oracle
先了解下，Oracle批量插入数据SQL
INSERT ALL
INTO TABLE_NAME(COLUMN1,COLUMN2...,COLUMNN)VALUES(VALUE1,VALUE2...,VALUEN)
INTO TABLE_NAME(COLUMN1,COLUMN2...,COLUMNN)VALUES(VALUE1,VALUE2...,VALUEN)
INTO TABLE_NAME(COLUMN1,COLUMN2...,COLUMNN)VALUES(VALUE1,VALUE2...,VALUEN)
SELECT * FROM DUAL
因此我们需要把SQL组装成这种结构,查看InsertBatchSomeColumn类，可以发现SQL组装逻辑在injectMappedStatement方法，因此我们模仿InsertBatchSomeColumn类，编写SQL组装逻辑
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.core.enums.SqlMethod;
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.extension.injector.methods.InsertBatchSomeColumn;
import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;
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.Map;
import java.util.function.Predicate;
@NoArgsConstructor
@AllArgsConstructor
@SuppressWarnings("serial")
public class OracleInsertBatchSomeColumn extends InsertBatchSomeColumn {
@Setter
@Accessors(chain = true)
private Predicate<TableFieldInfo> predicate;
private final String INSERT_BATCH_SQL="<script>\nINSERT ALL \n %s\n</script>";
@SuppressWarnings("Duplicates")
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
//pojo类型为Map时禁用
if (tableInfo.getEntityType().equals(Map.class)) {
return null;
}
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 columns = insertSqlColumn.substring(0, insertSqlColumn.length() - 1) ;
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 = convertForeach(insertSqlProperty, "list", tableInfo.getTableName(),columns, ENTITY, NEWLINE);
String keyProperty = null;
String keyColumn = null;
// 表包含主键处理逻辑,如果不包含主键当普通字段处理
if (tableInfo.havePK()) {
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(INSERT_BATCH_SQL, valuesScript);
SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
return this.addInsertMappedStatement(mapperClass, modelClass, getMethod(sqlMethod), sqlSource, keyGenerator, keyProperty, keyColumn);
}
public static String convertForeach(final String sqlScript, final String collection, final String tableName,final String columns, final String item, final String separator) {
StringBuilder sb = new StringBuilder("<foreach");
if (StringUtils.isNotBlank(collection)) {
sb.append(" collection=\"").append(collection).append("\"");
}
if (StringUtils.isNotBlank(item)) {
sb.append(" item=\"").append(item).append("\"");
}
if (StringUtils.isNotBlank(separator)) {
sb.append(" separator=\"").append(separator).append("\"");
}
sb.append(">").append("\n");
if (StringUtils.isNotBlank(tableName)) {
sb.append(" INTO ").append(tableName).append(" ");
}
if (StringUtils.isNotBlank(columns)) {
sb.append(LEFT_BRACKET).append(columns).append(RIGHT_BRACKET).append(" VALUES ");
}
return sb.append(sqlScript).append("\n").append("</foreach>\n").append(" SELECT ").append("*").append(" FROM dual").toString();
}
}
执行批量插入，会发现报错
Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='__frch_et_0.serialno', mode=IN, javaType=class java.lang.String, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #2 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 无效的列类型: 1111] with root cause
这是因为字段值为NULL时无法确定jdbcType是什么类型，导致插入失败，有两种解决方法，第一种是指定实体所有属性的jdbcType类型，如
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.Data;
import org.apache.ibatis.type.JdbcType;
import java.io.Serializable;
import java.util.Date;
@SuppressWarnings("serial")
@Data
public class TzVerifyLog extends Model<TzVerifyLog> {
private String id;
@TableField(value = "serialno",jdbcType = JdbcType.VARCHAR)
private String serialno;
@TableField(value = "verify_msg",jdbcType = JdbcType.VARCHAR)
private String verifyMsg;
@TableField(value = "type",jdbcType = JdbcType.VARCHAR)
private String type;
@TableField(value = "row_num",jdbcType = JdbcType.INTEGER)
private Integer rowNum;
@TableField(value = "createtime",jdbcType = JdbcType.DATE)
private Date createtime;
/**
* 获取主键值
*
* @return 主键值
*/
@Override
protected Serializable pkVal() {
return this.id;
}
}
第二种是设置mybatisplus的jdbc-type-for-null属性值
mybatis-plus:
configuration:
jdbc-type-for-null: varchar #空值时设置为varchar类型
2.4 service封装InsertBatchSomeColumn方法
service封装insertBatchSomeColumn方法，方便后面调用
- 新建一个IMyService接口继承IServic
import com.baomidou.mybatisplus.extension.service.IService;
import java.util.List;
public interface IMyService <T> extends IService<T> {
int insertBatchSomeColumn(List<T> entityList);
int insertBatchSomeColumn(List<T> entityList,int batchSize);
}
- 新建一个MyServiceImpl类继承ServiceImpl
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import java.util.ArrayList;
import java.util.List;
public class MyServiceImpl<M extends MyBaseMapper<T>, T>extends ServiceImpl<M,T> implements IMyService<T> {
@Override
public int insertBatchSomeColumn(List<T> entityList) {
return this.baseMapper.insertBatchSomeColumn(entityList);
}
@Override
public int insertBatchSomeColumn(List<T> entityList, int batchSize) {
int size=entityList.size();
if(size<batchSize){
return this.baseMapper.insertBatchSomeColumn(entityList);
}
int page=1;
if(size % batchSize ==0){
page=size/batchSize;
}else {
page=size/batchSize+1;
}
for (int i = 0; i < page; i++) {
List<T> sub = new ArrayList<>();
if(i==page-1){
sub=entityList.subList(i*batchSize, entityList.size());
}else {
sub.subList(i*batchSize,(i+1)*batchSize);
}
if(sub.size()>0){
baseMapper.insertBatchSomeColumn(sub);
}
}
return size;
}
}
- 实体Service接口和接口实现类都分别继承IMyService和MyServiceImpl
public interface ITzVerifyLogService extends IMyService<TzVerifyLog> {
}
import org.springframework.stereotype.Service;
@Service
public class TzVerifyLogServiceImpl extends MyServiceImpl<TzVerifyLogMapper, TzVerifyLog> implements ITzVerifyLogService {
}
