How to use SELECT IN clause in JDBCTemplates?

Question

This is my first experience with JDBCTemplates and I ran into a case where I need to use a query that looks like this:

SELECT * FROM table WHERE field IN (?)

How do I do that? I already tried passing a list/array value but that didn't do the trick, I get an exception. My current code looks like this:

Long id = getJdbcTemplate().queryForLong(query, new Object[]{fieldIds});

Spring Documentation states that there is no way of doing this besides generating the required number of "?" placeholders to match the size of the parameter List. Is there a workaround?

Endicott

The SQL IN clause does not accept a single variable to represent a list of values -- no database does, without using dynamic SQL. – Galactose

I was afraid that would be the case... I mean SELECT IN is as old as SQL itself, WTF!! I'm baffled there is no support for this. – Endicott

Answer 1

I don't think you can do this as a single '?'. It's nothing to do with Spring JDBC templates, it's core SQL.

You'll have to build up a (?, ?, ?) for as many of them as you need.

Willywilly

There is a workaround for this that doesn't require you to dynamically add "?" placeholders to the query. See my answer below. – Endicott

unbelievable that this is the accepted answer. see the upvoted answer below. – Lenette

Answer 2

There is a workaround using NamedParameterJdbcTemplate instead of SimpleJdbcDaoSupport, where you can do something like this:

List integerList = Arrays.asList(new Integer[] {1, 2, 3});
Map<String,Object> params = Collections.singletonMap("fields", integerList);    
Long id = namedParameterJdbcTemplate.queryForLong("SELECT * FROM table WHERE field IN (:fields)", params);

This, however, has a potentially catastrophic limitation regarding the number of parameters you can pass in the list which depends on the DB you are using.

Endicott

It works.catastrophic limitation did not occur in my project. – Halicarnassus

@janwen the limitation has to do with the number of parameters you can pass to a IN clause, usually the limit is 1000, but as I said, this depends on the DB. 1000 is pretty high so on most cases you should be fine, that's probably your case – Endicott

@Endicott is there any way we can increase limit ? – Irenairene

How to use it if I want to have a custom class object list (with RowMapper) as a result ? – Cilo

@Irenairene You do it in a two-step, in the same SQL transaction (really no need for transaction, but same context - i.e. don't close the Connection between): First make a temporary table, and insert your values there. Then you do the select with a one sided join, e.g. left join. Btw, you should probably delete the temp-table in a try-finally to clean up before letting the Connection go back to a DataSource pool or similar. – Greenroom

Answer 3

For long list (ex. Oracle has limitation for 1000 items) you can just separate it to more selects:

List<Long> listIds = Arrays.asList(1L, 2L, ..... , 10000L); // list with ids

String query = "select NOTE from NOTE where ID in (:listIds)";

List<String> noteListResult = new ArrayList<>();

int current = 0;
int iter = 100;

while (current < listIds.size()) {
    Map<String, List<Long>> noteIdsMap = Collections.singletonMap("listIds",
            listIds.subList(current, (current + iter > listIds.size()) ? listIds.size() : current + iter));

    List<String> noteListIter = namedParameterJdbcTemplate.queryForList(query, noteIdsMap, String.class);
    noteListResult.addAll(noteListIter);

    current += iter;
}

return noteListResult;

Household

Answer 4

Please try with MapSqlParameterSource with NamedParameterJdbcTemplate.

MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("array", inputarray);
    NamedParameterJdbcTemplate jdbctemplate = new NamedParameterJdbcTemplate(
            this.jdbcTemplate.getDataSource());

In query

IN (:array)

Verenaverene

Answer 5

There is a way to do it although I don't think it is the most correct. But I leave it here in case it helps someone.

SELECT * FROM table WHERE field IN ("+array.toString()+")

in this way jdbcTemplate reads the query as a complete string and it executes correctly.

Devisor