不支持在一条sql语句中插入多行

mybatisPlus批量保存报错 ： java.sql.SQLException: does not support insert multiple rows in one sql statement

用的BatchInsert逐行插入的？

mybatisPlus的插入可以直接saveBatch插入一个list集合

• 这篇博客也许可以解决你的问题👉 ：MybatisPlus 批量更新 异常 java.sql.SQLException: sql injection violation, multi-statement
• 以下回答由chatgpt基于相关博客总结生成:

  针对mybatisPlus批量保存数据时出现"不支持在一条sql语句中插入多行"错误，可以采用以下两种方案解决：

  1. 利用mybatisPlus提供的BatchExecutor进行批量操作。示例代码如下：

  List<T> entityList = new ArrayList<>(); //准备的需要批量保存的实体类List
  SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH);
  try{
    for(int i = 0; i < entityList.size(); i++){
      sqlSession.insert(entityName + "_mapper.insert", entityList.get(i));
      if(i % batchSize == 0){
        sqlSession.flushStatements();
        sqlSession.clearCache();
      }
    }
    sqlSession.flushStatements();
    sqlSession.commit();
  }finally{
    sqlSession.close();
  }
  

  1. 自定义BatchInsertMapper并在mybatisPlus配置文件中注入该mapper。在该mapper中利用foreach循环进行批量操作，以达到不在一条sql语句中插入多行的效果。示例代码如下：

  public interface BatchInsertMapper<T> extends BaseMapper<T> {
    /**
     * 批量插入方法
     *
     * @param list 批量插入的List
     * @return 插入的条数
     */
    @InsertProvider(type = BatchInsertProvider.class, method = "dynamicSQL")
    int batchInsert(List<T> list);
  }
  
  public class BatchInsertProvider extends SqlProviderSupport {
    public String batchInsert(Map<?, ?> map) {
      List<?> list = (List<?>) map.get("list");
      if (CollectionUtils.isEmpty(list)) {
        throw new RuntimeException("batch insert list is empty!");
      }
      Class<?> clazz = list.get(0).getClass();
      String tableName = getTableName(clazz);
      StringBuilder sql = new StringBuilder();
      sql.append("INSERT INTO ").append(tableName).append(" (");
      List<String> columns = getColumns(clazz, false);
      sql.append(CollUtil.join(columns, ","));
      sql.append(") VALUES ");
      String foreachSql = "list[" + map.get("index") + "]";
      sql.append("<foreach collection=\"").append(foreachSql).append("\" item=\"i\" index=\"index\" separator=\",\">(");
      for (int i = 0; i < columns.size(); i++) {
        sql.append("#{i.").append(columns.get(i)).append("},");
      }
      sql.deleteCharAt(sql.lastIndexOf(","));
      sql.append(")</foreach>");
      return sql.toString();
    }
  }
  

  在mybatisPlus配置文件中注入BatchInsertMapper：

  @Bean
  public BatchInsertMapper batchInsertMapper() {
    return new BatchInsertMapper() {};
  }
  
  @Bean
  public MybatisPlusInterceptor mybatisPlusInterceptor() {
    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
    interceptor.addInnerInterceptor(new TenantLineInnerInterceptor()); //多租户插件
    interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor()); //乐观锁插件
    interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); //分页插件
    return interceptor;
  }
  
  @Bean
  public SqlSessionFactory sqlSessionFactory() throws Exception {
    MybatisSqlSessionFactoryBean factoryBean = new MybatisSqlSessionFactoryBean();
    factoryBean.setDataSource(dataSource);
    factoryBean.setPlugins(mybatisPlusInterceptor());
    factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/**/*.xml"));
    factoryBean.setTypeHandlers(new TypeHandler<?>[]{new MybatisLongEnumTypeHandler<>()});
    GlobalConfig globalConfig = new GlobalConfig();
    globalConfig.setBanner(false);
    globalConfig.setMetaObjectHandler(new MyMetaObjectHandler());
    factoryBean.setGlobalConfig(globalConfig);
    return factoryBean.getObject();
  }