对事务的支持

使用c3p0连接池，编写工具类，实现对事务的支持

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

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JdbcUtils {
	private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
	//事务专用链接
	private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
	
	//使用连接池返回一个对象
	public static Connection getConnection() throws SQLException{
		Connection con = tl.get();
		if(con!=null) return con;
		return dataSource.getConnection();
	}
	
	//返回连接池对象
	public static DataSource getDataSource(){
		return dataSource;
	}
	
	//开启事务，并这是为手动提交
	public static void beginTransaction() throws SQLException{
		Connection con = tl.get();
		if(con!=null) throw new SQLException("已经开启了事务");
		con= getConnection();
		con.setAutoCommit(false);//设置为手动提交
		
		tl.set(con);//把当前线程的链接保存起来
	}
	
	//提交事务
	public static void rollbackTransation() throws SQLException{
		Connection con = tl.get();
		if(con == null)  throw new SQLException("还未开启事务，不能提交");
		
		con.commit();
		con.close();
		tl.remove();
	}
	
	//回滚事务
	public static void rollbackTransaction() throws SQLException {
		Connection con = tl.get();
		if(con==null) throw new SQLException("还未开启事务，不能回滚");
		
		con.rollback();
		con.close();
		tl.remove();
	}
	
	//释放连接
	public static void releaseConnection(Connection connection) throws SQLException {
		Connection con =tl.get();
		
		//如果是事务专用就不关闭
		//如果不是则关闭
		if(con == null) connection.close();
		if(con !=connection) connection.close();
	}
}

重写QueryRunner不需要传递con的方法

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

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

public class TxQueryRunner extends QueryRunner {

	@Override
	public int[] batch(String sql, Object[][] params) throws SQLException {
		/*
		 * 1.得到连接
		 * 2.执行父类方法，传递连接对象
		 * 3.释放连接
		 * 4.返回值
		 */
		Connection con = JdbcUtils.getConnection();
		int[] result = super.batch(con, sql, params);
		JdbcUtils.releaseConnection(con);
		return result;
	}

	@Override
	public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params)
			throws SQLException {
		// TODO Auto-generated method stub
		Connection con = JdbcUtils.getConnection();
		T result =  super.query(con,sql, rsh, params);
		JdbcUtils.releaseConnection(con);
		return result;
	}

	@Override
	public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {
		// TODO Auto-generated method stub
		Connection con = JdbcUtils.getConnection();
		T result = super.query(con,sql, rsh);
		JdbcUtils.releaseConnection(con);
		return result;
	}

	@Override
	public int update(String sql) throws SQLException {
		// TODO Auto-generated method stub
		Connection con = JdbcUtils.getConnection();
		int result = super.update(con,sql);
		JdbcUtils.releaseConnection(con);
		return result;
	}

	@Override
	public int update(String sql, Object param) throws SQLException {
		// TODO Auto-generated method stub
		Connection con = JdbcUtils.getConnection();
		int result = super.update(con,sql, param);
		JdbcUtils.releaseConnection(con);
		return result;
	}

	@Override
	public int update(String sql, Object... params) throws SQLException {
		// TODO Auto-generated method stub
		Connection con = JdbcUtils.getConnection();
		int  result = super.update(con,sql, params);
		JdbcUtils.releaseConnection(con);
		return result;
	}

}

Dao中只需提供查询语句

	public static void update(String name, double money) throws SQLException {
		QueryRunner qr = new TxQueryRunner();
		String sql = "update account set balance=balance+? where name=?";
		Object[] params = {money, name};
		
		qr.update(sql, params);
	}

在Sevice中只需开启事务，调用Dao，关闭事务即可

	public void serviceMethod() throws Exception {
		try {
			JdbcUtils.beginTransaction();
			
			dao.update(...);
			dao.update(...);
			JdbcUtils.commitTransaction();
		} catch (Exception e) {
			try {
				JdbcUtils.rollbackTransaction();
			} catch (SQLException e1) {
			}
			throw e;
		}
	}