美文网首页
JDBC应用的事务管理

JDBC应用的事务管理

作者: 小小蒜头 | 来源:发表于2017-10-10 11:01 被阅读52次

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

    相关文章

      网友评论

          本文标题:JDBC应用的事务管理

          本文链接:https://www.haomeiwen.com/subject/mifmyxtx.html