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
    )