Глава 11. HQL: Язык запросов Hibernate (The
Hibernate Query Language)

Hibernate is equiped with an extremely powerful query language that (quite
intentionally) looks very much like SQL. But don't be fooled by the syntax; HQL
is fully object-oriented, understanding notions like inheritence, polymorphism
and association.

11.1.
Case Sensitivity

Queries are case-insensitive, except for names of Java classes and
properties. So SeLeCT is the same as sELEct is the same as SELECT but net.sf.hibernate.eg.FOO is not net.sf.hibernate.eg.Foo and foo.barSet
is not foo.BARSET.

This manual uses lowercase HQL keywords. Some users find queries with
uppercase keywords more readable, but we find this convention ugly when embedded
in Java code.

11.2. The from
clause

The simplest possible Hibernate query is of the form:

from eg.Cat

which simply returns all instances of the class eg.Cat.

Most of the time, you will need to assign an alias, since you will want to refer to the Cat in other parts of the query.

from eg.Cat as cat

This query assigns the alias cat to Cat instances, so we could use that alias later in the query.
The as keyword is optional; we could also write:

from eg.Cat cat

Multiple classes may appear, resulting in a cartesian product or "cross"
join.

from Formula, Parameter

from Formula as form, Parameter as param

It is considered good practice to name query aliases using an initial
lowercase, consistent with Java naming standards for local variables (eg. domesticCat).

11.3.
Associations and joins

We may also assign aliases to associated entities, or even to elements of a
collection of values, using a join.

from eg.Cat as cat
inner join cat.mate as mate
left outer join cat.kittens as kitten
from eg.Cat as cat left join cat.mate.kittens as kittens
from Formula form full join form.parameter param

The supported join types are borrowed from ANSI SQL

inner join

left outer join

right outer join

full join (not usually useful)

The inner join, left outer join
and right outer join constructs may be abbreviated.

from eg.Cat as cat
join cat.mate as mate
left join cat.kittens as kitten

In addition, a "fetch" join allows associations or collections of values to
be initialized along with their parent objects, using a single select. This is
particularly useful in the case of a collection. It effectively overrides the
outer join and lazy declarations of the mapping file for associations and
collections.

A fetch join does not usually need to assign an alias, because the associated
objects should not be used in the where clause (or any
other clause). Also, the associated objects are not returned directly in the
query results. Instead, they may be accessed via the parent object.

Note that, in the current implementation, only one collection role may be
fetched in a query (everything else would be non-performant). Note also that the
fetch construct may not be used in queries called using
scroll() or iterate(). Finally,
note that full join fetch and right
join fetch are not meaningful.

11.4. The select
clause

The select clause picks which objects and properties
to return in the query result set. Consider:

select mate
from eg.Cat as cat
inner join cat.mate as mate

The query will select mates of other Cats. Actually, you may express this query more compactly as:

select cat.mate from eg.Cat cat

You may even select collection elements, using the special elements function. The following query returns all kittens of
any cat.

select elements(cat.kittens) from eg.Cat cat

Queries may return properties of any value type including properties of
component type:

select cat.name from eg.DomesticCat cat
where cat.name like 'fri%'
select cust.name.firstName from Customer as cust

Queries may return multiple objects and/or properties as an array of type Object[]

11.6.
Polymorphic queries

A query like:

from eg.Cat as cat

returns instances not only of Cat, but also of
subclasses like DomesticCat. Hibernate queries may name
any Java class or interface in the from clause. The query will return instances of all
persistent classes that extend that class or implement the interface. The
following query would return all persistent objects:

from java.lang.Object o

The interface Named might be implemented by various
persistent classes:

from eg.Named n, eg.Named m where n.name = m.name

Note that these last two queries will require more than one SQL SELECT. This means that the order by
clause does not correctly order the whole result set. (It also means you can't
call these queries using Query.scroll().)

11.7. The where
clause

The where clause allows you to narrow the list of
instances returned.

from eg.Cat as cat where cat.name='Fritz'

returns instances of Cat named 'Fritz'.

select foo
from eg.Foo foo, eg.Bar bar
where foo.startDate = bar.date

will return all instances of Foo for which there
exists an instance of bar with a date property equal to the startDate
property of the Foo. Compound path expressions make the
where clause extremely powerful. Consider:

from eg.Cat cat where cat.mate.name is not null

This query translates to an SQL query with a table (inner) join. If you were
to write something like

from eg.Foo foo
where foo.bar.baz.customer.address.city is not null

you would end up with a query that would require four table joins in SQL.

The = operator may be used to compare not only
properties, but also instances:

Likewise, the special property class accesses the
discriminator value of an instance in the case of polymorphic persistence. A
Java class name embedded in the where clause will be translated to its
discriminator value.

