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

Question

Say that I have a query of the form

SELECT * FROM MYTABLE WHERE MYCOL in (?)

And I want to parameterize the arguments to in.

Is there a straightforward way to do this in Java with JDBC, in a way that could work on multiple databases without modifying the SQL itself?

The closest question I've found had to do with C#, I'm wondering if there is something different for Java/JDBC.

Clyve

Answer 1

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.

Skijoring

Will this approach leads to SQL Injection?? – Soracco

@Kaylan: no single code line adds user-controlled input raw to the SQL query string. So there's definitely no means of a SQL injection risk. – Skijoring

jtds driver has a max param variable list of 2,000. – Beck

Between MySQL (driver 5.1.37) & PostgreSQL (driver 9.1-901), only PostgreSQL has some support for PreparedStatement#setArray() – Reneta

SQL Server does not setArray: github.com/microsoft/mssql-jdbc/blob/main/src/main/java/com/… – Reputation

Answer 2

Since nobody answer the case for a large IN clause (more than 100) I'll throw my solution to this problem which works nicely for JDBC. In short I replace the IN with a INNER JOIN on a tmp table.

What I do is make what I call a batch ids table and depending on the RDBMS I may make that a tmp table or in memory table.

The table has two columns. One column with the id from the IN Clause and another column with a batch id that I generate on the fly.

SELECT * FROM MYTABLE M INNER JOIN IDTABLE T ON T.MYCOL = M.MYCOL WHERE T.BATCH = ?

Before you select you shove your ids into the table with a given batch id. Then you just replace your original queries IN clause with a INNER JOIN matching on your ids table WHERE batch_id equals your current batch. After your done your delete the entries for you batch.

Overlooker

+1 This will be quite efficient for large data sets, and not break your database – Durrace

Hmm, wouldn't a semi join (IN or EXISTS predicate) possibly outperform the inner join? – Bombsight

@LukasEder YMMV with my SQL pseudo code. As always test/benchmark. It is an interesting idea. Speaking of which I should go look what our actual SQL is for when we do this. – Overlooker

Indeed. On MySQL, semi joins still risk being slower. But inner joins risk being incorrect ;) – Bombsight

@adam Why would we want to use this technique over the technique with passing an array as the value of a single bind variable for in subquery expression? Probably it may help for enormous n when we can't even fit all our values together in the memory of the application that requests the SQL command, but then we would have to split the set into multiple smaller sets anyway and using in with an array would again be sufficient. – Earshot

@Male, when you need to output a full ResultSet from you JDBC-function (with agreed/fixed API for example) that contains all the results, this would be a nice option: fill the temp-table and do a join in-stead-of an IN-in-batches with no way to merge the ResultSet. (Note: when you only have SELECT-rights you will not be able to create the temp-table sadly) – Reneta

@Reneta Thanks!, this is a good example of a situation when this approach is effectively necessary. – Earshot

Answer 3

The standard way to do this is (if you are using Spring JDBC) is to use the org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate class.

Using this class, it is possible to define a List as your SQL parameter and use the NamedParameterJdbcTemplate to replace a named parameter. For example:

public List<MyObject> getDatabaseObjects(List<String> params) {
    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    String sql = "select * from my_table where my_col in (:params)";
    List<MyObject> result = jdbcTemplate.query(sql, Collections.singletonMap("params", params), myRowMapper);
    return result;
}

Unfriendly

Answer 4

I solved this by constructing the SQL string with as many ? as I have values to look for.

SELECT * FROM MYTABLE WHERE MYCOL in (?,?,?,?)

First I searched for an array type I can pass into the statement, but all JDBC array types are vendor specific. So I stayed with the multiple ?.

Pless

That's what we're doing right now, but what I was hoping that there was a uniform way to do it without custom SQL... – Clyve

Also, if it's something like Oracle, it will have to reparse most every statement. – Pardue

Answer 5

I got the answer from docs.spring(19.7.3)

The SQL standard allows for selecting rows based on an expression that includes a variable list of values. A typical example would be select * from T_ACTOR where id in (1, 2, 3). This variable list is not directly supported for prepared statements by the JDBC standard; you cannot declare a variable number of placeholders. You need a number of variations with the desired number of placeholders prepared, or you need to generate the SQL string dynamically once you know how many placeholders are required. The named parameter support provided in the NamedParameterJdbcTemplate and JdbcTemplate takes the latter approach. Pass in the values as a java.util.List of primitive objects. This list will be used to insert the required placeholders and pass in the values during the statement execution.

Hope this can help you.

Wirth

Answer 6

AFAIK, there is no standard support in JDBC for handling Collections as parameters. It would be great if you could just pass in a List and that would be expanded.

Spring's JDBC access supports passing collections as parameters. You could look at how this is done for inspiration on coding this securely.

See Auto-expanding collections as JDBC parameters

(The article first discusses Hibernate, then goes on to discuss JDBC.)

Wigan

Answer 7

See my trial and It success,It is said that the list size has potential limitation. List l = Arrays.asList(new Integer[]{12496,12497,12498,12499}); Map param = Collections.singletonMap("goodsid",l);

    NamedParameterJdbcTemplate  namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());
    String sql = "SELECT bg.goodsid FROM beiker_goods bg WHERE bg.goodsid in(:goodsid)";
    List<Long> list = namedParameterJdbcTemplate.queryForList(sql, param2, Long.class);

Doubleton

Answer 8

sormula makes this simple (see Example 4):

ArrayList<Integer> partNumbers = new ArrayList<Integer>();
partNumbers.add(999);
partNumbers.add(777);
partNumbers.add(1234);

// set up
Database database = new Database(getConnection());
Table<Inventory> inventoryTable = database.getTable(Inventory.class);

// select operation for list "...WHERE PARTNUMBER IN (?, ?, ?)..."
for (Inventory inventory: inventoryTable.
    selectAllWhere("partNumberIn", partNumbers))    
{
    System.out.println(inventory.getPartNumber());
}

Circumsolar

Answer 9

There are different alternative approaches that we can use.

1. Execute Single Queries - slow and not recommended
2. Using Stored Procedure - database specific
3. Creating PreparedStatement Query dynamically - good performance but loose benefits of caching and needs recompilation
4. Using NULL in PreparedStatement Query - I think this is a good approach with optimal performance.

Check more details about these here.

Wasserman

Answer 10

One way i can think of is to use the java.sql.PreparedStatement and a bit of jury rigging

PreparedStatement preparedStmt = conn.prepareStatement("SELECT * FROM MYTABLE WHERE MYCOL in (?)");

... and then ...

preparedStmt.setString(1, [your stringged params]);

http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

Infamy

This will not work as it might be creating a query like ... WHERE MYCOL IN ('2,3,5,6') which is not the one you are trying to do. – Selfsufficient