Home
Prodotti
Red Hat JBoss Enterprise Application Platform
5
Hibernate Core Reference Guide
14.10. Expressions

Questo contenuto non è disponibile nella lingua selezionata.

14.10. Expressions

Expressions used in the where clause include the following:

mathematical operators: +, -, *, /
binary comparison operators: =, >=, <=, <>, !=, like
logical operations and, or, not
Parentheses ( ) that indicates 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(), and current_timestamp()
second(...), minute(...), hour(...), day(...), month(...), and 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 that can be quantified using some, all, exists, any, in.
Any database-supported SQL scalar function like sign(), trunc(), rtrim(), and sin()
JDBC-style positional parameters ?
named parameters :name, :start_date, and :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 can be used as follows:

from DomesticCat cat where cat.name between 'A' and 'B'

from DomesticCat cat where cat.name between 'A' and 'B'

Copy to Clipboard

Toggle word wrap

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

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

Copy to Clipboard

Toggle word wrap

The negated forms can be written as follows:

from DomesticCat cat where cat.name not between 'A' and 'B'

from DomesticCat cat where cat.name not between 'A' and 'B'

Copy to Clipboard

Toggle word wrap

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

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

Copy to Clipboard

Toggle word wrap

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:

<property name="hibernate.query.substitutions">true 1, false 0</property>

<property name="hibernate.query.substitutions">true 1, false 0</property>

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:

from Cat cat where cat.alive = true

from Cat cat where cat.alive = true

Copy to Clipboard

Toggle word wrap

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

Copy to Clipboard

Toggle word wrap

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

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

Copy to Clipboard

Toggle word wrap

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

Copy to Clipboard

Toggle word wrap

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

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

Copy to Clipboard

Toggle word wrap

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

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):

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

from Cat cat where exists elements(cat.kittens)

from Cat cat where exists elements(cat.kittens)

Copy to Clipboard

Toggle word wrap

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

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

Copy to Clipboard

Toggle word wrap

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

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 - 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

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 [] can 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 can be used:

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

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

Copy to Clipboard

Toggle word wrap

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)

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

Torna in cima

Questo contenuto non è disponibile nella lingua selezionata.

14.10. Expressions

Formazione

Prova, acquista e vendi

Community

Informazioni sulla documentazione di Red Hat

Rendiamo l’open source più inclusivo

Informazioni su Red Hat

Theme

Red Hat legal and privacy links

Red Hat legal and privacy links