This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
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');".
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
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.