1

I am trying to perform a read operation on my PostgreSQL database. I am using SpringTemplate and I am passing dynamic parameters to it.

"SELECT id, name FROM Student WHERE id IN :id"

Here, id=(1,2,3,4,5...)

What is the maximum number of elements I can pass in ID without breaking my JDBC connection and for the operation to work smoothly?

Shambhavi Rai
  • 321
  • 4
  • 19
  • Have you considered testing it? Rather easy. Start with 1024 or some number ridiculously higher than your actual possible maximum, and binary chop. – user207421 May 17 '21 at 05:47

2 Answers2

2

For the record, I don't think there's any problem on the JdbcTemplate side

"work smoothly" is rather subjective. But I've found that starting with 64 bind values, arrays seem to outperform in lists in PostgreSQL

In any case, there's a hard limit of 32767 parameters per statement in PostgreSQL.

Now, in a lot of cases, your ID list is the result of another query. If that list is not modified manually in the UI (e.g. via a set of check boxes), then why not just repeat that original query and turn your IN predicate into a semi join? E.g.

SELECT id, name 
FROM Student
WHERE id IN (
  SELECT id
  FROM some_other_query
  WHERE some_previous_predicate
)
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
1

Passing in lists of values for IN clause

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 SimpleJdbcTemplate 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.

Note

Be careful when passing in many values. The JDBC standard does not guarantee that you can use more than 100 values for an in expression list. Various databases exceed this number, but they usually have a hard limit for how many values are allowed. Oracle's limit is 1000.

In addition to the primitive values in the value list, you can create a java.util.List of object arrays. This list would support multiple expressions defined for the in clause such as select * from T_ACTOR where (id, last_name) in ((1, 'Johnson'), (2, 'Harrop')). This of course requires that your database supports this syntax.

Please refer this LINK for more details.

Pashyant Srivastava
  • 635
  • 3
  • 17
  • The best solution (for Postgres) would be to pass the whole list as a single JDBC array and change the query to `where = any (:idList)` - not sure if JdbcTemplate can handle that though –  May 17 '21 at 05:58
  • *"This variable list is not directly supported for prepared statements by the JDBC standard"*: While JDBC doesn't support variable lists of bind parameters, JdbcTemplate (which is what the OP is using) does. *"The JDBC standard does not guarantee that you can use more than 100 values for an in expression list"*: Where do you get this arbitrary number 100 from? *"Oracle's limit is 1000."* Oracle's limit is 1000 values in an `IN` list, not 1000 bind parameters. – Lukas Eder May 17 '21 at 07:20
  • Right, the same point i wanted to make. Putting query is something a variable can hold but ultimately Oracle or end database system is the place where it commit. Hence we should design by checking constraints applied by db. – Pashyant Srivastava May 17 '21 at 07:32