DBUtils简明教程

本文以Insert和Select操作为例子，介绍apache DBUtils的使用方法和实现机制。

The Commons DbUtils library is a small set of classes designed to make working with JDBC easier

准备工作

建立表格：

CREATE  TABLE `test`.`user` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `age` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

在http://dev.mysql.com/downloads/connector/j下载mysql-connector-java-5.1.34.tar.gz。

在http://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi下载commons-dbutils-1.6-bin.tar.gz。

首先创建DBConf.java，内容如下：

package hellodbutils;

public class DBConf {
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   static final String DB_URL = "jdbc:mysql://localhost/test";

    static final String USER = "username";
    static final String PASS = "password";
}

先看一个JDBC的示例

// 类hellodbutils.JDBCInsert
package hellodbutils;
import java.sql.*;

public class JDBCInsert {
    public static void main(String args[]) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            Class.forName(DBConf.JDBC_DRIVER);
            conn = DriverManager.getConnection(DBConf.DB_URL, DBConf.USER, DBConf.PASS);

            String sql = "INSERT INTO user(name, age) VALUES(?,?)";
            pstmt = conn.prepareStatement(sql);
            // insert a user
            pstmt.setString(1, "letian");
            pstmt.setInt(2, 18);
            pstmt.execute();
            // insert another user
            pstmt.setString(1, "letiantian");
            pstmt.setInt(2, 19);
            pstmt.execute();

            //select
            sql = "SELECT * FROM user";
            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()) {
                System.out.println(rs.getInt("id"));
                System.out.println(rs.getString("name"));
                System.out.println(rs.getInt("age"));
                System.out.println("------");
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }


    }
}

运行结果如下：

1
letian
18
------
2
letiantian
19
------

MySQL命令行客户端查询结果：

mysql> select * from user;
+----+------------+-----+
| id | name       | age |
+----+------------+-----+
|  1 | letian     |  18 |
|  2 | letiantian |  19 |
+----+------------+-----+

使用DBUtils插入数据

首先清空user表中的数据：

mysql> delete from user;
mysql> ALTER TABLE user AUTO_INCREMENT = 1;

编写代码：

// 类hellodbutils.DBUtilsInsert
package hellodbutils;

import java.sql.*;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.ArrayHandler;

public class DBUtilsInsert {
    public static void main(String args[]) {
        Connection conn;
        QueryRunner queryRunner = new QueryRunner();
        ArrayHandler arrayHandler = new ArrayHandler();
        try {
            Class.forName(DBConf.JDBC_DRIVER);
            conn = DriverManager.getConnection(DBConf.DB_URL, DBConf.USER, DBConf.PASS);

            String sql = "INSERT INTO user(name, age) VALUES(?,?)";
            Object[] objectArr= queryRunner.insert(conn, sql, arrayHandler, "樂天", 18);
            System.out.println("数组长度：" + objectArr.length + "；第0个元素的值：" +objectArr[0]);
            objectArr = queryRunner.insert(conn, sql, arrayHandler, "樂天天", 19);
            System.out.println("数组长度：" + objectArr.length + "；第0个元素的值：" +objectArr[0]);

        } catch (Exception ex) {
            ex.printStackTrace();
        }

    }
}

运行结果：

数组长度：1；第0个元素的值：1
数组长度：1；第0个元素的值：2

MySQL命令行客户端查询结果：

mysql> select * from user;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | 樂天      |  18 |
|  2 | 樂天天    |  19 |
+----+-----------+-----+
2 rows in set (0.33 sec)

源码分析：

上面的句子调用了类org.apache.commons.dbutils.QueryRunner的这个方法：

// 类`org.apache.commons.dbutils.QueryRunner`下方法insert(...)
public <T> T insert(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
    return insert(conn, false, sql, rsh, params);
}

最终调用的是下面的重载方法：

