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

1. Введение

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

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

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

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

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

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

2.1. С шаблоном Jdbc

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

List<Employee> getEmployeesFromIdList(List<Integer> ids) {
    String inSql = String.join(",", Collections.nCopies(ids.size(), "?"));
 
    List<Employee> 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. Например, если у нас есть три числа в списке идентификаторов , оператор SQL будет следующим:

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

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

2.2. С шаблоном NamedParameterJdbcTemplate

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

List<Employee> getEmployeesFromIdListNamed(List<Integer> ids) {
    SqlParameterSource parameters = new MapSqlParameterSource("ids", ids);
 
    List<Employee> 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<Employee> getEmployeesFromLargeIdList(List<Integer> ids) {
    jdbcTemplate.execute("CREATE TEMPORARY TABLE IF NOT EXISTS employee_tmp (id INT NOT NULL)");

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

    List<Employee> 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 .