JAVA-EE在MVC模式下完成银行转账业务不使用ThreadLocal通过传参的方式确保Connection对象一致性保证事务------计算机网络经典

package com.bjpowernode.Bank.Dao;

import com.bjpowernode.Bank.Pojo.Account;

import java.sql.Connection;
import java.util.List;

public interface AccountDao
{
    //DAO,Data Access Object数据访问对象
    //取名规范取决于处理的表名字
    int insert(Account account, Connection connection);
    int deleteByActno(Long id, Connection connection);
    int update(Account account, Connection connection);
    Account selectByActno(String actno, Connection connection);
    List<Account> selectAll(Connection connection);
}

package com.bjpowernode.Bank.Dao;

import com.bjpowernode.Bank.Pojo.Account;

import java.sql.Connection;
import java.util.List;

public interface AccountDao
{
    //DAO,Data Access Object数据访问对象
    //取名规范取决于处理的表名字
    int insert(Account account, Connection connection);
    int deleteByActno(Long id, Connection connection);
    int update(Account account, Connection connection);
    Account selectByActno(String actno, Connection connection);
    List<Account> selectAll(Connection connection);
}

package com.bjpowernode.Bank.Dao.Impl;

import com.bjpowernode.Bank.Dao.AccountDao;
import com.bjpowernode.Bank.Pojo.Account;
import com.bjpowernode.oa.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AccountDaoImpl implements AccountDao
{
    @Override
    public int insert(Account account, Connection connection)
    {
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        int count = 0;
        try
        {
            String sql = "insert into t_act(id,actno,balance) values(?,?,?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1,null);
            statement.setString(2,account.getActno());
            statement.setDouble(3,account.getBalance());
            count = statement.executeUpdate();
        }
        catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            DBUtil.close(null,statement,resultSet);
        }
        return count;
    }

    @Override
    public int deleteByActno(Long id, Connection connection) {
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        int count = 0;
        try
        {
            String sql = "delete from t_act where id = ?";
            statement = connection.prepareStatement(sql);
            statement.setLong(1,id);
            count = statement.executeUpdate();
        }
        catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            DBUtil.close(null,statement,resultSet);
        }
        return count;
    }

    @Override
    public int update(Account account, Connection connection) {
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        int count = 0;
        try
        {
            String sql = "update t_act set actno = ?,balance = ? where id = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,account.getActno());
            statement.setDouble(2,account.getBalance());
            statement.setLong(3,account.getId());
            count = statement.executeUpdate();
        }
        catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            DBUtil.close(null,statement,resultSet);
        }
        return count;
    }

    @Override
    public Account selectByActno(String actno, Connection connection) {
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        Account account = new Account();
        try
        {
            String sql = "select * from t_act where actno = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,actno);
            resultSet = statement.executeQuery();
            if(resultSet.next())
            {
                account.setActno(actno);
                Long id = resultSet.getLong("id");
                account.setId(id);
                double balance = resultSet.getDouble("balance");
                account.setBalance(balance);
            }
        }
        catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            DBUtil.close(null,statement,resultSet);
        }
        return account;
    }

    @Override
    public List<Account> selectAll(Connection connection) {
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        List<Account> list = null;
        try
        {
            String sql = "select * from t_act";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while(resultSet.next())
            {
                list = new ArrayList<Account>();
                Account account = new Account();
                String actno = resultSet.getString("actno");
                account.setActno(actno);
                Long id = resultSet.getLong("id");
                account.setId(id);
                double balance = resultSet.getDouble("balance");
                account.setBalance(balance);
                list.add(account);
            }
        }
        catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            DBUtil.close(null,statement,resultSet);
        }
        return list;
    }
}

package com.bjpowernode.Bank.Dao.Impl;

