• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

search with multiple words on multiple tables jdbc

 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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

 
Paul Clapham
Sheriff
Posts: 21565
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't see why your code doesn't work for any string at all. Are you saying that it doesn't? Or are you asking about some other code which you can't work out?

In either case a more detailed description would be helpful for all concerned.
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Thanks for the help.
 
Paul Clapham
Sheriff
Posts: 21565
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ron McLeod
Bartender
Pie
Posts: 1034
65
Android Eclipse IDE Java Linux MySQL Database Redhat
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 old and or cat from all tables columns"

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 ...
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your right I did mean columns, and that code seems to work great.



Now I just need to change the spaces from search to | and i'm all good.
Thank you both for the quick and helpful help
all done!! thanks
 
Junilu Lacar
Bartender
Pie
Posts: 8786
81
Android Eclipse IDE IntelliJ IDE Java Linux Mac Scala Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just so you know, building dynamic queries like that is a big security risk that makes your code extremely vulnerable to SQL injection attacks
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's for a lan only app in a small office, but thanks for the info.

Also could someone really get more info other than what is already being offered?
The query is already pointing to the columns that they are allowed to search, so what more could they do?

Again, thanks for the info.
 
Ron McLeod
Bartender
Pie
Posts: 1034
65
Android Eclipse IDE Java Linux MySQL Database Redhat
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim Rawley wrote:... so what more could they do?


https://xkcd.com/327/
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In addition to the injection attack possibilities, there's also the issue of escaping.
Say you wanted to search for names, and one was:
O'Reilly

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.

So it's not just about security.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim Rawley wrote:so what more could they do?

Famous last words...

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.

Hope it helps!
Kind regards,
Roel
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic