DBUtils 基本介绍
commons-dbutils是Apache组织提供的一个开源JDBC工具类库，它是对JDBC的简单封装。简化了jdbc编码的工作量
API介绍
org.apache.commons.dbutils.QueryRunner org.apache.commons.dbutils.ResultSetHandler 工具类：org.apache.commons.dbutils.DbUtils
代码
update()方法可用于Insert、update、delete
代码语言：javascript复制public class JDBCTest06 {
// 删除delete
public JDBCTest06() throws SQLException {
// 1. 创建QueryRunner 的实现类
QueryRunner queryRunner = new QueryRunner();
// 2. 使用其update 方法
String sql = "DELETE FROM customers " "WHERE id IN(?,>)";
DataSource dataSource = new ComboPooledDataSource("helloc3p0");
// 3. 在连接池获取连接
Connection connection = dataSource.getConnection();
queryRunner.update(connection,sql,12,13); // update方法可用于删除、更新、添加
connection.close();
}
}通过实现ResultSetHandler接口实现查询
创建 ResultSetHandler接口 的实现类，实现handle方法，queryRunner.query()的返回值取决于handle的返回值
代码语言：javascript复制public class DBUtilsTest {
QueryRunner queryRunner = new QueryRunner();
// 1. 创建 ResultSetHandler接口 的实现类，实现handle方法，queryRunner.query()的返回值取决于handle的返回值
class MyResultSetHandler implements ResultSetHandler{
@Override
public Object handle(ResultSet rs) throws SQLException {
List<Customer> customers = new ArrayList<>();
while (rs.next()){
Integer id = rs.getInt(1);
String name = rs.getString(2);
String email = rs.getString(3);
Date birth = rs.getDate(4);
Customer customer = new Customer(id,name,email,birth);
customers.add(customer);
}
return customers;
}
}
public void testQuery() throws SQLException {
Connection connection = null;
String sql = "select id,name,email,birth" "from customers";
Object object = queryRunner.query(connection, sql, new MyResultSetHandler());
System.out.println(object);
connection.close();
}
}queryRunner.query()源码分析
代码语言：javascript复制//1. QueryRunner 类的query()方法
public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
return this.<T>query(conn, false, sql, rsh, params);// 返回值是调用当前的类query的重载方法
}
// 2.当前的类query的重载方法
private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
throws SQLException {
PreparedStatement stmt = null;
ResultSet rs = null;
T result = null;
try {
stmt = this.prepareStatement(conn, sql);
this.fillStatement(stmt, params);
rs = this.wrap(stmt.executeQuery()); // wrap() 返回的是ResultSet
result = rsh.handle(rs); // handle()是ResultSetHandler接口定义的方法
} catch (SQLException e) {
this.rethrow(e, sql, params);
} finally {
try {
close(rs);
} finally {
close(stmt);
if (closeConn) {
close(conn);
}
}
}
return result;
}通过BeanHandler类实现查询
把结果集的第一条记录转为创建BeanHandler对象时传入的class参数对应的对象
代码语言：javascript复制 public void testBeanHandler() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = null;
String sql = "select id,name,email,birth" "from customers where id >= ?";
Object object = queryRunner.query(connection, sql, new BeanHandler(Customer.class),5);
System.out.println(object);
connection.close();
}通过BeanListHandler类实现查询
把结果集转为一个List，该List不为null，但可能为空集合（size()方法返回为0），若SQL语句有查询记录，List中存放创建BeanListHandler转入Class对象对应的对象
代码语言：javascript复制 public void testBeanListHandler() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = null;
String sql = "select id,name,email,birth" "from customers";
List<Customer> customers = queryRunner.query(connection,sql,new BeanListHandler<Customer>(Customer.class));
Object object = queryRunner.query(connection, sql, new BeanHandler(Customer.class),5);
System.out.println(object);
connection.close();
}通过MapHandler类实现查询
返回SQL对应的第一条记录对应的Map对象，键值对：键SQL查询的列名(不是别名)，列的值
代码语言：javascript复制public void testMapHandler() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = null;
String sql = "select id,name,email,birth" "from customers";
Map<String,Object> customers = queryRunner.query(connection,sql,new MapHandler());
Object object = queryRunner.query(connection, sql, new BeanHandler(Customer.class),5);
System.out.println(object);
connection.close();
}通过MapListHandler类实现查询
将结果集转为一个Map的List，Map对应查询的一条记录：键值堆：键SQL查询的列名（不是列的别名），值：列的值，而MapListHandler：返回的多条记录对应的Map的集合
代码语言：javascript复制 public void testMapListHandler() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = null;
String sql = "select id,name,email,birth" "from customers";
List<Map<String,Object>> result = queryRunner.query(connection,sql,new MapListHandler());
Object object = queryRunner.query(connection, sql, new BeanHandler(Customer.class),5);
System.out.println(object);
connection.close();
}ScalarHandler：把结果集转为一个数值（可以是任意基本数据类型和字符串，Date等）返回
代码语言：javascript复制public void ScalarHandler() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = null;
String sql = "select name" "from customers"; // 如果是两列的情况返回一列
List<Map<String,Object>> result = queryRunner.query(connection,sql,new MapListHandler());
Object object = queryRunner.query(connection, sql, new ScalarHandler<>(),5);
System.out.println(object);
connection.close();
}
