首先關于Statement和PreparedStatement的基本概念我就不再叙述了,可以參考這篇文章,下面我們來看幾個測試例子吧。
測試場景
現在我們向資料庫中的一張表中插入100000(10萬)條資料,測試使用Statement和PreparedStatement及PreparedStatement的Batch方式所需要的時間。
Dao基類
/**
*
* Description: 資料庫操作基類
*
* @author: crane-yuan
* @date: 2016年9月27日 下午1:40:04
*/
public class BaseDao {
protected Connection connection; // 通路資料庫的句柄
public void openConnection() throws Exception {
if (this.connection == null || this.connection.isClosed()) {
try {
DbInfo dbinfo = DbInfo.instance();
Class.forName(dbinfo.getDbdriver()); // 加載oracle的驅動
connection = DriverManager.getConnection(dbinfo.getUrl(),
dbinfo.getUser(), dbinfo.getPassword());
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println("請檢查驅動包是否正确");
throw e;
} catch (SQLException e) {
e.printStackTrace();
System.out.println("請檢查資料庫的配置項是否正确");
throw e;
}
}
}
public void closeConnection() throws Exception {
if (this.connection != null) {
this.connection.close();
}
}
}
Statement方式
/**
*
* Description: 測試Statement
*
* @throws Exception void
*/
public void testStatement() throws Exception {
System.out.println("--------------Statement no batch----------------");
String sql = "";
this.openConnection();
// 手動送出
this.connection.setAutoCommit(false);
Statement statement = this.connection.createStatement();
Long beginTime = System.currentTimeMillis();
System.out.println(new Date(beginTime));
try {
for (int i = ; i < ; i++) {
sql = "insert into testStatement(code) values(" + i + ")";
statement.executeUpdate(sql);
}
} catch (SQLException exception) {
exception.printStackTrace();
}
this.connection.commit();
statement.close();
this.connection.close();
Long endTime = System.currentTimeMillis();
System.out.println(new Date(endTime));
System.out.println("Statement:" + (endTime - beginTime) / + "秒");
}
結果:
--------------Statement no batch----------------
Wed Oct :: CST
Wed Oct :: CST
Statement:秒
PreparedStatement無Batch方式
/**
*
* Description: 測試PreparedStatement,不開啟Batch送出
*
* @throws Exception void
*/
public void testPreparedStatement() throws Exception {
System.out
.println("--------------PreparedStatement no batch----------------");
String sql = "insert into testStatement(code) values(?)";
this.openConnection();
// 手動送出
this.connection.setAutoCommit(false);
PreparedStatement ps = this.connection.prepareStatement(sql);
Long beginTime = System.currentTimeMillis();
System.out.println(new Date(beginTime));
try {
for (int i = ; i < ; i++) {
String code = "" + i;
ps.setString(, code);
ps.execute();
}
} catch (SQLException exception) {
exception.printStackTrace();
}
this.connection.commit();
ps.close();
this.connection.close();
Long endTime = System.currentTimeMillis();
System.out.println(new Date(endTime));
System.out.println("PreparedStatement:" + (endTime - beginTime) / + "秒");
}
結果
--------------PreparedStatement no batch----------------
Wed Oct :: CST
Wed Oct :: CST
PreparedStatement:秒
PreparedStatement有Batch方式
/**
*
* Description: 測試使用PreparedStatement,并且開啟Batch送出.
*
* @throws Exception void
*/
public void testPreparedStatementBatch() throws Exception {
System.out
.println("--------------PreparedStatement with batch----------------");
String sql = "insert into testStatement(code) values(?)";
this.openConnection();
// 手動送出
this.connection.setAutoCommit(false);
PreparedStatement ps = this.connection.prepareStatement(sql);
Long beginTime = System.currentTimeMillis();
System.out.println(new Date(beginTime));
int count = ;
try {
for (int i = ; i < ; i++) {
String code = "" + i;
ps.setString(, code);
ps.addBatch();
count++;
if (count == ) {
count = ;
ps.executeBatch();
this.connection.commit();
ps.clearBatch();
}
}
} catch (SQLException exception) {
exception.printStackTrace();
}
ps.close();
this.connection.close();
Long endTime = System.currentTimeMillis();
System.out.println(new Date(endTime));
System.out.println("PreparedStatement+batch:" + (endTime - beginTime) / + "秒");
}
結果
--------------PreparedStatement with batch----------------
Wed Oct :: CST
Wed Oct :: CST
PreparedStatement+batch:秒
總結
一般來說,PreparedStatement的Batch方式執行效率比PreparedStatement和Statement的都高,PreparedStatement無Batch方式比Statement方式也要高。
在實際開發中一般推薦使用PreparedStatement,不過PreparedStatement也有一些缺點,在這篇文章中暫時不在叙述了,這方面的對比将在下篇文章中詳細講解。