你不知道的DBUtils框架

DBUtils能干啥

Commons DbUtils库是 Apache 组织提供的一个开源 JDBC工具类库，它是结合连接池 C3P0 使用工具类。它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。它有三个核心功能，刚好用于解决项目实践中很容易碰到的问题。

QueryRunner 中提供对 sql 语句操作的 api
ResultSetHandler 接口，用于定义 select 操作后，怎样封装结果集
定义了关闭资源与事务处理的方法

常用API

QueryRunner类

org.apache.commons.dbutils.QueryRunner类是DBUtils库中的核心类。它执行带有可插入策略（增删改）的SQL

操作来处理ResultSets。

常用构造方法

QueryRunner()：默认构造方法，使用最多的。

QueryRunner(DataSource ds)：这个是带connection的，直接传入连接池，DBUtils 底层自动维护连接 connection 。

注意：如果涉及事务操作，应该使用第二种构造方法。具体见下面的事务操作。

常用方法（query和update）

查询
```
public <T> T query(Connection conn,String sql,ResultSetHandler<T> rsh,Object... params) throws SQLException
```
Execute an SQL SELECT query with replacement parameters. The caller is responsible for closing the connection.
- Type Parameters:
  
  T - The type of object that the handler returns
- Parameters:
  
  conn - The connection to execute the query in. 连接对象
  
  sql - The query to execute.
  
  rsh - The handler that converts the results into an object.
  
  params - The replacement parameters. SQL语句中可能需要的参数——可变类型的参数
- Returns:
  
  The object returned by the handler.
- Throws:
  
  SQLException - if a database access error occurs
```
public <T> T query(String sql,ResultSetHandler<T> rsh,Object... params)throws SQLException
```
Executes the given SELECT SQL query and returns a result object. The Connection is retrieved from the DataSource set in the constructor.
- Type Parameters:
  
  T - The type of object that the handler returns
- Parameters:
  
  sql - The SQL statement to execute.要执行的SQL语句
  
  rsh - The handler used to create the result object from the ResultSet.结果集处理对象
  
  params - Initialize the PreparedStatement's IN parameters with this array.SQL语句需要的参数
- Returns:
  
  An object generated by the handler.
- Throws:
  
  SQLException - if a database access error occurs
更新
```
public int update(Connection conn,String sql,Object... params)throws SQLException
```
Execute an SQL INSERT, UPDATE, or DELETE query.
- Parameters:
  
  conn - The connection to use to run the query. 连接对象
  
  sql - The SQL to execute.
  
  params - The query replacement parameters.
- Returns: The number of rows updated.
- Throws:SQLException - if a database access error occurs
```
public int update(String sql,Object... params)throws SQLException
```
Executes the given INSERT, UPDATE, or DELETE SQL statement. The Connection is retrieved from the DataSource set in the constructor. This Connection must be in auto-commit mode or the update will not be saved.
- Parameters:
  
  sql - The SQL statement to execute.
  
  params - Initializes the PreparedStatement's IN (i.e. '?') parameters.
- Returns: The number of rows updated.：
- Throws:SQLException - if a database access error occurs

ResultSetHandler接口

该接口用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式。该接口只有一个方法。

T handle(ResultSet rs) throws SQLException

Turn the ResultSet into an Object.

Parameters:

rs - The ResultSet to handle. It has not been touched before being passed to this method.
Returns:

An Object initialized with ResultSet data. It is legal for implementations to return null if the ResultSet contained 0 rows.
Throws:

SQLException - if a database access error occurs

如图所示，ResultSetHandler 接口的实现类一共有如下这些：

下面介绍常用的实现类，两个。

BeanHandler(Class<T> type)：将结果集中的第一行数据封装到一个对应的JavaBean实例中。