import com.bjpowernode.Bank.Dao.AccountDao;
import com.bjpowernode.Bank.Pojo.Account;
import com.bjpowernode.oa.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AccountDaoImpl implements AccountDao
{
    @Override
    public int insert(Account account, Connection connection)
    {
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        int count = 0;
        try
        {
            String sql = "insert into t_act(id,actno,balance) values(?,?,?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1,null);
            statement.setString(2,account.getActno());
            statement.setDouble(3,account.getBalance());
            count = statement.executeUpdate();
        }
        catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            DBUtil.close(null,statement,resultSet);
        }
        return count;
    }

    @Override
    public int deleteByActno(Long id, Connection connection) {
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        int count = 0;
        try
        {
            String sql = "delete from t_act where id = ?";
            statement = connection.prepareStatement(sql);
            statement.setLong(1,id);
            count = statement.executeUpdate();
        }
        catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            DBUtil.close(null,statement,resultSet);
        }
        return count;
    }

    @Override
    public int update(Account account, Connection connection) {
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        int count = 0;
        try
        {
            String sql = "update t_act set actno = ?,balance = ? where id = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,account.getActno());
            statement.setDouble(2,account.getBalance());
            statement.setLong(3,account.getId());
            count = statement.executeUpdate();
        }
        catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            DBUtil.close(null,statement,resultSet);
        }
        return count;
    }

    @Override
    public Account selectByActno(String actno, Connection connection) {
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        Account account = new Account();
        try
        {
            String sql = "select * from t_act where actno = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,actno);
            resultSet = statement.executeQuery();
            if(resultSet.next())
            {
                account.setActno(actno);
                Long id = resultSet.getLong("id");
                account.setId(id);
                double balance = resultSet.getDouble("balance");
                account.setBalance(balance);
            }
        }
        catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            DBUtil.close(null,statement,resultSet);
        }
        return account;
    }

    @Override
    public List<Account> selectAll(Connection connection) {
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        List<Account> list = null;
        try
        {
            String sql = "select * from t_act";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while(resultSet.next())
            {
                list = new ArrayList<Account>();
                Account account = new Account();
                String actno = resultSet.getString("actno");
                account.setActno(actno);
                Long id = resultSet.getLong("id");
                account.setId(id);
                double balance = resultSet.getDouble("balance");
                account.setBalance(balance);
                list.add(account);
            }
        }
        catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            DBUtil.close(null,statement,resultSet);
        }
        return list;
    }
}

package com.bjpowernode.Bank.exception;

public class AppException extends Exception
{
    public AppException(){}

    public AppException(String message) {
        super(message);
    }
}

package com.bjpowernode.Bank.exception;

public class AppException extends Exception
{
    public AppException(){}

    public AppException(String message) {
        super(message);
    }
}

package com.bjpowernode.Bank.exception;

public class MoneyNotEnoughException extends Exception
{
    public MoneyNotEnoughException(){}
    public MoneyNotEnoughException(String msg){super(msg);}
}

package com.bjpowernode.Bank.exception;

public class MoneyNotEnoughException extends Exception
{
    public MoneyNotEnoughException(){}
    public MoneyNotEnoughException(String msg){super(msg);}
}

package com.bjpowernode.Bank.Pojo;

public class Account
{
    private Long id;
    private String actno;
    private Double balance;

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", actno='" + actno + '\'' +
                ", balance=" + balance +
                '}';
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getActno() {
        return actno;
    }

    public void setActno(String actno) {
        this.actno = actno;
    }

    public Double getBalance() {
        return balance;
    }

    public void setBalance(Double balance) {
        this.balance = balance;
    }

    public Account(Long id, String actno, Double balance) {
        this.id = id;
        this.actno = actno;
        this.balance = balance;
    }

    public Account() {
    }
}

package com.bjpowernode.Bank.Pojo;

public class Account
{
    private Long id;
    private String actno;
    private Double balance;

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", actno='" + actno + '\'' +
                ", balance=" + balance +
                '}';
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getActno() {
        return actno;
    }

    public void setActno(String actno) {
        this.actno = actno;
    }

    public Double getBalance() {
        return balance;
    }

    public void setBalance(Double balance) {
        this.balance = balance;
    }

    public Account(Long id, String actno, Double balance) {
        this.id = id;
        this.actno = actno;
        this.balance = balance;
    }

    public Account() {
    }
}

package com.bjpowernode.Bank.service;

import com.bjpowernode.Bank.Dao.AccountDao;
import com.bjpowernode.Bank.exception.MoneyNotEnoughException;

