minshengshen (2) [Avatar] Offline
#1
All,
I have a very general question: In Hibernate, how to handle the special characters in the HQL?

The problem I am having is: I am working on a project and using Oracle as the backend data storage and Hibernate as the ORM solution. In the HQL, for some String attributes, we have ' in the String (for example, in the WHERE clause, we have something like where product_name='Bauer's Hibernate in Action book') as the value. The Oracle database sees ' in the Bauer's as the end of the String that causes an QueryException. I can write a filter to convert Bauser's to Bauer\''s. But I think Hibernate must have someway to handle these special characters already. Is there anybody there can point out to how to handle these database related special characters in HQL? Any suggestions will be greatly appreciated!!

Thanks in advance!
cheng.lee (2) [Avatar] Offline
#2
Re: how to escape special characters in HQL
Try using JDBC style positional parameter '?' instead of embedding the apostrophe directly. This way you don't have to deal with escaping this particular character.

Now the question is... how anyone realized how to escape special characters '%' and '_' ? I can write a SQL query like "select this from com.my.Test this where this.name like '!%' ESCAPE '!' " to escape in SQL style but it doesn't work in HQL (unexpected token error).

Any thoughts?
cheng.lee (2) [Avatar] Offline
#3
Re: how to escape special characters in HQL
Sorry, using the HQL "select this from com.my.Test this where this.name like '!%' ESCAPE '!' " works as expected. Got confused by other error.

Anyway... it's 4.26 AM so relax, don't blame at me smilie
nemo_m (1) [Avatar] Offline
#4
Re: how to escape special characters in HQL
Hi ,

I am using hibernate- criteria to get search results . I want to escape _ & % , characters .

I tried using
if(name.indexOf("_")!=-1){
name=name.replaceAll("_","/_");
}

if(name.indexOf("_")!=-1){
name= "'"+name+ "'"+ " ESCAPE '/' ";
...........


myCriteria.add(Expression.ilike("name", name));

but it is not giving me results ....can someone help on this