关于事务，前面文章中都有详解。这里讲的是如何在JDBC中应用事务管理（优解）。
原理解析
1. 实体类Account
package cn.itcast.domain;
/**
* Created by yvettee on 2017/10/9.
*/
public class Account {
private int id;
private String name;
private double money;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
}
2. 工具类
package cn.itcast.utils;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/**
* Created by yvettee on 2017/10/9.
*/
public class JdbcUtils_dbcp {
private static DataSource ds = null;
private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
static {
try {
InputStream in = JdbcUtils_dbcp.class.getClassLoader().getResourceAsStream("dbcpConfig.properties");
Properties prop = new Properties();
prop.load(in);
BasicDataSourceFactory factory = new BasicDataSourceFactory();
ds = factory.createDataSource(prop);
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static DataSource getDataSource() {
return ds;
}
public static Connection getConnection() throws SQLException {
try {
Connection conn = threadLocal.get();
if (conn == null) {
conn = ds.getConnection();
threadLocal.set(conn);
}
return conn;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
//提供一个开启事务的方法（由线程调用）
public static void startTransaction() {
//得到当前线程上绑定的连接，开启事务
try {
Connection conn = threadLocal.get();
if (conn == null) {//代表线程上没有绑定连接
conn = getConnection();//已经有连接了
threadLocal.set(conn);//绑定到线程上去
}
conn.setAutoCommit(false);//开启事务
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void commitTransaction() {
try {
Connection conn = threadLocal.get();
if (conn != null) {
conn.commit();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void closeConnection() {
try {
Connection conn = threadLocal.get();
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
threadLocal.remove();//解除当前线程上绑定的连接
}
}
}
3. 具体实现
package cn.itcast.dao;
import cn.itcast.domain.Account;
import cn.itcast.utils.JdbcUtils_dbcp;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import java.sql.Connection;
import java.sql.SQLException;
/**
* Created by yvettee on 2017/10/9.
*/
public class AccountDao {
private Connection conn;
public AccountDao() {
}
//根据传进来的连接操作数据库
public AccountDao(Connection conn) {
this.conn = conn;
}
public void update(Account a) {
try {
QueryRunner runner = new QueryRunner();
String sql = "update account set money=? where id=?";
Object params[] = {a.getMoney(), a.getId()};
runner.update(JdbcUtils_dbcp.getConnection(), sql, params);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public Account find(int id) {
try {
QueryRunner runner = new QueryRunner();
String sql = "select * from account where id=?";
return (Account) runner.query(JdbcUtils_dbcp.getConnection(), sql, id, new BeanHandler(Account.class));
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
如果不用到Spring管理方案，就会用到ThreadLocal，ThreadLocal相当于一个大大的Map集合。它保证了对数据库操作的多条语句都在一个事务里执行。
4. 进行转账的具体处理
package cn.itcast.service;
import cn.itcast.dao.AccountDao;
import cn.itcast.domain.Account;
import cn.itcast.utils.JdbcUtils_dbcp;
import org.junit.Test;
import java.sql.Connection;
import java.sql.SQLException;
/**
* Created by yvettee on 2017/10/9.
*/
public class BusinessService {
@Test
public void test() throws SQLException {
transfer(1, 2, 100);
}
/*public void transfer(int sourceId, int targetId, double money) throws SQLException {
Connection conn = null;
try {
conn = JdbcUtils_dbcp.getConnection();
//确保查询和修改都执行了再提交
conn.setAutoCommit(false);
AccountDao dao = new AccountDao(conn);
//找到a账户
Account a = dao.find(sourceId);//select
//找到b账户
Account b = dao.find(targetId);//select
a.setMoney(a.getMoney() - money);
b.setMoney(b.getMoney() + money);
dao.update(a);//update
dao.update(b);//update
conn.commit();
} finally {
if (conn != null) {
conn.close();
}
}
}*/
//用上ThreadLocal的事务管理
public void transfer(int sourceId, int targetId, double money) throws SQLException {
Connection conn = null;
try {
JdbcUtils_dbcp.startTransaction();
AccountDao dao = new AccountDao();
//找到a账户
Account a = dao.find(sourceId);//select
//找到b账户
Account b = dao.find(targetId);//select
a.setMoney(a.getMoney() - money);
b.setMoney(b.getMoney() + money);
dao.update(a);//update
dao.update(b);//update
JdbcUtils_dbcp.commitTransaction();
} finally {
JdbcUtils_dbcp.closeConnection();
}
}
}
网友评论