A Guide to Apache Commons DbUtils – Apache Commons DbUtils指南

1. Overview

1.概述

Apache Commons DbUtils is a small library that makes working with JDBC a lot easier.

Apache Commons DbUtils是一个小型库，它使JDBC的工作变得更加容易。

In this article, we’ll implement examples to showcase its features and capabilities.

在这篇文章中，我们将实现一些例子来展示它的特点和能力。

2. Setup

2.设置

2.1. Maven Dependencies

2.1.Maven的依赖性

First, we need to add the commons-dbutils and h2 dependencies to our pom.xml:

首先，我们需要将commons-dbutils和h2依赖关系添加到我们的pom.xml。

<dependency>
    <groupId>commons-dbutils</groupId>
    <artifactId>commons-dbutils</artifactId>
    <version>1.6</version>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.196</version>
</dependency>

You can find the latest version of commons-dbutils and h2 on Maven Central.

您可以在Maven中心找到最新版本的commons-dbutils和h2>。

2.2. Test Database

2.2.测试数据库

With our dependencies in place, let’s create a script to create the tables and records we’ll use:

有了我们的依赖性，让我们创建一个脚本来创建我们要使用的表和记录。

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@baeldung.com');
// ...

All example test cases in this article will use a newly created connection to an H2 in-memory database:

本文的所有测试案例的例子都将使用一个新创建的连接到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. POJOs

2.3.POJO[/strong]

Finally, we’ll need two simple classes:

最后，我们将需要两个简单的类。

public class Employee {
    private Integer id;
    private String firstName;
    private String lastName;
    private Double salary;
    private Date hiredDate;

    // standard constructors, getters, and setters
}

public class Email {
    private Integer id;
    private Integer employeeId;
    private String address;

    // standard constructors, getters, and setters
}

3. Introduction

3.简介

The DbUtils library provides the QueryRunner class as the main entry point for most of the available functionality.

DbUtils库提供了QueryRunner类作为大多数可用功能的主要入口点。

This class works by receiving a connection to the database, a SQL statement to be executed, and an optional list of parameters to supply values for the placeholders of the query.

这个类的工作原理是接收一个与数据库的连接，一个要执行的SQL语句，以及一个可选的参数列表，为查询的占位符提供数值。

As we’ll see later, a few methods also receive a ResultSetHandler implementation – which is responsible for transforming ResultSet instances into the objects our application expects.

正如我们稍后所见，一些方法也会收到一个ResultSetHandler实现–它负责将ResultSet实例转换为我们应用程序所期望的对象。

Of course, the library already provides several implementations that handle the most common transformations, such as lists, maps, and JavaBeans.

当然，该库已经提供了几个实现，处理最常见的转换，如列表、地图和JavaBeans。

4. Querying Data

4.查询数据

Now that we know the basics, we’re ready to query our database.

现在我们知道了基础知识，我们准备查询我们的数据库。

Let’s start with a quick example of obtaining all the records in the database as a list of maps using a MapListHandler:

让我们从一个快速的例子开始，使用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");
}

Next, here’s an example using a BeanListHandler to transform the results into Employee instances:

接下来，这里有一个使用BeanListHandler来将结果转化为Employee实例的例子。

@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");
}

For queries that return a single value, we can use a ScalarHandler:

对于返回单一数值的查询，我们可以使用一个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);
}

To learn all the ResultSerHandler implementations, you can refer to the ResultSetHandler documentation.

要了解所有的ResultSerHandler实现，你可以参考ResultSetHandler文档。

4.1. Custom Handlers

4.1.自定义处理程序

We can also create a custom handler to pass to QueryRunner‘s methods when we need more control on how the results will be transformed into objects.

当我们需要对结果如何转化为对象进行更多的控制时，我们也可以创建一个自定义的处理程序来传递给QueryRunner的方法。

This can be done by either implementing the ResultSetHandler interface or extending one of the existing implementations provided by the library.

这可以通过实现ResultSetHandler接口或扩展库所提供的一个现有实现来实现。

Let’s see how the second approach looks. First, let’s add another field to our Employee class:

让我们看看第二种方法是什么样子的。首先，让我们给我们的Employee类增加一个字段。

public class Employee {
    private List<Email> emails;
    // ...
}

