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 */@Datapublic 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.Driverjdbc.url=jdbc:mysql://localhost:3306/testjdbc.username=rootjdbc.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();                }            }        }    }