It's not a secret anymore!*
The moose likes JDBC and the fly likes select * from ABC where ID in (?) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "select * from ABC where ID in (?)" Watch "select * from ABC where ID in (?)" New topic
Author

select * from ABC where ID in (?)

Vince Hon
Ranch Hand

Joined: Feb 11, 2003
Posts: 117
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:


I don't want to use the following methods:



Vince Hon<br /> <br />SCJP 1.4 | SCWCD | SCBCD <br /><a href="http://vincehon.homeip.net:8000/VJW" target="_blank" rel="nofollow">http://vincehon.homeip.net:8000/VJW</a>
Phillip Koebbe
Greenhorn

Joined: Jun 22, 2005
Posts: 27
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
Rancher

Joined: Mar 06, 2001
Posts: 13459

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
Greenhorn

Joined: Jun 22, 2005
Posts: 27
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
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
So, what's the solutions for that?


kunal
James Boswell
Bartender

Joined: Nov 09, 2011
Posts: 1030
    
    5

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

Joined: Jun 05, 2010
Posts: 622
I have a List of 70 id. How to put that in 'IN' field?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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

Joined: Oct 21, 2008
Posts: 1107

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

Joined: Aug 22, 2010
Posts: 3611
    
  60

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
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: select * from ABC where ID in (?)