JDBC事务处理
一.JDBC事务处理概述
事务隔离级别 描述 TRANSACTION_NONE 该级别不支持事务 TRANSACTION_READ_UNCOMMITTED 该级别允许脏读、不可重复读和幻读 TRANSACTION_READ_COMMITTED 该级别禁止脏读,但允许不可重复读和幻读(默认) TRANSACTION_REPEATABLE_READ 该级别禁止脏读和不可重复读,但允许幻读 TRANSACTION_SERIALIZABLE 该级别禁止脏读、不可重复读和幻读
二.JDBC中使用事务
1.设置隔离级别
使用Connection对象调用setTransactionIsolation( )方法设置事务隔离级别
Connection对象.setTransactionIsolation(Connection.TRANSACTION_Level);
2.管控事务
java.sql.Connection接口中还提供了三个方法用于管控事务
方法 方法描述 setAutoCommit(boolean autoCommit) 该方法用于设置是否自动提交事务 commit() 该方法用于提交事务 rollback() 该方法用于撤销事务,即事务的回滚
3.使用管控事务
默认情况下,MySQL是关闭事务( 即打开自动提交) 的,也就是说:用户执行的每条SQL语句会立马生效。 JDBC,在默认情况下JDBC也是关闭事务( 即打开自动提交) 的,也就是说:在JDBC操作中每条SQL语句一旦执行 ,便会立即提交到数据库并立即生效且无法对其进行回滚操作。
Connection 对象. setAutoCommit ( false )
三.JDBC事务处理示例
1.数据准备
DROP TABLE IF EXISTS account;
-- 创建account表
CREATE TABLE account(
id INT primary key auto_increment,
name VARCHAR(40),
money FLOAT
);
-- 向表中插入数据
INSERT INTO account(name, money) VALUES ('A',1000);
INSERT INTO account(name, money) VALUES ('B',1000);
2.工具类JDBCUtil
package cn. com. demo5 ;
import java. sql. Connection ;
import java. sql. DriverManager ;
import java. sql. ResultSet ;
import java. sql. SQLException ;
import java. sql. Statement ;
public class JDBCUtils {
public static Connection getConnection ( ) throws SQLException , ClassNotFoundException {
Class . forName ( "com.mysql.jdbc.Driver" ) ;
String databaseUrl = "jdbc:mysql://localhost:3306/mydb" ;
String username = "root" ;
String password = "test" ;
Connection connection = DriverManager . getConnection ( databaseUrl, username, password) ;
return connection;
}
public static void release ( Statement statement) {
if ( statement != null ) {
try {
statement. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
statement = null ;
}
}
public static void release ( Connection connection) {
if ( connection != null ) {
try {
connection. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
connection = null ;
}
}
public static void release ( Statement statement, Connection connection) {
if ( statement != null ) {
try {
statement. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
statement = null ;
}
if ( connection != null ) {
try {
connection. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
connection = null ;
}
}
public static void release ( ResultSet resultSet, Statement statement, Connection connection) {
if ( resultSet != null ) {
try {
resultSet. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
resultSet = null ;
}
release ( statement, connection) ;
}
}
3.测试类
package cn.com.demo05;
import java.sql.Connection;
import java.sql.PreparedStatement ;
import java.sql.SQLException;
public class TestJDBC{
public static void main(String[] args) {
String outAccount = "A";
String inAccount = "B";
float amount = 200;
Connection connection = null;
PreparedStatement outPreparedStatement = null;
PreparedStatement inPreparedStatement = null;
try {
connection = JDBCUtils.getConnection();
// 关闭事务自动提交
connection.setAutoCommit(false);
// 账户A转出200
String outSQL = "UPDATE account SET money = money-? WHERE name=?";
outPreparedStatement = connection.prepareStatement(outSQL);
outPreparedStatement.setFloat(1, amount);
outPreparedStatement.setString(2, outAccount);
outPreparedStatement.executeUpdate();
//模拟异常
//int error=9527/0;
// 账户B转入200
String inSQL = "UPDATE account SET money=money+? where name=?";
inPreparedStatement = connection.prepareStatement(inSQL);
inPreparedStatement.setFloat(1, amount);
inPreparedStatement.setString(2, inAccount);
inPreparedStatement.executeUpdate();
// 提交事务
connection.commit();
System.out.println("转账成功");
} catch (Exception e) {
try {
// 回滚事务
connection.rollback();
System.out.println("转账失败");
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
JDBCUtils.release(connection);
JDBCUtils.release(inPreparedStatement);
JDBCUtils.release(outPreparedStatement);
}
}
}