JDBC事物&连接池&DBUtils

JDBC

一、事务

1. 事务应该写在Service层，因为DAO一般一个方法只会执行一个SQL，而Service层才会将多个SQL放在一起执行。
2. 查询无需处理事务，增删改才需要执行事务。

// 在方法执行开始时开启事务
connection.setAutoCommit(false); // 关闭自动提交

// 在业务SQL全部执行完毕后提交事务
connection.commit();

// 在异常处理中回滚事务
connection.rollback();

1.1 当没有设置事务时

通过转账的案例，可以看到，当没有事务时，会出现汇款方转出后，收款方未收到

public boolean transfer(String from, String to, String fromPwd, int money) throws SQLException{
        // 登录账号
        Account account = accountDAO.selectByAccountAndPwd(from, fromPwd);
        System.out.println("开始登录");
        if (account == null){
            System.out.println("登录失败");
        }else{
            System.out.println("登录成功");
            // 验证余额
            System.out.println("开始验证余额");
            int money1 = accountDAO.selectMoneyByAccount(from);
            if (money1 < money){
                System.out.println("余额不足");
            }else{
                // 验证对方账号是否存在
                System.out.println("开始查询对方账号是否存在");
                if(!accountDAO.selectAccount(to)){
                    System.out.println("对方账号不存在");
                }else {
                    System.out.println("转账开始。。。");
                    System.out.println("汇款方开始扣款");
                    accountDAO.decreaseMoney(from, money);
                    System.out.println("收款方开始收款");
                    int i = 5 / 0;
                    accountDAO.increaseMoney(to, money);
                    System.out.println("转账成功结束");
                }
            }
        }
        return false;
    }

1.2 初步设置事务

以下代码虽然看似设置了事务，但是无法实现回滚，因为事务回滚的前提是要求SQL在同一个连接中执行。

// 以下代码并不能真正实现事务
/**
     * 转账
     * @param from 汇款人账号
     * @param to 收款人账号
     * @param fromPwd 汇款人密码
     * @param money 转账金额
     * @return
     * @throws SQLException
     */
    @Override
    public boolean transfer(String from, String to, String fromPwd, int money) throws SQLException{
        Connection connection = null;
        try {
            connection = DBUtils.getConnection();
            connection.setAutoCommit(false); // 关闭自动提交
            // 登录账号
            Account account = accountDAO.selectByAccountAndPwd(from, fromPwd);
            System.out.println("开始登录");
            if (account == null){
                System.out.println("登录失败");
            }else{
                System.out.println("登录成功");
                // 验证余额
                System.out.println("开始验证余额");
                int money1 = accountDAO.selectMoneyByAccount(from);
                if (money1 < money){
                    System.out.println("余额不足");
                }else{
                    // 验证对方账号是否存在
                    System.out.println("开始查询对方账号是否存在");
                    if(!accountDAO.selectAccount(to)){
                        System.out.println("对方账号不存在");
                    }else {
                        System.out.println("转账开始。。。");
                        System.out.println("汇款方开始扣款");
                        accountDAO.decreaseMoney(from, money);
                        System.out.println("收款方开始收款");
                        int i = 5 / 0;
                        accountDAO.increaseMoney(to, money);
                        System.out.println("转账成功结束");
                        System.out.println("开始提交事务==");
                        connection.commit(); // 提交事务
                    }
                }
            }
        }catch (Exception e){
            e.printStackTrace();
            if (connection!=null){
                System.out.println("开始回滚");
                connection.rollback(); // 出现异常，回滚事务
            }
        }finally {
            DBUtils.close(connection, null);
        }
        return false;
    }

1.3 使用传参来共享同一个连接

既然需要在同一个连接中处理，才能回滚，那么可以使用传递connection参数来解决此问题

