Java msyql批量插入 十万条数据

 //检查来源表的中间表是否有更新
        Connection conn = null;
        Statement stmt = null;
        PreparedStatement ps = null;
        // 注册 JDBC 驱动
        long begintime = System.currentTimeMillis();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            // 打开链接
            System.out.println("----连接数据库...");
             
            String url = "jdbc:mysql://127.0.0.1:3306/demo?characterEncoding=utf8&serverTimezone=UTC&useSSL=false&rewriteBatchedStatements=true";

            conn = DriverManager.getConnection(
                    url,
                    "root", "123456");
            // 执行查询
            stmt = conn.createStatement();
            conn.setAutoCommit(false);//不允许自动提交数据

            String temp = "";
            int tempNum = 3; //插入字段的数量
            for (int tempi = 0; tempi < tempNum; tempi++) {
                temp += "?,";
            }
            temp = temp.substring(0, temp.lastIndexOf(","));
            String insertSql = "INSERT INTO a_test_syn(name,age,temp)  VALUES(" + temp + ")";
            ps = conn.prepareStatement(insertSql);

            int tNum = 0;
            for (int a = 1; a <= 100000; a++) {
                long begintimea = System.currentTimeMillis();
                ps.setObject(1,"name_"+tNum);
                ps.setObject(2,"name_"+tNum);
                ps.setObject(3,"name_"+tNum);
                //1、“攒”sql
                ps.addBatch();
                tNum++;
                if(a%500 == 0){
                    //2、执行batch
                    ps.executeBatch();
                    //3、清空batch
                    ps.clearBatch();
                }
                long begintimeb = System.currentTimeMillis();
                System.out.println("-第" + a + "批插入--耗时-" + (begintimeb - begintimea) + "ms");

            }
            ps.executeBatch();
            ps.clearBatch();
            //2、执行batch
//            ps.executeBatch();
//            //3、清空batch
//            ps.clearBatch();
            //提交数据
            conn.commit();

            if(ps != null){
                ps.close();
            }
            // 完成后关闭
            stmt.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        long endtime = System.currentTimeMillis();
        System.out.println("耗时-：" + (endtime - begintime)+"ms");

 

耗时-：19331ms

注意：使用批量提交数据，url一定要设置允许重写批量提交【rewriteBatchedStatements=true】