Apache的DBUtils框架学习

commons-dbutils简介

commons-dbutils是Apache组织提供的一个开源JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。因此dbutils成为很多不喜欢hibernate的公司的首选。 
commons-dbutils API介绍：

• org.apache.commons.dbutils.QueryRunner QueryRunner中有update()和query()方法
• org.apache.commons.dbutils.ResultSetHandler

工具类：

• org.apache.commons.dbutils.DbUtils

QueryRunner类使用讲解

该类简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。 
QueryRunner类提供了两个构造方法：

• 默认的构造方法。
• 需要一个javax.sql.DataSource来作参数的构造方法。

QueryRunner类的主要方法

• public Object query(Connection conn, String sql, Object[] params, ResultSetHandler rsh) throws SQLException
  执行一个查询操作，在这个查询中，对象数组中的每个元素值被用来作为查询语句的置换参数。该方法会自行处理PreparedStatement和ResultSet的创建和关闭。
• public Object query(String sql, Object[] params, ResultSetHandler rsh) throws SQLException 
  几乎与第一种方法一样；唯一的不同在于它不将数据库连接提供给方法，并且它是从提供给构造方法的数据源(DataSource)或使用的setDataSource方法中重新获得Connection。
• public Object query(Connection conn, String sql, ResultSetHandler rsh) throws SQLException 
  执行一个不需要置换参数的查询操作。
• public int update(Connection conn, String sql, Object[] params) throws SQLException 
  用来执行一个更新（插入、更新或删除）操作。
• public int update(Connection conn, String sql) throws SQLException 
  用来执行一个不需要置换参数的更新操作。

• public int[] batch(Connection conn, String sql, Object[][] params) throws SQLException 
  这个方法对应着批处理，经常用于在同一个表中批量插入数据，或批量更新表的数据。 
  该方法为何会接收二维数组Object[][] params呢？ 
  答：例如现在要想在同一个表中批量插入数据，编写的SQL语句为：

  String sql = "insert into users(id,name) values(?,?)";
  1
  1

  该方法接收二维数组Object[][] params，那么调用其的时候就要传递一个诸如这样的实参[[1,aa],[2,bb],[3,cc]]，即用二维数组里面的每一个一维数组生成一条sql语句。 
  那为何又会返回int[]呢？ 
  答：该方法的返回值是int[]，所以会返回诸如这样的结果：[1,1,1]，意思是生成的第一条sql语句影响数据库几行、生成的第二条sql语句影响数据库几行、生成的第三条sql语句影响数据库几行。

ResultSetHandler接口使用讲解

该接口用于处理Java.sql.ResultSet，将数据按要求转换为另一种形式。ResultSetHandler接口提供了一个单独的方法：Object handle (java.sql.ResultSet .rs)。

ResultSetHandler接口的实现类

• ArrayHandler：把结果集中的第一行数据转成对象数组。
• ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。
• BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。
• BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
• ColumnListHandler：将结果集中某一列的数据存放到List中。
• KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key。
• MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
• MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List。

DbUtils类使用讲解

DbUtils：提供如关闭连接、装载JDBC驱动程序等常规工作的工具类，里面的所有方法都是静态的。主要方法如下：

• public static void close(…) throws java.sql.SQLException 
  DbUtils类提供了三个重载的关闭方法。这些方法检查所提供的参数是不是NULL，如果不是的话，它们就关闭Connection、Statement和ResultSet。
• public static void closeQuietly(…) 
  这一类方法不仅能在Connection、Statement和ResultSet为NULL情况下避免关闭，还能隐藏一些在程序中抛出的SQLException。
• public static void commitAndCloseQuietly(Connection conn) 
  用来提交连接，然后关闭连接，并且在关闭连接时不抛出SQL异常。
• public static boolean loadDriver(java.lang.String driverClassName) 
  这一方法装载并注册JDBC驱动程序，如果成功就返回true。使用该方法，你不需要捕捉这个异常ClassNotFoundException。

