select * from ABC where ID in (?)

[Vince Hon]

I have a query (SELECT * FROM ABC WHERE ID IN (?)) and I want to pass in parameter a list of values (an array or something like that) to put in my condition IN.

So that the result sql is:

int[] a = {1, 2, 3};
SELECT * FROM ABC WHERE ID IN (1, 2, 3) 

I don't want to use the following methods:

PreparedStatement ps = new PreparedStatement("SELECT * FROM ABC WHERE ID IN (?);");
 
for(int x=0;x<a.length;x++) {  
     ps.setInt( a[i] ); 
     ResultSet rs = ps.executeQuery();  
     if(rs.next())   
     {
     }
}  

[Phillip Koebbe]

I haven't tried this, but it should work (theoretically):

Build an "IN" string by concatenating your values together so you end up with "1, 2, 3", then do a pstmt.setString(1, yourString). If my guess is correct, the resulting statment should look like "SELECT * FROM ABC WHERE Field1 IN ('1, 2, 3');".

Just a guess...

Peace,
Phillip

[David O'Meara]

Nice try, but that won't work, it will be treated as a single string and not a list of numbers. You're going to have to dynamically create a string (?, ?, ...) and dynamically bind values, sorry.

[Phillip Koebbe]

Duh. If I'd only looked at my own post, I would have seen that! That's what I get for dynamically building SQL some times and using prepared statements others.

My apologies for producing incorrect suggestions.

Peace,
Phillip

[Kunal Lakhani]

So, what's the solutions for that?

[James Boswell]

Building SQL dynamically is never a good idea from a security point of view. Always, where possible, use prepared statements.

[Kunal Lakhani]

I have a List of 70 id. How to put that in 'IN' field?

[Martin Vajsar]

There is an older, but still up-to-date article on that issue, and a recent thread concerning the same question.

[Wendy Gibbons]

the method doesn't change if you have 5 or 500
but 500 might be terribly slow.

[Martin Vajsar]

Wendy Gibbons wrote:the method doesn't change if you have 5 or 500
but 500 might be terribly slow.

It really depends. If there is no index on the column being searched, and the table is big enough, the two might conceivably take roughly the same amount of time (that is the time to full-scan the table). If there is an index and the two queries return similar number of rows, then again, there might be no difference. If there is an index on that column and the amount of returned rows is proportional to the number of values being searched, then the query performance probably will be different, but not necessarily proportional - if the table is small, the database optimizer could switch to full scan for the query with many parameters.

Even so, reading 500 rows from the database (assuming the query is a simple select and the column(s) being searched on are properly indexed) should not be terribly slow, I'd say. But then again, it depends

[Martin Vajsar]

James Boswell wrote:Building SQL dynamically is never a good idea from a security point of view. Always, where possible, use prepared statements.

Just to clarify, it is possible to build queries dynamically using PreparedStatement. The dangerous thing is stuffing literal values into SQL queries as constants, and that can be done even with PreparedStatement.