您的位置: 首页  >  IT文章  >  完成转账功能 1 数据库准备 2 其它准备 3 代码实现  4 解决conn在service层的问题

完成转账功能 1 数据库准备 2 其它准备 3 代码实现  4 解决conn在service层的问题

分类: IT文章 • 2022-08-26 09:30:09

1创建表

CREATE TABLE account(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20),
  money DOUBLE 
);

2添加数据

INSERT INTO account (NAME,money) VALUES ('tom',3000),('jack',3000);

2 其它准备

1）搭建好三层架构的模型

2）导入需要的jar包

3）导入c3p0连接池配置文件

4）编写c3p0工具类

3 代码实现

1 客户端转账页面transfer.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
    <form action="${pageContext.request.contextPath }/transfer" method="post">
        转出账户：<input type="text" name="out"/><br/>
        转入账户：<input type="text" name="in"/><br/>
        转张金额：<input type="text" name="money"/><br/>
        <input type="submit" value="确认转账"/>
        
    </form>
</body>
</html>

2 web层TransferServlet 

package www.test.web;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import www.test.service.TransferService;

public class TransferServlet extends HttpServlet {

    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        // 1 获取客户端输入的数据
        String out = request.getParameter("out");
        String in = request.getParameter("in");
        String moneyStr = request.getParameter("money");
        double money = Double.parseDouble(moneyStr);
        
        // 2 调用业务层的转账方法
        TransferService service = new TransferService();
        boolean isTransferSuccess = service.transfer(out,in,money);
        
        // 3 将处理结果返回给用户
           // 解决乱码
         response.setContentType("text/html;charset=UTF-8");
           // 输出结果
         if(isTransferSuccess){
             response.getWriter().write("恭喜你，转账成功");
         }else{
             response.getWriter().write("很遗憾，转账失败");
         }
    }

    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }

}

3 service层TransferService

package www.test.service;

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

import www.test.dao.TransferDao;
import www.test.utils.C3P0Utils;

public class TransferService {

    public boolean transfer(String out, String in, double money) {
        TransferDao dao = new TransferDao();
        
        boolean isTranferSuccess = true;
        Connection conn = null;
        try {
            // 1 获取一个连接
            conn = C3P0Utils.getConnection();
            // 2 开启事务
            conn.setAutoCommit(false);
            
            // 3 转出钱的方法 ,用flag判断操作成功
            boolean flagout = dao.out(conn,out,money);
            
            // 4 转入钱的方法
            boolean flagin = dao.in(conn,in,money);
            
            if(!(flagout&&flagin)){ //只要有一方失败都无效，执行回滚
                isTranferSuccess = false;
                conn.rollback();
            }
        } catch (SQLException e) {
            isTranferSuccess = false;
            try {
                // 5 回滚事务
                conn.rollback();
            } catch (SQLException e1) {
                
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally{
            try {
                // 6 提交事务 
                // 建议放到这里，就算不成功，会回滚，回滚之后继续提交。
                conn.commit();
            } catch (SQLException e) {
                
                e.printStackTrace();
            }
        }
        
        return isTranferSuccess;
    }

    

}

4 dao层TransferDao

package www.test.dao;

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

import org.apache.commons.dbutils.QueryRunner;

public class TransferDao {

    public boolean out(Connection conn, String out, double money) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "UPDATE account SET money=money-? WHERE NAME=?";
        Object[] params = {money,out};
        int num = qr.update(conn, sql, params);
        if(num>0){
            return true;
        }else{
            return false;
        }
    }
    public boolean in(Connection conn, String out, double money) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "UPDATE account SET money=money+? WHERE NAME=?";
        Object[] params = {money,out};
        int num = qr.update(conn, sql, params);
        if(num>0){
            return true;
        }else{
            return false;
        }
    }

}

 4 解决conn在service层的问题

1 在c3p0连接池工具类中加入事务的处理

