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

2019-03-22  本文已影响0人  不积小流_无以成江海

普通方式插入


  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);
        }
      }
    }
  }

事务提交

  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);
        }
      }
    }
  }

批量操作

事务提交+批量操作

  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);
        }
      }
    }
  }

上一篇下一篇

猜你喜欢

热点阅读