ApacheCommonsDbutils 学习笔记

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

1 <dependency>
2     <groupId>commons-dbutils</groupId>
3     <artifactId>commons-dbutils</artifactId>
4     <version>1.6</version>
5 </dependency>

maven

3.创建测试表数据

 1 SET FOREIGN_KEY_CHECKS=0;
 2 
 3 -- ----------------------------
 4 -- Table structure for person
 5 -- ----------------------------
 6 DROP TABLE IF EXISTS `person`;
 7 CREATE TABLE `person` (
 8   `id` bigint(20) NOT NULL AUTO_INCREMENT,
 9   `name` varchar(24) DEFAULT NULL,
10   `age` int(11) DEFAULT NULL,
11   `address` varchar(120) DEFAULT NULL,
12   PRIMARY KEY (`id`)
13 ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=gbk;
14 
15 -- ----------------------------
16 -- Records of person
17 -- ----------------------------
18 INSERT INTO `person` VALUES ('1', 'darkdog', '99', 'beijing');

SQL

4.开始测试

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

 1 class CommonDbutils {
 2 
 3     public static DataSource getDataSource() {
 4         MysqlDataSource ds = new MysqlDataSource();
 5         ds.setURL("jdbc:mysql://localhost:3306/test?user=root&password=root");
 6         return ds;
 7     }
 8 
 9     public static Connection getConnection() throws SQLException {
10         MysqlDataSource ds = (MysqlDataSource) getDataSource();
11         Connection con = ds.getConnection();
12         return con;
13     }
14 
15 }

CommonDbutils

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

 1 /**
 2  * 使用ResultSetHandler存储方式查询
 3  * 
 4  * @param id
 5  * @return
 6  * @throws SQLException
 7  */
 8 public Object[] getResultSet(Integer id) throws SQLException {
 9     ResultSetHandler<Object[]> rsh = new ResultSetHandler<Object[]>() {
10         public Object[] handle(ResultSet rs) throws SQLException {
11             if (!rs.next()) {
12                 return null;
13             }
14             ResultSetMetaData meta = rs.getMetaData();
15             int cols = meta.getColumnCount();
16             Object[] result = new Object[cols];
17 
18             for (int i = 0; i < cols; i++) {
19                 result[i] = rs.getObject(i + 1);
20             }
21             return result;
22         }
23     };
24 
25     /*
26     QueryRunner qr = new QueryRunner();
27     return qr.query(CommonDbutils.getConnection(),
28             "SELECT * FROM Person WHERE id=?", rsh, id);
29     */
30 
31     QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
32     return qr.query("SELECT * FROM Person WHERE id=?", rsh, id);
33 }

使用ResultSetHandler存储方式查询

 1 /**
 2  * 使用BeanHandler存储方式查询
 3  * 
 4  * @param id
 5  * @return
 6  * @throws SQLException
 7  * @throws IllegalArgumentException
 8  * @throws IllegalAccessException
 9  */
10 public Object getBean(Integer id) throws SQLException,
11         IllegalArgumentException, IllegalAccessException {
12     ResultSetHandler<Person> rsh = new BeanHandler<Person>(Person.class);
13     QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
14     return qr.query("select id, name, age, address from Person where id=?",
15             rsh, id);
16 }

使用BeanHandler存储方式查询

 1 /**
 2  * 使用BeanMapHandler存储方式查询
 3  * 
 4  * @param id
 5  * @return
 6  * @throws SQLException
 7  */
 8 public Map<Object, Person> getBeanMap(Integer id) throws SQLException {
 9     ResultSetHandler<Map<Object, Person>> rsh = new BeanMapHandler<Object, Person>(
10             Person.class);
11     QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
12     return qr.query("select id, name, age, address from Person", rsh);
13 }

使用BeanMapHandler存储方式查询

 1 /**
 2  * 使用BeanListHandler存储方式查询
 3  * 
 4  * @param params
 5  * @return
 6  * @throws SQLException
 7  */
 8 public List<Person> getBeanList() throws SQLException {
 9     ResultSetHandler<List<Person>> rsh = new BeanListHandler<Person>(
10             Person.class);
11     QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
12     String sql = "SELECT id, name, age, address FROM person";
13 
14     return qr.query(sql, rsh);
15 }

使用BeanListHandler存储方式查询

 1 /**
 2  * 使用ScalarHandler存储方式查询
 3  * 
 4  * @param id
 5  * @return
 6  * @throws SQLException
 7  */
 8 public Object getObject(Integer id) throws SQLException {
 9     ResultSetHandler<Object> rsh = new ScalarHandler<Object>();
10     QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
11     return qr.query("select name from Person where id=?", rsh, id);
12 }

使用ScalarHandler存储方式查询

 1 /**
 2  * 使用ArrayHandler存储方式查询
 3  * 
 4  * @param id
 5  * @return
 6  * @throws SQLException
 7  */
 8 public Object[] getArray(Integer id) throws SQLException {
 9     ResultSetHandler<Object[]> rsh = new ArrayHandler();
10     QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
11     return qr.query("SELECT * FROM Person WHERE id=?", rsh, id);
12 }

使用ArrayHandler存储方式查询

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

使用ArrayListHandler存储方式查询

 1 /**
 2  * 使用ColumnListHandler存储方式查询
 3  * 
 4  * @param index
 5  * @return
 6  * @throws SQLException
 7  */
 8 public List<Object> getColumnList(int index) throws SQLException {
 9     ResultSetHandler<List<Object>> rsh = new ColumnListHandler<Object>(
10             index);
11     QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
12     return qr.query("SELECT * FROM Person", rsh);
13 }

使用ColumnListHandler存储方式查询

 1 /**
 2  * 使用MapHandler存储方式查询
 3  * 
 4  * @param id
 5  * @return
 6  * @throws SQLException
 7  */
 8 public Map<String, Object> getMap(Integer id) throws SQLException {
 9     ResultSetHandler<Map<String, Object>> rsh = new MapHandler();
10     QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
11     return qr.query("SELECT * FROM Person WHERE id=?", rsh, id);
12 }

使用MapHandler存储方式查询

 1 /**
 2  * 使用MapListHandler存储方式查询
 3  * 
 4  * @return
 5  * @throws SQLException
 6  */
 7 public List<Map<String, Object>> getMapList() throws SQLException {
 8     ResultSetHandler<List<Map<String, Object>>> rsh = new MapListHandler();
 9     QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
10     return qr.query("SELECT * FROM Person", rsh);
11 }

使用MapListHandler存储方式查询

 1 /**
 2  * 使用KeyedHandler存储方式查询
 3  * 
 4  * @return
 5  * @throws SQLException
 6  */
 7 public Map<Object, Map<String, Object>> getKeyed() throws SQLException {
 8     ResultSetHandler<Map<Object, Map<String, Object>>> rsh = new KeyedHandler<Object>();
 9     QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
10     return qr.query("SELECT * FROM Person", rsh);
11 }

使用KeyedHandler存储方式查询

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

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

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

 1 /**
 2  * insert
 3  * @param p
 4  * @return
 5  * @throws SQLException
 6  */
 7 public int insert(Person p) throws SQLException {
 8     QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
 9     String sql = "insert into Person (id, name, age, address) values (?, ?, ?, ?)";
10     return qr.update(sql, p.getId(), p.getName(), p.getAge(), p.getAddress());
11 }
12 
13 /**
14  * update
15  * @param p
16  * @return
17  * @throws SQLException
18  */
19 public int update(Person p) throws SQLException {
20     QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
21     String sql = "update Person set name = ?, age = ?, address = ? where id = ?";
22     return qr.update(sql, p.getName(), p.getAge(), p.getAddress(), p.getId());
23 }
24 
25 /**
26  * delete
27  * @param id
28  * @return
29  * @throws SQLException
30  */
31 public int delete(Integer id) throws SQLException {
32     QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());
33     String sql = "delete from Person where id = ?";
34     return qr.update(sql, id);
35 }

INSERT, UPDATE, DELETE

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

DbUtils.closeQuietly(CommonDbutils.getConnection());