JDBC-2 Statement和PreparedStatement效率测试
package com.day1;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
public class StmtAndPsTest {
// 测试Statement
// 事务手动提交
// 不使用批处理
// 时间:5521 5792 4960
@Test
public void test1() {
Connection conn = null;
Statement stmt = null;
try {
conn = GetConnection.getConn();
stmt = conn.createStatement();
conn.setAutoCommit(false);
long start = System.currentTimeMillis();
stmt = conn.createStatement();
String sql = null;
for (int i = 1; i <= 10000; i++) {
sql = "insert into stus(id,name) values(" + i + ",'tom" + i + "')";
stmt.execute(sql);
}
conn.commit();
long end = System.currentTimeMillis();
System.out.println("共耗时" + (end - start) + "毫秒");
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
GetConnection.close(stmt, conn);
}
}
// 测试PreparedStatement
// 事务手动提交
// 不使用批处理
// 时间:2422 2089 1995
@Test
public void test2() {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = GetConnection.getConn();
conn.setAutoCommit(false);
long start = System.currentTimeMillis();
String sql = "insert into stus(id,name) values(?,?)";
ps = conn.prepareStatement(sql);
for (int i = 1; i <= 10000; i++) {
ps.setLong(1, i);
ps.setString(2, "tom" + i);
ps.execute();
}
conn.commit();
long end = System.currentTimeMillis();
System.out.println("共耗时" + (end - start) + "毫秒");
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
if (ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 测试Statement
// 事务手动提交
// 使用批处理
// 时间:2172 2132 2152
@Test
public void test3() {
Connection conn = null;
Statement stmt = null;
try {
conn = GetConnection.getConn();
stmt = conn.createStatement();
conn.setAutoCommit(false);
long start = System.currentTimeMillis();
stmt = conn.createStatement();
String sql = null;
for (int i = 1; i <= 10000; i++) {
sql = "insert into stus(id,name) values(" + i + ",'tom" + i + "')";
stmt.addBatch(sql);
if (i % 500 == 0) {
stmt.executeBatch();
}
}
stmt.executeBatch();
conn.commit();
long end = System.currentTimeMillis();
System.out.println("共耗时" + (end - start) + "毫秒");
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
GetConnection.close(stmt, conn);
}
}
// 测试PreparedStatement
// 事务手动提交
// 使用批处理
// 时间:164 123 154 每700条执行一次批处理
// 时间:198 155 185 每1000条执行一次批处理
@Test
public void test4() {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = GetConnection.getConn();
conn.setAutoCommit(false);
long start = System.currentTimeMillis();
String sql = "insert into stus(id,name) values(?,?)";
ps = conn.prepareStatement(sql);
for (int i = 1; i <= 10000; i++) {
ps.setLong(1, i);
ps.setString(2, "tom" + i);
ps.addBatch();
if (i % 1000 == 0) {
ps.executeBatch();
}
}
ps.executeBatch();
conn.commit();
long end = System.currentTimeMillis();
System.out.println("共耗时" + (end - start) + "毫秒");
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
if (ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
最终结果是:PreparedStatement(预处理)和批处理一起使用时效率最高。
我们下期再见!