-
Language:
English
-
Language:
English
Red Hat Training
A Red Hat training course is available for JBoss Enterprise Application Platform Common Criteria Certification
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 )