What is the best approach using JDBC for parameterizing an IN clause? [duplicate]

by

There’s indeed no straightforward way to do this in JDBC. Some JDBC drivers seem to support PreparedStatement#setArray() on the IN clause. I am only not sure which ones that are.

You could just use a helper method with String#join() and Collections#nCopies() to generate the placeholders for IN clause and another helper method to set all the values in a loop with PreparedStatement#setObject().

public static String preparePlaceHolders(int length) {
    return String.join(",", Collections.nCopies(length, "?"));
}

public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {
    for (int i = 0; i < values.length; i++) {
        preparedStatement.setObject(i + 1, values[i]);
    }
}

Here’s how you could use it:

private static final String SQL_FIND = "SELECT id, name, value FROM entity WHERE id IN (%s)";

public List<Entity> find(Set<Long> ids) throws SQLException {
    List<Entity> entities = new ArrayList<Entity>();
    String sql = String.format(SQL_FIND, preparePlaceHolders(ids.size()));

    try (
        Connection connection = dataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement(sql);
    ) {
        setValues(statement, ids.toArray());

        try (ResultSet resultSet = statement.executeQuery()) {
            while (resultSet.next()) {
                entities.add(map(resultSet));
            }
        }
    }

    return entities;
}

private static Entity map(ResultSet resultSet) throws SQLException {
    Enitity entity = new Entity();
    entity.setId(resultSet.getLong("id"));
    entity.setName(resultSet.getString("name"));
    entity.setValue(resultSet.getInt("value"));
    return entity;
}

Note that some databases have a limit of allowable amount of values in the IN clause. Oracle for example has this limit on 1000 items.

More Related Contents:

  1. Hours and seconds are lost when storing a date in the database [closed]
  2. Simulate Result set closing failed [closed]
  3. How to get the insert ID in JDBC?
  4. Multiple queries executed in java in single statement
  5. java.util.Date vs java.sql.Date
  6. PreparedStatement with list of parameters in a IN clause [duplicate]
  7. Using setDate in PreparedStatement
  8. Efficient way to do batch INSERTS with JDBC
  9. How to execute IN() SQL queries with Spring’s JDBCTemplate effectively?
  10. How can I get the SQL of a PreparedStatement?
  11. How does a PreparedStatement avoid or prevent SQL injection?
  12. right syntax to use near ‘?’
  13. Running a .sql script using MySQL with JDBC
  14. java.sql.SQLException: Exhausted Resultset
  15. How to enable logging for SQL statements when using JDBC
  16. Getting output from dbms_output.get_lines using JDBC
  17. handling DATETIME values 0000-00-00 00:00:00 in JDBC
  18. JDBC ResultSet get columns with table alias
  19. Java – Can’t use ResultSet after connection close
  20. Timezones in SQL DATE vs java.sql.Date
  21. SQL prepared statement how to select via multiple possible menu selections?
  22. Getting an exception ORA-00942: table or view does not exist – when inserting into an existing table
  23. Class.forName(JDBC_DRIVER) no longer needed?
  24. How do I use PostgreSQL JSON(B) operators containing a question mark “?” via JDBC
  25. Java – Storing SQL statements in an external file [closed]
  26. A datetime equivalent in java.sql ? (is there a java.sql.datetime ?)
  27. MS SQL Exception: Incorrect syntax near ‘@P0’
  28. Java JDBC connection status
  29. MyBatis executing multiple sql statements in one go, is that possible?
  30. Database independence through JDBC in java

Leave a Comment