通过JDBC操作事务示例

1. 简单的事务操作示例

import utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class TestTransaction {
    private static Connection connect = null;

    private static PreparedStatement preparedStatement = null;


    public static void main(String[] args) throws Exception {
        connect = JdbcUtils.getConnect();

        // 关闭自动提交
        connect.setAutoCommit(false);

        String sql = "update workdb.t_students set score = score - 10 where name = '小明'";
        preparedStatement = connect.prepareStatement(sql);
        preparedStatement.executeUpdate();

        String sql2 = "update workdb.t_students set score = score + 10 where name = '小美'";
        preparedStatement = connect.prepareStatement(sql2);
        preparedStatement.executeUpdate();

		// 提交事务
        connect.commit();
        System.out.println("commit successful!");

		// 关闭连接
        JdbcUtils.releaseConnection(connect, preparedStatement, null);
    }
}

执行前：

执行后：

2.测试事务回滚

import utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;

/**
 * @author: 乌蝇哥
 * @createDate: 2023/6/28
 * @description:
 */
public class TestTransaction {
    private static Connection connect = null;

    private static PreparedStatement preparedStatement = null;


    public static void main(String[] args) throws Exception {
        connect = JdbcUtils.getConnect();

        // 关闭自动提交
        connect.setAutoCommit(false);

        String sql = "update workdb.t_students set score = score - 10 where name = '小明'";
        preparedStatement = connect.prepareStatement(sql);
        preparedStatement.executeUpdate();
        // 如果这里提交了，则程序异常后，不能rollback回滚
//        connect.commit();

        // 插入异常, 只要没有commit提交事务，程序异常后会自动rollback
        int i = 2 / 0;

        String sql2 = "update workdb.t_students set score = score + 10 where name = '小美'";
        preparedStatement = connect.prepareStatement(sql2);
        preparedStatement.executeUpdate();

        // 提交事务
        connect.commit();
        System.out.println("commit successful!");

        // 关闭连接
        JdbcUtils.releaseConnection(connect, preparedStatement, null);
    }
}

执行提示异常：

数据没有改变：