public interface AccountService
{
    //业务类,专注于业务处理
    //不写别的,只处理业务信息
    void transfer(String fromActno,String toActno,double money) throws Exception;
}

package com.bjpowernode.Bank.service;

import com.bjpowernode.Bank.Dao.AccountDao;
import com.bjpowernode.Bank.exception.MoneyNotEnoughException;

public interface AccountService
{
    //业务类,专注于业务处理
    //不写别的,只处理业务信息
    void transfer(String fromActno,String toActno,double money) throws Exception;
}

package com.bjpowernode.Bank.service.Impl;

import com.bjpowernode.Bank.Dao.AccountDao;
import com.bjpowernode.Bank.Dao.Impl.AccountDaoImpl;
import com.bjpowernode.Bank.Pojo.Account;
import com.bjpowernode.Bank.exception.AppException;
import com.bjpowernode.Bank.exception.MoneyNotEnoughException;
import com.bjpowernode.Bank.service.AccountService;
import com.bjpowernode.oa.utils.DBUtil;

import java.sql.Connection;
import java.sql.SQLException;

public class AccountServiceImpl implements AccountService
{
    private AccountDao accountDao = new AccountDaoImpl();
    @Override
    public void transfer(String fromActno, String toActno, double money) throws Exception {
        //在service层开启事务
        try(Connection connection = DBUtil.getConnection();)
        {
            connection.setAutoCommit(false);
            Account fromAccount = accountDao.selectByActno(fromActno,connection);
            if(fromAccount.getBalance() < money)
            {
                throw new MoneyNotEnoughException("余额不足");
            }
            Account toAccount = accountDao.selectByActno(toActno,connection);
            fromAccount.setBalance(fromAccount.getBalance() - money);
            toAccount.setBalance(toAccount.getBalance() + money);
            int count = accountDao.update(fromAccount,connection);
            count = count + accountDao.update(toAccount,connection);
            if(count != 2)
            {
                throw new AppException("转账失败");
            }
            connection.commit();
        }
        catch(SQLException e)
        {
            throw new AppException("转账异常,请联系客服");
        }
        finally
        {
            DBUtil.close(DBUtil.getConnection(),null,null);
        }
    }
}

package com.bjpowernode.Bank.service.Impl;

import com.bjpowernode.Bank.Dao.AccountDao;
import com.bjpowernode.Bank.Dao.Impl.AccountDaoImpl;
import com.bjpowernode.Bank.Pojo.Account;
import com.bjpowernode.Bank.exception.AppException;
import com.bjpowernode.Bank.exception.MoneyNotEnoughException;
import com.bjpowernode.Bank.service.AccountService;
import com.bjpowernode.oa.utils.DBUtil;

import java.sql.Connection;
import java.sql.SQLException;

public class AccountServiceImpl implements AccountService
{
    private AccountDao accountDao = new AccountDaoImpl();
    @Override
    public void transfer(String fromActno, String toActno, double money) throws Exception {
        //在service层开启事务
        try(Connection connection = DBUtil.getConnection();)
        {
            connection.setAutoCommit(false);
            Account fromAccount = accountDao.selectByActno(fromActno,connection);
            if(fromAccount.getBalance() < money)
            {
                throw new MoneyNotEnoughException("余额不足");
            }
            Account toAccount = accountDao.selectByActno(toActno,connection);
            fromAccount.setBalance(fromAccount.getBalance() - money);
            toAccount.setBalance(toAccount.getBalance() + money);
            int count = accountDao.update(fromAccount,connection);
            count = count + accountDao.update(toAccount,connection);
            if(count != 2)
            {
                throw new AppException("转账失败");
            }
            connection.commit();
        }
        catch(SQLException e)
        {
            throw new AppException("转账异常,请联系客服");
        }
        finally
        {
            DBUtil.close(DBUtil.getConnection(),null,null);
        }
    }
}

package com.bjpowernode.Bank.servlet;

import com.bjpowernode.Bank.service.AccountService;
import com.bjpowernode.Bank.service.Impl.AccountServiceImpl;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

import java.io.IOException;
import java.io.PrintWriter;