使用DBUtils完成数据库的CRUD

在使用DBUtils完成数据库的CRUD之前，我们先编写测试用的SQL脚本：

create database day17;use day17;create table users (    id int primary key,    name varchar(40),    password varchar(40),    email varchar(60),    birthday date);
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

在应用程序中加入dbcp连接池

导入相关jar包：

• commons-dbcp-1.4.jar
• commons-pool-1.6.jar

在类目录下加入dbcp的配置文件：dbcpconfig.properties。dbcpconfig.properties的配置信息如下：

#连接设置driverClassName=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/day17username=rootpassword=yezi#<!-- 初始化连接 -->initialSize=10#最大连接数量maxActive=50#<!-- 最大空闲连接 -->maxIdle=20#<!-- 最小空闲连接 -->minIdle=5#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->maxWait=60000#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;] #注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。connectionProperties=useUnicode=true;characterEncoding=utf8#指定由连接池所创建的连接的自动提交（auto-commit）状态。defaultAutoCommit=true#driver default 指定由连接池所创建的连接的只读（read-only）状态。#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）defaultReadOnly=#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLEdefaultTransactionIsolation=READ_COMMITTED
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

如下图所示： 

使用QueryRunner类实现CRUD

在cn.itcast.domain包下创建一个封装数据的实体——User.java，对应数据库中的users表。User类的具体代码如下：

public class User {    private int id;    private String name;    private String password;    private String email;    private Date birthday;    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 String getPassword() {        return password;    }    public void setPassword(String password) {        this.password = password;    }    public String getEmail() {        return email;    }    public void setEmail(String email) {        this.email = email;    }    public Date getBirthday() {        return birthday;    }    public void setBirthday(Date birthday) {        this.birthday = birthday;    }}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

在cn.itcast.utils包下创建一个获取数据库连接的工具类(JdbcUtils)，该工具类(JdbcUtils)的代码为：

public class JdbcUtils {    private static DataSource ds = null;    static {        try {            Properties prop = new Properties();            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.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 {        return ds.getConnection();    }    /*     * 工具类里面现在没有必要提供release()方法，因为我们是使用dbutils操作数据库，     * 即调用dbutils的update()和query()方法操作数据库，他操作完数据库之后，会自动释放掉连接。     */}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

提示：工具类里面现在没有必要提供release()方法，因为我们是使用DBUtils操作数据库，即调用DBUtils的update()和query()方法操作数据库，它操作完数据库之后，会自动释放掉连接。 
现在在cn.itcast.dbutils.demo包下新建一个测试类Demo1.java，Demo1类的具体代码如下：

public class Demo1 {        // 使用dbutils完成数据库的CRUD    @Test    public void insert() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";        Object[] params = {2, "bbb", "123", "[e-mail]", new Date()};        runner.update(sql, params);    }    @Test    public void update() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "update users set email=? where id=?";        Object[] params = {"[e-mail]", 1};        runner.update(sql, params);    }    @Test    public void delete() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "delete from users where id=?";        runner.update(sql, 2);    }    @Test    public void find() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select * from users where id=?";        User user = (User) runner.query(sql, 1, new BeanHandler(User.class));        System.out.println(user.getEmail());    }    @Test    public void getAll() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select * from users";        List list = (List) runner.query(sql, new BeanListHandler(User.class));        System.out.println(list);    }    @Test    public void batch() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";        Object[][] params = new Object[3][5];        for (int i = 0; i < params.length; i++) {            params[i] = new Object[]{i+1, "aa"+i, "123", i+"@sian.com", new Date()};        }        runner.batch(sql, params);    }}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

测试dbutils各种类型的处理器

在使用DBUtils完成数据库的CRUD中，我们就已经测试过BeanHandler和BeanListHandler处理器，现在我们来一个个测试其余的处理器。

• ArrayHandler：把结果集中的第一行数据转成对象数组。测试代码如下：

