java JDBC批量提交数据性能差异

/* * To change this template, choose Tools | Templates * and open the template in the editor. */ package apptest;

import oracle.jdbc.*; import java.sql.*;

/** * * @author xiangbli */ public class Apptest {

/\*\*
 \* @param args the command line arguments
 \*/
public static void main(String\[\] args) throws SQLException {
    // TODO code application logic here

    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
    }catch(Exception e){}

    Connection cnn1=DriverManager.getConnection("jdbc:oracle:thin:@192.168.56.101:1521:cdb1", "c##maclean", "oracle");
    Statement stat1=cnn1.createStatement();
    cnn1.setAutoCommit(false);

ResultSet rst1=stat1.executeQuery("select * from v$version"); while(rst1.next()) { System.out.println(rst1.getString(1));

} long startTime = System.currentTimeMillis(); long stopTime = System.currentTimeMillis();

String str="begin \n --我是一个拼接起来的SQL匿名块 \n"; int i; for(i=0;i<=15000; i++)

 {

 str= str.concat(" insert into insertit values(?,?,?,?); \\n");

 }  
 str=str.concat("  commit ; end; ");
System.out.print(str);

cnn1.createStatement().execute("alter system flush shared\_pool");

System.out.print("\\n alter system flush shared\_pool 已刷新共享池,避免SQL游标缓存 影响第一次测试 \\n");

 PreparedStatement  pstmt = cnn1.prepareStatement(str);
 int j;
 for (j=0;j<=15000;j++)
 {

     pstmt.setInt(1+j\*4, 1);
     pstmt.setInt(2+j\*4, 1);
     pstmt.setInt(3+j\*4, 1);
     pstmt.setInt(4+j\*4, 1);

 }

// System.out.println (" Statement Execute Batch Value " +((OraclePreparedStatement)pstmt).getExecuteBatch());

startTime = System.currentTimeMillis(); pstmt.execute(); stopTime = System.currentTimeMillis(); System.out.println("拼接15000条INSERT SQL 第一次运行的耗时 Elapsed time was " + (stopTime - startTime) + " miliseconds.");

 startTime = System.currentTimeMillis();
 pstmt.execute();
 stopTime = System.currentTimeMillis();
 System.out.println("拼接15000条INSERT SQL 第二次运行的耗时 Elapsed time was " + (stopTime - startTime) + " miliseconds.");

cnn1.createStatement().execute("alter system flush shared\_pool");

System.out.print("\\n alter system flush shared\_pool 已刷新共享池,避免SQL游标缓存 影响第二次测试 \\n");

    startTime = System.currentTimeMillis();

    int batch=1000;

   PreparedStatement  pstmt2 = cnn1.prepareStatement("insert into insertit values(?,?,?,?)");
  ((OraclePreparedStatement)pstmt2).setExecuteBatch(batch);
   for (int z=0;z<=15000;z++)
   {
   pstmt2.setInt(1, z);
   pstmt2.setInt(2, z);
   pstmt2.setInt(3, z);
   pstmt2.setInt(4, z);

   pstmt2.executeUpdate();

   }
   ((OraclePreparedStatement)pstmt2).sendBatch();
 cnn1.commit();

 stopTime = System.currentTimeMillis();

  System.out.println("batch size= "+batch+" 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was " + (stopTime - startTime) + " miliseconds.");

  startTime = System.currentTimeMillis();

   PreparedStatement  pstmt3 = cnn1.prepareStatement("insert into insertit values(?,?,?,?)");
  ((OraclePreparedStatement)pstmt3).setExecuteBatch(batch);

   for (int z=0;z<=15000;z++)
   {
   pstmt3.setInt(1, z);
   pstmt3.setInt(2, z);
   pstmt3.setInt(3, z);
   pstmt3.setInt(4, z);

   pstmt3.executeUpdate();

   }
 ((OraclePreparedStatement)pstmt3).sendBatch();

 cnn1.commit();

 stopTime = System.currentTimeMillis();

  System.out.println("batch size= "+batch+" 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was " + (stopTime - startTime) + " miliseconds.");

  String insert = "insert into insertit values (?,?,?,?)";

  PreparedStatement  pstmt4 = cnn1.prepareStatement(insert);

   startTime = System.currentTimeMillis();

  for (int u=0;u<=15000;u++)

  {
      pstmt4.setInt(1, u);
      pstmt4.setInt(2, u);
      pstmt4.setInt(3, u);
      pstmt4.setInt(4, u);
      pstmt4.addBatch();
  }

  pstmt4.executeBatch();
   cnn1.commit();

 stopTime = System.currentTimeMillis();

   System.out.println(" BATCH update 第一次运行的耗时 Elapsed time was " + (stopTime - startTime) + " miliseconds.");

 }

}

评论

h
hw_7864

⭐️ Admin快速开发: https://show.cool-admin.com/

赞 0
h
hw_7864

⭐️ 官方文档: https://cool-js.com

赞 0
h
hw_7864

⭐️ chatgpt在线体验: https://cool-js.com/ai/chat-vip/index.html?r=helloworld

赞 0