30万数据怎么才能最快插入

之前看到公众号Java知音大佬的深感震撼，记录一波

mybatis方式

/**
 * 分批次批量插入
 * @throws IOException
 */
@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();
    int waitTime = 10;
    try {
        List<User> userList = new ArrayList<>();
        for (int i = 1; i <= 300000; i++) {
            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();
    }
}

原生JDBC方式

    @Test
    public void testJDBCBatchInsertUser() throws IOException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        String databaseURL = "jdbc:mysql://localhost:3306/jdbc";
        String user = "root";
        String password = "1234";
        try {
            connection = DriverManager.getConnection(databaseURL, user, password);
            // 关闭自动提交事务，改为手动提交
            connection.setAutoCommit(false);
            System.out.println("===== 开始插入数据 =====");
            long startTime = System.currentTimeMillis();
            String sqlInsert = "INSERT INTO user (id, name, pwd) VALUES ( ?,?,?)";
            preparedStatement = connection.prepareStatement(sqlInsert);

            Random random = new Random();
            for (int i = 1; i <= 300000; i++) {
                preparedStatement.setInt(1,  + i);
                preparedStatement.setString(2, String.valueOf(random.nextInt(100)));
                preparedStatement.setString(3, String.valueOf(random.nextInt(100)));

                // 添加到批处理中
                preparedStatement.addBatch();

                if (i % 5000 == 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();
                }
            }
        }
    }

我的电脑硬件比较差77s

原文地址