• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

select * from ABC where ID in (?)

 
Vince Hon
Ranch Hand
Posts: 117
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:

 
Phillip Koebbe
Greenhorn
Posts: 27
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 27
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 622
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So, what's the solutions for that?
 
James Boswell
Bartender
Posts: 1051
5
Chrome Eclipse IDE Hibernate
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Building SQL dynamically is never a good idea from a security point of view. Always, where possible, use prepared statements.
 
Kunal Lakhani
Ranch Hand
Posts: 622
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a List of 70 id. How to put that in 'IN' field?
 
Martin Vajsar
Sheriff
Pie
Posts: 3747
62
Chrome Netbeans IDE Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There is an older, but still up-to-date article on that issue, and a recent thread concerning the same question.
 
Wendy Gibbons
Bartender
Posts: 1107
Eclipse IDE Oracle VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
the method doesn't change if you have 5 or 500
but 500 might be terribly slow.
 
Martin Vajsar
Sheriff
Pie
Posts: 3747
62
Chrome Netbeans IDE Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 3747
62
Chrome Netbeans IDE Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic