JDBC处理事务
需求:模拟含有两个SOL语句的事务
(同时成功或同时回滚)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*
* mysql默认自动提交事务,执行一句,提交一句
* 模拟:一个事务有两个sql语句
* (1):用于更新 test库中teacher表的‘04’为‘06’
* (2):用与添加test库的teacher表的‘07’‘柯南’
* Connection和事务相关API
* 手动提交
* setAutoCommit
* 如果一切正常,提交事务,Connection连接对象.
* commit
* 如果有问题,回滚连接事务
* rollback
*
* */
public class TestTransaction {
public static void main(String[] args) throws SQLException {
update();
insert();
}
public static void update() throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
// 注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 获取连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
// 加一步 设置事务为手动提交
connection.setAutoCommit(false);
// SQL语句
String sql = "update teacher set tid = ? where tid = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,"06");
preparedStatement.setObject(2,"04");
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("更新成功");
connection.commit();
}else {
System.out.println("更新失败");
connection.rollback();
}
}catch (Exception e){
connection.rollback();
}finally {
// 关闭
preparedStatement.close();
connection.close();
}
}
public static void insert() throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
// 注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 获取连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
// 加一步 设置事务为手动提交
connection.setAutoCommit(false);
// SQL语句
String sql = "insert into teacher values(?,?,null) ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,"07");
preparedStatement.setObject(2,"柯南");
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("添加成功");
connection.commit();
}else {
System.out.println("添加失败");
connection.rollback();
}
}catch (Exception e){
connection.rollback();
}finally {
// 关闭
preparedStatement.close();
connection.close();
}
}
}
这样的话,一个SQL语句成功而另一个SQL语句失败的话只会回滚一个
解决方法:两个方法必须共享同一个Connection对象
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*
* mysql默认自动提交事务,执行一句,提交一句
* 模拟:一个事务有两个sql语句
* (1):用于更新 test库中teacher表的‘04’为‘06’
* (2):用与添加test库的teacher表的‘07’‘柯南’
* Connection和事务相关API
* 手动提交
* setAutoCommit
* 如果一切正常,提交事务,Connection连接对象.
* commit
* 如果有问题,回滚连接事务
* rollback
* 同一个事务必须共享同一个Connection对象
*
*
* 注意:
* 1.同一个事务的SQL必须保证是同一个Connection对象
* 2.在执行SQL之前Connection连接对象,setAutoCommit(false)
* 3.执行SQL
* 4.如果没有异常,一切正常,Connection连接对象.commit()
* 5.如果有问题,Connection连接对象,rollback()
* 6.关闭连接之前,请使用connection.setAutoCommit(true);
* */
public class TestTransaction {
public static void main(String[] args) throws SQLException {
// 注册驱动
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
update(connection);
insert(connection);
connection.commit();
} catch (ClassNotFoundException e) {
System.out.println("执行失败");
connection.rollback();
} catch (SQLException e) {
System.out.println("执行失败");
connection.rollback();
}finally {
// 为了和后面代码保持一致 因为后面的连接可能重复利用
connection.setAutoCommit(true);
connection.close();
}
}
public static void update(Connection connection) throws SQLException {
// 加一步 设置事务为手动提交
connection.setAutoCommit(false);
// SQL语句
String sql = "update teacher set tid = ? where tid = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,"16");
preparedStatement.setObject(2,"06");
int i = preparedStatement.executeUpdate();
System.out.println(i);
// 关闭 不能关闭Connection对象,防止一个SQL事务完成另一个还没开始
preparedStatement.close();
}
public static void insert(Connection connection) throws SQLException {
// 加一步 设置事务为手动提交
connection.setAutoCommit(false);
// SQL语句
String sql = "insert into teacher values(?,?,null) ";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,"07");
preparedStatement.setObject(2,"柯南");
int i = preparedStatement.executeUpdate();
// 关闭
preparedStatement.close();
}
}