So my code words for any one word, like "cat" or "dog"
but I would like to be able to use more than one word at a time like "smelly old cat"
I'm very new to jdbc and also SQL so I might be missing something simple.
Thanks for the help
It will work for any one word, but I would like to split the search and search for all words in my search box. "old cat" would search for old and cat from all tables not just the string "old cat". I thought I could use split() but then I don't know where to go.
Tim Rawley wrote:"old cat" would search for old and cat from all tables not just the string "old cat".
Sorry, I still don't understand the requirement. Are you looking for rows in which some column contains the string "old" and the string "cat"? Or are you looking for rows in which some column contains the string "old" or the string "cat"? Or rows in which some column contains the word "old" and another column contains the word "cat"? Or maybe rows in which some column contains the word "old" or some other column contains the word "cat"?
As a programmer you're going to be given instructions which are unclear from time to time. So you need to be able to recognize unclear instructions so you can go back and ask for clarification. What you've provided here is an unclear instruction; one clue which tells you that is that you have no idea how to implement it. Sure, sometimes that's because you need to learn more about something, but often it's because you don't know exactly what's being asked for. In this case it's certainly the latter.
Tim Rawley wrote:... search for all words in my search box. "old cat" would search for old and cat from all tables not just the string "old cat".
I think you mean "search for oldandorcat from all tablescolumns"
I don't know which RDBMS you are using, but MySQL has a REGEXP operator which matches based on regular expressions. If you build a regular expression out of your search words, you could do something like :
SELECT * FROM vipreserial WHERE first REGEXP 'old|cat|this|that|another' OR last REGEXP 'old|cat|this|that|another' OR ...
In addition to the injection attack possibilities, there's also the issue of escaping.
Say you wanted to search for names, and one was:
Your current one would fail, whereas one that used a PreparedStatement and bound the parameters would handle it.
Finally there's also a performance advantage with many DBs.
Databases tend to store plans for common queries, but the mapping is done on the text of the statement itself so:
SELECT columnA from some_table WHERE columnB = 'hardcoded value'
is a different query to:
SELECT columnA from some_table WHERE columnB = 'another hardcoded value'
resulting a individual plans being created, even though they would likely be the same.
With a PreparedStatement the query plan would be:
SELECT columnA from some_table WHERE columnB = ?
so both would use the same plan, saving what can be a lot of time for complicated queries to come up with an effective plan.
They could for example search on a word containing a quote (e.g. Domino's Pizza). And instead of returning all matching rows, you'll get a BadSqlGrammarException being thrown at runtime and you'll be wondering why because it worked for "smelly old cat" When writing queries you should always consider using prepared statements in order to prevent possible SQL injection attacks, to have character escaping out of the box and to have (slightly) better performance (as already explained by Dave). More info about prepared statements can be found here.