Apache-Commons-Dbutils 学习笔记

1.项目结构

org.apache.commons.dbutils 包

|__DbUtils：jdbc辅助方法集合，线程安全

|__ResultSetHandler<T>:转换ResultSets对象 为其他Object 接口,下面为它的一些具体实现类

    |__org.apache.commons.dbutils.handlers包
            |__AbstractKeyedHandler<K,V>：把ResultSet里面数据转换为用Map存储，抽象类

                  |__BeanMapHandler：map里面value保存的是一个bean类，根据相应key获取bean数据

                 |__KeyedHandler;ResultSetHandler h = new KeyedHandler("id"); 根据类中保存的key（id），获取相应value

            |__AbstractListHandler<T>：把ResultSet里面数据转换为用List存储，抽象类

                  |__ArrayListHandler：list为arrayList

                 |__ColumnListHandler：

|__MapListHandler：

            |__ArrayHandler：把ResultSet里面数据转换为用Object[]存储

            |__BeanHandler<T>：把ResultSet里面数据转换为用javaBean存储

            |__BeanListHandler<T>：把ResultSet里面数据转换为用List存储，list里面对象为javaBean

            |__MapHandler：把ResultSet里面数据转换为用Map存储

            |__ScalarHandler：

|__AbstractQueryRunner

     |__QueryRunner  ：基本查询类

     |__AsyncQueryRunner

|__RowProcessor

|__BasicRowProcessor

|__BeanProcessor

|__ProxyFactory：代理类

|__QueryLoader

|__ResultSetIterator

org.apache.commons.dbutils.wrappers

|__SqlNullCheckedResultSet

|__StringTrimmedResultSet

2.配置maven

<dependency>
    <groupId>commons-dbutils</groupId>
    <artifactId>commons-dbutils</artifactId>
    <version>1.6</version>
</dependency>

3.创建测试表数据

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for person
-- ----------------------------
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(120) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of person
-- ----------------------------
INSERT INTO `person` VALUES ('1', 'darkdog', '99', 'beijing');

4.开始测试

首先封装一个工具类用于获取数据库连接

class CommonDbutils {

    public static DataSource getDataSource() {
        MysqlDataSource ds = new MysqlDataSource();
        ds.setURL("jdbc:mysql://localhost:3306/test?user=root&password=root");
        return ds;
    }

    public static Connection getConnection() throws SQLException {
        MysqlDataSource ds = (MysqlDataSource) getDataSource();
        Connection con = ds.getConnection();
        return con;
    }

}

查询时，工具包提供了几种继承自ResultSetHandler， 用于处理结果集的Handler

/**
 * 使用ResultSetHandler存储方式查询
 * 
 * @param id
 * @return
 * @throws SQLException
 */
public Object[] getResultSet(Integer id) throws SQLException {
    ResultSetHandler<Object[]> rsh = new ResultSetHandler<Object[]>() {
        public Object[] handle(ResultSet rs) throws SQLException {
            if (!rs.next()) {
                return null;
            }
            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;
        }
    };

    /*
    QueryRunner qr = new QueryRunner();
    return qr.query(CommonDbutils.getConnection(),
            "SELECT * FROM Person WHERE id=?", rsh, id);
    */

    QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
    return qr.query("SELECT * FROM Person WHERE id=?", rsh, id);
}

/**
 * 使用BeanHandler存储方式查询
 * 
 * @param id
 * @return
 * @throws SQLException
 * @throws IllegalArgumentException
 * @throws IllegalAccessException
 */
public Object getBean(Integer id) throws SQLException,
        IllegalArgumentException, IllegalAccessException {
    ResultSetHandler<Person> rsh = new BeanHandler<Person>(Person.class);
    QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
    return qr.query("select id, name, age, address from Person where id=?",
            rsh, id);
}

/**
 * 使用BeanMapHandler存储方式查询
 * 
 * @param id
 * @return
 * @throws SQLException
 */
public Map<Object, Person> getBeanMap(Integer id) throws SQLException {
    ResultSetHandler<Map<Object, Person>> rsh = new BeanMapHandler<Object, Person>(
            Person.class);
    QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
    return qr.query("select id, name, age, address from Person", rsh);
}

