DButils + c3p0连接池 + 事务管理 servlet三层开发 demo

1. 导包：c3p0-0.9.1.2.jar数据源包; commons-dbutils-1.4.jar包; mysql-connector-java-5.0.4-bin.jar驱动包
2. C3P0-config.xml的配置文件
3. 直接上代码

代码演示：

MyDataSourceUtils.java工具类：作用：加载配置文件获得数据源，提供当前线程的连接，开启事务，关闭事务，回滚事务

package com.itheima.utils;

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

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class MyDataSourceUtils {

	//加载c3p0-config.xml配置文件，获得连接池
	private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
	
	//创建ThreadLocal
	private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
	

	//获取连接
	public static Connection getConnection() throws SQLException{
		return dataSource.getConnection();
	}
	
	//获得当前线程上绑定的conn
	public static Connection getCurrentConnection() throws SQLException{
		//从ThreadLocal寻找 当前线程是否有对应Connection
		Connection conn = tl.get();
		if(conn==null){
			//获得新的connection
			conn = getConnection();
			//将conn资源绑定到ThreadLocal（map）上
			tl.set(conn);
		}
		return conn;
	}
	
	//开启事务
	public static void startTransaction() throws SQLException{
		Connection conn = getCurrentConnection();
		conn.setAutoCommit(false);
	}
	
	//回滚事务
	public static void rollback() throws SQLException {
		getCurrentConnection().rollback();
	}

	//提交事务
	public static void commit() throws SQLException {
		Connection conn = getCurrentConnection();
		conn.commit();
		//将Connection从ThreadLocal中移除
		tl.remove();
		conn.close();
		
	}

}

网络层：

package com.itheima.transfer.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 com.itheima.transfer.service.TransferService;

public class TransferServlet extends HttpServlet {

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		
		//接受转账的参数
		String out = request.getParameter("out");
		String in = request.getParameter("in");
		String moneyStr = request.getParameter("money");
		double money = Double.parseDouble(moneyStr);
		
		//调用业务层的转账方法
		TransferService service = new TransferService();
		boolean isTransferSuccess = service.transfer(out,in,money);
		
		response.setContentType("text/html;charset=UTF-8");
		
		if(isTransferSuccess){
			response.getWriter().write("转账成功！！！");
		}else{
			response.getWriter().write("转账失败！！！");
		}
		
	}

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

服务层：

package com.itheima.transfer.service;

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

import com.itheima.transfer.dao.TransferDao;
import com.itheima.utils.DataSourceUtils;
import com.itheima.utils.MyDataSourceUtils;

public class TransferService {

	public boolean transfer(String out, String in, double money) {
		
		TransferDao dao = new TransferDao();
		
		boolean isTranferSuccess = true;
		//Connection conn = null;
		try {
			
			//开启事务
			//conn = DataSourceUtils.getConnection();
			//conn.setAutoCommit(false);
			//开启事务
			MyDataSourceUtils.startTransaction();
			
			//转出钱的方法  
			dao.out(out,money);
			//int i = 1/0;
			//转入钱的方法
			dao.in(in,money);
			
		} catch (Exception e) {
			isTranferSuccess = false;
			//回滚事务
			try {
				MyDataSourceUtils.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally{
			try {
				MyDataSourceUtils.commit();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return isTranferSuccess;
		
	}

}

DAO层：

package com.itheima.transfer.dao;

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

import org.apache.commons.dbutils.QueryRunner;

import com.itheima.utils.DataSourceUtils;
import com.itheima.utils.MyDataSourceUtils;

public class TransferDao {

	public void out(String out, double money) throws SQLException {
        /*因为更新涉及到事务，所以new QueryRunner时选择无参从当前线程里取当前连接，
		update(Connection conn,String sql,Object....params)*/
		QueryRunner runner = new QueryRunner();
		Connection conn = MyDataSourceUtils.getCurrentConnection();
		String sql = "update account set money=money-? where name=?";
		runner.update(conn, sql, money,out);
	}

	public void in(String in, double money) throws SQLException {
		QueryRunner runner = new QueryRunner();
		Connection conn = MyDataSourceUtils.getCurrentConnection();
		String sql = "update account set money=money+? where name=?";
		runner.update(conn, sql, money,in);
	}

}