JDBC PreparedStatement SQL IN clause

1. Introduction

A common use case while querying the database is to find a match for a column based on a list of input values. There are multiple ways to do this. The IN clause is one of the ways to provide multiple values for comparison for a given column.

In this tutorial, we’ll take a look at using the IN clause with the JDBC PreparedStatement.

2. Setup

Let’s create a Customer table and add some entries so that we can query them with the IN clause:

void populateDB() throws SQLException {
    String createTable = "CREATE TABLE CUSTOMER (id INT, first_name VARCHAR(50), last_name VARCHAR(50))";
    connection.createStatement().execute(createTable);

    String load = "INSERT INTO CUSTOMER (id, first_name, last_name) VALUES(?,?,?)";
    IntStream.rangeClosed(1, 100)
      .forEach(i -> {
          PreparedStatement preparedStatement1 = null;
          try {
              preparedStatement1 = connection.prepareStatement(load);
              preparedStatement1.setInt(1, i);
              preparedStatement1.setString(2, "firstname" + i);
              preparedStatement1.setString(3, "lastname" + i);
              preparedStatement1.execute();
          } catch (SQLException e) {
              throw new RuntimeException(e);
          }
      });
}

3. PreparedStatement

PreparedStatement represents an SQL statement, which is already pre-compiled and can be efficiently used multiple times with different sets of parameters.

Let’s take a look at the different ways in which we can use the IN clause with PreparedStatement.

3.1. IN Clause With StringBuilder

A simple way to construct a dynamic query is by appending the placeholders manually, for each value in the list. StringBuilder assists in concatenating strings effectively without creating additional objects:

ResultSet populateParamsWithStringBuilder(Connection connection, List<Integer> ids) 
  throws SQLException {
    StringBuilder stringBuilder = new StringBuilder();

    for (int i = 0; i < ids.size(); i++) {
        stringBuilder.append("?,");
    }
    String placeHolders = stringBuilder.deleteCharAt(stringBuilder.length() - 1)
      .toString();
    
    String sql = "select * from customer where id in (" + placeHolders + ")";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    for (int i = 1; i <= ids.size(); i++) {
        preparedStatement.setInt(i, ids.get(i - 1));
    }
    return preparedStatement.executeQuery();
}

In this method, we create a placeholder string by concatenating the placeholder (?) separated by a comma (,). Next, we concatenate the placeholder string with the query string, to create the final SQL statement to be used by the PreparedStatement.

Let’s execute a test case to verify the scenario:

@Test
void whenPopulatingINClauseWithStringBuilder_thenIsSuccess() throws SQLException {
    ResultSet resultSet = PreparedStatementInClause
      .populateParamsWithStringBuilder(connection, List.of(1, 2, 3, 4, 55));
    Assertions.assertNotNull(resultSet);
    resultSet.last();
    int size = resultSet.getRow();
    Assertions.assertEquals(5, size);
}

As we can see here, we’ve successfully fetched the customers with the provided ids using the IN clause.

3.2. IN Clause With Stream

Another approach to construct the IN clause is by using Stream API, by mapping all the values as a placeholder (?) and then providing them as parameters to the format() method of the String class:

ResultSet populateParamsWithStream(Connection connection, List<Integer> ids) throws SQLException {
    var sql = String.format("select * from customer where id IN (%s)", ids.stream()
      .map(v -> "?")
      .collect(Collectors.joining(", ")));
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    for (int i = 1; i <= ids.size(); i++) {
        preparedStatement.setInt(i, ids.get(i - 1));
    }
    return preparedStatement.executeQuery();
}

We can verify the above logic by executing a similar test wherein we pass the list of customer IDs and get back the expected results:

@Test
void whenPopulatingINClauseWithStream_thenIsSuccess() throws SQLException {
    ResultSet resultSet = PreparedStatementInClause
      .populateParamsWithStream(connection, List.of(1, 2, 3, 4, 55));
    Assertions.assertNotNull(resultSet);
    resultSet.last();
    int size = resultSet.getRow();
    Assertions.assertEquals(5, size);
}

3.3. IN Clause With setArray()

Finally, let’s take a look at the setArray() method of the PreparedStatement class:

ResultSet populateParamsWithArray(Connection connection, List<Integer> ids) throws SQLException {
    String sql = "SELECT * FROM customer where id IN (select * from table(x int = ?))";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    Array array = preparedStatement.getConnection()
      .createArrayOf("int", ids.toArray());
    preparedStatement.setArray(1, array);
    return preparedStatement.executeQuery();
}

In this method, we’ve altered the structure of the query. We provided a sub-query instead of directly adding the placeholder after the IN clause. This sub-query reads all the entries from the array provided as the value for the first placeholder and then provides those as the values for the IN clause.

Another important distinction is that we need to convert the List to an Array by specifying the type of values it holds.

Now, let’s verify the implementation with a simple test case:

@Test
void whenPopulatingINClauseWithArray_thenIsSuccess() throws SQLException {
    ResultSet resultSet = PreparedStatementInClause
      .populateParamsWithArray(connection, List.of(1, 2, 3, 4, 55));
    Assertions.assertNotNull(resultSet);
    resultSet.last();
    int size = resultSet.getRow();
    Assertions.assertEquals(5, size);
}

4. Conclusion

In this article, we explored the different ways in which we can create the query for the IN clause with JDBC PreparedStatement. Ultimately, all the approaches provide the same result, however using the Stream API is clean, straightforward, and database-independent.