Java怎么实现几十万条数据插入(30万条数据插入MySQL仅需13秒)

本文主要讲述通过MyBatis、JDBC等做大数据量数据插入的案例和结果。

30万条数据插入插入数据库验证

• • 实体类、mapper和配置文件定义
  • • User实体
    • mapper接口
    • mapper.xml文件
    • jdbc.properties
    • sqlMapConfig.xml
    • 不分批次直接梭哈
    • 循环逐条插入
    • MyBatis实现插入30万条数据
    • JDBC实现插入30万条数据
    • 总结

      验证的数据库表结构如下：

      CREATE TABLE `t_user` (
        `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
        `username` varchar(64) DEFAULT NULL COMMENT '用户名称',
        `age` int(4) DEFAULT NULL COMMENT '年龄',
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表';
      

      话不多说，开整！

      实体类、mapper和配置文件定义

      User实体

      /**
       * 
      用户实体
       *
       * @Author zjq
       * @Date 2021/8/3
       */
      @Data
      public class User {
          private int id;
          private String username;
          private int age;
      }
      

      mapper接口

      public interface UserMapper {
          /**
           * 批量插入用户
           * @param userList
           */
          void batchInsertUser(@Param("list") List userList);
      }
      

      mapper.xml文件

          
          
              insert into t_user(username,age) values
              
                  (
                  #{item.username},
                  #{item.age}
                  )
              
          
      

      jdbc.properties

      jdbc.driver=com.mysql.jdbc.Driver
      jdbc.url=jdbc:mysql://localhost:3306/test
      jdbc.username=root
      jdbc.password=root
      

      sqlMapConfig.xml

      不分批次直接梭哈

      MyBatis直接一次性批量插入30万条，代码如下：

          @Test
          public void testBatchInsertUser() throws IOException {
              InputStream resourceAsStream =
                      Resources.getResourceAsStream("sqlMapConfig.xml");
              SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
              SqlSession session = sqlSessionFactory.openSession();
              System.out.println("===== 开始插入数据 =====");
              long startTime = System.currentTimeMillis();
              try {
                  List userList = new ArrayList();
                  for (int i = 1; i 
                      User user = new User();
                      user.setId(i);
                      user.setUsername("共饮一杯无 " + i);
                      user.setAge((int) (Math.random() * 100));
                      userList.add(user);
                  }
                  session.insert("batchInsertUser", userList); // 最后插入剩余的数据
                  session.commit();
                  long spendTime = System.currentTimeMillis()-startTime;
                  System.out.println("成功插入 30 万条数据,耗时："+spendTime+"毫秒");
              } finally {
                  session.close();
              }
          }
      

      p可以看到控制台输出：/p blockquote pCause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (27759038 >yun 4194304). You can change this value on the server by setting the max_allowed_packet’ variable.

      

      超出最大数据包限制了，可以通过调整max_allowed_packet限制来提高可以传输的内容，不过由于30万条数据超出太多，这个不可取，梭哈看来是不行了 😅😅😅

      既然梭哈不行那我们就一条一条循环着插入行不行呢

      循环逐条插入

      mapper接口和mapper文件中新增单个用户新增的内容如下:

          /**
           * 新增单个用户
           * @param user
           */
          void insertUser(User user);
      

          
          
              insert into t_user(username,age) values
                  (
                  #{username},
                  #{age}
                  )
          
      

      调整执行代码如下：

          @Test
          public void testCirculateInsertUser() throws IOException {
              InputStream resourceAsStream =
                      Resources.getResourceAsStream("sqlMapConfig.xml");
              SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
              SqlSession session = sqlSessionFactory.openSession();
              System.out.println("===== 开始插入数据 =====");
              long startTime = System.currentTimeMillis();
              try {
                  for (int i = 1; i 
                      User user = new User();
                      user.setId(i);
                      user.setUsername("共饮一杯无 " + i);
                      user.setAge((int) (Math.random() * 100));
                      // 一条一条新增
                      session.insert("insertUser", user);
                      session.commit();
                  }
                  long spendTime = System.currentTimeMillis()-startTime;
                  System.out.println("成功插入 30 万条数据,耗时："+spendTime+"毫秒");
              } finally {
                  session.close();
              }
          }
      
              InputStream resourceAsStream =
                      Resources.getResourceAsStream("sqlMapConfig.xml");
              SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
              SqlSession session = sqlSessionFactory.openSession();
              System.out.println("===== 开始插入数据 =====");
              long startTime = System.currentTimeMillis();
              int waitTime = 10;
              try {
                  List
                      User user = new User();
                      user.setId(i);
                      user.setUsername("共饮一杯无 " + i);
                      user.setAge((int) (Math.random() * 100));
                      userList.add(user);
                      if (i % 1000 == 0) {
                          session.insert("batchInsertUser", userList);
                          // 每 1000 条数据提交一次事务
                          session.commit();
                          userList.clear();
                          // 等待一段时间
                          Thread.sleep(waitTime * 1000);
                      }
                  }
                  // 最后插入剩余的数据
                  if(!CollectionUtils.isEmpty(userList)) {
                      session.insert("batchInsertUser", userList);
                      session.commit();
                  }
                  long spendTime = System.currentTimeMillis()-startTime;
                  System.out.println("成功插入 30 万条数据,耗时："+spendTime+"毫秒");
              } catch (Exception e) {
                  e.printStackTrace();
              } finally {
                  session.close();
              }
          }
      
              InputStream resourceAsStream =
                      Resources.getResourceAsStream("sqlMapConfig.xml");
              SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
              SqlSession session = sqlSessionFactory.openSession();
              System.out.println("===== 开始插入数据 =====");
              long startTime = System.currentTimeMillis();
              int waitTime = 10;
              try {
                  List
                      User user = new User();
                      user.setId(i);
                      user.setUsername("共饮一杯无 " + i);
                      user.setAge((int) (Math.random() * 100));
                      userList.add(user);
                      if (i % 1000 == 0) {
                          session.insert("batchInsertUser", userList);
                          // 每 1000 条数据提交一次事务
                          session.commit();
                          userList.clear();
                      }
                  }
                  // 最后插入剩余的数据
                  if(!CollectionUtils.isEmpty(userList)) {
                      session.insert("batchInsertUser", userList);
                      session.commit();
                  }
                  long spendTime = System.currentTimeMillis()-startTime;
                  System.out.println("成功插入 30 万条数据,耗时："+spendTime+"毫秒");
              } catch (Exception e) {
                  e.printStackTrace();
              } finally {
                  session.close();
              }
          }
      
              Connection connection = null;
              PreparedStatement preparedStatement = null;
              String databaseURL = "jdbc:mysql://localhost:3306/test";
              String user = "root";
              String password = "root";
              try {
                  connection = DriverManager.getConnection(databaseURL, user, password);
                  // 关闭自动提交事务，改为手动提交
                  connection.setAutoCommit(false);
                  System.out.println("===== 开始插入数据 =====");
                  long startTime = System.currentTimeMillis();
                  String sqlInsert = "INSERT INTO t_user ( username, age) VALUES ( ?, ?)";
                  preparedStatement = connection.prepareStatement(sqlInsert);
                  Random random = new Random();
                  for (int i = 1; i 
                      preparedStatement.setString(1, "共饮一杯无 " + i);
                      preparedStatement.setInt(2, random.nextInt(100));
                      // 添加到批处理中
                      preparedStatement.addBatch();
                      if (i % 1000 == 0) {
                          // 每1000条数据提交一次
                          preparedStatement.executeBatch();
                          connection.commit();
                          System.out.println("成功插入第 "+ i+" 条数据");
                      }
                  }
                  // 处理剩余的数据
                  preparedStatement.executeBatch();
                  connection.commit();
                  long spendTime = System.currentTimeMillis()-startTime;
                  System.out.println("成功插入 30 万条数据,耗时："+spendTime+"毫秒");
              } catch (SQLException e) {
                  System.out.println("Error: " + e.getMessage());
              } finally {
                  if (preparedStatement != null) {
                      try {
                          preparedStatement.close();
                      } catch (SQLException e) {
                          e.printStackTrace();
                      }
                  }
                  if (connection != null) {
                      try {
                          connection.close();
                      } catch (SQLException e) {
                          e.printStackTrace();
                      }
                  }
              }
          }