이 콘텐츠는 선택한 언어로 제공되지 않습니다.

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

맨 위로 이동

이 콘텐츠는 선택한 언어로 제공되지 않습니다.

15.10. Expressions

자세한 정보

평가판, 구매 및 판매

커뮤니티

Red Hat 문서 정보

보다 포괄적 수용을 위한 오픈 소스 용어 교체

Red Hat 소개

Theme

Red Hat legal and privacy links

Red Hat legal and privacy links