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

慈云数据 2024-03-12 技术支持 74 0

本文主要讲述通过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.

        Cause: 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();
                        }
                    }
                }
            }
        
微信扫一扫加客服

微信扫一扫加客服

点击启动AI问答
Draggable Icon