@Override
    public boolean transfer(String from, String to, String fromPwd, int money) throws SQLException{
        Connection connection = null;
        try {
            connection = DBUtils.getConnection();
            connection.setAutoCommit(false); // 关闭自动提交
            // 登录账号
            Account account = accountDAO.selectByAccountAndPwd(from, fromPwd);
            System.out.println("开始登录");
            if (account == null){
                System.out.println("登录失败");
            }else{
                System.out.println("登录成功");
                // 验证余额
                System.out.println("开始验证余额");
                int money1 = accountDAO.selectMoneyByAccount(from);
                if (money1 < money){
                    System.out.println("余额不足");
                }else{
                    // 验证对方账号是否存在
                    System.out.println("开始查询对方账号是否存在");
                    if(!accountDAO.selectAccount(to)){
                        System.out.println("对方账号不存在");
                    }else {
                        System.out.println("转账开始。。。");
                        System.out.println("汇款方开始扣款");
                        accountDAO.decreaseMoney(connection, from, money);
                        System.out.println("收款方开始收款");
                        int i = 5 / 0;
                        accountDAO.increaseMoney(connection, to, money);
                        System.out.println("转账成功结束");
                        System.out.println("开始提交事务==");
                        connection.commit(); // 提交事务
                    }
                }
            }
        }catch (Exception e){
            e.printStackTrace();
            if (connection!=null){
                System.out.println("开始回滚");
                connection.rollback(); // 出现异常，回滚事务
            }
        }finally {
            DBUtils.close(connection, null);
        }
        return false;
    }

注意：在DAO中使用Service传递过来的connection时，不能关闭连接，因为该连接还要用来回滚或提交。

直接传递参数的方式虽然可以解决事务问题，但是对于接口造成了一定的污染，不推荐使用。

1.4 使用ThreadLocal实现[重点]

ThreadLocal的基本原理：

通过一个Map来存入需要共享的对象（此处是连接对象），key与当前线程关联。

由于Service中调用的各个dao中的方法是在同一个线程中执行，当dao的方法里需要使用连接时，会使用当前线程去获取该对象，以达到实现线程共享的目的。

public T get() {
    Thread t = Thread.currentThread();
    ThreadLocalMap map = getMap(t);
    if (map != null) {
        ThreadLocalMap.Entry e = map.getEntry(this);
        if (e != null) {
            @SuppressWarnings("unchecked")
            T result = (T)e.value;
            return result;
        }
    }
    return setInitialValue();
}

public void set(T value) {
    Thread t = Thread.currentThread();
    ThreadLocalMap map = getMap(t);
    if (map != null)
        map.set(this, value);
    else
        createMap(t, value);
}

修改DBUtils，将每次创建连接对象的机制修改为第一次才创建，后面都只是获取使用，注意关闭时要从集合移除。