@WebServlet("/bank/transfer")
public class AccountServlet extends HttpServlet
{
    private AccountService accountService = new AccountServiceImpl();
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {
        //接收数据
        String fromActno = request.getParameter("fromActno");
        String toActno = request.getParameter("toActno");
        Double value = Double.valueOf(request.getParameter("Value"));
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try
        {
            accountService.transfer(fromActno,toActno,value);
            out.println("转账成功");
        }
        catch (Exception e)
        {
            out.println(e.getMessage());
        }
    }
}

package com.bjpowernode.Bank.servlet;

import com.bjpowernode.Bank.service.AccountService;
import com.bjpowernode.Bank.service.Impl.AccountServiceImpl;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

import java.io.IOException;
import java.io.PrintWriter;

@WebServlet("/bank/transfer")
public class AccountServlet extends HttpServlet
{
    private AccountService accountService = new AccountServiceImpl();
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {
        //接收数据
        String fromActno = request.getParameter("fromActno");
        String toActno = request.getParameter("toActno");
        Double value = Double.valueOf(request.getParameter("Value"));
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try
        {
            accountService.transfer(fromActno,toActno,value);
            out.println("转账成功");
        }
        catch (Exception e)
        {
            out.println(e.getMessage());
        }
    }
}

package com.bjpowernode.Bank.utils;

import java.sql.*;
import java.util.ResourceBundle;

public class DBUtil
{
//    private static ThreadLocal<Connection> threadLocal;
    private static ResourceBundle bundle = ResourceBundle.getBundle("resources.jdbc");
    private static String driver = bundle.getString("driver");
    private static String url = bundle.getString("url");
    private static String user = bundle.getString("user");
    private static String password = bundle.getString("password");
    //JDBC工具类
    static
    {
        //注册驱动
        try
        {
//            Class.forName("com.mysql.jdbc.Driver");//避免写死
            Class.forName(driver);
        }
        catch (ClassNotFoundException e)
        {
            throw new RuntimeException(e);
        }
    }
    public static Connection getConnection() throws SQLException {
        //获取链接
        return DriverManager.getConnection(url,user,password);
    }
    //释放资源
    public static void close(Connection connection, Statement statement, ResultSet resultSet)
    {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

package com.bjpowernode.Bank.utils;

import java.sql.*;
import java.util.ResourceBundle;

public class DBUtil
{
//    private static ThreadLocal<Connection> threadLocal;
    private static ResourceBundle bundle = ResourceBundle.getBundle("resources.jdbc");
    private static String driver = bundle.getString("driver");
    private static String url = bundle.getString("url");
    private static String user = bundle.getString("user");
    private static String password = bundle.getString("password");
    //JDBC工具类
    static
    {
        //注册驱动
        try
        {
//            Class.forName("com.mysql.jdbc.Driver");//避免写死
            Class.forName(driver);
        }
        catch (ClassNotFoundException e)
        {
            throw new RuntimeException(e);
        }
    }
    public static Connection getConnection() throws SQLException {
        //获取链接
        return DriverManager.getConnection(url,user,password);
    }
    //释放资源
    public static void close(Connection connection, Statement statement, ResultSet resultSet)
    {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
    <head>
        <title>Title</title>
        <base href="${pageContext.request.scheme}://${pageContext.request.serverName}:${pageContext.request.serverPort}${pageContext.request.contextPath}/">
    </head>
    <body>
        <form action="bank/transfer" method="post">
            转出账户:<input type="text" name="fromActno"><br>
            转入账户:<input type="text" name="toActno"><br>
            转账金额:<input type="text" name="Value"><br>
            <input type="submit" name="转账">
        </form>
    </body>
</html>

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
    <head>
        <title>Title</title>
        <base href="${pageContext.request.scheme}://${pageContext.request.serverName}:${pageContext.request.serverPort}${pageContext.request.contextPath}/">
    </head>
    <body>
        <form action="bank/transfer" method="post">
            转出账户:<input type="text" name="fromActno"><br>
            转入账户:<input type="text" name="toActno"><br>
            转账金额:<input type="text" name="Value"><br>
            <input type="submit" name="转账">
        </form>
    </body>
</html>