1、在dao层用dbutils实现事务管理
1 //从a--->b帐户转100元
2 public void transfer() throws SQLException{
3 Connection conn = null;
4 try{
5 conn = JdbcUtils.getConnection();
6 conn.setAutoCommit(false);
7
8 QueryRunner runner = new QueryRunner(); //不能给数据源，给数据源的话runner执行完一条sql会自动关闭连接，无法实现事务
9 String sql1 = "update account set money=money-100 where name='aaa'";
10 runner.update(conn,sql1);
11
12 String sql2 = "update account set money=money+100 where name='bbb'";
13 runner.update(conn,sql2);
14
15 conn.commit();
16 }finally{
17 if(conn!=null){
18 conn.close();
19 }
20 }
21 }
2、service层和dao层分离实现事务管理
dao层
1 public class AccountDao {
2
3
4
5 public AccountDao() {
6 super();
7 // TODO Auto-generated constructor stub
8 }
9
10
11
12 private Connection conn;
13 public AccountDao(Connection conn){
14 this.conn = conn;
15 }
16
17
18 public void update(Account a){
19 try{
20 QueryRunner runner = new QueryRunner();
21 String sql = "update account set money=? where id=?";
22 Object params[] = {a.getMoney(),a.getId()};
23 runner.update(con,sql, params);
24 }catch (Exception e) {
25 throw new RuntimeException(e);
26 }
27 }
28
29 public Account find(int id){
30 try{
31 QueryRunner runner = new QueryRunner();
32 String sql = "select * from account where id=?";
33 return (Account) runner.query(con,sql, id, new BeanHandler(Account.class));
34 }catch (Exception e) {
35 throw new RuntimeException(e);
36 }
37 }
service层
1 public void transfer1(int sourceid,int targetid,double money) throws SQLException{
2
3 Connection conn = null;
4 try{
5 conn = JdbcUtils.getConnection();
6 conn.setAutoCommit(false); //开始事务
7
8
9 AccountDao dao = new AccountDao(conn);
10
11 Account a = dao.find(sourceid); //select
12 Account b = dao.find(targetid); //select
13
14 a.setMoney(a.getMoney()-money);
15 b.setMoney(b.getMoney()+money);
16
17 dao.update(a); //update
18
19 dao.update(b);//update
20
21 conn.commit(); //提交事务
22 }finally{
23 if(conn!=null) conn.close();
24 }
25 }
3、使用ThreadLocal实现事务管理
dao层
1 public void update(Account a){
2 try{
3 QueryRunner runner = new QueryRunner();
4 String sql = "update account set money=? where id=?";
5 Object params[] = {a.getMoney(),a.getId()};
6 runner.update(JdbcUtils.getConnection(),sql, params);
7 }catch (Exception e) {
8 throw new RuntimeException(e);
9 }
10 }
11
12 public Account find(int id){
13 try{
14 QueryRunner runner = new QueryRunner();
15 String sql = "select * from account where id=?";
16 return (Account) runner.query(JdbcUtils.getConnection(),sql, id, new BeanHandler(Account.class));
17 }catch (Exception e) {
18 throw new RuntimeException(e);
19 }
20 }
service层
1 //用上ThreadLocal的事务管理
2 public void transfer2(int sourceid,int targetid,double money) throws SQLException{
3
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();
16 }
17 }
JDBC工具类
1 public class JdbcUtils {
2 private static DataSource ds;
3
4 private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>(); //map
5 static{
6 try{
7 Properties prop = new Properties();
8 InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
9 prop.load(in);
10 BasicDataSourceFactory factory = new BasicDataSourceFactory();
11 ds = factory.createDataSource(prop);
12 }catch (Exception e) {
13 throw new ExceptionInInitializerError(e);
14 }
15 }
16
17 public static DataSource getDataSource(){
18 return ds;
19 }
20
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 }
34
35
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 }
49
50
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 }
61
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 }
74 }