public class DBUtils {
    // 创建一个保存资源文件信息的集合
    private static Properties DB_CONFIG = new Properties();
    // 创建存放共享连接对象的ThreadLocal对象（map）
    private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<>();
    static {
        // 读取根目录下的资源文件
        InputStream inputStream = DBUtils.class.getResourceAsStream("/dbconfig.properties");
        try {
            // 将资源文件中的信息加载到集合中
            DB_CONFIG.load(inputStream);
            // 通过集合中获取相应的驱动信息，并加载驱动
            Class.forName(DB_CONFIG.getProperty("jdbc.driver"));
        } catch (ClassNotFoundException e) {
            System.out.println("找不到加载驱动类");
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        Connection connection = THREAD_LOCAL.get(); // 在集合中获取连接对象
        // 如果连接为空，就创建一个连接
        if (connection == null){
            connection = DriverManager.getConnection(
                    DB_CONFIG.getProperty("jdbc.url"),
                    DB_CONFIG.getProperty("jdbc.username"),
                    DB_CONFIG.getProperty("jdbc.password"));
            // 将创建的连接放入集合
            THREAD_LOCAL.set(connection);
        }
        return connection;
    }

    public static void close(Connection connection, Statement statement) throws SQLException {
        if(statement != null){
            statement.close();
        }
        if (connection != null){
            connection.close();
//            THREAD_LOCAL.set(null);
            THREAD_LOCAL.remove(); // 关闭连接时从集合中移除
        }
    }

    public static void close(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if(resultSet != null){
            resultSet.close();
        }
        if(statement != null){
            statement.close();
        }
        if (connection != null){
            connection.close();
            THREAD_LOCAL.remove(); // 关闭连接时从集合中移除
        }
    }
}

注意：dao中不要关闭连接。

1.5 封装事务方法

public static void  begin(){
    try {
        Connection connection = getConnection();
        connection.setAutoCommit(false);
    }catch (Exception e){
        e.printStackTrace();
    }
}

public static void  commit(){
    try {
        Connection connection = getConnection();
        connection.commit();
    }catch (Exception e){
        e.printStackTrace();
    }
}

public static void  rollback(){
    try {
        Connection connection = getConnection();
        connection.rollback();
    }catch (Exception e){
        e.printStackTrace();
    }
}

public static void close() throws SQLException {
    Connection connection = getConnection();
    if (connection != null){
        connection.close();
        THREAD_LOCAL.remove(); // 关闭连接时从集合中移除
    }
}

二、三层架构

三层架构是一般情况项目至少分层，实际项目中可能不止三层。

视图层：一般与用户交互。接收用户输入的值保存到VO（value object值对象），将业务层数据输出到VO展示给用户。

业务层：处理业务逻辑。接收到视图层传递的VO数据，经过转换后变成BO（business object业务对象），交给数据访问层。调用数据访问层后返回的数据保存到BO，并转换成VO交给视图层。

数据访问层：处理数据库操作。接收到业务层的BO，转换成PO（persist object持久化对象），进行数据库处理。从数据库中查询数据（PO），转换成BO传递到业务层。

注意：PO转BO，BO转VO，使用的类叫做DTO（data transfer object）。

三、DaoUtils封装

在编写DAO类时，发现代码冗余过多，应该抽取并封装。

3.1 可变参数的用法

可变参数在定义的方法里，一般直接当作数组使用。

但是在调用过程中，与数组有一定的区别：

1. 如果参数没有，可以不传，但是数组必须要传，哪怕为null。
2. 如果参数没有，可变参数的长度为0，而数组为null时不能调用length属性，会空指针异常。
3. 当参数有一个或多个时，可变参数可以直接写，数组需要构建成数组结构。
4. 可变参数在参数列表中一般只能有一个，且在方法列表的最后。

3.2 增删改封装

增删改代码几乎大同小异，可以封装成一个方法。定义为：

public boolean commonUpdate(String sql, Object... args)throws SQLException{}

具体代码如下：

/**
     * 增删改封装
     * @param sql SQL语句
     * @param args SQL中占位符对应填充的参数
     * @return
     * @throws SQLException
     */
public boolean commonUpdate(String sql, Object... args)throws SQLException{
    Connection connection = DBUtils.getConnection(); // 获得连接
    PreparedStatement preparedStatement = connection.prepareStatement(sql); //预编译语句
    // 循环设置参数
    for(int i = 0, len = args.length; i < len; i++){
        preparedStatement.setObject(i+1, args[i]);
    }
    // 执行并返回影响行数是否大于0（即是否成功）
    return preparedStatement.executeUpdate() > 0;
    // 注意：由于连接是线程共享，应该在Service层中关闭，所以此处没有关闭连接
}

3.3 查询的封装

查询前半部分与增删改一样，区别在于获取查询结果后，需要将查询结果集进行封装。

由于并不知道调用者是具体查询哪个表，于是只能定义一个接口，用来将ResultSet封装成一个实体类，定义如下：

/**
 * 将表查询结果封装成对象
 */
public interface RowMapper<T> {
    T getResult(ResultSet resultSet) throws SQLException;
}

相应的查询方法可以在循环结果集时调用此接口中的方法获取实体对象，将真正实现的部分交给调用者。

/**
     * 查询所有
     * @param sql SQL语句
     * @param rowMapper 封装结果集映射接口
     * @param args QL中占位符对应填充的参数
     * @return
     * @throws SQLException
     */
public List<T> selectAll(String sql, RowMapper<T> rowMapper, Object... args)throws    SQLException{
    List<T> list = new ArrayList<>(); // 创建封装结果集的集合
    Connection connection = DBUtils.getConnection(); //获得连接
    PreparedStatement preparedStatement = connection.prepareStatement(sql); //预编译语句
    // 循环设置参数
    for(int i = 0, len = args.length; i < len; i++){
        preparedStatement.setObject(i+1, args[i]);
    }
    // 获得查询结果集
    ResultSet resultSet = preparedStatement.executeQuery();
    // 遍历结果集
    while (resultSet.next()){
        // 将每一行数据封装成一个实体类对象的结果
        T t = rowMapper.getResult(resultSet);
        // 添加集合中
        list.add(t);
    }
    return list;
}

调用者在调用时，需要首先实现该封装接口，才能真正调用，如下示例演示了Person实体对于封装的实现和最后的调用。

/**
 * 将person表查询结果封装成Person对象
 */
public class PersonRowMapper implements RowMapper<Person>{
    @Override
    public Person getResult(ResultSet resultSet) throws SQLException{
        // 属性和字段对应
        return new Person(resultSet.getInt("id"),
                resultSet.getString("name"),
                resultSet.getString("sex"),
                resultSet.getInt("age"));
    }
}

