jdbc批处理+手动事务+多线程实现81秒插入1000万数据（多线程版)

Bertha 。 2022-02-19 01:39 967阅读 1赞

*  **现在来试试多线程能够多少秒钟插入千万数据**

/**
     * @Author: guandezhi
     * @Date: 2019/4/13 15:35
     */
    public class JdbcUtils {
    
        private static String url = "jdbc:mysql://localhost:3306/user?useSSL=false&rewriteBatchedStatements=true";
        private static String user = "root";
        private static String password = "root";
    
        private static ExecutorService threadPool = Executors.newFixedThreadPool(50);
    
    
        public static void executeBatch() {
            Connection conn = null;
            PreparedStatement ps = null;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(url, user, password);
                String sql = "insert into user(name, mobile) values(?,?)";
                conn.setAutoCommit(false);
                ps = conn.prepareStatement(sql);
                for (int j = 0; j < 100000; j++) {
                    String mobile = "13356544556";
                    Integer randNum = 0;
                    Random random = new Random();
                    for (int i = 0; i < 1000; i++) {
                        randNum = random.nextInt(10);
                    }
                    ps.setString(1, "官德志");
                    ps.setString(2, mobile + String.valueOf(randNum));
                    ps.addBatch();
                }
                ps.executeBatch();
                conn.commit();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    
        public static void main(String[] args) throws InterruptedException {
            long beginTime = System.currentTimeMillis();
            CountDownLatch latch = new CountDownLatch(100);
            for (int i = 0; i < 100; i++) {
                threadPool.execute(() -> {
                    try {
                        JdbcUtils.executeBatch();
                    } catch (Exception e) {
                        System.out.println("插入数据异常");
                    } finally {
                        latch.countDown();
                    }
                });
            }
            latch.await();
            long endTime = System.currentTimeMillis();
            System.out.println("插入一千万数据用时：" + (endTime - beginTime) / 1000 + " 秒");
            threadPool.shutdown();
        }
    }

*  **测试一下**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODU5ODQ0_size_16_color_FFFFFF_t_70][]

**总结： **

**      1.多线程确实比单线程快很多**

**      2.此处开启多线程容易造成OOM,需要合理的设置线程大小和JVM参数。**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODU5ODQ0_size_16_color_FFFFFF_t_70]: /images/20220219/c59a8c0cef9f40abb6a16f37eaac01e4.png