• 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
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

hibernate mysql "Like"

 
Ranch Hand
Posts: 101
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi all,

I am having problems with issuing a HQL search query which has a "like" clause in it...the database that i am using is MySQL 5.0

for example my HQL is something like



I have highlighted the areas due to which the error is coming up.. i get some errors saying that the '%' + ? + '%' cannot be recognised , and that it is not correct MySQL syntax,,, when i investigated, i found out that mysql does not allow concatenation of strings to be done with '+' ,,

then i did something like LIKE '%?%' , but that also didnt work ,,

would appreciate if some one call tell a possible workaround for this,, basically i want to get thru this SQL query which uses "like"

Thanks
[ January 18, 2007: Message edited by: Mark Spritzler ]
 
ranger
Posts: 17346
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why are you using single quotes?

It is not recognizing single quotes.

you need double quotes and you need to escape your double quotes with the "/"

so

f.folderName LIKE '%' + ? + '%'

would be

f.folderName LIKE /"%" + :someBindVariableName + "%/"

Also your code will be cleaner and easier to read and maintain if you use bind parameters instead of "?" positional parameters.

Mark
 
s mahen perera
Ranch Hand
Posts: 101
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks,,

i tried it out,,
but, i am getting this error now..

11:09:56,361 ERROR PARSER:35 - *** ERROR: line 1:196: unexpected token: /

i didnt use bind names though,, used the same old ? s as positional parameters

Thanks
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Looks like you've just made a mistake with your syntax. Check it carefully. If you can't see the problem, post your query here and we'll have a look.
 
s mahen perera
Ranch Hand
Posts: 101
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi,
thanks,,

As Mark suggested,

i replaced the like sections with something like this

f.folderName LIKE /"%" + ? + "%/"

and then i am getting the above error i mentioned..
 
Mark Spritzler
ranger
Posts: 17346
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
since you have the query string posted we don't see the way it is constructed with your " and method calls, and while my method will work, it is still tough to determine when " in the variable assignment versus " in the query string sent to the database.



That is your query set to a string and escaping the double quotes where needed. I also took out you "?" and put in bind parameters instead. If you really had to use "?" which is a real pain to read and maintain, then take out the :bindName and replace it with ? do not put the ? with concatenating plus signs.

Mark
 
Everybody! Do the Funky Monkey! Like this tiny ad!
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic