JDBC-数据库事务(转账实例)

1.数据库设计

CREATE TABLE t_bank(
   id INT PRIMARY KEY AUTO_INCREMENT COMMENT '账号主键',
   account VARCHAR(20) NOT NULL UNIQUE COMMENT '账号',
   money  INT UNSIGNED COMMENT '金额,不能为负值') ;
   
INSERT INTO t_bank(account,money) VALUES
  ('张三',1000),('李四',1000);

2.代码结构设计

3.代码实现

• BankService

//在Service层统一注册驱动和创建连接
public class BankService {
    public void Transfer(String addAcount,String subAccount,int num) throws  Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu?user=root&password=123456");
        int flag=0;
        try {
            connection.setAutoCommit(false); //关闭自动提交
            BankDao bankDao = new BankDao();
            bankDao.addAMoney(num, addAcount, connection);
            bankDao.subAMoney(num, subAccount, connection);
            connection.commit(); //提交事务
            System.out.println(addAcount+"转入"+num+"  "+subAccount+"转出"+num);
            flag=1;
        }catch (Exception e){
            connection.rollback();//如果出错就事务回滚，否则可能出现加钱账户加钱成功，但是减钱账户减钱失败
            throw e;
        }finally {
            if (flag==1){
                System.out.println("转账成功");
            }else {
                System.out.println("转账失败");
            }
            connection.close();
        }
    }
}

• BankDao

public class BankDao {
    public void addAMoney(int num,String addAccount,Connection connection) throws Exception {
        String sql="update t_bank set money=money+ ? where account=?;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1,num);
        preparedStatement.setObject(2,addAccount);
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }
    public void subAMoney(int num,String subAccount,Connection connection) throws Exception {
        String sql="update t_bank set money=money- ? where account=?;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1,num);
        preparedStatement.setObject(2,subAccount);
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }
}

• BankTest

public class BankTest {
    public static void main(String[] args) throws Exception {
        String addAccount="张三";
        String subAccount="李四";
        int num=500;
        BankService bankService = new BankService();
        bankService.Transfer(addAccount,subAccount,num);
    }
}

• 运行结果(这是转账成功的图像，如果失败则只显示转账失败)