Java Statement和PreparedStatement性能测试（转）

本文转载自http://blog.csdn.net/liubo5005/article/details/7239935

先上代码：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;

public class TestSql {

 public static void main(String[] args) throws Exception {
  testStatement();
  testBatchPreparedStatement();
  testBatchPreparedStatement();
 }

 public static void testStatement() throws Exception {
  Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
  String url = "jdbc:db2://172.17.252.68:60012/glhssdb";
  Connection con = DriverManager.getConnection(url, "ppapdb2", "ppapdb2");
  Statement st = con.createStatement();

  Long beginTime1 = System.currentTimeMillis();
  System.out.print("insert...");
  for (int i = 0; i < 100000; i++) {

   String sql = "insert into GL_HISDB.TESTSQL(id,name) values (" + i
     + ",'" + i + "')";

   st.executeUpdate(sql);
  }
  Long endTime1 = System.currentTimeMillis();
  System.out.println("st：" + (endTime1 - beginTime1) / 1000 + "秒");// 计算时间
  st.close();
  con.close();
 }

 public static void testPreparedStatement() throws Exception {
  Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
  String url = "jdbc:db2://172.17.252.68:60012/glhssdb";
  Connection con = DriverManager.getConnection(url, "ppapdb2", "ppapdb2");
  PreparedStatement pst = con
    .prepareStatement("insert into GL_HISDB.TESTSQL(id,name) values (?,?)");

  Long beginTime1 = System.currentTimeMillis();
  System.out.print("insert...");
  for (int i = 0; i < 100000; i++) {

   pst.setInt(1, i);
   pst.setString(2, "" + i);
   pst.execute();
  }
  Long endTime1 = System.currentTimeMillis();
  System.out.println("pst：" + (endTime1 - beginTime1) / 1000 + "秒");// 计算时间
  pst.close();
  con.close();
 }

 public static void testBatchPreparedStatement() throws Exception {
  Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
  String url = "jdbc:db2://172.17.252.68:60012/glhssdb";
  Connection con = DriverManager.getConnection(url, "ppapdb2", "ppapdb2");
  PreparedStatement pst = con
    .prepareStatement("insert into GL_HISDB.TESTSQL(id,name) values (?,?)");

  Long beginTime1 = System.currentTimeMillis();
  con.setAutoCommit(false);// 手动提交
  System.out.print("insert...");
  for (int i = 0; i < 100000; i++) {

   pst.setInt(1, i);
   pst.setString(2, "" + i);
   pst.addBatch();

   if (i % 1000 == 0) {// 可以设置不同的大小；如50，100，500，1000等等
    pst.executeBatch();
    con.commit();
    pst.clearBatch();
   }

  }
  Long endTime1 = System.currentTimeMillis();
  System.out.println("pst batch：" + (endTime1 - beginTime1) / 1000 + "秒");// 计算时间
  pst.close();
  con.close();
 }
}

三种调用方式Statment、PreparedStatement 以及PreparedStatement  Batch，往DB2数据库插入10万条数据，跑出来的时间为：

1、insert...st：291秒 2、insert...pst：150秒 3、insert...pst batch：5秒 

总结引用经典：在JDBC应用中,如果你已经是稍有水平开发者,你就应该始终以PreparedStatement代替Statement.也就是说,在任何时候都不要使用Statement。