Win a copy of Programming with Types this week in the Angular and TypeScript forum
or The Design of Web APIs in the Web Services forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Paul Clapham
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Henry Wong
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Frits Walraven
  • Joe Ess
  • salvin francis

select * from ABC where ID in (?)

 
Ranch Hand
Posts: 117
  • 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:

 
Greenhorn
Posts: 27
  • 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
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • 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
  • 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
 
Ranch Hand
Posts: 622
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So, what's the solutions for that?
 
Bartender
Posts: 1051
5
Hibernate Eclipse IDE Chrome
  • 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
  • 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?
 
Sheriff
Posts: 3838
66
Netbeans IDE Oracle Firefox Browser
  • 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.
 
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • 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 Vashko
Sheriff
Posts: 3838
66
Netbeans IDE Oracle Firefox Browser
  • 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 Vashko
Sheriff
Posts: 3838
66
Netbeans IDE Oracle Firefox Browser
  • 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.
 
Liar, liar, pants on fire! refreshing plug:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!