This week's giveaway is in the JDBC forum.
We're giving away four copies of Java Database Connections & Transactions (e-book only) and have Marco Behler on-line!
See this thread for details.
Win a copy of Java Database Connections & Transactions (e-book only) this week in the JDBC 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
  • Devaka Cooray
  • Knute Snortum
  • Paul Clapham
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Frits Walraven
Bartenders:
  • Ganesh Patekar
  • Tim Holloway
  • salvin francis

Using multiple criteria with Where/IN clause in Sqlite  RSS feed

 
Ranch Hand
Posts: 39
1
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have spent the past few days working with Sqlite and I am able to successfully insert and update records and pull records from my test DB.   But I'm having a hard time pulling back records when multiple criteria are needed.

below you can see an example of what I'm trying to do but no matter how I build the list of values to replace the variable in the SQL string the query fails.   If I manually run the same query in my DB browser I get back results.  I assume there is a trick to chaining multiple values in Sqlite statements?

An example query I was running is SELECT * from ChannelData WHERE group IN ('HBO', 'SHOWTIME', 'STARZ')

Any suggestions would be greatly appreciated.

 
author & internet detective
Posts: 39286
741
Eclipse IDE Java VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Josh,
Good question. The ? can only be used to replace a single value. So the query you built is trying to match the list values including commas - not the intent. I wrote a Journal article with the options for dealing with this problem. The article is 10 years old, but it works exactly the same way today.
 
Josh Herron
Ranch Hand
Posts: 39
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Great!  Thank you.
 
Bartender
Posts: 1868
81
Android Chrome IntelliJ IDE Java MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I know that you are just starting out, but here is a quick note to keep in mind.

It is generally slower to do a select * from then to do a select field1, field2, field3 from.
Yes we could be talking about milliseconds or nanoseconds, but they do add up.
The reason is is slower is because first the database engine needs to query to get all of the field names.
Once that is done then the select statement is run with the * being replaced with the field names.
Doing a select * also retrieves all of the fields and some times you only need two or three of the fields so why select them all only to not use all of them?

Again, this may not be too big of a concern, however you should be aware of this when you get into executing many selects.
 
Josh Herron
Ranch Hand
Posts: 39
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Pete, I was just simplifying the select statement for troubleshooting the IN statement.  

I planned to refine to only select the 5 columns I actually need once I get the multiple IN logic working.   I have found in this journey that I'm most successful if I keep it simple in the beginning and then build from there.    Once I get some experience hopefully I can stop doing that and just do it right the first time.   LOL
 
Pete Letkeman
Bartender
Posts: 1868
81
Android Chrome IntelliJ IDE Java MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Josh Herron wrote:I have found in this journey that I'm most successful if I keep it simple in the beginning and then build from there.


I agree 100% with this.
And like I said, I know that you are a beginner.

Some people who have been doing SQL related operations/queries for years still do select * in their code.
And when I'm working directly with the database console I do select * more often then not when creating the query.

Josh Herron wrote:Once I get some experience hopefully I can stop doing that and just do it right the first time..


Right the first time, or wrong the first time, that does not matter that much. What and where it matters is if production code has it wrong or inefficient.
At least that is how I see it.
 
Pete Letkeman
Bartender
Posts: 1868
81
Android Chrome IntelliJ IDE Java MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well I guess, if you continue to do it incorrectly or inefficiently then you could be wasting time and resources so this could matter in a non production environment as well, just not that much as in a production environment.
 
Jeanne Boyarsky
author & internet detective
Posts: 39286
741
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Pete Letkeman wrote:Yes we could be talking about milliseconds or nanoseconds, but they do add up.
The reason is is slower is because first the database engine needs to query to get all of the field names.


If the database is on a different server, it is way more than milliseconds because the extra fields need to be transferred across the network. That adds up to real time!
 
I have gone to look for myself. If I should return before I get back, keep me here with this tiny ad:
how do I do my own kindle-like thing - without amazon
https://coderanch.com/t/711421/engineering/kindle-amazon
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!