1、在dao层用dbutils实现事务管理
//从a--->b帐户转100元
public void transfer() throws SQLException{
Connection conn = null;
try{
conn = JdbcUtils.getConnection();
conn.setAutoCommit(false); QueryRunner runner = new QueryRunner(); //不能给数据源，给数据源的话runner执行完一条sql会自动关闭连接，无法实现事务
String sql1 = "update account set money=money-100 where name='aaa'";
runner.update(conn,sql1); String sql2 = "update account set money=money+100 where name='bbb'";
runner.update(conn,sql2); conn.commit();
}finally{
if(conn!=null){
conn.close();
}
}
}
2、service层和dao层分离实现事务管理
dao层
public class AccountDao {
public AccountDao() {
super();
// TODO Auto-generated constructor stub
}
12 private Connection conn;
13 public AccountDao(Connection conn){
14 this.conn = conn;
15 }
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(con,sql, params);
}catch (Exception 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(con,sql, id, new BeanHandler(Account.class));
}catch (Exception e) {
throw new RuntimeException(e);
}
}service层
public void transfer1(int sourceid,int targetid,double money) throws SQLException{
Connection conn = null;
try{
conn = JdbcUtils.getConnection();
conn.setAutoCommit(false); //开始事务
AccountDao dao = new AccountDao(conn);
Account a = dao.find(sourceid); //select
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();
}
}3、使用ThreadLocal实现事务管理
dao层
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.getConnection(),sql, params);
}catch (Exception 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.getConnection(),sql, id, new BeanHandler(Account.class));
}catch (Exception e) {
throw new RuntimeException(e);
}
}service层
//用上ThreadLocal的事务管理
public void transfer2(int sourceid,int targetid,double money) throws SQLException{ 4 try{
5 JdbcUtils.startTransaction();
6 AccountDao dao = new AccountDao();
7 Account a = dao.find(sourceid); //select
8 Account b = dao.find(targetid); //select
9 a.setMoney(a.getMoney()-money);
10 b.setMoney(b.getMoney()+money);
11 dao.update(a); //update
12 dao.update(b);//update
13 JdbcUtils.commitTransaction();
14 }finally{
15 JdbcUtils.closeConnection();
}
}
JDBC工具类
public class JdbcUtils {
private static DataSource ds;
4 private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>(); //map
static{
try{
Properties prop = new Properties();
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.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;
}
21 public static Connection getConnection() throws SQLException{
22 try{
23 //得到当前线程上绑定的连接
24 Connection conn = tl.get();
25 if(conn==null){ //代表线程上没有绑定连接
26 conn = ds.getConnection();
27 tl.set(conn);
28 }
29 return conn;
30 }catch (Exception e) {
31 throw new RuntimeException(e);
32 }
33 }
36 public static void startTransaction(){
37 try{
38 //得到当前线程上绑定连接开启事务
39 Connection conn = tl.get();
40 if(conn==null){ //代表线程上没有绑定连接
41 conn = ds.getConnection();
42 tl.set(conn);
43 }
44 conn.setAutoCommit(false);
45 }catch (Exception e) {
46 throw new RuntimeException(e);
47 }
48 }
51 public static void commitTransaction(){
52 try{
53 Connection conn = tl.get();
54 if(conn!=null){
55 conn.commit();
56 }
57 }catch (Exception e) {
58 throw new RuntimeException(e);
59 }
60 }
62 public static void closeConnection(){
63 try{
64 Connection conn = tl.get();
65 if(conn!=null){
66 conn.close();
67 }
68 }catch (Exception e) {
69 throw new RuntimeException(e);
70 }finally{
71 tl.remove(); //千万注意，解除当前线程上绑定的链接（从threadlocal容器中移除对应当前线程的链接）
72 }
73 }
}