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
-
string concatenation ...||...
or concat(...,...)
-
current_date()
, current_time()
, current_timestamp()
-
second(...)
, minute(...)
, hour(...)
, day(...)
, month(...)
, year(...)
,
-
Any function or operator defined by EJB-QL 3.0: substring(), trim(), lower(), upper(), length(), locate(), abs(), sqrt(), bit_length(), mod()
-
coalesce()
and nullif()
-
str()
for converting numeric or temporal values to a readable string
-
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
-
the HQL index()
function, that applies to aliases of a joined indexed collection
-
HQL functions that take collection-valued path expressions: size(), minelement(), maxelement(), minindex(), maxindex()
, along with the special elements()
and indices
functions which may be quantified using some, all, exists, any, in
.
-
Any database-supported SQL scalar function like sign()
, trunc()
, rtrim()
, sin()
-
JDBC-style positional parameters ?
-
named parameters :name
, :start_date
, :x1
-
SQL literals 'foo'
, 69
, 6.66E+2
, '1970-01-01 10:00:01.0'
-
Java public static final
constants eg.Color.TABBY
in
and between
may be used as follows:
from DomesticCat cat where cat.name between 'A' and 'B'
from DomesticCat cat where cat.name in ( 'Foo', 'Bar', 'Baz' )
and the negated forms may be written
from DomesticCat cat where cat.name not between 'A' and 'B'
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:
<property name="hibernate.query.substitutions">true 1, false 0</property>
This will replace the keywords true
and false
with the literals 1
and 0
in the translated SQL from this HQL:
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.
from Cat cat where cat.kittens.size > 0
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.
from Calendar cal where maxelement(cal.holidays) > current_date
from Order order where maxindex(order.items) > 100
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).
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)
from Cat cat where exists elements(cat.kittens)
from Player p where 3 > all elements(p.scores)
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 Hibernate3.
Elements of indexed collections (arrays, lists, maps) may be referred to by index (in a where clause only):
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
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
)