JDBC事务（三）ThreadLocal绑定Connection

处理一个请求即开启一个线程，在三层中，执行三层中的方法都是用的同一个线程。

我们开启一个事务，使用conn.setAutoCommit(false); conn应该属于ado层，不应该出现在service层，但处理事务应该在service层执行。

针对上述矛盾，我们考虑将Connection绑定到ThreadLocal中，因为三层的执行都是在同一个线程，当需要Connection时，从ThreadLocal中取即可。

更改上一章转账按例，结构如下：

更改C3P0线程池的工具类 MyDataSourceUtils

package cn.sasa.util;

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

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class MyDataSourceUtils {
    private static DataSource dataSource = new ComboPooledDataSource("mydb");

    public static DataSource getDataSource() {
        return dataSource;
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    //当前线程
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();

    // 获得当前线程上绑定的conn
    public static Connection getCurrentConnection() throws SQLException {
        // 从ThreadLocal找 当前线程是否有对应的Connection
        Connection conn = threadLocal.get();
        if (conn == null) {
            conn = getConnection();
            // 将conn绑定到ThreadLocal（map）上
            threadLocal.set(conn);
        }
        return conn;
    }

    // 开启事务
    public static void StartTransaction() throws SQLException {
        Connection conn = getCurrentConnection();
        conn.setAutoCommit(false);
    }

    // 回滚事务
    public static void rollback() throws SQLException {
        Connection conn = getCurrentConnection();
        conn.rollback();
        threadLocal.remove();
        conn.close();
    }

    // 提交事务
    public static void commit() throws SQLException {
        Connection conn = getCurrentConnection();
        conn.commit();
        threadLocal.remove();
        conn.close();
    }
}

更改service层代码：

package cn.sasa.service;

import java.sql.SQLException;

import cn.sasa.dao.TransferDao;
import cn.sasa.util.MyDataSourceUtils;

public class TransferService {

    public boolean doTran(String outAccount, String inAccount, double money)  {
        boolean flag = true;
        
        try {
            MyDataSourceUtils.StartTransaction();
            
            TransferDao tran = new TransferDao();
            int rs1 = tran.doOutAccount(outAccount, money);
            int rs2 = tran.doInAccount(inAccount, money);
            
            if(rs1<=0 || rs2<=0) {
                MyDataSourceUtils.rollback();
                flag=false;
            }
            
        } catch (Exception e) {
            flag = false;
            try {
                MyDataSourceUtils.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            try {
                MyDataSourceUtils.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return flag;
    }
}

更改dao层：

package cn.sasa.dao;

import java.sql.SQLException;

import org.apache.commons.dbutils.QueryRunner;

import cn.sasa.util.MyDataSourceUtils;

public class TransferDao {
    
    //资金转出
    public int doOutAccount( String outAccount, double money) throws SQLException {
        QueryRunner runner = new QueryRunner();
        String sql = "update account set money=money-? where name=?";
        int rs = runner.update(MyDataSourceUtils.getCurrentConnection(), sql, money,outAccount);
        return rs;
    }
    
    //资金转入
    public int doInAccount(String inAccount, double money) throws SQLException {
        QueryRunner runner = new QueryRunner();
        String sql = "update account set money=money+? where name=?";
        int rs = runner.update(MyDataSourceUtils.getCurrentConnection(), sql, money,inAccount);
        return rs;
    }
}

web层与客户端jsp页面略。

客户端请求一次即开启一个线程，再次请求则开启另一个线程。