Java之JDBC批量插入数据

最新推荐文章于 2025-09-17 10:30:00 发布

原创最新推荐文章于 2025-09-17 10:30:00 发布 · 2.3w 阅读

49 ·

CC 4.0 BY-SA版权

java 专栏收录该内容

16 篇文章

订阅专栏

本文详细介绍了使用批量操作和事务提交来显著提高MySQL数据库插入性能的方法。通过对比不同策略，展示了如何将10万条数据的插入时间从13秒减少到1.2秒，揭示了批量操作结合事务提交的高效性。

Github个人博客：https://joeyos.github.io

普通插入方式

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