MySQL数据库事务实例（模拟银行转账）

末蓝、 2022-06-13 00:51 393阅读 0赞

在数据库系列文章中[MySQL数据库事务基本操作](http://blog.csdn.net/fengpojian/article/details/73571983)
    介绍了MySQL数据库基本的事务操作。这篇文章将介绍一个实例来更好的理解学习MySQL数据库事务。
    这个Demo主要是模拟银行转账的过程，两个账户发生转账关系时，首先改变双方账户的余额状态。
    随后验证转出方账户是否有足够的余额来支撑此次的转账操作，若余额不足，则抛出“账户余额不足”异常。
    请注意，这个Demo只适用于单线程的情况。

java
    /**
     * 单线程下的数据库事务
     * 模拟银行转账
     * @author LinGer
     *
     */
    public class Transaction
    {
        
        public boolean transfrom(int fromId, int toId, double amount)
        {
            Connection con = MyUtil.getCon();
            String sql1 = "update tb_account set balance = balance - ? where userId = ?;";
            String sql2 = "update tb_account set balance = balance + ? where userId = ?;";
            String sql3 = "select balance  from tb_account where userId = ?;";
            PreparedStatement ps1 = null;
            PreparedStatement ps2 = null;
            PreparedStatement ps3 = null;
            ResultSet rs = null;
            try
            {
                //开启事务
                con.setAutoCommit(false);
                //转账时涉及的两个账户以及各账户的金额变动
                ps1 = con.prepareStatement(sql1);
                ps1.setDouble(1, amount);
                ps1.setInt(2, fromId);
                ps1.executeUpdate();
    
                ps2 = con.prepareStatement(sql2);
                ps2.setDouble(1, amount);
                ps2.setInt(2, toId);
                ps2.executeUpdate();
    
                //检查转出方账户的余额是否足够支持此次转账金额；如果余额不足，则抛出“余额不足”异常，并回滚
                ps3 = con.prepareStatement(sql3);
                ps3.setInt(1, fromId);
                rs = ps3.executeQuery();
                Double balance = 0.0;
                if(rs.next())
                {
                    balance = rs.getDouble("balance");
                }
                if(balance < 0)
                {
                    throw new Exception("账户余额不足");
                }
                con.commit();
                return true;
            } catch (Exception e)
            {
                e.printStackTrace();
            }
            try
            {
                con.rollback();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }finally{
                try
                {
                    rs.close();
                    ps1.close();
                    ps2.close();
                    ps3.close();
                    con.close();
                } catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }
            return false;
        }
    }

java
    /**
     * 工具类 获得Connection连接
     * @return
     */
    public static Connection getCon()
        {
            String DBDriver = "com.mysql.jdbc.Driver";
            String DBUrl = "jdbc:mysql://localhost:3306/study?autoReconnect=true&"
                    + "failOverReadOnly=false&useUnicode=true&characterEncoding=UTF-8";
            String name = "root";
            String pass = "123";
            Connection con = null;
            try {
                Class.forName(DBDriver);
                con = DriverManager.getConnection(DBUrl,name,pass);
            } catch (Exception e) {
                e.printStackTrace();
            }
    
            return con;
        }

java
    /**
     * 实体类 模拟银行账户
     * @author LinGer
     *
     */
    public class User
    {
        
        private int userId;
        private String userName;
        private Double balance; //账户余额
    
        //get && set
        public int getUserId()
        {
            return userId;
        }
        public void setUserId(int userId)
        {
            this.userId = userId;
        }
        public String getUserName()
        {
            return userName;
        }
        public void setUserName(String userName)
        {
            this.userName = userName;
        }
        public Double getBalance()
        {
            return balance;
        }
        public void setBalance(Double balance)
        {
            this.balance = balance;
        }
    }

这篇博客的代码参考了https://www.oschina.net/code/snippet_2271303_44733#62843
    非常感谢博主。