  // 测试dbutils的各个结果集处理器public class Demo2 {    @Test    public void test1() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select * from users";        Object[] result = runner.query(sql, new ArrayHandler());        System.out.println(result[0]);        System.out.println(result[1]);    }}
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

• ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。测试代码如下：

  // 测试dbutils的各个结果集处理器public class Demo2 {    @Test    public void test2() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select * from users";        List list = runner.query(sql, new ArrayListHandler());        System.out.println(list);    }}
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

• ColumnListHandler：将结果集中某一列的数据存放到List中。测试代码如下：

  // 测试dbutils的各个结果集处理器public class Demo2 {    @Test    public void test3() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select * from users";        List list = (List) runner.query(sql, new ColumnListHandler("name"));        System.out.println(list);    }}
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

  我们也可以自己重新写出这样一个ColumnListHandler处理器，如：

  class ColumnListHandler1 implements ResultSetHandler {    private String columnName;    public ColumnListHandler1(String columnName) {        this.columnName = columnName;    }    @Override    public Object handle(ResultSet rs) throws SQLException {        List list = new ArrayList();        while (rs.next()) {            list.add(rs.getObject(columnName));        }        return list;    }}
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18

  用我们自己编写的ColumnListHandler1处理器来测试，代码为：

  // 测试dbutils的各个结果集处理器public class Demo2 {    @Test    public void test3() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select * from users";        List list = (List) runner.query(sql, new ColumnListHandler1("name"));        System.out.println(list);    }}
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

• KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key。 
  这话听起来有些绕口，但我用一张图来解释就很明白了。 
  
  明白原理之后，就很容易写测试代码了，测试代码为：

  // 测试dbutils的各个结果集处理器public class Demo2 {    @Test    public void test4() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select * from users";        Map<Integer, Map<String, Object>> map = (Map<Integer, Map<String, Object>>) runner.query(sql, new KeyedHandler("id"));        for (Map.Entry<Integer, Map<String, Object>> me : map.entrySet()) {            int id = me.getKey();            for (Map.Entry<String, Object> entry : me.getValue().entrySet()) {                String name = entry.getKey();                Object value = entry.getValue();                System.out.println(name+"="+value);            }        }    }}
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17

• 当想要查询某张表中的总记录数时，我们可以用ArrayHandler处理器来处理。作为一个新手，第一次写代码时，可能会这样写：

  // 测试dbutils的各个结果集处理器public class Demo2 {    @Test    public void test5() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select count(*) from users";        Object[] result = runner.query(sql, new ArrayHandler());        int totalrecord = (int) result[0];        System.out.println(totalrecord);    }}
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

  JUnit测试可发现报如下异常：

  java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Integer
  1
  1

  原因是：查询结果——总记录数是作为java.lang.Long类型返回的。所以我们应该把代码修改为：

  // 测试dbutils的各个结果集处理器public class Demo2 {    @Test    public void test5() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select count(*) from users";        Object[] result = runner.query(sql, new ArrayHandler());        long totalrecord = (Long) result[0]; // 查询结果——总记录数是作为java.lang.Long类型返回的        int num = (int) totalrecord;        System.out.println(num);    }}
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

  除此之外，我们又可写为：

  // 测试dbutils的各个结果集处理器public class Demo2 {    @Test    public void test5() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select count(*) from users";        Object[] result = runner.query(sql, new ArrayHandler());        int totalrecord =  ((Long) result[0]).intValue();        System.out.println(totalrecord);    }}
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

  更加专业一点的方法是使用ScalarHandler处理器，测试代码如下：

  // 测试dbutils的各个结果集处理器public class Demo2 {    @Test    public void test5() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select count(*) from users";        int totalrecord = ((Long)runner.query(sql, new ScalarHandler(1))).intValue();        System.out.println(totalrecord);    }}