/**
 * 使用BeanListHandler存储方式查询
 * 
 * @param params
 * @return
 * @throws SQLException
 */
public List<Person> getBeanList() throws SQLException {
    ResultSetHandler<List<Person>> rsh = new BeanListHandler<Person>(
            Person.class);
    QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
    String sql = "SELECT id, name, age, address FROM person";

    return qr.query(sql, rsh);
}

/**
 * 使用ScalarHandler存储方式查询
 * 
 * @param id
 * @return
 * @throws SQLException
 */
public Object getObject(Integer id) throws SQLException {
    ResultSetHandler<Object> rsh = new ScalarHandler<Object>();
    QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
    return qr.query("select name from Person where id=?", rsh, id);
}

/**
 * 使用ArrayHandler存储方式查询
 * 
 * @param id
 * @return
 * @throws SQLException
 */
public Object[] getArray(Integer id) throws SQLException {
    ResultSetHandler<Object[]> rsh = new ArrayHandler();
    QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
    return qr.query("SELECT * FROM Person WHERE id=?", rsh, id);
}

/**
 * 使用ArrayListHandler存储方式查询
 * 
 * @param id
 * @return
 * @throws SQLException
 */
public List<Object[]> getArrayList(Integer id) throws SQLException {
    ResultSetHandler<List<Object[]>> rsh = new ArrayListHandler();
    QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
    return qr.query("SELECT * FROM Person WHERE id=?", rsh, id);
}

/**
 * 使用ColumnListHandler存储方式查询
 * 
 * @param index
 * @return
 * @throws SQLException
 */
public List<Object> getColumnList(int index) throws SQLException {
    ResultSetHandler<List<Object>> rsh = new ColumnListHandler<Object>(
            index);
    QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
    return qr.query("SELECT * FROM Person", rsh);
}

/**
 * 使用MapHandler存储方式查询
 * 
 * @param id
 * @return
 * @throws SQLException
 */
public Map<String, Object> getMap(Integer id) throws SQLException {
    ResultSetHandler<Map<String, Object>> rsh = new MapHandler();
    QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
    return qr.query("SELECT * FROM Person WHERE id=?", rsh, id);
}

/**
 * 使用MapListHandler存储方式查询
 * 
 * @return
 * @throws SQLException
 */
public List<Map<String, Object>> getMapList() throws SQLException {
    ResultSetHandler<List<Map<String, Object>>> rsh = new MapListHandler();
    QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
    return qr.query("SELECT * FROM Person", rsh);
}

/**
 * 使用KeyedHandler存储方式查询
 * 
 * @return
 * @throws SQLException
 */
public Map<Object, Map<String, Object>> getKeyed() throws SQLException {
    ResultSetHandler<Map<Object, Map<String, Object>>> rsh = new KeyedHandler<Object>();
    QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
    return qr.query("SELECT * FROM Person", rsh);
}

以上是所有查询相关, 除了第一个ResultSetHandler其余几个几乎不需要读API文档就可以从返回值类型上看出区别

由于ResultSetHandler是一个接口所以必须实现handle方法来处理每个返回对象。

以下 insert, update, delete 用的都是QueryRunner中的update方法

/**
 * insert
 * @param p
 * @return
 * @throws SQLException
 */
public int insert(Person p) throws SQLException {
    QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
    String sql = "insert into Person (id, name, age, address) values (?, ?, ?, ?)";
    return qr.update(sql, p.getId(), p.getName(), p.getAge(), p.getAddress());
}

/**
 * update
 * @param p
 * @return
 * @throws SQLException
 */
public int update(Person p) throws SQLException {
    QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
    String sql = "update Person set name = ?, age = ?, address = ? where id = ?";
    return qr.update(sql, p.getName(), p.getAge(), p.getAddress(), p.getId());
}

/**
 * delete
 * @param id
 * @return
 * @throws SQLException
 */
public int delete(Integer id) throws SQLException {
    QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
    String sql = "delete from Person where id = ?";
    return qr.update(sql, id);
}

最后关掉数据库连接, 使用DBUtils类中提供了一些简单封装的静态方法

DbUtils.closeQuietly(CommonDbutils.getConnection());