JPQL - EJB 3 in Action - page 428

The expression will return TRUE when c.categoryName has values such as Recycle
from Garbage , Recycle from Mr. Dumpster , and RecycleMania—the
Hulkster strikes again! .
Suppose you want to retrieve a result set in which a string expression doesn't match a liter-
al. You can use the NOT operator in conjunction with the LIKE operator as in the following
example:
WHERE c.categoryName NOT LIKE '%Recycle%'
The expression will return FALSE when c.categoryName has any values that include
Recycle as any part of the return value, because in this example you used % before and
after the filter string.
In most applications you probably want to supply a parameter for flexibility rather than use
a string literal. You can use positional parameters as shown here to accomplish this:
WHERE c.categoryName NOT LIKE ?1
Here the result set will contain all c.categoryName s that aren't like values bound to
the positional parameter ?1 .
Dealing with null values and empty collections
So far we've been able to avoid discussing null and how an expression deals with null val-
ues. Alas, now it's time to deal with this little mystery. You have to remember that null is
different from an empty string, and JPQL treats them differently. But not all databases treat
an empty string and null differently. You already know that JPQL is translated into SQL by
the persistence provider. If the database returns TRUE when an empty string is compared
with null, you can't rely on consistent results from your queries across two different data-
bases. We recommend that you test this situation with your database.
When a conditional expression encounters a null value, the expression evaluates to null
or UNKNOWN . A complex WHERE clause that combines more than one conditional expres-
sion with a Boolean operator such as AND may produce a result that's unknown. Table 11.4
is a truth table that lists the results of a conditional expression when it's compared with a
null value.