 /**
     * 查询
     * @return
     * @throws SQLException
     */
@Override
public List<Person> selectAll() throws SQLException {
    return daoUtils.selectAll("SELECT * FROM t_person", new PersonRowMapper());
}

四、连接池的使用

4.1 连接池的作用

连接池本质就是使用一个集合来创建并管理、释放一组连接。

优点：1. 避免连接的频繁创建、销毁造成额外资源的消耗

2. 对一组连接进行统一的管理，例如等待机制，最大活动连接，空闲连接，是否自动提交事务等

public class DBUtils {
    private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<>();
    private static final Properties PROPERTIES = new Properties();
    private static DataSource dataSource; // 定义数据源连接池

    static {
        try {
            InputStream inputStream = DBUtils.class.getResourceAsStream("/dbconfig.properties");
            PROPERTIES.load(inputStream);
            dataSource = DruidDataSourceFactory.createDataSource(PROPERTIES); // 通过properties文件来设置连接池属性，创建连接池
        }catch (Exception ex){
            ex.printStackTrace();
        }
    }


    public static Connection getConnection() throws SQLException{
        Connection connection = THREAD_LOCAL.get();
        if (connection == null){
            connection = dataSource.getConnection(); // 在连接池里面获取连接
            THREAD_LOCAL.set(connection);
        }
        return connection;
    }

    public static void close() throws SQLException{
        Connection connection = getConnection();
        if (connection != null){
            connection.close();
            THREAD_LOCAL.remove();
        }
    }
}

url=jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8
username=root
password=root
driverClassName=com.mysql.jdbc.Driver
# 最大活动连接数
maxActive=50
# 初始化连接数
initialSize=10
# 最大等待时间（单位：毫秒）
maxWait=5000

注意：properties资源文件中，key的名称必须要与DruidDataSourceFactory源代码中的key对应，而不是随意一个名称。

五、DBUtils用法

DBUtils是一个简单封装了JDBC的第三方库，用以简化数据库操作。需要导入jar包。

// 创建一个数据库操作对象（参数是数据源连接池）
private QueryRunner runner =  new QueryRunner(DBUtils.getDataSource());

/**
     * 添加
     * @param person
     * @return
     * @throws SQLException
     */
@Override
public boolean insert(Person person) throws SQLException {
    return runner.update("INSERT INTO t_person(name, sex, age) VALUES(?,?,?)",
                         person.getName(), person.getSex(), person.getAge()) > 0;
}

/**
     * 修改
     * @param person
     * @return
     * @throws SQLException
     */
@Override
public boolean update(Person person) throws SQLException {
    return runner.update("UPDATE t_person SET name = ?, sex = ?, age = ? WHERE id = ?",
                         person.getName(), person.getSex(), person.getAge(), person.getId()) > 0;
}

/**
     * 删除
     * @param id
     * @return
     * @throws SQLException
     */
@Override
public boolean delete(int id) throws SQLException {
    return runner.update("DELETE FROM t_person WHERE id = ?", id) > 0;
}

/**
     * 查询
     * @return
     * @throws SQLException
     */
@Override
public List<Person> selectAll() throws SQLException {
    // ResultSetHandler需要自行手动设置结果集和实体类映射关系
    return runner.query("SELECT * FROM t_person", new ResultSetHandler<List<Person>>() {
        @Override
        public List<Person> handle(ResultSet resultSet) throws SQLException {
            List<Person> list = new ArrayList<>();
            while (resultSet.next()){
                Person person = new Person(resultSet.getInt("id"),
                                           resultSet.getString("name"),
                                           resultSet.getString("sex"),
                                           resultSet.getInt("age"));
                list.add(person);
            }
            return list;
        }
    });
}

/**
     * 查询
     * @return
     * @throws SQLException
     */
public List<Person> selectAll1() throws SQLException {
    // 当实体类属性和数据库表字段一致时，可以简单使用BeanListHandler自动封装
    return runner.query("SELECT * FROM t_person", new BeanListHandler<Person>(Person.class));
}

/**
     * 根据id查询
     * @param id
     * @return
     * @throws SQLException
     */
public Person selectOne(int id) throws  SQLException{
    // 如果返回一条记录，可以使用BeanHandler
    return runner.query("SELECT * FROM t_person WHERE id = ?", new BeanHandler<Person>(Person.class), id);
}

/**
     * 查询结果行数
     * @return
     * @throws SQLException
     */
public int selectCount() throws  SQLException{
    // 如果是单行单列，ScalarHandler
    return runner.query("SELECT count(1) FROM t_person", new ScalarHandler<>());
}