使用连接池，Commons dbutils 处理事务，对数据库进行操作

Dbutils

使用Druid连接池

package week2.jdbctest.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import week2.jdbctest.bankexception.BankException;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
public class DbUtils {
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
    private static DataSource dataSource;
    static {
        Properties properties = new Properties();
        InputStream rs = DbUtils.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            if (rs != null) {
                properties.load(rs);
            }
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new BankException("连接池创建失败",e);
        }
    }
    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("获取连接失败",e);
        }
    }
    public static void closeAuto(){
        Connection conn = threadLocal.get();
        try {
            if (conn.getAutoCommit()){
                conn.close();//并不是关闭，而是放回连接池
                threadLocal.remove();
                System.out.println("xxx");
            }
        } catch (SQLException e) {
            throw new BankException("释放连接失败");
        }
    }
//    事务四方法
    // 开启事务
    public static void begin(){
        Connection conn = getConnection();
        if (conn != null) {
            try {
                conn.setAutoCommit(false);
            } catch (SQLException e) {
                throw new BankException("事务kaiqi失败",e);
            }
        }
    }
    // 提交
    public static void commit() {
        Connection conn = getConnection();
        if (conn != null) {
            try {
                conn.commit();
            } catch (SQLException e) {
                throw new BankException("事务提交失败",e);
            }
        }
    }
//    回滚
    public static void rollback() {
        Connection conn = getConnection();
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                throw new BankException("事务回滚失败",e);
            }
        }
    }
    public static void closeNotAuto(){
        Connection conn = getConnection();
        if (conn != null) {
            try {
                conn.close();
                threadLocal.remove();
            } catch (SQLException e) {
                throw new BankException("事务连接关闭失败",e);
            }
        }

    }

}

domain

public class Bank {
    private int id;
    private double money;
    // query方法采用反射生成对象，不写无参，会报错
    public Bank() {
    }
    public Bank(int id, double money) {
        this.id = id;
        this.money = money;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public double getMoney() {
        return money;
    }

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

    @Override
    public String toString() {
        return "Bank{" +
                "id=" + id +
                ", money=" + money +
                '}';
    }
}

dao

public interface BankDao {
    void saveMoney(int id , double money);
    void takeMoney(int id, double money);
    List<Bank> listBank();
    Long getCount();
    List<Double> listBalance();
    double getBalance(int id);
}

impl

package week2.jdbctest.dao.impl;
import org.apache.commons.dbutils.ColumnHandler;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import week2.jdbctest.bankexception.BankException;
import week2.jdbctest.dao.BankDao;
import week2.jdbctest.domain.Bank;
import week2.jdbctest.utils.DbUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class BankDaoImpl implements BankDao {
//    查询语句query使用线程池，增删改update使用具体conn，需要使用事务
    QueryRunner qr = new QueryRunner(DbUtils.getDataSource());
//    存钱
    @Override
    public void saveMoney(int id , double money) {
        Connection conn = null;
        try {
            conn = DbUtils.getConnection();
            qr.update(conn,"update bank set money =money+? where id = ?",money,id );
        }catch (SQLException e){
            throw new BankException("存入失败",e);
        }finally {
            DbUtils.closeAuto();
        }
    }
//  取钱
    @Override
    public void takeMoney(int id , double money) {
        Connection conn = null;
        try {
            conn = DbUtils.getConnection();
            System.out.println(conn.hashCode());
            qr.update(conn,"update bank set money =money-? where id = ?",money,id );
        }catch (SQLException e){
            throw new BankException("存入失败",e);
        }finally {
            DbUtils.closeAuto();
        }
    }
//    余额查询
    public double getBalance(int id){
        Connection connection =null;
        try {
            connection= DbUtils.getConnection();
            // BeanHandle 返回单个对象
            Bank bank =  qr.query(connection,"select id,money where id =?",new BeanHandler<>(Bank.class),id);
            if (bank!=null){
                return bank.getMoney();
            }
            return 0;
        } catch (SQLException e) {
            throw new BankException("余额查询失败");
        }
        finally {

        }
    }
//    用户列表展示查询
    public List<Bank> listBank(){
        try {
//            BeanListHandler 返回一个含对象列表
            List<Bank> list = qr.query("select id,money from bank",new BeanListHandler<>(Bank.class));
            return list;
        } catch (SQLException e) {
            throw new BankException("用户列表展示失败");
        }
    }
//    获得用户总数
//    ScalarHandler 返回单行单列，可用于统计 类型为long
    public Long getCount(){
        try {
            return qr.query("select count(*) from bank",new ScalarHandler<>());
        } catch (SQLException e) {
            throw new BankException("统计失败");
        }
    }
//    展示所有余额
//    ColumnHandler 获得某列数据
    public List<Double> listBalance(){
        List<Double> list = null;
        try {
            list = qr.query("select money from bank",new ColumnListHandler<Double>());
        } catch (SQLException e) {
            throw new BankException("获取余额失败");
        }
        return list;
    }
}

Service

public interface BankService {
    void trans(int id, int roId, double money);
    void listBank();
    double getBalance(int id);
    Long getCount();
    void listBalance();
}

impl

转账事务

package week2.jdbctest.service.impl;

import week2.jdbctest.dao.BankDao;
import week2.jdbctest.dao.impl.BankDaoImpl;
import week2.jdbctest.domain.Bank;
import week2.jdbctest.service.BankService;
import week2.jdbctest.utils.DbUtils;
import java.util.List;
public class BankServiceImpl implements BankService {
    BankDao bankDao = new BankDaoImpl();
//    转账
    @Override
    public void trans(int id, int toId, double money) {
        DbUtils.begin();
        try {
            bankDao.saveMoney(toId,money);
//            模拟特殊情况如停电
//            int n = 10/0;
            bankDao.takeMoney(id,money);
            DbUtils.commit();
        } catch (Exception e) {
            DbUtils.rollback();
        } finally {
            DbUtils.closeNotAuto();
        }
    }
    @Override
    public double getBalance(int id) {
        return bankDao.getBalance(id);
    }
    @Override
    public Long getCount() {
        return bankDao.getCount();
    }
    @Override
    public void listBalance() {
        bankDao.listBalance().forEach(System.out::println);
    }
    public void listBank(){
        List<Bank> list = bankDao.listBank();
        if (list == null) {
            System.out.println("无");
        }
        for (Bank bank : list) {
            System.out.println(bank.toString());
        }
    }
}