What is the jdbctemplate dynamic query parameters limit?

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?

Answer

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
)