原理--试过再说.根据jdbc规范: The Connection attribute auto-commit specifies when to end transactions. Enabling auto-commit causes the JDBC driver to do a transaction commit after each individual SQL statement as soon as it is complete. The point at which a statement is considered to be “complete” depends on the type of SQL statement as well as what the application does after executing it:1 For Insert, Update, Delete, and DDL statements, the statement is complete as soon as it has finished executing.2 For Select statements, the statement is complete when the associated result set is closed. The result set is closed as soon as one of the following occurs: - all of the rows have been retrieved - the associated Statement object is re-executed - another Statement object is executed on the same connection3 For CallableStatement objects, the statement is complete when all of the associated result sets have been closed.所以,按我的方法应该会快很多。mysql table是分类型的,用支持transact的类型也许明显。(我没有mysql,用的pg试过/快上百倍)import java.sql.*;/** * @author Administrator * * 2004-6-11 */ public class TestDB { private static String driver = "org.postgresql.Driver"; private static String user = "sys"; private static String pwd = "sys"; private static String url = "jdbc:postgresql://127.0.0.1/test";
public static void main(String[] args) { Connection con = null; try { Class.forName(driver); } catch (ClassNotFoundException ex) { ex.printStackTrace(); } try { con = DriverManager.getConnection(url, user, pwd); con.setAutoCommit(false); Statement stmt = con.createStatement(); String v = "hellohellohellohellohellohellohellohellohellohellohellohello"; String sql = "insert into t1 values('" + v + "','" + v + "','" + v + "','" + v + "')"; String sql1 = "insert into t1 values(?,?,?,?)"; PreparedStatement pstmt = con.prepareStatement(sql1); int count = 10000; stmt.execute("delete from t1"); long start = System.currentTimeMillis(); con.commit(); //method 1
for (int i = 0; i < count; i++) stmt.execute(sql); System.out.println( "用stmt execute 原始方式逐条添加记录耗时 : " + (System.currentTimeMillis() - start)); con.commit(); stmt.execute("delete from t1"); start = System.currentTimeMillis(); //method2 for (int i = 0; i < count; i++) stmt.addBatch(sql); stmt.executeBatch(); con.commit(); System.out.println( "用stmt addBatch executeBatch 方式批量添加记录耗时 : " + (System.currentTimeMillis() - start)); stmt.execute("delete from t1"); start = System.currentTimeMillis(); //method 3 for (int i = 0; i < count; i++) { pstmt.setString(1, v); pstmt.setString(2, v); pstmt.setString(3, v); pstmt.setString(4, v); pstmt.addBatch(); } pstmt.executeBatch(); con.commit(); System.out.println( "用pstmt addBatch executeBatch 方式批量添加记录耗时 : " + (System.currentTimeMillis() - start)); } catch (SQLException ex1) { ex1.printStackTrace(); } finally { try { con.close(); }catch(SQLException ex2) {} } } }
con.setAutoCommit(false);
//massive op
con.commit();原理--试过再说
用stmt addBatch executeBatch 方式批量添加记录耗时 : 22084
用pstmt addBatch executeBatch 方式批量添加记录耗时 : 22990
The Connection attribute auto-commit specifies when to end transactions. Enabling
auto-commit causes the JDBC driver to do a transaction commit after each
individual SQL statement as soon as it is complete. The point at which a statement
is considered to be “complete” depends on the type of SQL statement as well as
what the application does after executing it:1 For Insert, Update, Delete, and DDL statements, the statement is complete as soon
as it has finished executing.2 For Select statements, the statement is complete when the associated result set is closed. The result set is closed as soon as one of the following occurs:
- all of the rows have been retrieved
- the associated Statement object is re-executed
- another Statement object is executed on the same connection3 For CallableStatement objects, the statement is complete when all of the
associated result sets have been closed.所以,按我的方法应该会快很多。mysql table是分类型的,用支持transact的类型也许明显。(我没有mysql,用的pg试过/快上百倍)import java.sql.*;/**
* @author Administrator
*
* 2004-6-11
*/
public class TestDB {
private static String driver = "org.postgresql.Driver";
private static String user = "sys";
private static String pwd = "sys";
private static String url = "jdbc:postgresql://127.0.0.1/test";
public static void main(String[] args) {
Connection con = null;
try {
Class.forName(driver);
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
} try {
con = DriverManager.getConnection(url, user, pwd);
con.setAutoCommit(false);
Statement stmt = con.createStatement(); String v =
"hellohellohellohellohellohellohellohellohellohellohellohello";
String sql =
"insert into t1 values('"
+ v
+ "','"
+ v
+ "','"
+ v
+ "','"
+ v
+ "')"; String sql1 = "insert into t1 values(?,?,?,?)";
PreparedStatement pstmt = con.prepareStatement(sql1); int count = 10000;
stmt.execute("delete from t1");
long start = System.currentTimeMillis();
con.commit();
//method 1
for (int i = 0; i < count; i++)
stmt.execute(sql); System.out.println(
"用stmt execute 原始方式逐条添加记录耗时 : "
+ (System.currentTimeMillis() - start));
con.commit();
stmt.execute("delete from t1");
start = System.currentTimeMillis(); //method2
for (int i = 0; i < count; i++)
stmt.addBatch(sql);
stmt.executeBatch();
con.commit();
System.out.println(
"用stmt addBatch executeBatch 方式批量添加记录耗时 : "
+ (System.currentTimeMillis() - start)); stmt.execute("delete from t1");
start = System.currentTimeMillis(); //method 3
for (int i = 0; i < count; i++) {
pstmt.setString(1, v);
pstmt.setString(2, v);
pstmt.setString(3, v);
pstmt.setString(4, v);
pstmt.addBatch();
}
pstmt.executeBatch();
con.commit();
System.out.println(
"用pstmt addBatch executeBatch 方式批量添加记录耗时 : "
+ (System.currentTimeMillis() - start)); } catch (SQLException ex1) {
ex1.printStackTrace();
}
finally {
try {
con.close();
}catch(SQLException ex2) {}
}
}
}
我看过介绍。mysql好像不支持。你用oracle或者sql2000都可以batch绝对快。
最好去看看MYSQL关于BATCH ,PREPAREDSTATEMENT,是否能提高性能。