1. 概述
Apache Commons DbUtils 是一个轻量级库，能显著简化 JDBC 操作。本文将通过实际案例展示其核心功能和最佳实践。
2. 环境搭建
2.1. Maven 依赖
首先在 pom.xml 中添加核心依赖：
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.7</version>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.196</version>
</dependency>
✅ 最新版本可在 Maven Central 获取
2.2. 测试数据库
创建测试表和数据脚本：
CREATE TABLE employee(
id int NOT NULL PRIMARY KEY auto_increment,
firstname varchar(255),
lastname varchar(255),
salary double,
hireddate date,
);
CREATE TABLE email(
id int NOT NULL PRIMARY KEY auto_increment,
employeeid int,
address varchar(255)
);
INSERT INTO employee (firstname,lastname,salary,hireddate)
VALUES ('John', 'Doe', 10000.10, to_date('01-01-2001','dd-mm-yyyy'));
-- 其他测试数据...
INSERT INTO email (employeeid,address)
VALUES (1, 'john.doe@example.com');
-- 其他测试数据...
测试用例基类初始化 H2 内存数据库：
public class DbUtilsUnitTest {
private Connection connection;
@Before
public void setupDB() throws Exception {
Class.forName("org.h2.Driver");
String db
= "jdbc:h2:mem:;INIT=runscript from 'classpath:/employees.sql'";
connection = DriverManager.getConnection(db);
}
@After
public void closeBD() {
DbUtils.closeQuietly(connection);
}
// ...
}
2.3. 实体类
定义两个基础 POJO：
public class Employee {
private Integer id;
private String firstName;
private String lastName;
private Double salary;
private Date hiredDate;
// 标准构造器/getter/setter
}
public class Email {
private Integer id;
private Integer employeeId;
private String address;
// 标准构造器/getter/setter
}
3. 核心机制
DbUtils 的核心入口是 QueryRunner 类，其工作流程如下：
- 接收数据库连接
- 执行 SQL 语句
- 处理可选参数占位符
- 通过 ResultSetHandler 转换结果集
⚠️ 库已内置常用处理器（如 List/Map/JavaBean 转换器），开箱即用。
4. 数据查询
4.1. 基础查询
使用 MapListHandler 获取结果集：
@Test
public void givenResultHandler_whenExecutingQuery_thenExpectedList()
throws SQLException {
MapListHandler beanListHandler = new MapListHandler();
QueryRunner runner = new QueryRunner();
List<Map<String, Object>> list
= runner.query(connection, "SELECT * FROM employee", beanListHandler);
assertEquals(list.size(), 5);
assertEquals(list.get(0).get("firstname"), "John");
assertEquals(list.get(4).get("firstname"), "Christian");
}
使用 BeanListHandler 直接映射实体：
@Test
public void givenResultHandler_whenExecutingQuery_thenEmployeeList()
throws SQLException {
BeanListHandler<Employee> beanListHandler
= new BeanListHandler<>(Employee.class);
QueryRunner runner = new QueryRunner();
List<Employee> employeeList
= runner.query(connection, "SELECT * FROM employee", beanListHandler);
assertEquals(employeeList.size(), 5);
assertEquals(employeeList.get(0).getFirstName(), "John");
assertEquals(employeeList.get(4).getFirstName(), "Christian");
}
单值查询用 ScalarHandler：
@Test
public void givenResultHandler_whenExecutingQuery_thenExpectedScalar()
throws SQLException {
ScalarHandler<Long> scalarHandler = new ScalarHandler<>();
QueryRunner runner = new QueryRunner();
String query = "SELECT COUNT(*) FROM employee";
long count
= runner.query(connection, query, scalarHandler);
assertEquals(count, 5);
}
4.2. 自定义处理器
当内置处理器不满足需求时，可通过两种方式扩展：
- 实现 ResultSetHandler 接口
- 继承现有处理器（推荐）
场景：为 Employee 关联 Email 列表。先修改实体类：
public class Employee {
private List<Email> emails;
// ...
}
创建自定义处理器：
public class EmployeeHandler extends BeanListHandler<Employee> {
private Connection connection;
public EmployeeHandler(Connection con) {
super(Employee.class);
this.connection = con;
}
@Override
public List<Employee> handle(ResultSet rs) throws SQLException {
List<Employee> employees = super.handle(rs);
QueryRunner runner = new QueryRunner();
BeanListHandler<Email> handler = new BeanListHandler<>(Email.class);
String query = "SELECT * FROM email WHERE employeeid = ?";
for (Employee employee : employees) {
List<Email> emails
= runner.query(connection, query, handler, employee.getId());
employee.setEmails(emails);
}
return employees;
}
}
验证效果：
@Test
public void
givenResultHandler_whenExecutingQuery_thenEmailsSetted()
throws SQLException {
EmployeeHandler employeeHandler = new EmployeeHandler(connection);
QueryRunner runner = new QueryRunner();
List<Employee> employees
= runner.query(connection, "SELECT * FROM employee", employeeHandler);
assertEquals(employees.get(0).getEmails().size(), 2);
assertEquals(employees.get(2).getEmails().size(), 3);
}
4.3. 列名映射
当数据库列名与 Java 字段名不一致时（如下划线命名法），使用 RowProcessor 解决映射问题。
创建测试表：
CREATE TABLE employee_legacy (
id int NOT NULL PRIMARY KEY auto_increment,
first_name varchar(255),
last_name varchar(255),
salary double,
hired_date date,
);
INSERT INTO employee_legacy (first_name,last_name,salary,hired_date)
VALUES ('John', 'Doe', 10000.10, to_date('01-01-2001','dd-mm-yyyy'));
-- 其他测试数据...
修改处理器添加映射规则：
public class EmployeeHandler extends BeanListHandler<Employee> {
// ...
public EmployeeHandler(Connection con) {
super(Employee.class,
new BasicRowProcessor(new BeanProcessor(getColumnsToFieldsMap())));
// ...
}
public static Map<String, String> getColumnsToFieldsMap() {
Map<String, String> columnsToFieldsMap = new HashMap<>();
columnsToFieldsMap.put("FIRST_NAME", "firstName");
columnsToFieldsMap.put("LAST_NAME", "lastName");
columnsToFieldsMap.put("HIRED_DATE", "hiredDate");
return columnsToFieldsMap;
}
// ...
}
测试映射效果：
@Test
public void
givenResultHandler_whenExecutingQuery_thenAllPropertiesSetted()
throws SQLException {
EmployeeHandler employeeHandler = new EmployeeHandler(connection);
QueryRunner runner = new QueryRunner();
String query = "SELECT * FROM employee_legacy";
List<Employee> employees
= runner.query(connection, query, employeeHandler);
assertEquals((int) employees.get(0).getId(), 1);
assertEquals(employees.get(0).getFirstName(), "John");
}
5. 数据插入
5.1. 基础插入
使用 update() 方法：
@Test
public void whenInserting_thenInserted() throws SQLException {
QueryRunner runner = new QueryRunner();
String insertSQL
= "INSERT INTO employee (firstname,lastname,salary, hireddate) "
+ "VALUES (?, ?, ?, ?)";
int numRowsInserted
= runner.update(
connection, insertSQL, "Leia", "Kane", 60000.60, new Date());
assertEquals(numRowsInserted, 1);
}
5.2. 获取自增主键
使用 insert() 方法配合处理器：
@Test
public void
givenHandler_whenInserting_thenExpectedId() throws SQLException {
ScalarHandler<Integer> scalarHandler = new ScalarHandler<>();
QueryRunner runner = new QueryRunner();
String insertSQL
= "INSERT INTO employee (firstname,lastname,salary, hireddate) "
+ "VALUES (?, ?, ?, ?)";
int newId
= runner.insert(
connection, insertSQL, scalarHandler,
"Jenny", "Medici", 60000.60, new Date());
assertEquals(newId, 6);
}
6. 更新与删除
6.1. 数据更新
简单粗暴的薪资调整：
@Test
public void givenSalary_whenUpdating_thenUpdated()
throws SQLException {
double salary = 35000;
QueryRunner runner = new QueryRunner();
String updateSQL
= "UPDATE employee SET salary = salary * 1.1 WHERE salary <= ?";
int numRowsUpdated = runner.update(connection, updateSQL, salary);
assertEquals(numRowsUpdated, 3);
}
6.2. 数据删除
根据 ID 删除记录：
@Test
public void whenDeletingRecord_thenDeleted() throws SQLException {
QueryRunner runner = new QueryRunner();
String deleteSQL = "DELETE FROM employee WHERE id = ?";
int numRowsDeleted = runner.update(connection, deleteSQL, 3);
assertEquals(numRowsDeleted, 1);
}
7. 异步操作
使用 AsyncQueryRunner 实现异步查询：
@Test
public void
givenAsyncRunner_whenExecutingQuery_thenExpectedList() throws Exception {
AsyncQueryRunner runner
= new AsyncQueryRunner(Executors.newCachedThreadPool());
EmployeeHandler employeeHandler = new EmployeeHandler(connection);
String query = "SELECT * FROM employee";
Future<List<Employee>> future
= runner.query(connection, query, employeeHandler);
List<Employee> employeeList = future.get(10, TimeUnit.SECONDS);
assertEquals(employeeList.size(), 5);
}
8. 总结
本文完整展示了 Apache Commons DbUtils 的核心能力：
- ✅ 灵活的结果集转换（Map/List/Bean）
- ✅ 自定义处理器实现复杂映射
- ✅ 自增主键获取方案
- ✅ 异步操作支持
📌 关键优势：用极简代码规避 JDBC 繁琐操作，特别适合中小型项目快速开发。完整示例代码见 GitHub 仓库。