CODEWITHSUNDEEP
javaoracle-databasehibernatejpaorm
tfm
tfm

Reputation: 63

Hibernate replace string condition

I'm having some trouble to create a query where a necessary condition is to use a like into a field where I need to replace a special character. Something like this:

public List<MyClass> search(Myclass object){

Criteria cri = criteria(MyClass.class);
if(object.getName() != null){
    cri.add(Restrictions.sqlRestriction("replace("+cri.getAlias()+".name,"+object.getSpecialCharacter()+", '') like '"+object.getName()+"'"));  
}
if(dominio.getType()!= null){
    cri.add(Restrictions.eq("type", object.getType()));
}
if(dominio.getWorkspace() != null){
    cri.add(Restrictions.eq("workspace", object.getWorkspace()));
}
if(dominio.getStatus() != null){
cri.add(Restrictions.eq("status", object.getStatus()));
}

return cri.list();

}

With this code I've got the error below:

this_.ID_MYCLASS as ID_MYCLASS1_33_0_,
this_.NM_MYCLASS as NM_MYCLASS4_33_0_,
this_.ID_STATUS as ID_STATU5_33_0_,
this_.ID_TYPE as ID_TYPE_7_33_0_,
this_.ID_WORKSPACE as ID_WORKS8_33_0_ 
from KDTB_MYCLASS this_ 
where replace(this.name,'_', '') like 'COD' 
and this_.ID_WORKSPACE=?

ORA-00904: "THIS"."NAME": invalid identifier

What am I doing wrong?

Thanks in advance!

Upvotes: 3

Views: 5843

Answers (2)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154030

Try this instead:

cri.add(Restrictions.sqlRestriction("replace({alias}.name,"+object.getSpecialCharacter()+", '') like '"+object.getName()+"'"));

Upvotes: 1

Barett
Barett

Reputation: 5948

You could try this as the first parameter of sqlRestriction:

 "replace("+cri.getAlias()+"_.name,"

Ultimately, you're pointing out that HQL doesn't support the replace routine. If you have more trouble, consider ditching Criteria & use a native SQL query for this.

Upvotes: 0

Related Questions