转自：http://www.cnblogs.com/ysw-go/
使用DBUtils提供的功能需要使用commons-dbutils-1.6.jar这个JAR包，在Apache官网可以下载到
使用DBUtils进行更新操
测试QueryRunner类的update方法 ,该方法可用于insert,update,delete操作
具体代码实现
1 @Test
2 public void testQuertRunnerUpdate() {
3 // 1.创建QueryRunner的实现类
4 String sql = "delete from customers" + " where id in (?,?)";
5 Connection connection = null;
6 try {
7 connection = JDBCTools.getConnection();
8 queryRunner.update(connection, sql, 3, 4);
9
10 } catch (Exception e) {
11 e.printStackTrace();
12 } finally {
13 JDBCTools.release(null, null, connection);
14 }
15 }
我们可以查看一下update方法的源码，可以看到是调用的是重载形式的update：
conn:数据库连接
sql：SQL语句
params:填充占位符的参数
1 public int update(Connection conn, String sql, Object... params) throws SQLException {
2 return update(conn, false, sql, params);
3 }
看具体实现代码：
1 private int update(Connection conn, boolean closeConn, String sql, Object... params) throws SQLException {
2 if (conn == null) {
3 throw new SQLException("Null connection");
4 }
5
6 if (sql == null) {
7 if (closeConn) {
8 close(conn);
9 }
10 throw new SQLException("Null SQL statement");
11 }
12 //封装使用的是PreparedStatement
13 PreparedStatement stmt = null;
14 int rows = 0;
15
16 try {
17 stmt = this.prepareStatement(conn, sql);
//填充占位符的操作
18 this.fillStatement(stmt, params);
//执行更新操作
19 rows = stmt.executeUpdate();
20
21 } catch (SQLException e) {
22 this.rethrow(e, sql, params);
23
24 } finally {
25 close(stmt);
26 if (closeConn) {
27 close(conn);
28 }
29 }
30
31 return rows;
32 }
可以看到QueryRunner中封装的都是我们所熟悉的基本操作，测试我们的testQuertRunnerUpdate方法，可以发现对应id为3,4的记录已经被删除
测试QueryRunner的查询方法query
1 // 因为是线程安全的,所以queryRunner可以放到这个位置
2 QueryRunner queryRunner = new QueryRunner();
3
4 class MyResultSetHandler implements ResultSetHandler {
5
6 @Override
7 public Object handle(ResultSet resultSet) throws SQLException {
8 List<Customer> customers = new ArrayList<Customer>();
9 while (resultSet.next()) {
10 int id = resultSet.getInt(1);
11 String name = resultSet.getString(2);
12 String email = resultSet.getString(3);
13 Date birth = resultSet.getDate(4);
14 Customer customer = new Customer(id, name, email, birth);
15 customers.add(customer);
16 }
17 return customers;
18 }
19
20 }
21
22 @Test
23 public void testQuery() {
24 QueryRunner queryRunner = new QueryRunner();
25 Connection connection = null;
26 try {
27 connection = JDBCTools.getConnection();
28 String sql = "select id,name,email,birth" + " from customers";
29 Object obj = queryRunner.query(connection, sql,
30 new MyResultSetHandler());
31 System.out.println(obj);
32 } catch (Exception e) {
33 e.printStackTrace();
34 } finally {
35 JDBCTools.release(null, null, connection);
36 }
37 }
运行方法可以看到我们要查询的结果：
[Customer [id=1, name=ATGUIGU, email=simale@163.com, birth=2016-05-08], Customer [id=2, name=ABCDE, email=abcd@guigu.com, birth=2016-05-08], Customer [id=5, name=dsv, email=474405914@qq.com, birth=2016-05-10]]
我们查看一下query方法的源码
1 private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
2 throws SQLException {
3 if (conn == null) {
4 throw new SQLException("Null connection");
5 }
6
7 if (sql == null) {
8 if (closeConn) {
9 close(conn);
10 }
11 throw new SQLException("Null SQL statement");
12 }
13
14 if (rsh == null) {
15 if (closeConn) {
16 close(conn);
17 }
18 throw new SQLException("Null ResultSetHandler");
19 }
20
21 PreparedStatement stmt = null;
22 ResultSet rs = null;
23 T result = null;
24
25 try {
26 stmt = this.prepareStatement(conn, sql);
27 this.fillStatement(stmt, params);
28 rs = this.wrap(stmt.executeQuery());
29 result = rsh.handle(rs);
30
31 } catch (SQLException e) {
32 this.rethrow(e, sql, params);
33
34 } finally {
35 try {
36 close(rs);
37 } finally {
38 close(stmt);
39 if (closeConn) {
40 close(conn);
41 }
42 }
43 }
44
45 return result;
46 }
其中最关键的是这四个语句：
stmt = this.prepareStatement(conn, sql);
this.fillStatement(stmt, params);
rs = this.wrap(stmt.executeQuery());
result = rsh.handle(rs);
测试DBUtils工具类 QueryRunner:
* query(Connection conn,String sql,ResultSetHandler rsh){
* 这三个步骤就是为了得到结果集
* stmt = this.prepareStatement(conn, sql);
* this.fillStatement(stmt, params);
* rs = this.wrap(stmt.executeQuery());
* 调用传入的ResultSetHandler对象的handle方法，并且把前面得到的ResultSet对象作为参数传入
* result =rsh.handle(rs);
* 把result作为结果返回
* return result
* }
测试BeanHandler的用法
BeanHandler：把结果集的第一条记录转为创建BeanHandler 对象时传入的Class参数对应的对象。
具体测试代码：
1 public void testBeanHandler() {
2 Connection connection = null;
3 try {
4 connection = JDBCTools.getConnection();
5 String sql = "select id,name,email,birth"
6 + " from customers where id=?";
7 Customer customer = queryRunner.query(connection, sql,
8 new BeanHandler(Customer.class), 5);
9 System.out.println(customer);
10 } catch (Exception e) {
11 e.printStackTrace();
12 } finally {
13 JDBCTools.release(null, null, connection);
14 }
15 }
查看源码可以看到query函数中用到的handler函数实现的功能是这样的
1 @Override
2 public T handle(ResultSet rs) throws SQLException {
3 return rs.next() ? this.convert.toBean(rs, this.type) : null;
4 }
实现的功能是把结果集的第一条记录转为创建BeanHandler 对象时传入的Class参数对应的对象。
其中的toBean方法的实现方式：(多看源码吧，有点乱了)
public <T> T toBean(ResultSet rs, Class<T> type) throws SQLException {
PropertyDescriptor[] props = this.propertyDescriptors(type);
ResultSetMetaData rsmd = rs.getMetaData();
int[] columnToProperty = this.mapColumnsToProperties(rsmd, props);
return this.createBean(rs, type, props, columnToProperty);
}
测试BeanListHandler的用法
和前面那个差不多，只不过将查询的结果放到了一个List容器中了
1 @Test
2 public void testBeanListHandler() {
3 Connection connection = null;
4 try {
5 connection = JDBCTools.getConnection();
6 String sql = "select id,name,email,birth" + " from customers";
7 List<Customer> customers = queryRunner.query(connection, sql,
8 new BeanListHandler(Customer.class));
9 System.out.println(customers);
10 } catch (Exception e) {
11 e.printStackTrace();
12 } finally {
13 JDBCTools.release(null, null, connection);
14 }
15 }
运行结果：
[Customer [id=1, name=ATGUIGU, email=simale@163.com, birth=2016-05-08], Customer [id=2, name=ABCDE, email=abcd@guigu.com, birth=2016-05-08], Customer [id=5, name=dsv, email=474405914@qq.com, birth=2016-05-10]]
测试MapHandler的用法
MapHandler：返回SQL对应的第一条记录对应的Map对象 键：SQL查询的列名(不是列别名) 值：列的值
1 @Test
2 public void testMapHandler() {
3 Connection connection = null;
4 try {
5 connection = JDBCTools.getConnection();
6 String sql = "select id,name,email,birth" + " from customers";
7 Map<String, Object> result = queryRunner.query(connection, sql,
8 new MapHandler());
9 System.out.println(result);
10 } catch (Exception e) {
11 e.printStackTrace();
12 } finally {
13 JDBCTools.release(null, null, connection);
14 }
15 }
运行结果：
{id=1, name=ATGUIGU, email=simale@163.com, birth=2016-05-08}
测试MapListHandler的用法
MapListHandler:将结果集转为一个Map的List, Map对应查询的一条记录的Map对象
键：SQL查询的列名(不是列别名)
值：列的值 而MapListHandler：返回的多条记录对应的Map集合
1 @Test
2 public void testMapListHandler() {
3 Connection connection = null;
4 try {
5 connection = JDBCTools.getConnection();
6 String sql = "select id,name,email,birth" + " from customers";
7 List<Map<String, Object>> result = queryRunner.query(connection,
8 sql, new MapListHandler());
9 System.out.println(result);
10 } catch (Exception e) {
11 e.printStackTrace();
12 } finally {
13 JDBCTools.release(null, null, connection);
14 }
15 }
运行结果：
[{id=1, name=ATGUIGU, email=simale@163.com, birth=2016-05-08}, {id=2, name=ABCDE, email=abcd@guigu.com, birth=2016-05-08}, {id=5, name=dsv, email=474405914@qq.com, birth=2016-05-10}]
测试ScalarHandler的用法
ScalarHandle：把结果集转为一个数值(可以是任意基本数据类型和字符串，Date等)返回
1 @Test
2 public void testScalarHandler() {
3 Connection connection = null;
4 try {
5 connection = JDBCTools.getConnection();
6 String sql = "select count(id) from customers";
7 Object result = queryRunner.query(connection, sql,
8 new ScalarHandler());
9 System.out.println(result);
10 } catch (Exception e) {
11 e.printStackTrace();
12 } finally {
13 JDBCTools.release(null, null, connection);
14 }
15 }
运行结果：3
至此为止我们学习了JDBC中常用的一些知识点，下面我们就使用DBUtils编写通用的DAO(DAO其实就是封装了操作数据库数据的一些方法的接口)
使用DButils编写通用的DAO
第一步：先写一个访问数据的DAO接口
1 package com.atguigu.jdbc;
2
3 import java.sql.Connection;
4 import java.util.List;
5
6 /**
7 * 访问数据的DAO接口
8 * 里面定义好访问数据表的各种方法
9 * @author Administrator
10 * @param T:DAO处理的实体类的类型
11 */
12 public interface DAO {
13 /**
14 * 批量处理的方法
15 * @param connection
16 * @param sql
17 * @param args:填充占位符的Object[]类型的可变参数
18 */
19 void batch(Connection connection,
20 String sql,Object[] ...args);
21 /**
22 * 返回一个具体的一个值，例如总人数，平均工资
23 * 某一个认得email
24 * @param connection
25 * @param sql
26 * @param args
27 * @return
28 */
29 <E> E getForValue(Connection connection,
30 String sql, Object ...args);
31
32 /**
33 * 返回T的一个集合
34 * @param <T>
35 * @param connection
36 * @param sql
37 * @param args
38 * @return
39 */
40 <T> List<T> getForList(Connection connection,
41 String sql,Object ...args);
42 /**
43 * 返回一个T的对象
44 * @param <T>
45 * @param connection
46 * @param sql
47 * @param args
48 * @return
49 * @throws Exception
50 */
51 <T> T get(Connection connection,String sql,Object ...args) throws Exception;
52 /**
53 *
54 * @param connection:数据库连接
55 * @param sql：SQL语句
56 * @param args：填充占位符的可变参数
57 */
58 void update(Connection connection,String sql,Object ...args);
59 }
第二步：定义实现该接口的实例类JdbcDAOImpl
1 package com.atguigu.jdbc;
2
3 import java.sql.Connection;
4 import java.util.List;
5
6 import org.apache.commons.dbutils.QueryRunner;
7 import org.apache.commons.dbutils.handlers.BeanHandler;
8
9 /**
10 * 使用QueryRunner提供其具体的实现
11 * @author Administrator
12 *
13 * @param <T>:子类需传入的泛型类型
14 */
15 public class JdbcDAOImpl<T> implements DAO {
16 private QueryRunner queryRunner=null;
17 private Class<T> type;
18 public JdbcDAOImpl() {
19 queryRunner=new QueryRunner();
20 type= (Class<T>) ReflectionUtils.getGenericSuperClass(getClass(),0);
21 }
22 @Override
23 public void batch(Connection connection, String sql, Object[]... args) {
24 }
25
26 @Override
27 public <E> E getForValue(Connection connection, String sql, Object... args) {
28
29 return null;
30 }
31
32 @Override
33 public List<T> getForList(Connection connection, String sql,
34 Object... args) {
35
36 return null;
37 }
38
39 @Override
40 public T get(Connection connection, String sql, Object... args) throws Exception {
41
42 return queryRunner.query(connection, sql,
43 new BeanHandler<>(type),args);
44 }
45
46 @Override
47 public void update(Connection connection, String sql, Object... args) {
48 }
49
50
51 }
第三步：定义一个具体操作的类继承自JdbcDAOImpl
1 package com.atguigu.jdbc;
2
3 public class CustomerDAO extends JdbcDAOImpl<Customer>{
4
5 }
第四步：我们测试一下我们已经实现的get方法，其他的方法实现过程类似
1 package com.atguigu.jdbc;
2
3 import static org.junit.Assert.*;
4
5 import java.sql.Connection;
6
7 import org.junit.Test;
8
9 public class CustomerDaoTest {
10 CustomerDAO customerDAO=new CustomerDAO();
11
12 @Test
13 public void testBatch() {
14 fail("Not yet implemented");
15 }
16
17 @Test
18 public void testGetForValue() {
19 fail("Not yet implemented");
20 }
21
22 @Test
23 public void testGetForList() {
24 fail("Not yet implemented");
25 }
26
27 @Test
28 public void testGet() {
29 Connection connection=null;
30 try {
31 connection=JDBCTools.getConnection();
32 String sql = "select id,name,email,birth"
33 + " from customers where id=?";
34 Customer customer=customerDAO.get(connection, sql, 5);
35 System.out.println(customer);
36 } catch (Exception e) {
37 e.printStackTrace();
38 }finally{
39 JDBCTools.release(null, null, connection);
40 }
41 }
42
43 @Test
44 public void testUpdate() {
45 fail("Not yet implemented");
46 }
47
48 }
运行一下：
Customer [id=5, name=dsv, email=474405914@qq.com, birth=2016-05-10]
可以通过我们的get方法得到我们想要的数据信息