JDBC实现往MySQL插入百万级数据最快的方式

批量操作+事务

 1 private String url = "jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";
 2     private String user = "root";
 3     private String password = "123456";
 4     @Test
 5     public void Test(){
 6         Connection conn = null;
 7         PreparedStatement pstm =null;
 8         ResultSet rt = null;
 9         try {
10             Class.forName("com.mysql.jdbc.Driver");
11             conn = DriverManager.getConnection(url, user, password);        
12             String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
13             pstm = conn.prepareStatement(sql);
14             conn.setAutoCommit(false);
15             Long startTime = System.currentTimeMillis();
16             Random rand = new Random();
17             int a,b,c,d;
18             for (int i = 1; i <= 100000; i++) {
19                     pstm.setInt(1, i);
20                     pstm.setInt(2, i);
21                     a = rand.nextInt(10);
22                     b = rand.nextInt(10);
23                     c = rand.nextInt(10);
24                     d = rand.nextInt(10);
25                     pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
26                     pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
27                     pstm.addBatch();
28             }
29             pstm.executeBatch();
30             conn.commit();
31             Long endTime = System.currentTimeMillis();
32             System.out.println("OK,用时：" + (endTime - startTime)); 
33         } catch (Exception e) {
34             e.printStackTrace();
35             throw new RuntimeException(e);
36         }finally{
37             if(pstm!=null){
38                 try {
39                     pstm.close();
40                 } catch (SQLException e) {
41                     e.printStackTrace();
42                     throw new RuntimeException(e);
43                 }
44             }
45             if(conn!=null){
46                 try {
47                     conn.close();
48                 } catch (SQLException e) {
49                     e.printStackTrace();
50                     throw new RuntimeException(e);
51                 }
52             }
53         }
54     }