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???

Answer 1

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)