0

I have a Java service that creates a prepared statement, executes and then uses the resultSet. However, I have the potential scenario where the list of id passed to the select could be anywhere between 1 and 10000+. I have looked at batching the prepared statement. However, what I would really like to do is send the complete set or id to the sql and have it break up the query and return a complete result set.

select col1 from tab1 where id IN (?,?,?...);

Any pointers appreciated!

cbm64
  • 1,059
  • 2
  • 12
  • 24
  • Did you check the performance of such query? If id is unique primary key, query itself should be rather fast – Antoniossss Mar 23 '15 at 13:27
  • If you were using an ORM system such as Hibernate you could create a named query that takes a List of IDs as a named parameter – steven35 Mar 23 '15 at 13:29

2 Answers2

1

Convert the Comma separated values into Rows then insert the distinct rows into a temp table and join the temp table with main table.

To convert CSV to Rows check here

Community
  • 1
  • 1
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
0

It is better to have a table, even a temporary one, containing the ID's you have in the in to join with Tab1. This avoid any possible error due to too many things in the IN, and it is probably better even in term of performance.

Felice Pollano
  • 32,832
  • 9
  • 75
  • 115