jdbc是否设置自动提交对性能的影响

项目中DB层会定时向mysql批量提交sql操作，之前是未设置是否自动提交也就是自动提交，性能很差，后来改为手动事务就好了，把一堆sql作为一个事务提交，如果有失败再单个提交。
写了个例子，测试了下性能，发现1000句的更新语句，手动提交事务有300倍的性能提升。
实例代码：

 


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
 
 
 
public class TestDB {
	static Connection conn = getDBConnection("com.mysql.jdbc.Driver", "jdbc:mysql://127.0.0.1:3306/test1?useUnicode=true&characterEncoding=utf8&createDatabaseIfNotExist=true&serverTimezone=UTC&useSSL=false",
			"root","root");
	public static Connection getDBConnection(String driver, String urlDB, String user, String pwd)  {
		Connection conn;
		try {
			// 连接MYSQL数据库
			Class.forName(driver);
			conn = DriverManager.getConnection(urlDB, user, pwd);
			return conn;
		} catch (Exception e) { 
			e.printStackTrace();
		}
		
		return null;
	}
	public static void main(String[] args) throws Exception {
		conn.createStatement().execute("CREATE TABLE `test` (  `id` int(11) NOT NULL,  `val` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
		conn.createStatement().execute("INSERT INTO `test` VALUES (1,1)");
		test3(false, 1000);
		test3(false, 1000);
		test3(true, 1000);
		test4(false, 1000);
		test4(true, 1000);
		test5();
		test6();
		conn.createStatement().execute("DROP TABLE `test` ");
		conn.close();
		System.out.println("test database ok");
	}
	public static void test1() throws Exception{
		long t = System.currentTimeMillis();
		
		Statement  ps = conn.createStatement();
		for(int i = 0;i < 100;i++){
			ps.execute("UPDATE test SET `val`="+(2000+i)+" WHERE id=1");
		}
		ps.close();
		
		long e = System.currentTimeMillis();
		System.out.println("test1 used:"+(e-t));
	}
	public static void test3(boolean autoCommit, int count) throws Exception{
		long t = System.currentTimeMillis();
		conn.setAutoCommit(autoCommit);
		Statement  ps = conn.createStatement();
		for(int i = 0;i < count;i++){
			ps.execute("UPDATE test SET `val`="+(2000+i)+" WHERE id=1");
		}
//		ps.close();
		if(!autoCommit){
			conn.commit();
		}
		long e = System.currentTimeMillis();
		System.out.println("test createStatement , count="+count+" autoCommit="+autoCommit+" used:"+(e-t));
	}
	public static void test4(boolean autoCommit, int count) throws Exception{
		long t = System.currentTimeMillis();
		conn.setAutoCommit(autoCommit);
		PreparedStatement  ps = conn.prepareStatement("UPDATE test SET `val`=? WHERE id=?");
		for(int i = 0;i < count;i++){
			ps.setObject(1, 2000+i);
			ps.setObject(2, 1); 
			ps.execute();
		}
		ps.close();
		if(!autoCommit){
			conn.commit();
		}
		long e = System.currentTimeMillis();
		System.out.println("test prepareStatement, count="+count+", autoCommit="+autoCommit+", used:"+(e-t));
	}
	public static void test5() throws Exception{
		long t = System.currentTimeMillis();
		conn.setAutoCommit(false);
		Statement  ps = conn.createStatement();
		for(int i = 0;i < 1000;i++){
			ps.addBatch("UPDATE test SET `val`="+(2000+i)+" WHERE id=1");
		}
		ps.executeBatch();
		ps.close();
		conn.commit();
		long e = System.currentTimeMillis();
		System.out.println("test5:"+(e-t));
	}
	public static void test6() throws Exception{
		long t = System.currentTimeMillis();
		conn.setAutoCommit(false);
		PreparedStatement  ps = conn.prepareStatement("UPDATE test SET `val`=? WHERE id=?");
		for(int i = 0;i < 1000;i++){
			ps.setObject(1, 2000+i);
			ps.setObject(2, 1); 
			ps.addBatch();
		}
		ps.executeBatch();
		ps.close();
		conn.commit();
		long e = System.currentTimeMillis();
		System.out.println("test6:"+(e-t));
	}
}

执行结果：

引用

test createStatement , count=1000 autoCommit=false used:200
test createStatement , count=1000 autoCommit=false used:150
test createStatement , count=1000 autoCommit=true used:29423
test prepareStatement, count=1000, autoCommit=false, used:199
test prepareStatement, count=1000, autoCommit=true, used:29996
test5:243
test6:167
test database ok