• Post Reply Bookmark Topic Watch Topic
  • New Topic

SQL Statement - WHERE x= any item in Array  RSS feed

 
Brent Van Scoy
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

Working on a simple class project with SQL Statements. My statement is suppose to return a value (double price) if an item is listed in an ArrayList ( billItems[] )

I'm not certain is my where statement is correctly working.

Any thoughts are apprciated. Thanks, Brent

 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 66307
152
IntelliJ IDE Java jQuery Mac Mac OS X
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Research the IN operator (and the PreparedStatement).
 
Brent Van Scoy
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okay. Most of what I found was posted questions, not a lot of documentation; probably because of the keywords I was using.

WHERE name IN billItems[]

is the simplest form I found. I will keep reading. Thanks
 
Paul Clapham
Sheriff
Posts: 22835
43
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm confused. Do you really have a column in your database named "billItems[]"? And when you said that it was an ArrayList, that confused me even more because that's a Java construct and your database isn't going to know anything about that. But on the other hand you didn't actually use billItems as a Java construct. Could you un-confuse me by explaining just what you are trying to do?
 
Brent Van Scoy
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry about that, I had myself turned around. I was using an Array (not an arrayList) and I needed to put the column name where I had my array in the for statement. After some more reading, I sorted out those issues, then I was able to use a for loop to to check each row for the condition in
my WHERE statement. Sometimes, I wonder if I"ll ever figure this stuff out!

Thanks for the comments. Brent
 
Stuie Clarky
Ranch Hand
Posts: 98
Eclipse IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could use the loop to create a string containing all your items, then append that to the end of your query string. So instead of having a db call for each item in the array, you could have a single db call using all the columns. So

WHERE name in ('arrayItem1', 'arrayItem2', 'arrayItem3'...)

Also as was suggested, have a look at PreparedStatement, very useful.


Cheers,

S
 
Brent Van Scoy
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This what I came up with. Thanks for the comments

 
Campbell Ritchie
Marshal
Posts: 56576
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It is hazardous to try casting to (String). The + operator is overloaded; you can read about its behaviour and find out that the cast is probably unnecessary.
 
K. Tsang
Bartender
Posts: 3648
16
Firefox Browser Java Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If the name column in the database is indeed a string then why not create the items array as String[] instead of Object[]? That way you can use the enhanced for loop to avoid the casting and all.
 
Brent Van Scoy
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That is a good point. I did not even see that! Thanks.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!