PreparedStatement with thousands parameters in a IN clause

Question

I did read the solution from this Question PreparedStatement IN clause alternatives?. But in my case, I have about 5000 parameters in a In clause and it would lead to java.sql.SQLException: Prepared or callable statement has more than 2000 parameter markers.

I was using a SQL like

String sql = "select * from project in " + projectIds.toString()

projectIds is a StringBuilder which is like "(1,2,3,4....)" But code security report says that it might lead to a sql injection. So I have to use ? placeholder to avoid it.

I tried to use

String sql = "select * from project where charindex(','+convert(varchar(max),id)+',', ?)>0";
statement.setString(1,projectIds.toString);//projectIds like ",1,2,3,4,"..

But it ends up with an incorrect syntax error.

Is there any solution???

Your sql, *charindex* seems not correct. Suggest you check it again, and rewrite it in another way if you can . — Minus, Mar 14 '13 at 02:48

score 2 · Answer 1 · answered Mar 14 '13 at 02:57

2

Hogan's suggestion to use a table instead is a good one. The only thing I'd change is the query, because JOIN produces a row for every value in tablelist. Instead (guessing at your column names)

select * from project 
where projectID in (select id from tablelist)

or

where exists (select 1 from tablelist where id = projectID)

answered Mar 14 '13 at 02:57

James K. Lowden

7,574
1
16
31

Please see the comment I replied to Hogan. Thx – Wang Wei Mar 14 '13 at 03:00
If databases A and B are on the same server, you could join them if you can arrange for permissions. You could also insert into a global temporary table. Really, the DBAs should help you, not erect stupid obstacles. Worst case, you can pass in a string, loop over it with charindex or something, insert the values in to a table variable, and join. – James K. Lowden Mar 14 '13 at 03:11
Join does not produce a row, it was an inner join... a left join would do so. I did not use a left join.\ – Hogan Mar 14 '13 at 13:30

Hogan · Answer 2 · 2013-03-14T13:34:16.620

The best way to do this is to not use an in statement. Instead you should put all the values you want to check for in a table and use a join.

For example if you had a table with one column (called id) that had the list then your statement would look like this:

 select *
 from project
 join tablelist on project.project = tablelist.id

This will be much faster because SQL servers are quite good at performing joins quickly.

You can also do this with a CTE. For example:

WITH tablelist as
(
   SELECT 1 AS id
   UNION ALL
   SELECT 3
   UNION ALL
   SELECT 4
   UNION ALL
   SELECT 5
   UNION ALL
   SELECT 6
   UNION ALL
   SELECT 7
   // More if needed
)
select *
from project
join tablelist on project.project = tablelist.id

This can be sent as one big query and would work. Your only limit would be max size of a query, I've don't don't know if such a limit exists.

Actually, I don't have the update access to the database. We have 2 databases, A is a database for another App, which we don't have update access, and B is our own database. What I want to do is to retrieve the latest data from database A for those project IDs in database B. Any suggestion?? — Wang Wei, Mar 14 '13 at 02:57

score 1 · Answer 3 · answered Mar 14 '13 at 03:52

1

select * from project in " + projectIds.toString()

is incorrect syntax, it should be something like

select * from project where id in (" + projectIds + ")";

answered Mar 14 '13 at 03:52

Evgeniy Dorofeev

133,369
30
199
275

PreparedStatement with thousands parameters in a IN clause

3 Answers3