12.4.13. About HQL Predicates

HQL Predicates

Nullness Predicate

Check a value for nullness. Can be applied to basic attribute references, entity references and parameters. HQL additionally allows it to be applied to component/embeddable types.

Example 12.14. Nullness Checking Examples

// select everyone with an associated address
select p
from Person p
where p.address is not null

// select everyone without an associated address
select p
from Person p
where p.address is null

Like Predicate

Performs a like comparison on string values. The syntax is:

like_expression ::=
   string_expression
   [NOT] LIKE pattern_value
   [ESCAPE escape_character]

The semantics follow that of the SQL like expression. The pattern_value is the pattern to attempt to match in the string_expression. Just like SQL, pattern_value can use "_" and "%" as wildcards. The meanings are the same. "_" matches any single character. "%" matches any number of characters.

The optional escape_character is used to specify an escape character used to escape the special meaning of "_" and "%" in the pattern_value. This is useful when needing to search on patterns including either "_" or "%".

Example 12.15. Like Predicate Examples

select p
from Person p
where p.name like '%Schmidt'

select p
from Person p
where p.name not like 'Jingleheimmer%'

// find any with name starting with "sp_"
select sp
from StoredProcedureMetadata sp
where sp.name like 'sp|_%' escape '|'

Between Predicate

Analogous to the SQL BETWEEN expression. Perform a evaluation that a value is within the range of 2 other values. All the operands should have comparable types.

Example 12.16. Between Predicate Examples

select p
from Customer c
    join c.paymentHistory p
where c.id = 123
  and index(p) between 0 and 9

select c
from Customer c
where c.president.dateOfBirth
        between {d '1945-01-01'}
            and {d '1965-01-01'}

select o
from Order o
where o.total between 500 and 5000

select p
from Person p
where p.name between 'A' and 'E'