Java之JDBC用事务和批处理插入大量数据

普通方式插入

• 10万条数据，耗时13秒

  private String url = "jdbc:mysql://localhost:3306/test01";
  private String user = "root";
  private String password = "123456";

  @Test
  public void Test () {
    Connection conn = null;
    PreparedStatement pstm =null;
    ResultSet rt = null;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      conn = DriverManager.getConnection(url, user, password);   
      String sql = "INSERT INTO myTable values(?,?)";
      pstm = conn.prepareStatement(sql);
      Long startTime = System.currentTimeMillis();
      for (int i = 1; i <= 100000; i++) {
          pstm.setInt(1, i);
          pstm.setInt(2, i);
          pstm.executeUpdate();
      }
      Long endTime = System.currentTimeMillis();
      System.out.println("用时：" + (endTime - startTime));
    } catch (Exception e) {
      e.printStackTrace();
      throw new RuntimeException(e);
    } finally {
      if (pstm!=null) {
        try {
          pstm.close();
        } catch (SQLException e) {
          e.printStackTrace();
          throw new RuntimeException(e);
        }
      }
      if (conn!=null) {
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
          throw new RuntimeException(e);
        }
      }
    }
  }

事务提交

• 设置conn.setAutoCommit(false);
• 最后在所有命令执行完之后再提交事务conn.commit();
• 10w条数据11秒

  private String url = "jdbc:mysql://localhost:3306/test01";
  private String user = "root";
  private String password = "123456";
  @Test
  public void Test () {
    Connection conn = null;
    PreparedStatement pstm =null;
    ResultSet rt = null;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      conn = DriverManager.getConnection(url, user, password);   
      String sql = "INSERT INTO myTable values(?,?)";
      pstm = conn.prepareStatement(sql);
      Long startTime = System.currentTimeMillis();
      conn.setAutoCommit(false);
      for (int i = 1; i <= 100000; i++) {
          pstm.setInt(1, i);
          pstm.setInt(2, i);
          pstm.executeUpdate();
      }
      conn.commit();
      Long endTime = System.currentTimeMillis();
      System.out.println("用时：" + (endTime - startTime));
    } catch (Exception e) {
      e.printStackTrace();
      throw new RuntimeException(e);
    } finally {
      if (pstm!=null) {
        try {
          pstm.close();
        } catch (SQLException e) {
          e.printStackTrace();
          throw new RuntimeException(e);
        }
      }
      if (conn!=null) {
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
          throw new RuntimeException(e);
        }
      }
    }
  }

批量操作

• 注意更改url = “jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true”;
• pstm.addBatch()代替pstm.executeUpdate();
• 最后批量操作pstm.executeBatch();
• 10w条数据1.3秒！！！

事务提交+批量操作

• 10w条数据大概1.2秒。

  private String url = "jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";
  private String user = "root";
  private String password = "123456";
  @Test
  public void Test () {
    Connection conn = null;
    PreparedStatement pstm =null;
    ResultSet rt = null;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      conn = DriverManager.getConnection(url, user, password);   
      String sql = "INSERT INTO myTable values(?,?)";
      pstm = conn.prepareStatement(sql);
      Long startTime = System.currentTimeMillis();
      conn.setAutoCommit(false);
      for (int i = 1; i <= 100000; i++) {
          pstm.setInt(1, i);
          pstm.setInt(2, i);
          pstm.addBatch();
      }
      pstm.executeBatch();
      conn.commit();
      Long endTime = System.currentTimeMillis();
      System.out.println("用时：" + (endTime - startTime));
    } catch (Exception e) {
      e.printStackTrace();
      throw new RuntimeException(e);
    } finally {
      if (pstm!=null) {
        try {
          pstm.close();
        } catch (SQLException e) {
          e.printStackTrace();
          throw new RuntimeException(e);
        }
      }
      if (conn!=null) {
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
          throw new RuntimeException(e);
        }
      }
    }
  }