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
andnot member of
- "Simple" case,
case ... when ... then ... else ... end
, and "searched" case,case when ... then ... else ... end (specific to HQL)
- string concatenation
...||...
orconcat(...,...) (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()
andnullif()
cast(... as ...)
, where the second argument is the name of a Hibernate type, andextract(... from ...)
if ANSIcast()
andextract()
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
constantseg.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 )