Руководство по Apache Commons DbUtils

1. Обзор

Apache Commons DbUtils — это небольшая библиотека, которая значительно упрощает работу с JDBC.

В этой статье мы реализуем примеры, чтобы продемонстрировать его функции и возможности.

2. Настройка

2.1. Зависимости Maven

Во- первых, нам нужно добавить зависимости 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>

Вы можете найти последнюю версию commons-dbutils и h2 на 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@foreach.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;

    // standard constructors, getters, and setters
}

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

    // standard constructors, getters, and setters
}

3. Введение

Библиотека DbUtils предоставляет класс QueryRunner в качестве основной точки входа для большинства доступных функций.

Этот класс работает, получая соединение с базой данных, оператор SQL для выполнения и необязательный список параметров для предоставления значений для заполнителей запроса.

Как мы увидим позже, несколько методов также получают реализацию ResultSetHandler , которая отвечает за преобразование экземпляров ResultSet в объекты, ожидаемые нашим приложением.

Конечно, в библиотеке уже есть несколько реализаций, обрабатывающих наиболее распространенные преобразования, такие как списки, карты и JavaBeans.

4. Запрос данных

Теперь, когда мы знаем основы, мы готовы запросить нашу базу данных.

Давайте начнем с быстрого примера получения всех записей в базе данных в виде списка карт с помощью 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 для преобразования результатов в экземпляры 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");
}

Для запросов, возвращающих одно значение, мы можем использовать 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);
}

Чтобы изучить все реализации ResultSerHandler , вы можете обратиться к документации ResultSetHandler .

4.1. Пользовательские обработчики

Мы также можем создать собственный обработчик для передачи методам QueryRunner , когда нам нужно больше контроля над тем, как результаты будут преобразованы в объекты.

Это можно сделать либо путем реализации интерфейса ResultSetHandler , либо путем расширения одной из существующих реализаций, предоставляемых библиотекой.

Посмотрим, как выглядит второй подход. Во-первых, давайте добавим еще одно поле в наш класс Employee :

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

Теперь давайте создадим класс, который расширяет тип 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;
    }
}

Обратите внимание, что мы ожидаем объект Connection в конструкторе, чтобы мы могли выполнять запросы для получения электронных писем.

Наконец, давайте проверим наш код, чтобы убедиться, что все работает так, как ожидалось:

@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. Пользовательские процессоры строк

В наших примерах имена столбцов таблицы сотрудников совпадают с именами полей нашего класса Employee (сопоставление нечувствительно к регистру). Однако это не всегда так — например, когда в именах столбцов используются символы подчеркивания для разделения составных слов.

В этих ситуациях мы можем воспользоваться интерфейсом 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'));
// ...

Теперь давайте изменим наш класс 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;
    }
    // ...
}

Обратите внимание, что мы используем BeanProcessor для фактического сопоставления столбцов с полями и только для тех, к которым необходимо обратиться.

Наконец, давайте проверим, все ли в порядке:

@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. Вставка записей

Класс QueryRunner предоставляет два подхода к созданию записей в базе данных.

Первый — использовать метод 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);
}

Второй — использовать метод 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. Обновление и удаление

Метод update() класса QueryRunner также можно использовать для изменения и удаления записей из нашей базы данных.

Его использование тривиально. Вот пример того, как обновить зарплату сотрудника:

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

А вот еще один, чтобы удалить сотрудника с данным идентификатором:

@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. Асинхронные операции

DbUtils предоставляет класс AsyncQueryRunner для асинхронного выполнения операций. Методы этого класса аналогичны методам класса QueryRunner , за исключением того, что они возвращают экземпляр Future .

Вот пример получения всех сотрудников в базе данных с ожиданием до 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. Заключение

В этом руководстве мы рассмотрели наиболее примечательные функции библиотеки Apache Commons DbUtils.

Мы запрашивали данные и преобразовывали их в различные типы объектов, вставляли записи, получая сгенерированные первичные ключи, а также обновляли и удаляли данные на основе заданных критериев. Мы также воспользовались классом AsyncQueryRunner для асинхронного выполнения операции запроса.

И, как всегда, полный исходный код этой статьи можно найти на Github .