JDBC将Connection对象绑定到ThreadLocal当前线程上

转账业务模拟

DAO代码：

package com.dao;

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

import com.utils.MyJDBCUtil_C3P0;

public class AccountDao {
	/**
	 * 转账功能
	 * @param money
	 * @param username
	 */
	public void update(double money,String username){
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			//从当前线程中获取连接
			conn = MyJDBCUtil_C3P0.getConnection();
			String sql = "update t_account set money = money + ? where username = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setDouble(1, money);
			stmt.setString(2, username);
			stmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//释放资源
			//MyJDBCUtil_C3P0.release(stmt, conn);
		}
	}
}

Service代码：

package com.service;

import java.sql.SQLException;

import com.dao.AccountDao;
import com.utils.MyJDBCUtil_C3P0;

public class AccountService {
	
	public void payMoney(String from,String to,double money){
		//转账现扣除，再增加
		AccountDao dao = new AccountDao();
		try {
			//开启事务(同时获取当前线程中的conn对象)
			MyJDBCUtil_C3P0.beginTransaction();
			//扣钱
			dao.update(-money, from);
			//加钱
			dao.update(money, to);
			//提交事务
			MyJDBCUtil_C3P0.commitTransaction();
		} catch (SQLException e) {
			try {
				MyJDBCUtil_C3P0.rollbackTransaction();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally{
			try {
				MyJDBCUtil_C3P0.getConnection().close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

自定义JDBC工具类，使用C3P0连接池

package com.utils;

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

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * 操作JDBC的工具类
 * @author 58351
 *
 */
public class MyJDBCUtil_C3P0 {
	
	//从C3P0连接池获取连接
	public static ComboPooledDataSource dataSource = new ComboPooledDataSource();
	//将conn对象绑定到ThreadLocal本地线程中
	//创建ThreadLocal对象
	public static ThreadLocal<Connection> tl = new ThreadLocal<>();
	/**
	 * 获取连接
	 * @throws SQLException 
	 */
	public static Connection getConnection() throws SQLException{
		Connection conn = tl.get();;
		if(conn == null){
			//conn为空就创建一个新的conn
			conn = dataSource.getConnection();
			//将conn存入到当前线程中去
			tl.set(conn);
		}
		return conn;
	}
	
	//开启事务的方法
	public static void beginTransaction() throws SQLException{
			Connection conn = tl.get();
			if(conn == null){
				conn = dataSource.getConnection();
				tl.set(conn);
			}
			conn.setAutoCommit(false);
	}
	
	//提交事务的方法
	public static void commitTransaction() throws SQLException{
		Connection conn = tl.get();
		if(conn == null){
			conn = dataSource.getConnection();
			tl.set(conn);
		}
		conn.commit();
		//从当前线程中移除conn
		tl.remove();
	}
	
	//回滚事务的方法
	public static void rollbackTransaction() throws SQLException{
		Connection conn = tl.get();
		if(conn == null){
			conn = dataSource.getConnection();
			tl.set(conn);
		}
		conn.rollback();
		//从当前线程中移除conn
		tl.remove();
	}
	/**
	 * 释放资源
	 */
	public static void release(ResultSet rs,Statement stmt,Connection conn){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
		if(stmt != null){
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			stmt = null;
		}
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;
		}
	}
	public static void release(Statement stmt,Connection conn){
		if(stmt != null){
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			stmt = null;
		}
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;
		}
	}
}

C3P0连接池配置文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<!-- 默认的配置，如果一会读取配置，默认找该配置 -->
	<default-config>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql:///jdbc1?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false</property>
		<property name="user">root</property>
		<property name="password">password</property>
	</default-config>
		
	<!-- 查找指定名称空间 -->
	<named-config name="test">
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql:///jdbc1?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false</property>
		<property name="user">root</property>
		<property name="password">root</property>
	</named-config>
		
</c3p0-config>