spring-jdbc for IN query having more than 1000 ids

Question

I have a query like this -

SELECT * FROM mytable WHERE id IN()

The IN will get list of ids which are more than 1000. So my query fails on Oracle.

One option is that I insert the ids in a temp table and change the above query to join with this new table.

Does spring-jdbc provides anything to solve this? Is there any pattern I can follow to write my DAO?

Answer 1

I would just partition the list of IDs in chunks of 1000, and execute the same query for each chunk. This can be easily encapsulated in a reusable utility method.

Answer 2

Where do the ids come from in the first place? If they can be retrieved from existing tables then it would be easy to construct your sql with a subselect against the tables containing the ids?