package www.test.utils;

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

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {

    // 1 获得Connection ----- 从连接池中获取
    private static DataSource dataSource = new ComboPooledDataSource();

    // 2 创建ThreadLocal 存储的类型是Connection
    private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();

    // 3 直接可以获取一个连接池
    public static DataSource getDataSource() {
        return dataSource;
    }

    // 4 直接获取一个连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    // 5 获取绑定到ThreadLocal上的连接对象
    public static Connection getCurrentConnection() throws SQLException {
        //从ThreadLocal寻找 当前线程是否有对应Connection
        Connection con = tl.get();
        if (con == null) {
            //获得新的connection
            con = dataSource.getConnection();
            //将conn资源绑定到ThreadLocal（map）上
            tl.set(con);
        }
        return con;
    }

    // 6 开启事务
    public static void startTransaction() throws SQLException {
        Connection con = getCurrentConnection();
        if (con != null) {
            con.setAutoCommit(false);
        }
    }

    // 7 事务回滚
    public static void rollback() throws SQLException {
        Connection con = getCurrentConnection();
        if (con != null) {
            con.rollback();
        }
    }

    //  8 提交并且 关闭资源及从ThreadLocall中释放
    public static void commitAndRelease() throws SQLException {
        Connection con = getCurrentConnection();
        if (con != null) {
            con.commit(); // 事务提交
            con.close();// 关闭资源
            tl.remove();// 从线程绑定中移除
        }
    }

    // 9 关闭资源方法
    public static void close(ResultSet rs, Statement st, Connection con) throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (st != null) {
            st.close();
        }
        if (con != null) {
            con.close();
        }
    }
}

2 service层代码修改完整版

package www.test.service;

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

import www.test.dao.TransferDao;
import www.test.utils.C3P0Utils;

public class TransferService{

    public boolean transfer(String out, String in, double money) {
        TransferDao dao = new TransferDao();
        
        boolean isTranferSuccess = true;
//        Connection conn = null;
        try {
            // 1 获取一个连接
//            conn = C3P0Utils.getConnection();
            // 2 开启事务
//            conn.setAutoCommit(false);
            C3P0Utils.startTransaction();
            
            // 3 转出钱的方法 ,用flag判断操作成功
//            boolean flagout = dao.out(conn,out,money);
            boolean flagout = dao.out(out,money);
            
            // 4 转入钱的方法
//            boolean flagin = dao.in(conn,in,money);
            boolean flagin = dao.in(in,money);
            
            if(!(flagout&&flagin)){ //只要有一方失败都无效，执行回滚
                isTranferSuccess = false;
//                conn.rollback();
                C3P0Utils.rollback();
            }
        } catch (SQLException e) {
            isTranferSuccess = false;
            try {
                // 5 回滚事务
//                conn.rollback();
                C3P0Utils.rollback();
            } catch (SQLException e1) {
                
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally{
            try {
                // 6 提交事务 
                // 建议放到这里，就算不成功，会回滚，回滚之后继续提交。
//                conn.commit();
                //提交的时候就关闭资源，连接从线程绑定中移除
                C3P0Utils.commitAndRelease(); 
            } catch (SQLException e) {
                
                e.printStackTrace();
            }
        }
        
        return isTranferSuccess;
    }

}

3 dao层代码修改完整版

package www.test.dao;

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

import org.apache.commons.dbutils.QueryRunner;

import www.test.utils.C3P0Utils;

public class TransferDao {

    public boolean out(String out, double money) throws SQLException {
        // 获取的是绑定在ThreadLoacal上的conn
        QueryRunner qr = new QueryRunner();
        Connection conn = C3P0Utils.getCurrentConnection();
        String sql = "UPDATE account SET money=money-? WHERE NAME=?";
        Object[] params = {money,out};
        int num = qr.update(conn, sql, params);
        if(num>0){
            return true;
        }else{
            return false;
        }
    }
    public boolean in(String out, double money) throws SQLException {
        QueryRunner qr = new QueryRunner();
        // 获取的是绑定在ThreadLoacal上的conn
        Connection conn = C3P0Utils.getCurrentConnection();
        String sql = "UPDATE account SET money=money+? WHERE NAME=?";
        Object[] params = {money,out};
        int num = qr.update(conn, sql, params);
        if(num>0){
            return true;
        }else{
            return false;
        }
    }

}

相关推荐