JDBC应用的事务管理

关于事务，前面文章中都有详解。这里讲的是如何在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();
        }
    }
}

源代码：https://github.com/yvettee36/ThreadLocal