Este contenido no está disponible en el idioma seleccionado.

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 between 'A' and 'B'

Copy to Clipboard

Toggle word wrap

select cat from DomesticCat cat where cat.name in ( 'Foo', 'Bar', 'Baz' )

select cat from DomesticCat cat where cat.name in ( 'Foo', 'Bar', 'Baz' )

Copy to Clipboard

Toggle word wrap

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 between 'A' and 'B'

Copy to Clipboard

Toggle word wrap

select cat from DomesticCat cat where cat.name not in ( 'Foo', 'Bar', 'Baz' )

select cat from DomesticCat cat where cat.name not in ( 'Foo', 'Bar', 'Baz' )

Copy to Clipboard

Toggle word wrap

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

hibernate.query.substitutions true 1, false 0

Copy to Clipboard

Toggle word wrap

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

select cat from Cat cat where cat.alive = true

Copy to Clipboard

Toggle word wrap

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 cat.kittens.size > 0

Copy to Clipboard

Toggle word wrap

select cat from Cat cat where size(cat.kittens) > 0

select cat from Cat cat where size(cat.kittens) > 0

Copy to Clipboard

Toggle word wrap

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 cal from Calendar cal where maxelement(cal.holidays) > current date

Copy to Clipboard

Toggle word wrap

select order from Order order where maxindex(order.items) > 100

select order from Order order where maxindex(order.items) > 100

Copy to Clipboard

Toggle word wrap

select order from Order order where minelement(order.items) > 10000

select order from Order order where minelement(order.items) > 10000

Copy to Clipboard

Toggle word wrap

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 mother from Cat as mother, Cat as kit
where kit in elements(foo.kittens)

Copy to Clipboard

Toggle word wrap

select p from NameList list, Person p
where p.name = some elements(list.names)

select p from NameList list, Person p
where p.name = some elements(list.names)

Copy to Clipboard

Toggle word wrap

select cat from Cat cat where exists elements(cat.kittens)

select cat from Cat cat where exists elements(cat.kittens)

Copy to Clipboard

Toggle word wrap

select cat from Player p where 3 > all elements(p.scores)

select cat from Player p where 3 > all elements(p.scores)

Copy to Clipboard

Toggle word wrap

select cat from Show show where 'fizard' in indices(show.acts)

select cat from Show show where 'fizard' in indices(show.acts)

Copy to Clipboard

Toggle word wrap

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 order from Order order where order.items[0].id = 1234

Copy to Clipboard

Toggle word wrap

select person from Person person, Calendar calendar
where calendar.holidays['national day'] = person.birthDay
    and person.nationality.calendar = calendar

select person from Person person, Calendar calendar
where calendar.holidays['national day'] = person.birthDay
    and person.nationality.calendar = calendar

Copy to Clipboard

Toggle word wrap

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[ order.deliveredItemIndices[0] ] = item and order.id = 11

Copy to Clipboard

Toggle word wrap

select item from Item item, Order order
where order.items[ maxindex(order.items) ] = item and order.id = 11

select item from Item item, Order order
where order.items[ maxindex(order.items) ] = item and order.id = 11

Copy to Clipboard

Toggle word wrap

The expression inside [] may even be an arithmetic expression.

select item from Item item, Order order
where order.items[ size(order.items) - 1 ] = item

select item from Item item, Order order
where order.items[ size(order.items) - 1 ] = item

Copy to Clipboard

Toggle word wrap

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

select item, index(item) from Order order 
    join order.items item
where index(item) < 5

Copy to Clipboard

Toggle word wrap

Scalar SQL functions supported by the underlying database may be used

select cat from DomesticCat cat where upper(cat.name) like 'FRI%'

select cat from DomesticCat cat where upper(cat.name) like 'FRI%'

Copy to Clipboard

Toggle word wrap

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)

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)

Copy to Clipboard

Toggle word wrap

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
    )

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
    )

Copy to Clipboard

Toggle word wrap

Este contenido no está disponible en el idioma seleccionado.

7.8. Expressions

Aprender

Pruebe, compre y venda

Comunidades

Acerca de la documentación de Red Hat

Hacer que el código abierto sea más inclusivo

Acerca de Red Hat

Theme

Red Hat legal and privacy links

Red Hat legal and privacy links