mybatis-plus的批量新增/批量更新以及问题

 for(int i=0;i

//定义线程池 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(); } 

//不用关注里面的业务代码 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); } 

 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); } 

  update offer set  area=#{offer.area},  fees=#{offer.fees},  description=#{offer.description},  update_time=#{offer.updateTime}, update_user_id=#{offer.updateUserId}, update_user_name=#{offer.updateUserName} where id = #{offer.id} 

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

/** * 自定义方法SQL注入器 * 【注意】这个类名，可以随便命名 */ public class MyInjector extends DefaultSqlInjector  { /** * 如果只需增加方法，保留MyBatis plus自带方法， * 可以先获取super.getMethodList()，再添加add */ @Override public 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; } } 

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

/** * 公共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); } 

/** * 批量新增 */ @Slf4j public class InsertBatchMethod extends AbstractMethod { /** * insert into user(id, name, age) values (1, "a", 17), (2, "b", 18);  */ @Override public 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;  */ @Override public 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); } } 

public interface OfferMapper extends CommonMapper { } 

Inserting NULL into a column that has been declared NOT NULL. For multiple-row INSERT statements or INSERT INTO ... SELECT statements, the column is set to the implicit default value for the column data type. This is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. INSERT INTO ... SELECT statements are handled the same way as multiple-row inserts because the server does not examine the result set from the SELECT to see whether it returns a single row. (For a single-row INSERT, no warning occurs when NULL is inserted into a NOT NULL column. Instead, the statement fails with an error.) 在声明为NOT NULL的列中插入NULL。对于多行INSERT语句或 插入…SELECT语句时，该列被设置为该列的隐式默认值 数据类型。数值类型为0，字符串类型为空字符串(")，值为" 0 " 用于日期和时间类型。插入…SELECT语句的处理方式与多行语句相同 插入，因为服务器不检查SELECT的结果集，看它是否返回 单行。(对于单行INSERT，当NULL插入到NOT NULL列时，不会出现警告。 相反，语句失败并报错。) 

select @@sql_mode; 结果：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 

INSERT INTO `oss` VALUES (1, -1, '', '测试用文件.docx', '', '', 0, '', '2022-12-08 16:21:33', 1, '系统管理员', NULL, -1, ''); INSERT INTO `oss` VALUES (2, -1, '', '测试用文件.docx', '', '', 0, '', '2022-12-08 16:32:32', 1, '系统管理员', NULL, -1, ''); INSERT INTO `oss` VALUES (3, -1, '', '测试用文件.docx', '', '', 0, '', '2022-12-08 16:33:17', 1, '系统管理员', NULL, -1, ''); INSERT INTO `oss` VALUES (4, -1, '', '测试用文件.docx', '', '', 0, '', '2022-12-08 16:44:30', 1, '系统管理员', NULL, -1, ''); INSERT INTO `oss` VALUES (5, -1, '', '测试用文件.docx', '', '', 0, '', '2022-12-08 16:45:28', 1, '系统管理员', NULL, -1, ''); INSERT INTO `oss` VALUES (6, -1, '', '合同导入测试.doc','', '', 0, '', '2022-12-08 16:47:03', 1, '系统管理员', NULL, -1, ''); INSERT INTO `oss` VALUES (7, -1, '', '合同导入测试.doc','', '', 0, '', '2022-12-08 16:48:03', 1, '系统管理员', NULL, -1, ''); INSERT INTO `oss` VALUES (8, -1, '', '测试用文件.docx', '', '', 1, '', '2022-12-08 16:49:35', 1, '系统管理员', NULL, -1, ''); INSERT INTO `oss` VALUES (9, -1, '', '新建文本文档.doc','', '', 0, '', '2022-12-08 17:12:36', 1, '系统管理员', NULL, -1, '');