jdbc template failsafe IN statement

Question

Is there a way to get multiple rows using Spring's jdbc template that is fail safe? Something like in this post: https://stackoverflow.com/a/1327222/1321514

Set<Integer> ids = ...;

MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("ids", ids);

List<Foo> foo = getJdbcTemplate().query("SELECT * FROM foo WHERE a IN (:ids)",
     getRowMapper(), parameters);

But being able to return all the ids regardless the DB's limit for the number of parameters. So if the DB can only accept 2000 parameters within the IN clause and I'm passing 2001 I get the 2001 records.

score 2 · Answer 1 · answered Sep 17 '15 at 15:18

2

You can do this with the help of temporary tables wherein first you need to create it n then dump all values IN values on it. Later you need to use that table in a join or sub-query to associate with main table where you wanted your IN caluse initially. Complete JDBCTemplate code can be found here http://forum.spring.io/forum/spring-projects/data/63634-spring-jdbc-and-sybase-temp-tables

answered Sep 17 '15 at 15:18

Avis

2,197
18
28

Thanks, I wanted to avoid using temporary tables, but I guess the only other option is to do it in batches, selecting 500 ids or so each time and merging them together. – pablo Sep 17 '15 at 15:38
That's correct either temporary table or in batches. – Avis Sep 17 '15 at 15:42

score 0 · Accepted Answer · answered Sep 17 '15 at 16:09

You can do it by breaking up the parameters list into smaller batches of around 100 or maybe more. I remember that I had written a similar code where I was creating a dynamic select query based on the batch count and passing a subset of the total parameters. For example, In case of 2001 records my function would have created 21 queries such as SELECT * FROM <table> WHERE <column> IN (?,..<batchSize>..,?).

score 0 · Answer 3 · answered Sep 17 '15 at 16:14

I am (quite radical) user of parameters (in query), but series of int's in clause "in" I concatenate as string (in Java using StringBuilder of-course), so final query string is:

"SELECT * FROM foo WHERE a IN (215, 567, 957, 1298)"

To be clear: IN based on enumeration, not relation to slave table, this is different story

Limit of query string length is quite big (different in databases? probably yes)

jdbc template failsafe IN statement

3 Answers3