JDBC事务与批处理

1.事务
当一个Connectioin对象创建时，默认情况下是自动提交，即每次执行一条SQL成功时都会自动commit，不能回滚
取消自动提交：connection.setAutoCommit(false);
所有SQL执行成功后提交事务：connection.commit();
catch到执行异常时回滚：connection.rollback();
也可设置回滚点：
Savepoint savepoint = connection.setSavepoint("savepoint");
connection.rollback(savepoint);

2.批处理
//Submits a batch of commands to the database for execution and
//if all commands execute successfully, returns an array of update counts.
public class BatchTest {

    /*
     CREATE TABLE student(id INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(32));
     */

    @Test
    public void test1() throws Exception {

        String url = "jdbc:mysql://localhost:3306/testdb";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "654321");

        //1.获取Driver（反射获取）
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取Connection
        Connection connection = DriverManager.getConnection(url, properties);

        //3.执行SQL
        String sql = "INSERT INTO student VALUES (null, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 1; i <= 5000; i++) {
            preparedStatement.setString(1, "student" + i);
            preparedStatement.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println("插入一共耗时：" + (end - start));//插入一共耗时：13658

        //4.释放资源
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void test2() throws Exception {

		//打开mysql的批处理：rewriteBatchedStatements=true
        String url = "jdbc:mysql://localhost:3306/testdb?rewriteBatchedStatements=true";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "654321");

        //1.获取Driver（反射获取）
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取Connection
        Connection connection = DriverManager.getConnection(url, properties);

        //3.执行SQL
        String sql = "INSERT INTO student VALUES (null, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 1; i <= 5000; i++) {
            preparedStatement.setString(1, "student" + i);
            preparedStatement.addBatch();
			//设置积累到1000条commands再执行
            if (i % 1000 == 0) {
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }
        }
        preparedStatement.executeBatch();
        long end = System.currentTimeMillis();
        System.out.println("batch插入一共耗时：" + (end - start));//batch插入一共耗时：120

        //4.释放资源
        preparedStatement.close();
        connection.close();
    }
}