7.8. Expressions

Expressions allowed in the where clause include most of the kind of things you could write in SQL:
  • mathematical operators +, -, *, /
  • binary comparison operators =, >=, <=, <>, !=, like
  • logical operations and, or, not
  • Parentheses ( ), indicating grouping
  • in, not in, between, is null, is not null, is empty, is not empty, member of and not member of
  • "Simple" case, case ... when ... then ... else ... end, and "searched" case, case when ... then ... else ... end (specific to HQL)
  • string concatenation ...||... or concat(...,...) (use concat() for portable EJB-QL queries)
  • current_date(), current_time(), current_timestamp()
  • second(...), minute(...), hour(...), day(...), month(...), year(...), (specific to HQL)
  • Any function or operator defined by EJB-QL 3.0: substring(), trim(), lower(), upper(), length(), locate(), abs(), sqrt(), bit_length()
  • coalesce() and nullif()
  • cast(... as ...), where the second argument is the name of a Hibernate type, and extract(... from ...) if ANSI cast() and extract() is supported by the underlying database
  • Any database-supported SQL scalar function like sign(), trunc(), rtrim(), sin()
  • JDBC IN parameters ?
  • named parameters :name, :start_date, :x1
  • SQL literals 'foo', 69, '1970-01-01 10:00:01.0'
  • Java public static final constants eg.Color.TABBY
in and between may be used as follows: 
select cat from DomesticCat cat where cat.name between 'A' and 'B'
select cat from DomesticCat cat where cat.name in ( 'Foo', 'Bar', 'Baz' )
and the negated forms may be written 
select cat from DomesticCat cat where cat.name not between 'A' and 'B'
select cat from DomesticCat cat where cat.name not in ( 'Foo', 'Bar', 'Baz' )
Likewise, is null and is not null may be used to test for null values.
Booleans may be easily used in expressions by declaring HQL query substitutions in Hibernate configuration: 
hibernate.query.substitutions true 1, false 0
This will replace the keywords true and false with the literals 1 and 0 in the translated SQL from this HQL: 
select cat from Cat cat where cat.alive = true
You may test the size of a collection with the special property size, or the special size() function (HQL specific feature). 
select cat from Cat cat where cat.kittens.size > 0
select cat from Cat cat where size(cat.kittens) > 0
For indexed collections, you may refer to the minimum and maximum indices using minindex and maxindex functions. Similarly, you may refer to the minimum and maximum elements of a collection of basic type using the minelement and maxelement functions. These are HQL specific features. 
select cal from Calendar cal where maxelement(cal.holidays) > current date
select order from Order order where maxindex(order.items) > 100
select order from Order order where minelement(order.items) > 10000
The SQL functions any, some, all, exists, in are supported when passed the element or index set of a collection (elements and indices functions) or the result of a subquery (see below). While subqueries are supported by EJB-QL, elements and indices are specific HQL features. 
select mother from Cat as mother, Cat as kit
where kit in elements(foo.kittens)
select p from NameList list, Person p
where p.name = some elements(list.names)
select cat from Cat cat where exists elements(cat.kittens)
select cat from Player p where 3 > all elements(p.scores)
select cat from Show show where 'fizard' in indices(show.acts)
Note that these constructs - size, elements, indices, minindex, maxindex, minelement, maxelement - may only be used in the where clause in Hibernate.
In HQL, elements of indexed collections (arrays, lists, maps) may be referred to by index (in a where clause only): 
select order from Order order where order.items[0].id = 1234
select person from Person person, Calendar calendar
where calendar.holidays['national day'] = person.birthDay
    and person.nationality.calendar = calendar
select item from Item item, Order order
where order.items[ order.deliveredItemIndices[0] ] = item and order.id = 11
select item from Item item, Order order
where order.items[ maxindex(order.items) ] = item and order.id = 11
The expression inside [] may even be an arithmetic expression. 
select item from Item item, Order order
where order.items[ size(order.items) - 1 ] = item
HQL also provides the built-in index() function, for elements of a one-to-many association or collection of values. 
select item, index(item) from Order order 
    join order.items item
where index(item) < 5
Scalar SQL functions supported by the underlying database may be used 
select cat from DomesticCat cat where upper(cat.name) like 'FRI%'
If you are not yet convinced by all this, think how much longer and less readable the following query would be in SQL: 
select cust
from Product prod,
    Store store
    inner join store.customers cust
where prod.name = 'widget'
    and store.location.name in ( 'Melbourne', 'Sydney' )
    and prod = all elements(cust.currentOrder.lineItems)
Hint: something like 
SELECT cust.name, cust.address, cust.phone, cust.id, cust.current_order
FROM customers cust,
    stores store,
    locations loc,
    store_customers sc,
    product prod
WHERE prod.name = 'widget'
    AND store.loc_id = loc.id
    AND loc.name IN ( 'Melbourne', 'Sydney' )
    AND sc.store_id = store.id
    AND sc.cust_id = cust.id
    AND prod.id = ALL(
        SELECT item.prod_id
        FROM line_items item, orders o
        WHERE item.order_id = o.id
            AND cust.current_order = o.id
    )