Java 利用Dbutils工具类，实现事务

数据库信息

entity

Bank实体类：

public class Bank {
    private String bankNum;
    private BigDecimal money;
    private String bankName;
    private String address;

    public Bank() {
    }

    public Bank(String bankNum, BigDecimal money, String bankName, String address) {
        this.bankNum = bankNum;
        this.money = money;
        this.bankName = bankName;
        this.address = address;
    }

    public String getBankNum() {
        return bankNum;
    }

    public void setBankNum(String bankNum) {
        this.bankNum = bankNum;
    }

    public BigDecimal getMoney() {
        return money;
    }

    public void setMoney(BigDecimal money) {
        this.money = money;
    }

    public String getBankName() {
        return bankName;
    }

    public void setBankName(String bankName) {
        this.bankName = bankName;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "Bank{" +
                "bankNum='" + bankNum + '\'' +
                ", money=" + money +
                ", bankName='" + bankName + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

util

DbUtils:

public class DbUtils {
    private static DataSource dataSource;
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
    //创建连接池
    static{
        Properties properties = new Properties();
        InputStream resource = DbUtils.class.getClassLoader().getResourceAsStream("commonsDb.properties");
        try {
            properties.load(resource);
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //得到连接池
    public static DataSource getDataSource(){
        return dataSource;
    }
    //得到连接
    public static Connection getConnection(){
        try {
            Connection conn = threadLocal.get();
            if(conn==null) {
                conn = dataSource.getConnection();
                threadLocal.set(conn);
            }
            return conn;
        }catch (SQLException e){
            throw new BankException("获取连接失败...");
        }
    }
    //开启事务
    public static void beginTransaction(){
        try {
            Connection conn = getConnection();
            if(conn != null){
                conn.setAutoCommit(false);
            }
        }catch (SQLException e){
            throw new BankException("开启事务失败...");
        }
    }
    //提交事务
    public static void commitTransaction(){
        try {
            Connection conn = getConnection();
            if(conn != null){
                conn.commit();
            }
        }catch (SQLException e){
            throw new BankException("提交事务失败...");
        }
    }
    //关闭事务
    public static void rollbackTransaction(){
        try {
            Connection conn = getConnection();
            if(conn != null){
                conn.rollback();
            }
        }catch (SQLException e){
            throw new BankException("回滚失败...");
        }
    }
    //如果没开启事务，放回连接池
    public static void closeAuto(){
        try {
            Connection conn = getConnection();
            if(conn.getAutoCommit()){
                conn.close();
                threadLocal.remove();
            }
        }catch (SQLException e){
            throw new BankException("关闭失败");
        }
    }
    //开始事务，关闭连接
    public static void closeNotAuto(){
        try {
            Connection conn = getConnection();
            conn.setAutoCommit(true);
            conn.close();
            threadLocal.remove();
        }catch (SQLException e){
            throw new BankException("关闭失败...");
        }
    }

}

service:

BankService:

public interface BankService {
    //转账
    void transMoney(String fromBankNum, String toBanNum, BigDecimal money);
    //查询
    Bank queryByBankNum(String BankNum);
    //查询所有
    List<Bank> queryAll();
    //查询个数
    long queryCount();
    //查询一列
    List<String> queryByColumns(String columnName);
}

BankServiceImpl:

public class BankServiceImpl implements BankService {
    private BankDao bankDao = new BankDaoImpl();
    @Override
    public void transMoney(String fromBankNum, String toBanNum, BigDecimal money) {
        try {
            DbUtils.beginTransaction();
            bankDao.takeMoney(DbUtils.getConnection(),fromBankNum,money);
            //假设停电...
            //int num = 10/0;
            bankDao.saveMoney(DbUtils.getConnection(),toBanNum,money);
            //提交事务
            DbUtils.commitTransaction();
        }catch (Exception e){
            //回滚
            DbUtils.rollbackTransaction();
            throw new BankException("停电了...");
        }finally {
            //关闭事务
            DbUtils.closeNotAuto();
        }
    }

    @Override
    public Bank queryByBankNum(String BankNum) {
        return bankDao.queryByBankNum(BankNum);
    }

    @Override
    public List<Bank> queryAll() {
        return bankDao.queryAllBanks();
    }

    @Override
    public long queryCount() {
        return bankDao.queryCount();
    }

    @Override
    public List<String> queryByColumns(String columnName) {
        return bankDao.queryColumns(columnName);
    }
}

dao

BankDao:

public interface BankDao {
    //存钱
    void saveMoney(Connection conn,String bankNum, BigDecimal money);
    //取钱
    void takeMoney(Connection conn,String bankNum, BigDecimal money);
    //查询所有
    List<Bank> queryAllBanks();
    //查询个人
    Bank queryByBankNum(String bankNum);
    //查询个数
    long queryCount();
    //查询一列
    List<String> queryColumns(String columnName);
}

BankDaoImpl:

public class BankDaoImpl implements BankDao {
    //创建dbUtils工具
    private QueryRunner que = new QueryRunner(DbUtils.getDataSource());
    @Override
    public void saveMoney(Connection conn,String bankNum, BigDecimal money) {
        String sql = "update bank set money=money+? where bankNum=?";
        Object[] prams = {money, bankNum};
        try {
            que.update(conn,sql,prams);
        }catch(SQLException e){
            throw new BookException("存钱失败...");
        }finally {
            DbUtils.closeAuto();
        }
    }

    @Override
    public void takeMoney(Connection conn,String bankNum, BigDecimal money) {
        String sql = "update bank set money=money-? where bankNum=?";
        Object[] prams = {money, bankNum};
        try {
            que.update(conn,sql,prams);
        }catch(SQLException e){
            throw new BookException("存钱失败...");
        }finally {
            DbUtils.closeAuto();
        }
    }

    @Override
    public List<Bank> queryAllBanks() {
        String sql = "select bankNum,money,bankName,address from bank";
        try {
            return que.query(sql,new BeanListHandler<>(Bank.class));
        }catch (SQLException e){
            throw new BookException("查询所有失败...");
        }
    }

    @Override
    public Bank queryByBankNum(String bankNum) {
        String sql = "select bankNum,money,bankName,address from bank where bankNum = ?;";
        try {
            return que.query(sql,new BeanHandler<>(Bank.class),bankNum);
        }catch (SQLException e){
            throw new BookException("查询账号失败...");
        }
    }

    @Override
    public long queryCount() {
        try {
            String sql = "select count(*) from bank";
            return que.query(sql,new ScalarHandler<>());
        }catch (SQLException e){
            throw new RuntimeException("查询个数失败...");
        }
    }

    @Override
    public List<String> queryColumns(String columnName) {
        String sql = "select bankNum from bank ";
        try {
            return que.query(sql,new ColumnListHandler<>());
        }catch (SQLException e){
            throw new BankException("查询列失败...");
        }
    }
}

exception

BankException:

public class BankException extends RuntimeException {
    public BankException() {
        super();
    }

    public BankException(String message) {
        super(message);
    }

    public BankException(String message, Throwable cause) {
        super(message, cause);
    }

    public BankException(Throwable cause) {
        super(cause);
    }

    protected BankException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) {
        super(message, cause, enableSuppression, writableStackTrace);
    }
}

view

public class SystemView {
    private static BankService bankService = new BankServiceImpl();
    public static void main(String[] args) {
        try {
            //showAllBanks();
            //trans();
            //queryByBankNum();
            //queryCount();
            queryColumns();
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
    }
    public static void showAllBanks(){
        System.out.println("--------查询所有的用户信息-------");
            List<Bank> banks = bankService.queryAll();
            if(banks != null){
                for (Bank bank : banks) {
                    System.out.println(bank);
                }
            }
    }
    public static void trans(){
        System.out.println("------------转账------------------");
        bankService.transMoney("1111", "2222", new BigDecimal(2000));
        System.out.println("转账成功...");
    }
    public static void queryByBankNum(){
        System.out.println("---------根据卡号查信息----------");
        Bank bank = bankService.queryByBankNum("3333");
        System.out.println(bank);
    }
    public static void queryCount(){
        System.out.println("---------查询用户个数---------");
        System.out.println(bankService.queryCount());
    }
    public static void queryColumns(){
        System.out.println("-------------查一列----------");
        List<String> bankNums = bankService.queryByColumns("bankNum");
        if(bankNums != null){
            System.out.println(bankNums);
        }

    }
}