When we use to Expressions that used in the where clause include the following:
1. mathematical operators: +, -, *, /
2. binary comparison operators: =, >=, <=, <>, !=, like
3. logical operations and, or, not
4. Parentheses ( ) that indicates grouping
5. in, not in, between, is null, is not null, is empty, is not empty, member of and not member of
6. "Simple" case, case ... when ... then ... else ... end, and "searched" case, case when ... then ... else ... end
7. string concatenation ...||... or concat(...,...)
8. current_date(), current_time(), and current_timestamp()
9. second(...), minute(...), hour(...), day(...), month(...), and year(...)
10. Any function or operator defined by EJB-QL 3.0: substring(), trim(), lower(), upper(), length(), locate(), abs(), sqrt(), bit_length(), mod()
11. coalesce() and nullif()
12. str() for converting numeric or temporal values to a readable string
13. 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
14. the HQL index() function, that applies to aliases of a joined indexed collection
15. HQL functions that take collection-valued path expressions: size(), minelement(), maxelement(), minindex(), maxindex(), along with the special elements() and indices functions that can be quantified using some, all, exists, any, in.
16. Any database-supported SQL scalar function like sign(), trunc(), rtrim(), and sin()
17. JDBC-style positional parameters ?
18. named parameters :name, :start_date, and :x1
19. SQL literals 'foo', 69, 6.66E+2, '1970-01-01 10:00:01.0'
20. Java public static final constants eg.Color.TABBY
We can use the given above property as follows:
from DomesticStudent student where student.name between 'A' and 'B'
from DomesticStudent student where student.name in ( 'Foo', 'Bar', 'Baz' )
The negated forms can be written as follows:
from DomesticStudent student where student.name not between 'A' and 'B'
from DomesticStudent student where cat.name not in ( 'Foo', 'Bar', 'Baz' )
Similarly, is null and is not null can be used to test for null values. Booleans can be easily used in expressions by declaring HQL query substitutions in Hibernate configuration:
This will replace the keywords true and false with the literals 1 and 0 in the translated SQL from this HQL:
from Student student where student.alive = true
You can test the size of a collection with the special property size or the special size() function.
from Student student where student.kittens.size > 0
from Student student where size(student.kittens) > 0
For indexed collections, you can refer to the minimum and maximum indices using minindex and maxindex functions. Similarly, you can refer to the minimum and maximum elements of a collection of basic type using the minelement and maxelement functions.
For example:
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 Student as boy, Student as kit where kit in elements(zoo.kittens)
select p from NameList list, Person p where p.name = some elements(list.names)
from Student student 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 - can only be used in the where clause in Hibernate3. Elements of indexed collections (arrays, lists, and maps) can 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 [] can 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 can be used:
from DomesticStudent student where upper(student.name) like 'FRI%'
Consider 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)
We can use above property like as we given below:
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
)