JPA/Criteria API - NotLike

user_6927542 0
Viewed 393

I want to use Predicate notLike(Expression x, String pattern) to replace the following query:

select * from mytable
where mytable.myParameter not in ('ABC%','XYZ');

But when I execute my query using the following predicate:

Predicate notLike = builder.notLike(fromMyTable.get(MyTable_.myParameter), "ABC%|BCD");

is not working.

But if I create 2 predicates for the both strings: "ABC%" and "XYZ" is working.

Is my pattern written wrong or where is the problem? I tried to change the pattern in many ways but is still not working.

Thank you!

jpa-2.0 predicate oracle
1 Answers

In SQL A not in (X,Y,Z) predicate is a shortcut for the following condition:

A <> X AND A <> Y AND A <> Z

this condition uses "not equal" operators, which compare operands for exact unequality.
It is not ossible to use IN or NOT IN operators for pattern-matching.
You must rewrite your query using LIKE operator which supports pattern matching:

select * from mytable
where mytable.myParameter not in ('XYZ')
  and mytable.myParameter not LIKE 'ABC%';

or

select * from mytable
where mytable.myParameter <> 'XYZ'
  and mytable.myParameter not LIKE 'ABC%';
user_25683410
Related