使用JDBC的事务处理

 创建表：

DROP TABLE `account` ;

-- 增加2条记录 ，用于测试ReslultSet

CREATE TABLE `account`(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	balance DOUBLE NOT NULL DEFAULT 0) CHARACTER SET utf8;

INSERT INTO `account` VALUES(NULL, '马 云', 3000);
INSERT INTO `account` VALUES(NULL, '马化腾', 10000);

UPDATE ACCOUNT SET balance = 3000 WHERE id =1;
SELECT * FROM `account`;

package day38.Transaction_;

import day38.utils.JDBCUtils;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @Author:monian
 * @Wo yi wu ta,wei shou shu er!
 * @Date:2023/11/15 22:00
 */
public class Transaction_ {
    @Test
    public void noTransaction() throws SQLException {

        //操作转账的业务

        //得到连接
        Connection connection = null;
        //填写sql语句
        String sqlsel = "select * from account";
        String sqlma = "update account set balance = balance - 100 where id = 1";
        String sqlteng = "update account set balance = balance + 100 where id= 2";
        //调用查询语句
        ResultSet set = null;
        // 执行语句
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();
            //执行第一条语句
            preparedStatement = connection.prepareStatement(sqlma);
            preparedStatement.executeUpdate();
            //执行第二条语句
            int m = 1 / 0;
            preparedStatement = connection.prepareStatement(sqlteng);
            //执行第三条语句
            preparedStatement.executeUpdate();
            preparedStatement = connection.prepareStatement(sqlsel);
            //调用返回结果，调用结果集
            set = preparedStatement.executeQuery();

            while (set.next()) {
                String name = set.getString("name");
                int id = set.getInt("id");
                double balance = set.getDouble("balance");
                System.out.println(id + "\t" + name + "\t" + balance + "\t");
            }
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            JDBCUtils.close(set, preparedStatement, connection);
        }
    }


    @Test
    public void Transaction() throws SQLException {
        //操作转账的业务

        //得到连接
        Connection connection = null;
        //填写sql语句
        String sqlsel = "select * from account";
        String sqlma = "update account set balance = balance - 100 where id = 1";
        String sqlteng = "update account set balance = balance + 100 where id= 2";
        //调用查询语句
        ResultSet set = null;
        // 执行语句
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();
            //将 connection 设置为不自动提交
            connection.setAutoCommit(false);
            //执行第一条语句
            preparedStatement = connection.prepareStatement(sqlma);
            preparedStatement.executeUpdate();
            //执行第二条语句
//            int m = 1 / 0;
            preparedStatement = connection.prepareStatement(sqlteng);
            //执行第三条语句
            preparedStatement.executeUpdate();
            preparedStatement = connection.prepareStatement(sqlsel);
            //调用返回结果，调用结果集
            set = preparedStatement.executeQuery();

            while (set.next()) {
                String name = set.getString("name");
                int id = set.getInt("id");
                double balance = set.getDouble("balance");
                System.out.println(id + "\t" + name + "\t" + balance + "\t");
            }
            //这里提交事务
            connection.commit();
        } catch (SQLException e) {
            //这里我们利用进行回滚，即撤销执行的SQL
            //默认回滚到事务开始的状态
            System.out.println("执行发生了异常，撤销执行的sql");
            try {
                connection.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            throw new RuntimeException();
        } finally {
            JDBCUtils.close(set, preparedStatement, connection);
        }
    }
}

在没有添加事务的时候，如果中间有剩下的语句没有执行，而前面的语句已经执行了。者并不是我们想要的结果，此时就需要创建事务。

注意事项：

1. connection默认是自动提交的，因此，使用事务需要将connection设置成flase。
2. 在事务中间，如果报错，或者语句没有执行完毕，使用回滚。connection.rollback();
3. 在事务最后执行的最后添加，connection.commit;代表所有语句都能执行成功，提交事务。