Now, let’s create a class that extends the BeanListHandler type and sets the email list for each employee:

现在，让我们创建一个扩展BeanListHandler类型的类，为每个雇员设置电子邮件列表。

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;
    }
}

Notice we are expecting a Connection object in the constructor so that we can execute the queries to get the emails.

注意我们在构造函数中期待一个Connection对象，这样我们就可以执行查询以获得电子邮件。

Finally, let’s test our code to see if everything is working as expected:

最后，让我们测试一下我们的代码，看看一切是否按预期工作。

@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.2. Custom Row Processors

4.2.自定义行处理器

In our examples, the column names of the employee table match the field names of our Employee class (the matching is case insensitive). However, that’s not always the case – for instance when column names use underscores to separate compound words.

在我们的例子中，employee表的列名与我们的Employee类的字段名相匹配（匹配是不分大小写的）。然而，情况并非总是如此–例如，当列名使用下划线来分隔复合词时。

In these situations, we can take advantage of the RowProcessor interface and its implementations to map the column names to the appropriate fields in our classes.

在这些情况下，我们可以利用RowProcessor接口及其实现，将列名映射到我们类中的适当字段。

Let’s see how this looks like. First, let’s create another table and insert some records into it:

让我们看看这看起来像什么。首先，让我们创建另一个表并向其中插入一些记录。

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'));
// ...

Now, let’s modify our EmployeeHandler class:

现在，让我们修改我们的EmployeeHandler类。

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;
    }
    // ...
}

Notice we are using a BeanProcessor to do the actual mapping of columns to fields and only for those that need to be addressed.

请注意，我们使用一个BeanProcessor来完成列与字段的实际映射，而且只针对那些需要处理的字段。

Finally, let’s test everything is ok:

最后，让我们测试一下一切是否正常。

@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. Inserting Records

5.插入记录

The QueryRunner class provides two approaches to creating records in a database.

QueryRunner类提供了两种在数据库中创建记录的方法。

The first one is to use the update() method and pass the SQL statement and an optional list of replacement parameters. The method returns the number of inserted records:

第一个是使用update()方法，并传递SQL语句和一个可选的替换参数列表。该方法返回插入记录的数量。

@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);
}

The second one is to use the insert() method that, in addition to the SQL statement and replacement parameters, needs a ResultSetHandler to transform the resulting auto-generated keys. The return value will be what the handler returns:

第二种是使用insert()方法，除了SQL语句和替换参数外，还需要一个ResultSetHandler来转换产生的自动生成的键。返回值将是处理程序返回的内容。

@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. Updating and Deleting

6.更新和删除

The update() method of the QueryRunner class can also be used to modify and erase records from our database.

QueryRunner类的update()方法也可以用来修改和删除我们数据库中的记录。

Its usage is trivial. Here’s an example of how to update an employee’s salary:

它的使用是微不足道的。下面是一个如何更新雇员工资的例子。

@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);
}

And here’s another to delete an employee with the given id:

下面是另一个删除给定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. Asynchronous Operations

7.异步操作

DbUtils provides the AsyncQueryRunner class to execute operations asynchronously. The methods on this class have a correspondence with those of QueryRunner class, except that they return a Future instance.

DbUtils提供了AsyncQueryRunner类来异步执行操作。这个类的方法与QueryRunner类的方法有对应关系，只是它们返回一个Future实例。

Here’s an example to obtain all employees in the database, waiting up to 10 seconds to get the results:

下面是一个获取数据库中所有雇员的例子，最多等待10秒就能得到结果。

@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. Conclusion

8.结论

In this tutorial, we explored the most notable features of the Apache Commons DbUtils library.

在本教程中，我们探讨了Apache Commons DbUtils库的最显著的特点。

We queried data and transformed it into different object types, inserted records obtaining the generated primary keys and updated and deleted data based on a given criteria. We also took advantage of the AsyncQueryRunner class to asynchronously execute a query operation.

我们查询数据并将其转换为不同的对象类型，插入记录获得生成的主键，并根据给定的标准更新和删除数据。我们还利用了AsyncQueryRunner类来异步执行查询操作。

And, as always, the complete source code for this article can be found over on Github.

而且，像往常一样，本文的完整源代码可以在Github上找到over。