1. 序章

SQLステートメントでは、IN演算子を使用して、式がリスト内の任意の値と一致するかどうかをテストできます。 As such, we can use the IN operator instead of multiple OR conditions.

In this tutorial, we’ll learn how to pass a list of values into the IN clause of a Spring JDBC template query.

2. ListパラメータをIN句に渡す

IN演算子を使用すると、WHERE句で複数の値を指定できます。 たとえば、これを使用して、指定されたIDリストにIDが含まれているすべての従業員を検索できます。

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

通常、IN句内の値の総数は可変です。 したがって、値の動的リストをサポートできるプレースホルダーを作成する必要があります。

2.1. JdbcTemplateを使用

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() ‘?’を含むプレースホルダー文字列を生成します。 カンマで区切られた文字。 Then we put this string into the IN clause of our SQL statement. たとえば、 ids リストに3つの数値がある場合、SQLステートメントは次のようになります。

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

query メソッドでは、 ids リストをパラメーターとして渡し、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;
}

このメソッドでは、最初に、入力IDリストを含むMapSqlParameterSourceオブジェクトを作成します。 Then we only use one named parameter to represent the dynamic list of values.

Under the hood, NamedParameterJdbcTemplate substitutes the named parameters for the ‘?’ placeholders, and uses JdbcTemplate to execute the query.

3. 大きなリストの処理

When we have a large number of values in a list, we should consider alternate ways to pass them into the JdbcTemplate query.

たとえば、OracleデータベースはIN句で1,000を超えるリテラルをサポートしていません。

One way to do this is to create a temporary table for the list. ただし、データベースが異なれば、一時テーブルを作成する方法も異なります。 For instance, we can use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table in the Oracle database.

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

Here, we first create a temporary table to hold all the values of the input list. Then we insert the input list’s values into the table.

In our resulting SQL statement, the values in the IN clause are from the temporary table, and we avoid constructing an IN clause with a large number of placeholders.

Finally, after we finish the query, we can clean up the temporary table for future use.

4. 結論

In this article, we demonstrated how to use JdbcTemplate and NamedParameterJdbcTemplate to pass a list of values for the IN clause of a SQL query. We also provided an alternate way to handle a large number of list values by using a temporary table.

いつものように、記事のソースコードはGitHubから入手できます。