买瓜前装模作样拍一拍,是对西瓜最起码的尊重。—吃货语录
一、在JDBC中事务的应用
- 如果JDBC连接处于自动提交模式,默认情况下,则每个SQL语句在完成后都会提交到数据库。
- 要启用手动事务支持,需要使用Connection对象的setAutoCommit()方法。如果将boolean false传递给setAutoCommit(),则关闭自动提交。另外,可以传递一个布尔值true来重新打开它。
try{
//Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);
Statement statement = conn.createStatement();
String SQL1 = "INSERT INTO Employees values (106, 20, 'Rita')";
statement.executeUpdate(SQL1);
//Submit a wrong SQL statement that breaks
String SQL2 = "INSERTED IN Employees VALUES (107, 22, 'Sita')";
statement.executeUpdate(SQL2);
// If there is no error.
conn.commit();
}catch(SQLException e){
// If there is any error.
conn.rollback();
}
二、SavePoints
定义:可以理解为游戏中的中途存档点的意思,在游戏中死去不会重头开始,数据会回滚到存档点的位置。
Connection对象有两种新的方法来管理保存点 -
- setSavepoint(String savepointName):定义新的保存点。它还返回一个Savepoint对象。
- releaseSavepoint(Savepoint savepointName):删除保存点。请注意,它需要一个Savepoint
对象作为参数。此对象通常是由setSavepoint()方法生成的保存点。
Savepoint savepoint = null;
try{
//Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);
Statement statement = conn.createStatement();
String SQL1 = "INSERT INTO Employees VALUES (106, 20, 'Rita')";
statement.executeUpdate(SQL1);
savepoint = conn.setSavepoint("savepoint");
//Submit a wrong SQL statement that breaks
String SQL2 = "INSERTED IN Employees VALUES (107, 22, 'Sita')";
statement.executeUpdate(SQL2);
conn.commit();
}catch(SQLException se){
conn.rollback(savepoint);
// 回到保存点后,进行数据提交,只会进行第一个语句的插入
conn.commit();
}
三、实际案例中一定要进行事务的操作:转账
A账户的金额增加,与B账户的金额减少,必须同时成功,或者同时失败
因此,是否设置自动提交为false,要结合具体的业务要求
try{
conn.setAutoCommit(false);
Statement statement = conn.createStatement();
String SQL1 = "update money set cash=cash-100 where userid=1";
statement.executeUpdate(SQL1);
String SQL2 = "update money set cash=cash+100 where userid=2";
statement.executeUpdate(SQL2);
conn.commit();
}catch(SQLException e){
conn.rollback();
}
四、批处理
批量处理允许我们将相关的SQL语句分组到批处理中,并通过对数据库的一次调用提交它们。
当一次向数据库发送多个SQL语句时,可以减少连接数据库的开销,从而提高性能。
两种方式的批处理:
- 状态通道
- 预状态通道
Statement
只要是增删改,都可以进行事务的提交
try{
//Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);
Statement statement = conn.createStatement();
String SQL1 = "insert into Employees values (106, 20, 'Rita')";
statement.addBatch(SQL1);
String SQL2 = "insert into Employees values (107, 22, 'Sita')";
statement.addBatch(SQL2);
String SQL3 = "insert into Employees values (108, 23, 'Pika')";
statement.addBatch(SQL3);
// 返回一个batch中,受影响行数的数组
int[] ints = statement.executeBatch();
// If there is no error.
conn.commit();
}catch(SQLException e){
// If there is any error.
conn.rollback();
}
PreparedStatement
try{
//Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);
String sql = "insert into teacher(tname) values (?)";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1,"张三1");
statement.addBatch();
statement.setString(1,"张三2");
statement.addBatch();
statement.setString(1,"张三3");
statement.addBatch();
int[] ints = statement.executeBatch();
// If there is no error.
conn.commit();
}catch(SQLException e){
// If there is any error.
conn.rollback();
}