// 根据id查询
@Test
public void testFindById(){
    QueryRunner queryRunner = new QueryRunner(C3P0Util.getDataSource());
    Account account = null;
    try {
        account = (Account)queryRunner.query("select * from account where id =?",new BeanHandler<Account>(Account.class),1);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    System.out.println(account);
}

BeanListHandler(Class<T> type)：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。

public void testFindAll(){
    QueryRunner queryRunner = new QueryRunner(C3P0Util.getDataSource());
    List<Account> accountAll = null;
    try {
        accountAll = (List<Account>)queryRunner.query("select * from account",new BeanListHandler<Account>(Account.class));
    } catch (SQLException e) {
        e.printStackTrace();
    }
    System.out.println(accountAll);
}

DbUtils工具类

DbUtils ：提供如加载驱动、关闭连接、事务提交、回滚等常规工作的工具类，所有方法都是静态的。主要方法如下：

注意：图片来源见水印

事务管理

为什么构造函数中有的需要传入数据源，而有的不需要？在 update 和 query 方法中为什么有的需要传入连接对象，而有的不需要？

不需要事务管理时，通常使用QueryRunner(DataSource ds)的实例，然后在执行诸如 update 和 query 方法的时候就使用不需要传入连接对象的方法就好了

需要进行事务管理时，我们必须保证所有的操作都是使用同一个连接对象完成的，因此在构建 QueryRunner 对象实例的时使用空参构造函数，但是在执行 update 或者 query 这些方法的时候，需要使用传入连接对象的方法，而且必须保证同一个事务操作中传入的连接对象是同一个。

Maven中使用DBUtils

DBUtils框架+C3P0连接池实现一个银行账户CRUD的操作。

创建maven工程，导入相应坐标

    <packaging>jar</packaging>
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>

        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.4</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

创建account账户表

create table account(
id int primary key auto_increment,
name varchar(40),
money float
)character set utf8 collate utf8_general_ci;
insert into account(name,money) values('aaa',1000);
insert into account(name,money) values('bbb',1000);
insert into account(name,money) values('ccc',1000);

编写账户的实体类

/**
 * @ClassName: Account
 * @author: benjamin
 * @version: 1.0
 * @description: 账户的实体类
 * @createTime: 2019/07/25/15:05
 */

public class Account implements Serializable {

    private Integer id;
    private String name;
    private Float money;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Float getMoney() {
        return money;
    }

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

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

编写账户持久层接口以及实现类

/**
 * @ClassName: IAccountDao
 * @author: benjamin
 * @createTime: 2019/08/03/09:58
 * 账户的持久层接口
 */
public interface IAccountDao {
    void save(Account account);

    void update(Account account);

    void delete(Integer accountId);

    Account findById(Integer accountId);

    List<Account> findAll();
}

实现类

/**
 * @ClassName: AccountDaoImpl
 * @author: benjamin
 * @version: 1.0
 * @description: 持久层实现类
 * @createTime: 2019/08/03/10:00
 */

public class AccountDaoImpl implements IAccountDao {

    private QueryRunner runner = new QueryRunner(C3P0Util.getDataSource());

    // 增
    @Override
    public void save(Account account) {
        try {
            runner.update("insert into account(name,money)values(?,?)",account.getName(),account.getMoney());
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }

    // 删
    @Override
    public void delete(Integer accountId) {
        try {
            runner.update("delete from account where id=?",accountId);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    // 改
    @Override
    public void update(Account account) {
        try {
            runner.update("update account set name=?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }

    // 查一个
    @Override
    public Account findById(Integer accountId) {
        try {
            return (Account)runner.query("select * from account where id = ?",new BeanHandler<Account>(Account.class),accountId);
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }
    // 查全部
    @Override
    public List<Account> findAll() {
        try {
            return (List<Account>)runner.query("select * from account",new BeanListHandler<Account>(Account.class));
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }
}

在resource文件夹下创建c3p0-config.xml文件，配置数据库连接信息

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<default-config>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://192.168.214.128:3306/eesy</property>
		<property name="user">root</property>
		<property name="password">ben123</property>
		<property name="acquireIncrement">5</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">5</property>
		<property name="maxPoolSize">20</property>
	</default-config>
</c3p0-config>

在com.ben.utils.C3P0Util，创建数据库连接池对象 ComboPooledDataSource

/**
 * @ClassName: C3P0Util
 * @author: benjamin
 * @version: 1.0
 * @description: C3P0
 * @createTime: 2019/08/03/10:01
 */

public class C3P0Util {
    // 1. 创建数据库连接池对象
    private static ComboPooledDataSource ds = new ComboPooledDataSource();

    public static DataSource getDataSource(){
        return ds;
    }

    // 获取连接池对象
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    public static void main(String[] args) {
        System.out.println(getDataSource());
    }

}

编写测试文件

package com.ben;

import com.ben.dao.IAccountDao;
import com.ben.dao.impl.AccountDaoImpl;
import com.ben.domain.Account;
import org.junit.Test;

import java.util.List;

/**
 * @ClassName: DBUtilsTest
 * @author: benjamin
 * @version: 1.0
 * @description: TODO
 * @createTime: 2019/08/03/10:10
 */

public class DBUtilsTest {
    public static void main(String[] args) {

    }

    @Test
    public void testSave(){

        // 创建一个新用户
        Account account = new Account();
        account.setName("hehe");
        account.setMoney(2345f);

        IAccountDao accountDao = new AccountDaoImpl();
        accountDao.save(account);
    }

    @Test
    public void testFindAll(){
        IAccountDao accountDao = new AccountDaoImpl();
        List<Account> all = accountDao.findAll();
        System.out.println(all);
    }
}