from eg.Cat cat where cat.class = eg.DomesticCat

You may also specify properties of components or composite user types (and of
components of components, etc). Never try to use a path-expression that ends in
a property of component type (as opposed to a property of a component). For
example, if store.owner is an entity with a component address

store.owner.address.city // okay
store.owner.address // error!

An "any" type has the special properties id and class, allowing us to express a join in the following way
(where AuditLog.item is a property mapped with <any>).

For indexed collections, you may refer to the minimum and maximum indices
using minIndex and maxIndex.
Similarly, you may refer to the minimum and maximum elements of a collection of
basic type using minElement and maxElement.

from Calendar cal where cal.holidays.maxElement > current date

There are also functional forms (which, unlike the constructs above, are not
case sensitive):

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 eg.Cat as mother, eg.Cat as kit
where kit in elements(foo.kittens)
select p from eg.NameList list, eg.Person p
where p.name = some elements(list.names)
from eg.Cat cat where exists elements(cat.kittens)
from eg.Player p where 3 > all elements(p.scores)
from eg.Show show where 'fizard' in indices(show.acts)

Note that neither the group by clause nor the order by clause may contain arithmetic expressions.

11.11.
Subqueries

For databases that support subselects, Hibernate supports subqueries within
queries. A subquery must be surrounded by parentheses (often by an SQL aggregate
function call). Even correlated subqueries (subqueries that refer to an alias in
the outer query) are allowed.

from eg.Cat as fatcat
where fatcat.weight > (
select avg(cat.weight) from eg.DomesticCat cat
)
from eg.DomesticCat as cat
where cat.name = some (
select name.nickName from eg.Name as name
)
from eg.Cat as cat
where not exists (
from eg.Cat as mate where mate.mate = cat
)
from eg.DomesticCat as cat
where cat.name not in (
select name.nickName from eg.Name as name
)

11.12. HQL
examples

Hibernate queries can be quite powerful and complex. In fact, the power of
the query language is one of Hibernate's main selling points. Here are some
example queries very similar to queries that I used on a recent project. Note
that most queries you will write are much simpler than these!

The following query returns the order id, number of items and total value of
the order for all unpaid orders for a particular customer and given minimum
total value, ordering the results by total value. In determining the prices, it
uses the current catalog. The resulting SQL query, against the ORDER, ORDER_LINE, PRODUCT, CATALOG and PRICE tables has four inner joins and an (uncorrelated)
subselect.

select order.id, sum(price.amount), count(item)
from Order as order
join order.lineItems as item
join item.product as product,
Catalog as catalog
join catalog.prices as price
where order.paid = false
and order.customer = :customer
and price.product = product
and catalog.effectiveDate < sysdate
and catalog.effectiveDate >= all (
select cat.effectiveDate
from Catalog as cat
where cat.effectiveDate < sysdate
)
group by order
having sum(price.amount) > :minAmount
order by sum(price.amount) desc

What a monster! Actually, in real life, I'm not very keen on subqueries, so
my query was really more like this:

select order.id, sum(price.amount), count(item)
from Order as order
join order.lineItems as item
join item.product as product,
Catalog as catalog
join catalog.prices as price
where order.paid = false
and order.customer = :customer
and price.product = product
and catalog = :currentCatalog
group by order
having sum(price.amount) > :minAmount
order by sum(price.amount) desc

The next query counts the number of payments in each status, excluding all
payments in the AWAITING_APPROVAL status where the most
recent status change was made by the current user. It translates to an SQL query
with two inner joins and a correlated subselect against the PAYMENT, PAYMENT_STATUS and PAYMENT_STATUS_CHANGE tables.

If I would have mapped the statusChanges collection as
a list, instead of a set, the query would have been much simpler to write.

select count(payment), status.name
from Payment as payment
join payment.currentStatus as status
where payment.status.name <> PaymentStatus.AWAITING_APPROVAL
or payment.statusChanges[ maxIndex(payment.statusChanges) ].user <> :currentUser
group by status.name, status.sortOrder
order by status.sortOrder

The next query uses the MS SQL Server isNull()
function to return all the accounts and unpaid payments for the organization to
which the current user belongs. It translates to an SQL query with three inner
joins, an outer join and a subselect against the ACCOUNT,
PAYMENT, PAYMENT_STATUS, ACCOUNT_TYPE, ORGANIZATION and ORG_USER tables.

select account, payment
from Account as account
left outer join account.payments as payment
where :currentUser in elements(account.holder.users)
and PaymentStatus.UNPAID = isNull(payment.currentStatus.name, PaymentStatus.UNPAID)
order by account.type.sortOrder, account.accountNumber, payment.dueDate

For some databases, we would need to do away with the (correlated) subselect.