• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

search with multiple words on multiple tables jdbc

 
Ranch Hand
Posts: 46
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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

 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 46
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Marshal
Posts: 4491
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 46
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Sheriff
Posts: 17644
300
Mac Android IntelliJ IDE Eclipse IDE Spring Debian Java Ubuntu Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 46
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 4491
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Rawley wrote:... so what more could they do?



https://xkcd.com/327/
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic