Hi. Is there any way in Hibernate 3 to use a unnamed parameter in a "like '%foo%'" part of a HQL where clause?

Example:



If i try that exact syntax via the Spring HibernateTemplate i get this exception:

You should pass the "%"s in with the positional parameter. Hibernate isn't recognizing your '?' as a positional parameter marker because it is in quotes.

Do this:


And then set the positional parameter in the normal way but with %'s added to the beginning and end.[/quote]

Or this seems to work for me:



I'm using named parameters, but that shouldn't make a difference.

Good ideas - both work in my test code. Thanks.

ok... This work fine, except if I inject quote in the positional parameter.
How can I resolve it?!

ty

public SearchBean myMethod(String search){
...
}
where "search" = Mac'Donalds

Its don't works...
The solution is use Criteria, buts its really so difficult where we have a lot of relationship...

Before passing the 'search' information to the Hibernate you could have an utility method that precedes the single quotes with backslash character.

For example Mac'Donalds becomes Mac\'Donalds. This worked for me, hopefully it should also work for you.

How do you "bind" the value [Mac'Donalds] into the query?

Using parameters? If so, Hibernate simply passes those values onto the jdbc PreparedStatement as-is and it is actually the resposnibility of the jdbc driver to ensure that parameter bind values are properly escaped. If that is not the case, then you have a very buggy jdbc driver.

If you do not really "bind" the value, and instead just use string concatenation to "inject" the valiue into the query string, then yes, you'd need to manually escape it first.

To be clear, the following will work:
session.createQuery( "from Eatery e where e.corporateEntity like ?" )
.setString( 1, "%Mac'Donalds%" )
.list();

Steve, you are true. The jdbc driver was escaping respective information and I dont need to do specifically.

I have a question regarding the numbers used to bind positional parameters. Will it start from 0 or 1. In your above query you have been using

setString( 1, "Mac'Donalds" );

When I use '1' instead of '0', was getting the following exception.



But when I use setString( 0, "Mac'Donalds" ) everything is working fine. I am confused especially with the exception message is conflicting to the number I used to bind parameters.

-----
an update - I was using setParameter( 1, "Mac'Donalds" );

and when I use setString( 1, "Mac'Donalds" ) the exception is as follows

no, that was a copy/paste error; it should be setString( 0, ... )

Thanks for clearing my confusion.