# 第 33 节 批处理与事务处理

在之前所使用的全部的数据库操作严格来讲是属于JDBC1.0中就规定的操作模式，而最新的JDBC是4.0版本，但是没人去使用，从JDBC2.0开始增加了一些神奇功能：可滚动的结果集、可以利用结果集执行增加、更新、删除操作、批处理操作。

所谓的批处理指的是一次性向数据库中发出多条操作命令，一起执行。如果要想操作批处理主要还是在`Statment`与`PreparedStatment`接口上定义的。

* `Statement`接口定义的方法：
  * 增加批处理：`public void addBatch(String sql) throws SQLException`；
  * 执行批处理：`public int[] executeBatch() throws SQLException`；
    * 返回的数组是包含了所有批处理语句的执行结果；
* `PreparedStatment`接口定义的方法：
  * 增加批处理：`public void addBatch() throws SQLException`；

```java
public class TestDemo {
	private static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DBURL = "jdbc:oracle:thin:@localhost:1521:orcl";
	private static final String USER = "scott";
	private static final String PASSWORD = "tiger";
	public static void main(String[] args) throws Exception {
		Class.forName(DBDRIVER);
		Connection conn = DriverManager.getConnection(DBURL, USER, PASSWORD);
		Statement stmt = conn.createStatement();
		stmt.addBatch("INSERT INTO member(mid,name) VALUES(myseq.nextval,'测试A')");
		stmt.addBatch("INSERT INTO member(mid,name) VALUES(myseq.nextval,'测试B')");
		stmt.addBatch("INSERT INTO member(mid,name) VALUES(myseq.nextval,'测试C')");
		stmt.addBatch("INSERT INTO member(mid,name) VALUES(myseq.nextval,'测试D')");
		stmt.addBatch("INSERT INTO member(mid,name) VALUES(myseq.nextval,'测试E')");
		stmt.addBatch("INSERT INTO member(mid,name) VALUES(myseq.nextval,'测试F')");
		int[] result = stmt.executeBatch(); // 执行批处理
		System.out.println(Arrays.toString(result));
		conn.close();
	}
}
```

如果假设以上的五条批处理属于一组关联的操作，如果中间有一条语句执行失败，其他的不应该成功。

在批处理操作的过程之中，由于JDBC具备有自动的事务提交，所以一旦中间的语句出现了错误，那么结果就是错误前的语句正常执行，错误后的语句就不执行了，很明显这不应该。

可以使用JDBC提供的事务处理操作来进行手工的事务控制，所有的操作方法都在`Connection`接口里定义：

* 事务提交：`public void commit() throws SQLException`；
* 事务回滚：`public void rollback() throws SQLException`；
* 设置是否为自动提交：`public void setAutoCommit(boolean autoCommit) throws SQLException`；

**范例：** 利用事务处理

```java
public class TestDemo {
	private static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DBURL = "jdbc:oracle:thin:@localhost:1521:orcl";
	private static final String USER = "scott";
	private static final String PASSWORD = "tiger";
	public static void main(String[] args) throws Exception {
		Class.forName(DBDRIVER);
		Connection conn = DriverManager.getConnection(DBURL, USER, PASSWORD);
		Statement stmt = conn.createStatement();
		conn.setAutoCommit(false);
		try {
			stmt.addBatch("INSERT INTO member(mid,name) VALUES(myseq.nextval,'测试A')");
			stmt.addBatch("INSERT INTO member(mid,name) VALUES(myseq.nextval,'测试B')");
			stmt.addBatch("INSERT INTO member(mid,name) VALUES(myseq.nextval,'测试C')");
			stmt.addBatch("INSERT INTO member(mid,name) VALUES(myseq.nextval,'测试D')");
			stmt.addBatch("INSERT INTO member(mid,name) VALUES(myseq.nextval,'测试E')");
			stmt.addBatch("INSERT INTO member(mid,name) VALUES(myseq.nextval,'测试F')");
			int[] result = stmt.executeBatch(); // 执行批处理
			System.out.println(Arrays.toString(result));
			conn.commit(); // 如果没有错误进行提交
		} catch (Exception e) {
			e.printStackTrace();
			conn.rollback(); // 如果出现异常则进行回滚
		}
		conn.close();
	}
}
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://shepherd-xie.gitbook.io/be-a-javaer/di-3-zhang-java-gao-ji-bian-cheng/section-33.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.