一、无事务处理的缺陷分析
1.错误分析
在该函数中,一共建立了四个数据库连接;前面的三个可以顺利完成并且提交事务,但是后面的一个却因异常而无法提交;即事务处理放在了持久层,而没有放在业务层;需要注意,一切事务处理都需要在业务层;最终导致资金错误的情况;
2.解决办法:
解决的办法就是将四个连接合并为一个连接,要么一起成功,要么一起失败;即使用ThreadLocal对象把Connection和当前线程绑定,从而使一个线层只有一个能控制事物的对象
二、项目代码
1.项目整体构造
2.持久层接口
package com.huhai.dao;
import com.huhai.domain.Account;
import java.util.List;
/**
* 账户的持久层接口
*/
public interface IAccountDao {
/**
* 查询所有
* @return
*/
List findAllAccount();
/**
* 查询一个
* @return
*/
Account findAccountById(Integer accountId);
/**
* 保存
* @param account
*/
void saveAccount(Account account);
/**
* 更新
* @param account
*/
void updateAccount(Account account);
/**
* 删除
* @param acccountId
*/
void deleteAccount(Integer acccountId);
/**
*
* 如果有唯一的结果就返回
* 如果没有结果就返回Null
* 如果有多个结果就抛出异常
*/
Account findAccountByName(String accountName);
}
3.持久层接口实现类
package com.huhai.dao.impl;
import com.huhai.dao.IAccountDao;
import com.huhai.domain.Account;
import com.huhai.utils.ConnectionUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.util.List;
/**
* 账户的持久层实现类
*/
public class AccountDaoImpl implements IAccountDao {
private QueryRunner runner;
private ConnectionUtil connectionUtil;
public void setConnectionUtil(ConnectionUtil connectionUtil) {
this.connectionUtil = connectionUtil;
}
public void setRunner(QueryRunner runner) {
this.runner = runner;
}
@Override
public List findAllAccount() {
try{
return runner.query(connectionUtil.getThreadConnection(), "select * from account",new BeanListHandler(Account.class));
}catch (Exception e) {
throw new RuntimeException(e);
}
}
@Override
public Account findAccountById(Integer accountId) {
try{
return runner.query(connectionUtil.getThreadConnection(), "select * from account where id = ? ",new BeanHandler(Account.class),accountId);
}catch (Exception e) {
throw new RuntimeException(e);
}
}
@Override
public void saveAccount(Account account) {
try{
runner.update(connectionUtil.getThreadConnection(), "insert into account(name,money)values(?,?)",account.getName(),account.getMoney());
}catch (Exception e) {
throw new RuntimeException(e);
}
}
@Override
public void updateAccount(Account account) {
try{
runner.update(connectionUtil.getThreadConnection(), "update account set name=?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
}catch (Exception e) {
throw new RuntimeException(e);
}
}
@Override
public void deleteAccount(Integer accountId) {
try{
runner.update(connectionUtil.getThreadConnection(), "delete from account where id=?",accountId);
}catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 根据名称返回查询结果
* 如果有唯一的结果就返回
* 如果没有结果就返回Null
* 如果有多个结果就抛出异常
*/
@Override
public Account findAccountByName(String accountName){
try{
List accounts = runner.query(connectionUtil.getThreadConnection(), "select * from account where name = ? ", new BeanListHandler(Account.class), accountName);
if(accounts == null || accounts.size() == 0){
return null;
}else if(accounts.size() > 1){
throw new RuntimeException("结果集不唯一");
}else{
return accounts.get(0);
}
}catch (Exception e) {
throw new RuntimeException(e);
}
}
}
4.持久层序列化类
package com.huhai.domain;
import java.io.Serializable;
/**
* 账户的实体类
*/
public class Account implements Serializable {
private Integer id;
private String name;
private Float money;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Float getMoney() {
return money;
}
public void setMoney(Float money) {
this.money = money;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", money=" + money +
'}';
}
}
5.业务层接口
package com.huhai.service;
import com.huhai.domain.Account;
import java.util.List;
/**
* 账户的业务层接口
*/
public interface IAccountService {
/**
* 查询所有
* @return
*/
List findAllAccount();
/**
* 查询一个
* @return
*/
Account findAccountById(Integer accountId);
/**
* 保存
* @param account
*/
void saveAccount(Account account);
/**
* 更新
* @param account
*/
void updateAccount(Account account);
/**
* 删除
* @param acccountId
*/
void deleteAccount(Integer acccountId);
/**
* 转账操作
*/
public void transfer(String sourceName,String targetName, float money);
}
6.业务层接口实现类
package com.huhai.service.impl;
import com.huhai.dao.IAccountDao;
import com.huhai.domain.Account;
import com.huhai.service.IAccountService;
import com.huhai.utils.TransActionManager;
import java.util.List;
/**
* 账户的业务层实现类
* 所有的事物控制应该都在业务层,而并非在持久层
*/
public class AccountServiceImpl implements IAccountService{
private IAccountDao accountDao;
private TransActionManager transActionManager;
//添加set方法,让spring自动注入
public void setTransActionManager(TransActionManager transActionManager) {
this.transActionManager = transActionManager;
}
//添加set方法,让spring自动注入
public void setAccountDao(IAccountDao accountDao) {
this.accountDao = accountDao;
}
@Override
public List findAllAccount() {
try {
//开启事务
transActionManager.startTransAction();
//执行操作
List accounts = accountDao.findAllAccount();
//提交事务
transActionManager.commitTransAction();
//返回结果
return accounts;
}catch (Exception e){
//回滚事物
transActionManager.rollBackTransAction();
throw new RuntimeException();
}finally {
//释放连接
transActionManager.releaseConnection();
}
}
@Override
public Account findAccountById(Integer accountId) {
try {
//开启事务
transActionManager.startTransAction();
//执行操作
Account account = accountDao.findAccountById(accountId);
//提交事务
transActionManager.commitTransAction();
//返回结果
return account;
}catch (Exception e){
//回滚事物
transActionManager.rollBackTransAction();
throw new RuntimeException();
}finally {
//释放连接
transActionManager.releaseConnection();
}
}
@Override
public void saveAccount(Account account) {
try {
//开启事务
transActionManager.startTransAction();
//执行操作
accountDao.saveAccount(account);
//提交事务
transActionManager.commitTransAction();
}catch (Exception e){
//回滚事物
transActionManager.rollBackTransAction();
}finally {
//释放连接
transActionManager.releaseConnection();
}
}
@Override
public void updateAccount(Account account) {
try {
//开启事务
transActionManager.startTransAction();
//执行操作
accountDao.updateAccount(account);
//提交事务
transActionManager.commitTransAction();
}catch (Exception e){
//回滚事物
transActionManager.rollBackTransAction();
}finally {
//释放连接
transActionManager.releaseConnection();
}
}
@Override
public void deleteAccount(Integer acccountId) {
try {
//开启事务
transActionManager.startTransAction();
//执行操作
accountDao.deleteAccount(acccountId);
//提交事务
transActionManager.commitTransAction();
}catch (Exception e){
//回滚事物
transActionManager.rollBackTransAction();
}finally {
//释放连接
transActionManager.releaseConnection();
}
}
/**
* 转账操作
* 该方法有严重的错误,在两账户更新数据的过程中如果产生了异常程序奔溃,则会发生资金有转出,无转入,即凭空消失了若干钱,这是决不允许的
*/
@Override
public void transfer(String sourceName, String targetName, float money) {
try {
//1.开启事务
transActionManager.startTransAction();
//2.执行操作
//2.1根据名称查询转出账户
Account accountSource = accountDao.findAccountByName(sourceName);
//2.2根据名称查询转入账户
Account accountTarget = accountDao.findAccountByName(targetName);
//2.3转出账户减钱
accountSource.setMoney(accountSource.getMoney() - money);
//2.4转入账户加钱
accountTarget.setMoney(accountTarget.getMoney() + money);
//2.5更新转出账户
accountDao.updateAccount(accountSource);
//2.6更新转入账户
accountDao.updateAccount(accountTarget);
//3提交事务
transActionManager.commitTransAction();
}catch (Exception e){
//4回滚事物
transActionManager.rollBackTransAction();
}finally {
//5释放连接
transActionManager.releaseConnection();
}
}
}
7.数据库连接池
package com.huhai.utils;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
/**
* 连接的工具类,它用于从数据源中获取一个连接,并且实现和线程的绑定
*/
public class ConnectionUtil {
private ThreadLocal threadLocal = new ThreadLocal();
private DataSource dataSource;
//添加set方法,让spring自动注入
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
/**
* 获取当前线层的连接
*/
public Connection getThreadConnection() throws SQLException {
/**
* 从ThreadLocal获取
*/
Connection conn = threadLocal.get();
if(conn == null){
//如果当前线程没有连接,则从数据源中获取一个连接,并且和当前线程绑定,即存入ThreadLocal中
conn = dataSource.getConnection();
threadLocal.set(conn);
}
//返回当前线程上的连接
return conn;
}
/**
* 解绑线程和连接
* 无论是线程池还是连接池,调用close方法并不是关闭,而是将取出来的线程或连接还回池中
* 而并非关闭连接或线程
* 因此在下次使用时,我们获取的时候还能获取到,但是它却不能用了(因为被close了)
* 所以完成一次线程操作后需要解绑
* 这也是WEB开发中需要注意的问题
*/
public void removeConnection(){
threadLocal.remove();
}
}
8.事务处理管理类
package com.huhai.utils;
import java.sql.SQLException;
/**
* 和事务管理相关的工具类
* 包含了开启事务,提交事务,回滚事物,释放连接
*/
public class TransActionManager {
private ConnectionUtil connectionUtil;
public void setConnectionUtil(ConnectionUtil connectionUtil) {
this.connectionUtil = connectionUtil;
}
//开启事务
public void startTransAction(){
try {
//关闭自动提交,设置为手动提交,以保证事务处理的正确性
connectionUtil.getThreadConnection().setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
}
//提交事务
public void commitTransAction(){
try {
connectionUtil.getThreadConnection().commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
//回滚事物
public void rollBackTransAction(){
try {
connectionUtil.getThreadConnection().rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
//释放连接
public void releaseConnection(){
try {
connectionUtil.getThreadConnection().close();
} catch (SQLException e) {
e.printStackTrace();
}
//解绑线程和连接
connectionUtil.removeConnection();
}
}
9.测试类
package com.huhai.test;
import com.huhai.domain.Account;
import com.huhai.service.IAccountService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.List;
/**
* 使用Junit单元测试:测试我们的配置
*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:bean.xml")
public class AccountServiceTest {
@Autowired
private IAccountService as;
@Test
public void testFindAll() {
//3.执行方法
List accounts = as.findAllAccount();
for(Account account : accounts){
System.out.println(account);
}
}
@Test
public void testFindOne() {
//3.执行方法
Account account = as.findAccountById(1);
System.out.println(account);
}
@Test
public void testSave() {
Account account = new Account();
account.setName("aaa");
account.setMoney(12345f);
//3.执行方法
as.saveAccount(account);
}
@Test
public void testUpdate() {
//3.执行方法
Account account = as.findAccountById(1);
account.setMoney(23456f);
as.updateAccount(account);
}
@Test
public void testDelete() {
//3.执行方法
as.deleteAccount(0);
}
/**
*
* 转出账号名称
* 转入账号名称
* 转出金额
*/
@Test
public void transferTest(){
as.transfer("aaa", "bbb", 100f);
}
}
10.bean.xml配置文件
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
11.pom.xml配置文件
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
4.0.0
com.huhai
demo17
1.0-SNAPSHOT
jar
org.springframework
spring-context
5.0.2.RELEASE
org.springframework
spring-test
5.0.2.RELEASE
commons-dbutils
commons-dbutils
1.4
mysql
mysql-connector-java
5.1.6
c3p0
c3p0
0.9.1.2
junit
junit
4.12
12.数据库
create database user;
use user;
create table account(
id int primary key auto_increment,
name varchar(40),
money float
)character set utf8 collate utf8_general_ci;
insert into account(name,money) values('aaa',1000);
insert into account(name,money) values('bbb',1000);
insert into account(name,money) values('ccc',1000);