DBUtils简介
一、简介
Commons DbUtils库是为了简化JDBC操作的类，它有以下优点：
-
没有资源泄漏：正确的JDBC编码并不难，但它既费时又乏味，通常会导致可能难以追踪的连接泄漏问题；
-
更干净、更清晰的持久性代码：它将操作数据库所需的代码量大大减少，清楚地表达了业务逻辑，且不会被资源清理的逻辑弄得乱七八糟；
-
从ResultSet自动填充JavaBean属性：不需要通过调用setter方法手动将列值复制到Bean实例中，ResultSet的每一行都可以由一个完全填充的Bean实例表示。
二、安装
下载commons-dbutils-1.7.jar或使用maven配置：
pom.xml：
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.7</version>
</dependency>
三、基础样例
样例中使用MySQL，需要配置：
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
数据库表：
create table employee(
id int primary key auto_increment,
age int,
firstname varchar(30),
lastname varchar(30)
)
JavaBean：
public class Employee {
private int id;
private int age;
private String firstname;
private String lastname;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirstname() {
return firstname;
}
public void setFirstname(String firstname) {
this.firstname = firstname;
}
public String getLastname() {
return lastname;
}
public void setLastname(String lastname) {
this.lastname = lastname;
}
}
数据库连接信息：
public class MainApp {
private static final String USERNAME = "root";
private static final String PASSWORD = "root";
private static final String DIRVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost:3306/test";
//......
}
1、insert
public static void main(String[] args) throws SQLException {
DbUtils.loadDriver(DIRVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
QueryRunner queryRunner = new QueryRunner();
String sql = "insert into employee(age, firstname, lastname) values(?,?,?)";
try{
int inserted = queryRunner.update(conn, sql, 20, "John", "Porter");
System.out.println(String.format("%s record(s) inserted", inserted));
}finally{
DbUtils.close(conn);
}
}
输出：
1 record(s) inserted
2、update
public static void main(String[] args) throws SQLException {
DbUtils.loadDriver(DIRVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
QueryRunner queryRunner = new QueryRunner();
String sql = "update employee set age = ? where id = ?";
try{
int updated = queryRunner.update(conn, sql, 28, 1);
System.out.println(String.format("%s record(s) updated", updated));
}finally{
DbUtils.close(conn);
}
}
输出：
1 record(s) updated
3、query
public static void main(String[] args) throws SQLException {
DbUtils.loadDriver(DIRVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
QueryRunner queryRunner = new QueryRunner();
ResultSetHandler<Employee> resultSet = new BeanHandler<>(Employee.class);
String sql = "select * from employee where id = ?";
try{
Employee employee = queryRunner.query(conn, sql, resultSet, 1);
System.out.println("ID: " + employee.getId());
System.out.println("Age: " + employee.getAge());
System.out.println("FirstName: " + employee.getFirstname());
System.out.println("LastName: " + employee.getLastname());
}finally{
DbUtils.close(conn);
}
}
输出：
ID: 1
Age: 28
FirstName: John
LastName: Porter
4、delete
public static void main(String[] args) throws SQLException {
DbUtils.loadDriver(DIRVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
QueryRunner queryRunner = new QueryRunner();
String sql = "delete from employee where id = ?";
try{
int deleted = queryRunner.update(conn, sql, 1);
System.out.println(String.format("%s record(s) deleted", deleted));
}finally{
DbUtils.close(conn);
}
}
输出：
1 record(s) deleted
四、常用接口和类
除了前面用到的QueryRunner、BeanHandler类，还有以下常用的接口和类：
1、AsyncQueryRunner
AsyncQueryRunner可以异步执行长时间运行的SQL查询，它是线程安全的。用法与QueryRunner相似，但它返回Callable对象。
public static void main(String[] args) throws Exception {
DbUtils.loadDriver(DIRVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
AsyncQueryRunner queryRunner = new AsyncQueryRunner(Executors.newCachedThreadPool());
String sql = "update employee set age = ? where id = ?";
try{
Future<Integer> future = queryRunner.update(conn, sql, 36, 2);
Integer updated = future.get(3, TimeUnit.SECONDS);
System.out.println(String.format("%s record(s) updated", updated));
}finally{
DbUtils.close(conn);
}
}
2、ResultSetHandler
ResultSetHandler接口负责转换ResultSet对象。
public static void main(String[] args) throws SQLException {
DbUtils.loadDriver(DIRVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
QueryRunner queryRunner = new QueryRunner();
//自定义查询单行返回结果
ResultSetHandler<Object[]> rsh = new ResultSetHandler<Object[]>() {
@Override
public Object[] handle(ResultSet resultSet) throws SQLException {
if(resultSet.next()){
ResultSetMetaData meta = resultSet.getMetaData();
int count = meta.getColumnCount();
Object[] result = new Object[count];
for(int i = 0; i < count; i++){
result[i] = resultSet.getObject(i + 1);
}
return result;
}else{
return null;
}
}
};
String sql = "select * from employee where id = ?";
try{
Object[] result = queryRunner.query(conn, sql, rsh, 3);
System.out.println(Arrays.toString(result));
}finally{
DbUtils.close(conn);
}
}
输出：
[3, 33, Damien, Scott]
3、BeanListHandler
BeanListHandler是ResultSetHandler接口的实现类，可以将ResultSet的所有行转换为JavaBean的列表（BeanHandler是将单行转换为JavaBean），它是线程安全的。
public static void main(String[] args) throws SQLException {
DbUtils.loadDriver(DIRVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
QueryRunner queryRunner = new QueryRunner();
ResultSetHandler<List<Employee>> rsh = new BeanListHandler<>(Employee.class);
String sql = "select * from employee";
try{
List<Employee> employees = queryRunner.query(conn, sql, rsh);
for(Employee employee : employees){
String format = "ID: %s, Age: %s, FirstName: %s, LastName: %s.";
System.out.println(String.format(format, employee.getId(), employee.getAge(), employee.getFirstname(), employee.getLastname()));
}
}finally{
DbUtils.close(conn);
}
}
输出：
ID: 2, Age: 36, FirstName: Michael, LastName: Stonebridge.
ID: 3, Age: 33, FirstName: Damien, LastName: Scott.
ID: 4, Age: 38, FirstName: Rachel, LastName: Dalton.
ID: 5, Age: 30, FirstName: Julia, LastName: Richmond.
ID: 6, Age: 40, FirstName: Philip, LastName: Locke.
4、ArrayListHandler
ArrayListHandler是ResultSetHandler接口的实现类，它可以将查询结果集的行转换为对Object[]，它是线程安全的。
public static void main(String[] args) throws SQLException {
DbUtils.loadDriver(DIRVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
QueryRunner queryRunner = new QueryRunner();
String sql = "select * from employee";
try{
List<Object[]> employees = queryRunner.query(conn, sql, new ArrayListHandler());
for(Object[] employee : employees){
System.out.println(Arrays.toString(employee));
}
}finally{
DbUtils.close(conn);
}
}
输出：
[2, 36, Michael, Stonebridge]
[3, 33, Damien, Scott]
[4, 38, Rachel, Dalton]
[5, 30, Julia, Richmond]
[6, 40, Philip, Locke]
5、MapListHandler
MapListHandler是ResultSetHandler接口的实现类，它可以将查询结果集的行转换为Map的列表，它是线程安全的。
public static void main(String[] args) throws SQLException {
DbUtils.loadDriver(DIRVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
QueryRunner queryRunner = new QueryRunner();
String sql = "select * from employee";
try{
List<Map<String, Object>> employees = queryRunner.query(conn, sql, new MapListHandler());
for(Map<String, Object> employee : employees){
System.out.println(employee);
}
}finally{
DbUtils.close(conn);
}
}
输出：
{id=2, age=36, firstname=Michael, lastname=Stonebridge}
{id=3, age=33, firstname=Damien, lastname=Scott}
{id=4, age=38, firstname=Rachel, lastname=Dalton}
{id=5, age=30, firstname=Julia, lastname=Richmond}
{id=6, age=40, firstname=Philip, lastname=Locke}
五、高级用法
1、自定义处理程序
可以通过实现ResultSetHandler接口或通过扩展ResultSetHandler的任何现有实现类来创建自定义的处理程序，类似ResultSetHandler中的例子。
Employee类中增加name属性：
public class Employee {
//...
private String name;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
自定义处理类：
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.dbutils.handlers.BeanHandler;
public class EmployeeHandler extends BeanHandler<Employee>{
public EmployeeHandler(Class<? extends Employee> type) {
super(type);
}
@Override
public Employee handle(ResultSet rs) throws SQLException {
Employee employee = super.handle(rs);
employee.setName(employee.getFirstname() + "-" + employee.getLastname());
return employee;
}
}
测试类：
public static void main(String[] args) throws SQLException {
DbUtils.loadDriver(DIRVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
QueryRunner queryRunner = new QueryRunner();
ResultSetHandler<Employee> resultSet = new EmployeeHandler(Employee.class);
String sql = "select * from employee where id = ?";
try{
Employee employee = queryRunner.query(conn, sql, resultSet, 5);
System.out.println("ID: " + employee.getId());
System.out.println("Age: " + employee.getAge());
System.out.println("Name: " + employee.getName());
}finally{
DbUtils.close(conn);
}
}
输出：
ID: 5
Age: 30
Name: Julia-Richmond
2、自定义行处理器
如果数据库表的列名和JavaBean的属性名不一致，可以通过自定义BasicRowProcessor对象来定义它们的映射关系。
假设JavaBean的定义如下：
public class EmployeeInfo {
private int id;
private int age;
private String first;
private String last;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
}
自定义处理器：
import java.util.HashMap;
import java.util.Map;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
public class EmployeeInfoHandler extends BeanHandler<EmployeeInfo>{
public EmployeeInfoHandler(Class<? extends EmployeeInfo> type) {
super(EmployeeInfo.class, new BasicRowProcessor(new BeanProcessor(getColumnToProperty())));
}
private static Map<String, String> getColumnToProperty() {
Map<String, String> columnToProperty = new HashMap<>();
columnToProperty.put("firstname", "first");
columnToProperty.put("lastname", "last");
return columnToProperty;
}
}
测试类：
public static void main(String[] args) throws SQLException {
DbUtils.loadDriver(DIRVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
QueryRunner queryRunner = new QueryRunner();
ResultSetHandler<EmployeeInfo> resultSet = new EmployeeInfoHandler(EmployeeInfo.class);
String sql = "select * from employee where id = ?";
try{
EmployeeInfo info = queryRunner.query(conn, sql, resultSet, 5);
System.out.println("ID: " + info.getId());
System.out.println("Age: " + info.getAge());
System.out.println("FirstName: " + info.getFirst());
System.out.println("LastName: " + info.getLast());
}finally{
DbUtils.close(conn);
}
}
输出：
ID: 5
Age: 30
FirstName: Julia
LastName: Richmond
3、使用数据源
在pom.xml中增加配置：
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.9.0</version>
</dependency>
自定义数据源：
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
public class CustomDataSource {
private static final String USERNAME = "root";
private static final String PASSWORD = "root";
private static final String DIRVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost:3306/test";
private static final BasicDataSource basicDataSource;
static{
basicDataSource = new BasicDataSource();
basicDataSource.setDriverClassName(DIRVER);
basicDataSource.setUrl(URL);
basicDataSource.setUsername(USERNAME);
basicDataSource.setPassword(PASSWORD);
}
public static DataSource getInstance(){
return basicDataSource;
}
}
测试类：
public static void main(String[] args) throws SQLException {
QueryRunner queryRunner = new QueryRunner(CustomDataSource.getInstance());
ResultSetHandler<Employee> rsh = new BeanHandler<>(Employee.class);
String sql = "select * from employee where id = ?";
Employee employee = queryRunner.query(sql, rsh, 5);
System.out.println("ID: " + employee.getId());
System.out.println("Age: " + employee.getAge());
System.out.println("FirstName: " + employee.getFirstname());
System.out.println("LastName: " + employee.getLastname());
}
输出：
ID: 5
Age: 30
FirstName: Julia
LastName: Richmond