一. 为什么要传递Connection?

在前面的概述中我们知道, JDBC事务处理的作用对象为Connection, 因此要想控制操作在同一个事务里面, 

我们必须要传递Connection, 确保使用的是同一个Connection.

二. 如何传递Connection?

本实例使用转账的例子: 即从A账户转100元到B账户, 这需要做两次update表操作

1. 代码结构图:

2. 建表语句:

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `id` int(10) NOT NULL,
  `name` varchar(20) NOT NULL,
  `money` int(20) NOT NULL,
  PRIMARY KEY  (`id`)
) 

INSERT INTO `account` VALUES ('1', 'lucy', '1000');
INSERT INTO `account` VALUES ('2', 'lili', '1000');

public class Account {
	private int id;
	private String name;
	private int money;

	// getter and setter
}

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<default-config>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql:///test</property>
		<property name="user">root</property>
		<property name="password">root</property>

		<property name="acquireIncrement">50</property>
		<property name="initialPoolSize">100</property>
		<property name="minPoolSize">50</property>
		<property name="maxPoolSize">1000</property>
	</default-config> 
</c3p0-config> 

public class JDBCUtils {
	private static DataSource dataSource;
	static {
		// 加载C3P0连接池
		dataSource = new ComboPooledDataSource();
	}

	public static DataSource getDataSource() {
		return dataSource;
	}

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

}

/**
 * 业务逻辑层
 */
public class AccountService {
	
	public void transfer(Account outAccount, Account inAccount, int money) throws SQLException {
		
		// 开启 事务 
		Connection conn = JDBCUtils.getConnection();
		conn.setAutoCommit(false);

		// 查询两个账户
		AccountDAO accountDAO = new AccountDAO();
		outAccount = accountDAO.findAccountById(outAccount.getId());
		inAccount = accountDAO.findAccountById(inAccount.getId());

		// 转账 - 修改原账户金额 
		outAccount.setMoney(outAccount.getMoney() - money);
		inAccount.setMoney(inAccount.getMoney() + money);

		try {
			// 更新账户金额, 注意: 这里往Dao层传递连接
			accountDAO.update(outAccount, conn);
			// int x = 1 / 0; 
			accountDAO.update(inAccount, conn);
			
			// 转账成功, 提交事务
			conn.commit();
		} catch (Exception e) {
			// 转账失败, 回滚事务
			conn.rollback();
			e.printStackTrace();
		}
	}
}

/**
 * DAO层: CRUD
 */
public class AccountDAO {
	
	// 查询账户
	public Account findAccountById(int id) throws SQLException {
		String sql = "select * from account where id = ?";
		Object[] params = {id};
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		return queryRunner.query(sql, new BeanHandler<Account>(Account.class), params);
	}
	
	// 更新账户: 接受传递的连接
	public void update(Account account, Connection conn) throws SQLException {
		String sql = "update account set name = ?, money = ? where id = ?";
		Object[] params = {account.getName(), account.getMoney(), account.getId()};
		QueryRunner queryRunner = new QueryRunner();
		queryRunner.update(conn, sql, params);
	}
}

public class TransferTest {
	
	@Test
	public void transferTest() throws SQLException {
		Account out = new Account();
		out.setId(1);
		
		Account in = new Account();
		in.setId(2);
		AccountService accountService = new AccountService();
		accountService.transfer(out, in, 100);
	}
}

三. 总结:

上面传递Connection对象的方法虽然可以完成事务处理的目的, 但是这样的做法是丑陋的, 原因在于: 为了完成事务处理的目的, 

我们需要将一个底层Connection类在service层和Dao层之间进行传递, Dao层的方法需要接受这个Connection对象, 这种做法是典型的API污染.

Java事务(二) - 传递Connection