NamedParameterJdbcTemplate doesn't work when IN clause more than 100 or 1000

Question

I am using NamedParameterJdbcTemplate to run my query. The query is like:

SELECT id, desc FROM tableA WHERE id IN (:custIds);

Now, I am calling a web service and it is returning a List of ids. So I used NamedParameterJdbcTemplate to map the List of ids to "custIds". However, I got an issue when the List of ids reaches more than 1000. I've read that the DB will not be able to process a IN containing more than 100 or 1000.

As I am restricted to only receiving a List of ids, can you suggest what's best to use aside from NamedParameterJdbcTemplate?

How about creating a temporary table with the ids and using that in the query? — Kayaman, Aug 23 '14 at 14:21
In my experience the limit on WHERE...IN comes from the database, not Spring JDBC. See [How to put more than 1000 values into an Oracle IN clause](http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause) What database are you using? — Paul, Aug 23 '14 at 14:36
Yes, I agree also that the limit in on database. I'm using Oracle. — iPhoneJavaDev, Aug 23 '14 at 14:50

score 4 · Answer 1 · edited May 23 '17 at 10:33

You can't use more than 1000 entries in IN clause. There are few solutions as mentioned below:

Use inner query to solve this issue. You can create a temporary table and use that in your IN clause.

sample query:

select id,desc from table_name where id in (select id from temp_table)

Break it in the batch of 1000 entries using multiple IN clause separated by OR clause.

sample query:

select id,desc from table_name
  where
      id in (1,2,3,...1000)
  or
      id in (1001,1002,1003,...2000)
  or
      id in (2001,2002,...)

Alternately use union all instead of OR clause as mentioned above with queries of 1000 entries in IN clause

sample query:

select id,desc from table_name where id in (1,2,3,4,...,1000)
union all
select id,desc from table_name where id in (1001,1002,...2000)
union all
select id,desc from table_name where id in (2001,2002,...)

NamedParameterJdbcTemplate doesn't work when IN clause more than 100 or 1000

1 Answers1