CODEWITHSUNDEEP
hibernatehqljpa-2.0jpql
Francesco
Francesco

Reputation: 2382

False translation from hql to sql

In my (hql) query I have the following where-clause:

LOWER( f_umlautfree2( replace(user.lastName, ' ', '')||replace(user.firstName, ' ', '')  ) ) LIKE LOWER ('%'||( f_umlautfree2( replace(replace(:name, ',', ''), ' ', '') ) )||'%')

If I let print the "real" sql query out, it is "translated" this way:

lower(idmanlight.f_umlautfree2((replace(user0_.lastName||' '||'')||replace(user0_.firstName||' '||'')))) like lower(('%'||idmanlight.f_umlautfree2(replace(replace('%Croci%'||','||'')||' '||''))||'%'))

causing an error something like "No function replace(text) exist..."

Why all the , (between the function's brackets) are replaced with ||?

This is the complete hql query:

select
    distinct user.dbId,
    user.lastName,
    user.firstName
from
    User user  LEFT join user.permissions
where
    LOWER( f_umlautfree2( replace(user.lastName, ' ', '')||replace(user.firstName, ' ', '')  ) ) LIKE LOWER ('%'||( f_umlautfree2( replace(replace(:name, ',', ''), ' ', '') ) )||'%')
and
    (user.employee IS NOT NULL OR user.lecturer IS NOT NULL OR user.ethRelated IS NOT NULL OR user.pdbGuest IS NOT NULL)
and
    user.valid = :valid  
order by
    user.lastName, user.firstName asc

and this is the complete resulting sql query:

select
     distinct user0_.dbId as col_0_0_,
     user0_.lastName as col_1_0_,
     user0_.firstName as col_2_0_
from
     idmanlight.NETHZ_USER user0_
left outer join
     idmanlight.NETHZ_USER_PERMISSION permission1_
         on user0_.dbId=permission1_.NETHZ_USER_dbId
left outer join
     idmanlight.PERMISSION permission2_
         on permission1_.permissions_dbId=permission2_.dbId
where
     (
         lower(idmanlight.f_umlautfree2((replace(user0_.lastName||' '||'')||replace(user0_.firstName||' '||'')))) like lower(('%'||idmanlight.f_umlautfree2(replace(replace('%Croci%'||','||'')||' '||''))||'%'))
     )
     and (
         user0_.employee is not null
         or user0_.lecturer is not null
         or user0_.ethRelated is not null
         or user0_.pdbGuest is not null
     )
     and user0_.valid=?
 order by
     user0_.lastName,
     user0_.firstName asc

Upvotes: 3

Views: 1125

Answers (1)

Zsolt Süli
Zsolt Süli

Reputation: 316

HQL does not support replace function. You should register it with Dialect.registerFunction().

(here is an example of how to do it)

Upvotes: 2

Related Questions