JDBC批量插入mysql数据

• 单线程(单条循环)插入50000条记录：
  每执行一次就要访问一次数据库

  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
   
   
  public class Test {
   
      public static void main(String[] args) {
           
           Connection conn;
           Statement stmt;
           ResultSet rs = null;
           String url = "jdbc:sqlserver://localhost:1433;DatabaseName=test;";
           String sql = "insert into student(name,age) values('wang',12)";
           try {
               // 连接数据库
               conn = DriverManager.getConnection(url, "sa", "123456");
               // 建立Statement对象
               stmt = conn.createStatement();
               /**
                * Statement createStatement() 创建一个 Statement 对象来将 SQL 语句发送到数据库。
                */
               // 执行数据库查询语句
               long starttime=System.currentTimeMillis();
               for(int i=0; i<50000;i++){
                   stmt.executeUpdate(sql);
               }
               long spendtime=System.currentTimeMillis()-starttime;
               System.out.println( "单线程批处理花费时间:"+spendtime);
               /**
                * ResultSet executeQuery(String sql) throws SQLException 执行给定的 SQL
                * 语句，该语句返回单个 ResultSet 对象
                */
               if (rs != null) {
                   rs.close();
                   rs = null;
               }
               if (stmt != null) {
                   stmt.close();
                   stmt = null;
               }
               if (conn != null) {
                   conn.close();
                   conn = null;
               }
           } catch (SQLException e) {
               e.printStackTrace();
               System.out.println("数据库连接失败");
           }       
      }   
  }
  

• 单线程(批处理)插入50000条记录：
  stmt.addBatch()：把要执行的多条sql语句放在一起，通过stmt.executeBatch()只访问一次数据库，就前面的多条sql语句一起插入

  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
   
  public class Test {
   
      public static void main(String[] args) {
           
           Connection conn;
           Statement stmt;
           ResultSet rs = null;
           String url = "jdbc:sqlserver://localhost:1433;DatabaseName=test;";
           String sql = "insert into student(name,age) values('wang',12)";
           try {
               // 连接数据库
               conn = DriverManager.getConnection(url, "sa", "123456");
               // 建立Statement对象
               stmt = conn.createStatement();
               /**
                * Statement createStatement() 创建一个 Statement 对象来将 SQL 语句发送到数据库。
                */
               // 执行数据库查询语句
               long starttime=System.currentTimeMillis();
               for(int i=0; i<50000;i++){
                   stmt.addBatch("insert into student(name,age) values('wang',12)");
               }
               stmt.executeBatch();
               long spendtime=System.currentTimeMillis()-starttime;
               System.out.println( "单线程批处理花费时间:"+spendtime);
               /**
                * ResultSet executeQuery(String sql) throws SQLException 执行给定的 SQL
                * 语句，该语句返回单个 ResultSet 对象
                */
               if (rs != null) {
                   rs.close();
                   rs = null;
               }
               if (stmt != null) {
                   stmt.close();
                   stmt = null;
               }
               if (conn != null) {
                   conn.close();
                   conn = null;
               }
           } catch (SQLException e) {
               e.printStackTrace();
               System.out.println("数据库连接失败");
           } 
      }   
  }
  

• 多线程(单条循环)插入50000条记录：
  启动5个线程，每个线程插入10000条记录

  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.util.UUID;
  import java.util.concurrent.CountDownLatch;
   
   
  public class InsertTest {
   
        private String url="jdbc:sqlserver://localhost:1433;DatabaseName=test;";
          private String user="sa";
          private String password="123456";
          public Connection getConnect(){
              Connection con = null;
               try {
                  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                  con=DriverManager.getConnection(url, user, password);
              } catch (Exception e) {
                  e.printStackTrace();
              }
               return con;
          }
          public void multiThreadImport( final int ThreadNum){
              final CountDownLatch cdl= new CountDownLatch(ThreadNum);
              long starttime=System.currentTimeMillis();
              for(int k=1;k<=ThreadNum;k++){
                  new Thread(new Runnable() {
                      @Override
                      public void run() {
                          Connection con=getConnect();
                          try {
                              Statement st=con.createStatement();
                              for(int i=1;i<=10000;i++){
                                  //st.addBatch("insert into student(name,age) values('wang',12)");
                                  st.executeUpdate("insert into student(name,age) values('wang',12)");
                              }
                              //st.executeBatch();
                              cdl.countDown();
                          } catch (Exception e) {
                          }finally{
                              try {
                                  con.close();
                              } catch (SQLException e) {
                                  e.printStackTrace();
                              }
                          }
                      }
                  }).start();
              }
              try {
                  cdl.await();
                  long spendtime=System.currentTimeMillis()-starttime;
                  System.out.println( ThreadNum+"个线程花费时间:"+spendtime);
              } catch (InterruptedException e) {
                  e.printStackTrace();
              }
   
          }
   
          public static void main(String[] args) throws Exception {
              InsertTest ti=new InsertTest();
              ti.multiThreadImport(5);
          }
  }
  

• 多线程+批处理插入50000条记录：
  启动5个线程

  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.util.UUID;
  import java.util.concurrent.CountDownLatch;
   
   
  public class InsertTest {
   
        private String url="jdbc:sqlserver://localhost:1433;DatabaseName=test;";
          private String user="sa";
          private String password="Rfid123456";
          public Connection getConnect(){
              Connection con = null;
               try {
                  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                  con=DriverManager.getConnection(url, user, password);
              } catch (Exception e) {
                  e.printStackTrace();
              }
               return con;
          }
          public void multiThreadImport( final int ThreadNum){
              final CountDownLatch cdl= new CountDownLatch(ThreadNum);//定义线程数量
              long starttime=System.currentTimeMillis();
              for(int k=1;k<=ThreadNum;k++){
                  new Thread(new Runnable() {
                      @Override
                      public void run() {
                          Connection con=getConnect();
                          try {
                              Statement st=con.createStatement();
                              for(int i=1;i<=50000/ThreadNum;i++){
                                  st.addBatch("insert into student(name,age) values('wang',12)");
                                  if(i%500 == 0){
                                      st.executeBatch();
                                  }
                              }
                              cdl.countDown();    //执行完一个线程，递减1
                          } catch (Exception e) {
                          }finally{
                              try {
                                  con.close();
                              } catch (SQLException e) {
                                  e.printStackTrace();
                              }
                          }
                      }
                  }).start();
              }
              try {
                  cdl.await();    //前面线程没执行完，其他线程等待，不往下执行
                  long spendtime=System.currentTimeMillis()-starttime;
                  System.out.println( ThreadNum+"个线程花费时间:"+spendtime);
              } catch (InterruptedException e) {
                  e.printStackTrace();
              }
   
          }
   
          public static void main(String[] args) throws Exception {
              InsertTest ti=new InsertTest();
              ti.multiThreadImport(5);
          }
  }