批量新增/删除

• • 1.代码中foreach insert/update
  • 2.多线程foreach insert/update
  • 3.mybatis xml中foreach
  • 4.mybatis-plus扩展

现工作中有需求要进行批量新增和修改

对比了网上的几种方式

• 代码中foreach insert/update
• 多线程foreach insert/update
• mybatis xml中foreach
• mybatis-plus扩展

第一种就不说了，重复的IO连接与断开效率极低，性能很差，不考虑
第二种使用多线程进行批量插入/修改，时间会大大降低，但还会有频繁建立断开IO，性能不好
第三种其实就是在拼sql，但是不同业务要拼不同的sql，复用性很差 第四种本质也是拼sql，但是通过简单的配置就可以达到不同业务的复用

1.代码中foreach insert/update

 for(int i=0;iofferMapper.insert(offerDO);}

更新同理

2.多线程foreach insert/update

工作中也使用过多线程批量更新，新增同理

//定义线程池
private static final Long KEEP_ALIVE_TIME = 60L;
private static final int APS = Runtime.getRuntime().availableProcessors();
private static final ThreadPoolExecutor THREAD_POOL_EXECUTOR = new ThreadPoolExecutor(APS * 2,APS * 4,KEEP_ALIVE_TIME,TimeUnit.SECONDS,new LinkedBlockingDeque<>(256),new ThreadFactoryBuilder().setNameFormat("分拣出库-pool-%d").build(),new ThreadPoolExecutor.CallerRunsPolicy()
);//使用
try {taskExecute(list, yearList);
} catch (Exception e) {log.error("分拣出库更新失败：{}", e);
}// 处理单个任务数据（year是分库分表用的）
private void taskExecute(List list, List yearList) throws Exception {if (CollectionUtils.isEmpty(list)) {return;}final CountDownLatch latch = new CountDownLatch(list.size());for (SortingOutboundProductDetailDO data : list) {THREAD_POOL_EXECUTOR.submit(() -> {try {//更新从表sortingOutboundProductDetailMapper.update(null,new LambdaUpdateWrapper().eq(SortingOutboundProductDetailDO::getId, data.getId()).in(SortingOutboundProductDetailDO::getYear, yearList).set(SortingOutboundProductDetailDO::getOutboundNumber, data.getOutboundNumber()));} finally {if (latch != null) {latch.countDown();}}});}latch.await();
}

3.mybatis xml中foreach

批量新增

//不用关注里面的业务代码
private int insert(OfferSaveRequest request){List insertOffer = request.getOfferList().stream().map(obj -> {OfferDO offerDO = new OfferDO();offerDO.setId(IdWorker.getId());offerDO.setFirstSubjectId(request.getFirstSubjectId());offerDO.setWarehouseNum(request.getWarehouseNum());offerDO.setExpressCompany(obj.getExpressCompany());offerDO.setExpressCompanyName(obj.getExpressCompanyName());offerDO.setArea(obj.getArea());offerDO.setExpensesItemName(obj.getExpensesItemName());offerDO.setUnit(obj.getUnit());offerDO.setFees(obj.getFees());offerDO.setDescription(obj.getDescription());offerDO.setTransportType(generateTransportType(obj.getExpensesItemName()));offerDO.setCreateTime(new Date());offerDO.setCreateUserId(1L);offerDO.setCreateUserName("管理员");return offerDO;}).collect(Collectors.toList());return offerMapper.batchInsert(insertOffer);
}

xml

INSERT INTO offer(id,first_subject_id,warehouse_num,express_company,express_company_name,area,expenses_item_name,unit,fees,description,create_time,create_user_id,create_user_name)values(#{offer.id},#{offer.firstSubjectId},#{offer.warehouseNum},#{offer.expressCompany},#{offer.expressCompanyName},#{offer.area},#{offer.expensesItemName},#{offer.unit},#{offer.fees},#{offer.description},#{offer.createTime},#{offer.createUserId},#{offer.createUserName})

批量修改

//不用关注里面的业务代码
List updateList = request.getOfferList().stream().filter(obj -> obj.getId() != null).collect(Collectors.toList());
if (updateList.size() > 0) {List updateOffer = updateList.stream().map(obj -> {OfferDO offerDO = new OfferDO();offerDO.setId(obj.getId());offerDO.setArea(obj.getArea());offerDO.setFees(obj.getFees());offerDO.setDescription(obj.getDescription());offerDO.setUpdateTime(new Date());offerDO.setUpdateUserId(1L);offerDO.setUpdateUserName("管理员");return offerDO;}).collect(Collectors.toList());offerMapper.batchUpdate(updateOffer);
}

xml

updateoffersetarea=#{offer.area},fees=#{offer.fees},description=#{offer.description},update_time=#{offer.updateTime},update_user_id=#{offer.updateUserId},update_user_name=#{offer.updateUserName}whereid = #{offer.id}

批量修改还需要在配置文件中配置&allowMultiQueries=true，否则报错

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&allowMultiQueries=true

4.mybatis-plus扩展

1.创建sql注入器

/*** 自定义方法SQL注入器* 【注意】这个类名，可以随便命名*/
public class MyInjector extends DefaultSqlInjector  {/*** 如果只需增加方法，保留MyBatis plus自带方法，* 可以先获取super.getMethodList()，再添加add*/@Overridepublic List getMethodList(Class mapperClass) {// 注意：此SQL注入器继承了DefaultSqlInjector(默认注入器)，调用了DefaultSqlInjector的getMethodList方法，保留了mybatis-plus的自带方法List methodList = super.getMethodList(mapperClass);methodList.add(new InsertBatchMethod());methodList.add(new UpdateBatchMethod());return methodList;}}

2.注入容器

@Configuration
@MapperScan("com.yida.mapper")
public class MybatisPlusPageConfig {@Beanpublic MyInjector myInjector(){return new MyInjector();}
}

3.定义通用mapper

/*** 公共mapper* 要实现批量新增/修改 继承此类** @param */
public interface CommonMapper extends BaseMapper {/*** 自定义批量插入* 如果要自动填充，@Param(xx) xx参数名必须是 list/collection/array 3个的其中之一*/int insertBatch(@Param("list") List list);/*** 自定义批量更新，条件为主键* 如果要自动填充，@Param(xx) xx参数名必须是 list/collection/array 3个的其中之一*/int updateBatch(@Param("list") List list);
}

4.新增/修改

/*** 批量新增*/
@Slf4j
public class InsertBatchMethod extends AbstractMethod {/*** insert into user(id, name, age) values (1, "a", 17), (2, "b", 18);*/@Overridepublic MappedStatement injectMappedStatement(Class mapperClass, Class modelClass, TableInfo tableInfo) {final String sql = "";final String fieldSql = prepareFieldSql(tableInfo);final String valueSql = prepareValuesSql(tableInfo);final String sqlResult = String.format(sql, tableInfo.getTableName(), fieldSql, valueSql);log.debug("sqlResult----->{}", sqlResult);SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);// 第三个参数必须和RootMapper的自定义方法名一致return this.addInsertMappedStatement(mapperClass, modelClass, "insertBatch", sqlSource, new NoKeyGenerator(), null, null);}private String prepareFieldSql(TableInfo tableInfo) {StringBuilder fieldSql = new StringBuilder();fieldSql.append(tableInfo.getKeyColumn()).append(",");tableInfo.getFieldList().forEach(x -> {//新增时修改字段不填充if (!("update_time".equals(x.getColumn()))&&!("update_user_id".equals(x.getColumn()))&&!("update_user_name".equals(x.getColumn()))){fieldSql.append(x.getColumn()).append(",");}});fieldSql.delete(fieldSql.length() - 1, fieldSql.length());fieldSql.insert(0, "(");fieldSql.append(")");return fieldSql.toString();}private String prepareValuesSql(TableInfo tableInfo) {final StringBuilder valueSql = new StringBuilder();valueSql.append("");valueSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},");tableInfo.getFieldList().forEach(x -> {if (!("updateTime".equals(x.getProperty()))&&!("updateUserId".equals(x.getProperty()))&&!("updateUserName".equals(x.getProperty()))){valueSql.append("#{item.").append(x.getProperty()).append("},");}});valueSql.delete(valueSql.length() - 1, valueSql.length());valueSql.append("");return valueSql.toString();}
}

/*** 批量更新方法实现，条件为主键，选择性更新*/
@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;*/@Overridepublic MappedStatement injectMappedStatement(Class mapperClass, Class modelClass, TableInfo tableInfo) {String sql = "";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);}}

5.使用，将原有的继承BaseMapper的方法，改写为继承CommonMapper，后续批量操作，直接使用新增的两个方法进行处理即可。

public interface OfferMapper extends CommonMapper {
}

新增：offerMapper.insertBatch(insertOffer)
更新：offerMapper.updateBatch(updateOffer)