DBUtils簡明教程

本文以Insert和Select操作為例子，介紹apache DBUtils的使用方法和實現(xiàn)機制。

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

準(zhǔn)備工作

建立表格：

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。

首先創(chuàng)建DBConf.java，內(nèi)容如下：

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();
        }


    }
}

運行結(jié)果如下：

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

MySQL命令行客戶端查詢結(jié)果：

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

使用DBUtils插入數(shù)據(jù)

首先清空user表中的數(shù)據(jù)：

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("數(shù)組長度：" + objectArr.length + "；第0個元素的值：" +objectArr[0]);
            objectArr = queryRunner.insert(conn, sql, arrayHandler, "樂天天", 19);
            System.out.println("數(shù)組長度：" + objectArr.length + "；第0個元素的值：" +objectArr[0]);

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

    }
}

運行結(jié)果：

數(shù)組長度：1；第0個元素的值：1
數(shù)組長度：1；第0個元素的值：2

MySQL命令行客戶端查詢結(jié)果：

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

源碼分析：

上面的句子調(diào)用了類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);
}

最終調(diào)用的是下面的重載方法：

// 類`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的主要內(nèi)容是：

// 類`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(...)函數(shù)有一參數(shù)是ResultSetHandler<T> rsh，ResultSetHandler用來處理sql查詢后得到的結(jié)果。在類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(...)函數(shù)詳細如下：

// 類org.apache.commons.dbutils.BasicRowProcessor下的toArray(...)函數(shù)
@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插入數(shù)據(jù)的思路也就知道了。

使用DBUtils獲取一條數(shù)據(jù)

編寫代碼：

// 類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();
        }

    }
}

運行結(jié)果：

{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()?？匆幌?code style="margin-right: 0px; margin-left: 0px; padding-top: 3px; padding-bottom: 3px; border: 1px solid rgb(225, 225, 232); outline: 0px; font-size: 12px; vertical-align: baseline; font-family: Monaco, Menlo, 'Courier New', monospace; color: rgb(221, 17, 68); word-wrap: break-word; background-color: rgb(247, 247, 249);">toMap(...)函數(shù)：

// 類org.apache.commons.dbutils.BasicRowProcessor下的toMap(...)函數(shù)
@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內(nèi)部：

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獲取多條數(shù)據(jù)

編寫代碼：

// 類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();
        }
    }   
}

運行結(jié)果：

[{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中當(dāng)前指向的數(shù)據(jù)，并轉(zhuǎn)換為Map；而handle()方法將獲得ResultSet rs所能找到的所有數(shù)據(jù)對應(yīng)的Map構(gòu)成的ArrayList。