JDBC回滚_事务

通常我们执行关联的sql语句时,需要保证都执行完成,比如转账操作,一个金额增加的同时另一个必须减少

数据库

mysql> select * from fz04;
+--------+------+
| name   | num  |
+--------+------+
| 小明   |  900 |
| 小黄   | 1100 |
+--------+------+
2 rows in set (0.00 sec)
mysql>

转账操作

public class Main1 {
    public static void main(String[] args) throws Exception{
        String url = "jdbc:mysql://127.0.0.1:3306/fzhl_";
        String user = "root";
        String pass = "root";

        Connection connection = DriverManager.getConnection(url,user,pass);

        String sql = "update fz04 set num = num - ? where name = ?";
        String sql2 = "update fz04 set num = num + ? where name = ?";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,100);
        preparedStatement.setString(2,"小明");
        System.out.println(preparedStatement.executeUpdate()!=0?"成功":"失败");

        preparedStatement = connection.prepareStatement(sql2);
        preparedStatement.setInt(1,100);
        preparedStatement.setString(2,"小黄");
        System.out.println(preparedStatement.executeUpdate()!=0?"成功":"失败");
      
        connection.close();
        preparedStatement.close();
    }
}

但在sql语句中插入一个异常这段程序就会卡壳

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,100);
        preparedStatement.setString(2,"小明");
        System.out.println(preparedStatement.executeUpdate()!=0?"成功":"失败");
/
        int a = 1/0;
/
        preparedStatement = connection.prepareStatement(sql2);
        preparedStatement.setInt(1,100);
        preparedStatement.setString(2,"小黄");
        System.out.println(preparedStatement.executeUpdate()!=0?"成功":"失败");

结果

D:\java-实验\java_2023\Jdbc\jdbc\libs\mysql-connector-j-8.0.33.jar huigun.Main1
成功
Exception in thread "main" java.lang.ArithmeticException: / by zero
	at huigun.Main1.main(Main1.java:25)

进程已结束,退出代码1

可以看到只执行一条sql语句,但语句是关联的,不能出现这种状况,所以开始使用事务来解决该操作

事务的3要素

1设置事务

// 设置开启事务  false为打开开关
connection.setAutoCommit(false);

2无异常后之前设置事务之前所有

// 提交事务
connection.commit();

3出现异常开始捕获,然后后回滚

// 回滚的事务点
// 在没有设置事务点时 默认回到事务开始的状态
connection.rollback();

演示

public class Main1 {
    public static void main(String[] args) throws Exception{
        String url = "jdbc:mysql://127.0.0.1:3306/fzhl_";
        String user = "root";
        String pass = "root";

        Connection connection = DriverManager.getConnection(url,user,pass);

        String sql = "update fz04 set num = num - ? where name = ?";
        String sql2 = "update fz04 set num = num + ? where name = ?";

        PreparedStatement preparedStatement = null;

        try {
            // 开启事务
            connection.setAutoCommit(false);

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,100);
            preparedStatement.setString(2,"小明");
            System.out.println(preparedStatement.executeUpdate()!=0?"成功":"失败");

            int a = 1/0;

            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setInt(1,100);
            preparedStatement.setString(2,"小黄");
            System.out.println(preparedStatement.executeUpdate()!=0?"成功":"失败");

            // 提交事务
            connection.commit();

        }catch (Exception e){
            System.out.println("出现异常开始执行回滚");
            // 回滚的事务点
            // 在没有设置事务点时 默认回到事务开始的状态
            connection.rollback();
        }

        connection.close();
        preparedStatement.close();
    }
}