Использование списка значений в предложении JdbcTemplate IN

Автор оригинала: Gang Wu.

1. введение

В инструкции SQL мы можем использовать оператор IN для проверки соответствия выражения любому значению в списке. Поэтому мы можем использовать оператор IN вместо нескольких условий ИЛИ.

В этом уроке мы покажем, как передать список значений в предложение IN запроса Spring JdbcTemplate.

2. Передача параметра списка в предложение IN

Оператор IN позволяет нам указать несколько значений в предложении WHERE. Например, мы можем использовать его для поиска всех сотрудников, чей идентификатор находится в указанном списке идентификаторов:

SELECT * FROM EMPLOYEE WHERE id IN (1, 2, 3)

Как правило, общее количество значений внутри предложения IN является переменным. Поэтому нам необходимо создать заполнитель, который может поддерживать динамический список значений.

2.1. С JdbcTemplate

С помощью JdbcTemplate мы можем использовать символы ‘?’ в качестве заполнителей для списка значений. Количество символов”? ” будет таким же, как и размер списка:

List getEmployeesFromIdList(List ids) {
    String inSql = String.join(",", Collections.nCopies(ids.size(), "?"));
 
    List employees = jdbcTemplate.query(
      String.format("SELECT * FROM EMPLOYEE WHERE id IN (%s)", inSql), 
      ids.toArray(), 
      (rs, rowNum) -> new Employee(rs.getInt("id"), rs.getString("first_name"), 
        rs.getString("last_name")));

    return employees;
}

В этом методе мы сначала создаем строку-заполнитель, содержащую символы ids.size () ‘?’, разделенные запятыми . Затем мы помещаем эту строку в предложение IN нашего оператора SQL. Например, если у нас есть три числа в списке ids , оператор SQL:

SELECT * FROM EMPLOYEE WHERE id IN (?,?,?)

В методе query мы передаем список ids в качестве параметра, соответствующего заполнителям внутри предложения IN. Таким образом, мы можем выполнить динамическую инструкцию SQL на основе входного списка значений.

2.2. С NamedParameterJdbcTemplate

Другой способ обработки динамического списка значений-использовать NamedParameterJdbcTemplate . Например, мы можем напрямую создать именованный параметр для списка входных данных:

List getEmployeesFromIdListNamed(List ids) {
    SqlParameterSource parameters = new MapSqlParameterSource("ids", ids);
 
    List employees = namedJdbcTemplate.query(
      "SELECT * FROM EMPLOYEE WHERE id IN (:ids)", 
      parameters, 
      (rs, rowNum) -> new Employee(rs.getInt("id"), rs.getString("first_name"),
        rs.getString("last_name")));

    return employees;
}

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

Под капотом NamedParameterJdbcTemplate заменяет именованные параметры заполнителями ‘?’ и использует JdbcTemplate для выполнения запроса.

3. Обработка большого списка

Когда у нас есть большое количество значений в списке, мы должны рассмотреть альтернативные способы передачи их в запрос JdbcTemplate .

Например, база данных Oracle не поддерживает более 1000 литералов в предложении IN.

Один из способов сделать это – создать временную таблицу для списка . Однако разные базы данных могут иметь разные способы создания временных таблиц. Например, мы можем использовать инструкцию CREATE GLOBAL TEMPORARY TABLE для создания временной таблицы в базе данных Oracle.

Давайте создадим временную таблицу для базы данных H2:

List getEmployeesFromLargeIdList(List ids) {
    jdbcTemplate.execute("CREATE TEMPORARY TABLE IF NOT EXISTS employee_tmp (id INT NOT NULL)");

    List employeeIds = new ArrayList<>();
    for (Integer id : ids) {
        employeeIds.add(new Object[] { id });
    }
    jdbcTemplate.batchUpdate("INSERT INTO employee_tmp VALUES(?)", employeeIds);

    List employees = jdbcTemplate.query(
      "SELECT * FROM EMPLOYEE WHERE id IN (SELECT id FROM employee_tmp)", 
      (rs, rowNum) -> new Employee(rs.getInt("id"), rs.getString("first_name"),
      rs.getString("last_name")));

    jdbcTemplate.update("DELETE FROM employee_tmp");
 
    return employees;
}

Здесь мы сначала создадим временную таблицу для хранения всех значений входного списка. Затем мы вставляем значения входного списка в эту таблицу.

В нашем результирующем SQL-операторе значения в предложении IN взяты из временной таблицы , и мы избежали создания предложения IN с большим количеством заполнителей.

Наконец, после завершения запроса мы очищаем временную таблицу для дальнейшего повторного использования.

4. Заключение

В этом уроке мы показали, как использовать JdbcTemplate и NamedParameterJdbcTemplate для передачи списка значений для предложения IN SQL-запроса. Кроме того, мы предоставили альтернативный способ обработки большого количества значений списка с помощью временной таблицы.

Как всегда, исходный код статьи доступен на GitHub .