Ad

JPA - Write A "Reverse" Like Predicate (like Operator Applied On Column Instead Of Inparam) - Oracle DBMS

- 1 answer

I need to write a "reverse" like condition with jpa predicate over oracle DBMS. The example query i need to build is:

select 
    entity.*
from
    entity
where
    upper(?) like '%'||upper(entity.column)||'%'

the upper(?) like '%'||upper(entity.column)||'%' condition work in Oracle, but i haven't found any way to express with JPA predicate.

The working code of a like predicate is like this:

Predicate like = cb.like(cb.upper(entity.get(Entity_.column)), "%" + inputstr.toUpperCase() + "%");

but i need to write a like predicate as below:

Predicate like = cb.like(inputstr.toUpperCase(),"%"+cb.upper(entity.get(Entity_.column))+"%");

I have solved the problem with a namedQuery, but this condition may not be the only to apply. If present i have to add others 2 and condition and i don't want to define 3 namedQuery.

There is a way to express a "reverse" like with a JPA predicate?

Ad

Answer

I solved using instr called through locate, using 2 defined expression, to switch the operators:

Expression<String> tableColumnInUpper = cb.upper(entity.get(Entity_.column));
Expression<String> searchStringUpper = cb.upper(cb.literal(searchString));
predicates.add(cb.gt(cb.locate(searchStringUpper, tableColumnInUpper), 0));

With this code i was able to call INSTR(upper(?),upper(entity.column)) > 0

However, i wasn't able to find a solution to to define the expression I wanted to use, upper(?) like '%'||upper(entity.column)||'%', but are equivalent, so that's okay!

Ad
source: stackoverflow.com
Ad