// 类`org.apache.commons.dbutils.QueryRunner`下方法insert(...)
private <T> T insert(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
        throws SQLException {

    // .... 删去了若干代码

    PreparedStatement stmt = null;
    T generatedKeys = null;

    try {
        stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        this.fillStatement(stmt, params);
        stmt.executeUpdate();
        ResultSet resultSet = stmt.getGeneratedKeys(); 
        generatedKeys = rsh.handle(resultSet);
    } catch (SQLException e) {
        this.rethrow(e, sql, params);
    } finally {
        close(stmt);
        if (closeConn) {
            close(conn);
        }
    }

    return generatedKeys;
}

this.fillStatement方法来自类org.apache.commons.dbutils.QueryRunner的父类org.apache.commons.dbutils.AbstractQueryRunner，fillStatement的主要内容是：

// 类`org.apache.commons.dbutils.AbstractQueryRunner`下方法fillStatement(...)
public void fillStatement(PreparedStatement stmt, Object... params)
        throws SQLException {

    // .... 删去了若干代码

    for (int i = 0; i < params.length; i++) {
        if (params[i] != null) {
            stmt.setObject(i + 1, params[i]);
        } else {
            // .... 删去了若干代码
        }
    }
}

javadoc中如下介绍stmt.getGeneratedKeys()：

ResultSet getGeneratedKeys()
                           throws SQLException
Retrieves any auto-generated keys created as a result of executing this Statement object. If this Statement object did not generate any keys, an empty ResultSet object is returned.
Note:If the columns which represent the auto-generated keys were not specified, the JDBC driver implementation will determine the columns which best represent the auto-generated keys.

类org.apache.commons.dbutils.QueryRunner的insert(...)函数有一参数是ResultSetHandler<T> rsh，ResultSetHandler用来处理sql查询后得到的结果。在类hellodbutils.DBUtilsInsert中，我们使用的ResultSetHandler是类org.apache.commons.dbutils.handlers.ArrayHandler：

// 类`org.apache.commons.dbutils.handlers.ArrayHandler`
public class ArrayHandler implements ResultSetHandler<Object[]> {
    // .... 删去了若干代码
    @Override
    public Object[] handle(ResultSet rs) throws SQLException {
        return rs.next() ? this.convert.toArray(rs) : EMPTY_ARRAY;
    }
}

this.convert默认值是new BasicRowProcessor()。类BasicRowProcessor在包org.apache.commons.dbutils中，其中toArray(...)函数详细如下：

// 类org.apache.commons.dbutils.BasicRowProcessor下的toArray(...)函数
@Override
public Object[] toArray(ResultSet rs) throws SQLException {
    ResultSetMetaData meta = rs.getMetaData();
    int cols = meta.getColumnCount();
    Object[] result = new Object[cols];

    for (int i = 0; i < cols; i++) {
        result[i] = rs.getObject(i + 1);
    }

    return result;
}

好了，到了这里，类hellodbutils.DBUtilsInsert使用DBUtils插入数据的思路也就知道了。

使用DBUtils获取一条数据

编写代码：

// 类hellodbutils.DBUtilsSelect
package hellodbutils;

import java.util.Map;
import java.sql.*;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

public class DBUtilsSelect {
    public static void main(String args[]) {
        Connection conn;
        QueryRunner queryRunner = new QueryRunner();
        try {
            Class.forName(DBConf.JDBC_DRIVER);
            conn = DriverManager.getConnection(DBConf.DB_URL, DBConf.USER, DBConf.PASS);

            String sql = "SELECT id, name, age FROM user WHERE id=?";
            Map<String, Object> resultMap = queryRunner.query(conn, sql, new MapHandler(), 1);
            System.out.println(resultMap);

        } catch (Exception ex) {
            ex.printStackTrace();
        }

    }
}

运行结果：

{id=1, name=樂天, age=18}

源码分析：

我们看一下org.apache.commons.dbutils.handlers.MapHandler做了什么：

// 类org.apache.commons.dbutils.handlers.MapHandler
public class MapHandler implements ResultSetHandler<Map<String, Object>> {

    // .... 删去了若干代码

    @Override
    public Map<String, Object> handle(ResultSet rs) throws SQLException {
        return rs.next() ? this.convert.toMap(rs) : null;
    }

}

这里，this.convert默认值也是new BasicRowProcessor()。看一下toMap(...)函数：

// 类org.apache.commons.dbutils.BasicRowProcessor下的toMap(...)函数
@Override
public Map<String, Object> toMap(ResultSet rs) throws SQLException {
    Map<String, Object> result = new CaseInsensitiveHashMap();
    ResultSetMetaData rsmd = rs.getMetaData();
    int cols = rsmd.getColumnCount();

    for (int i = 1; i <= cols; i++) {
        String columnName = rsmd.getColumnLabel(i);
        if (null == columnName || 0 == columnName.length()) {
          columnName = rsmd.getColumnName(i);
        }
        result.put(columnName, rs.getObject(i));
    }

    return result;
}

CaseInsensitiveHashMap定义在类BasicRowProcessor内部：

private static class CaseInsensitiveHashMap extends LinkedHashMap<String, Object> {

    private final Map<String, String> lowerCaseMap = new HashMap<String, String>();

    private static final long serialVersionUID = -2848100435296897392L;

    @Override
    public boolean containsKey(Object key) {
        Object realKey = lowerCaseMap.get(key.toString().toLowerCase(Locale.ENGLISH));
        return super.containsKey(realKey);
    }

    @Override
    public Object get(Object key) {
        Object realKey = lowerCaseMap.get(key.toString().toLowerCase(Locale.ENGLISH));
        return super.get(realKey);
    }

    @Override
    public Object put(String key, Object value) {
        Object oldKey = lowerCaseMap.put(key.toLowerCase(Locale.ENGLISH), key);
        Object oldValue = super.remove(oldKey);
        super.put(key, value);
        return oldValue;
    }

    @Override
    public void putAll(Map<? extends String, ?> m) {
        for (Map.Entry<? extends String, ?> entry : m.entrySet()) {
            String key = entry.getKey();
            Object value = entry.getValue();
            this.put(key, value);
        }
    }


    @Override
    public Object remove(Object key) {
        Object realKey = lowerCaseMap.remove(key.toString().toLowerCase(Locale.ENGLISH));
        return super.remove(realKey);
    }
}

使用DBUtils获取多条数据

编写代码：

// 类hellodbutils.DBUtilsSelect2
package hellodbutils;

import java.util.Map;
import java.util.List;
import java.sql.*;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

public class DBUtilsSelect2 {
    public static void main(String args[]) {
        Connection conn;
        QueryRunner queryRunner = new QueryRunner();
        try {
            Class.forName(DBConf.JDBC_DRIVER);
            conn = DriverManager.getConnection(DBConf.DB_URL, DBConf.USER, DBConf.PASS);

            String sql = "SELECT id, name, age FROM user";
            List<Map<String, Object>> result = queryRunner.query(conn, sql, new MapListHandler());
            System.out.println(result);

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }   
}

运行结果：

[{id=1, name=樂天, age=18}, {id=2, name=樂天天, age=19}]

源码分析：

先看一下类org.apache.commons.dbutils.handlers.MapListHandler：

// 类 org.apache.commons.dbutils.handlers.MapListHandler
public class MapListHandler extends AbstractListHandler<Map<String, Object>> {

    // .... 删去了若干代码

    public MapListHandler() {
        this(ArrayHandler.ROW_PROCESSOR);
    }

    // .... 删去了若干代码
    @Override
    protected Map<String, Object> handleRow(ResultSet rs) throws SQLException {
        return this.convert.toMap(rs);
    }

}

handle(...)方法在MapListHandler的父类org.apache.commons.dbutils.handlers.AbstractListHandler中：

public abstract class AbstractListHandler<T> implements ResultSetHandler<List<T>> {
    @Override
    public List<T> handle(ResultSet rs) throws SQLException {
        List<T> rows = new ArrayList<T>();
        while (rs.next()) {
            rows.add(this.handleRow(rs));
        }
        return rows;
    }

    protected abstract T handleRow(ResultSet rs) throws SQLException;
}

使用handleRow(...)处理ResultSet rs中当前指向的数据，并转换为Map；而handle()方法将获得ResultSet rs所能找到的所有数据对应的Map构成的ArrayList。