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();//创建ThreadLocalprivate static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();//获取连接public static Connection getConnection() throws SQLException{return dataSource.getConnection();}//获得当前线程上绑定的connpublic static Connection getCurrentConnection() throws SQLException{//从ThreadLocal寻找 当前线程是否有对应ConnectionConnection conn = tl.get();if(conn==null){//获得新的connectionconn = 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);}}