7.7. The where clause
The
where
clause allows you to narrow the list of instances returned. If no alias exists, you may refer to properties by name:
select cat from Cat cat where cat.name='Fritz'
returns instances of
Cat
named 'Fritz'.
select foo from Foo foo, 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:
select cat from 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
select foo from 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:
select cat, rival from Cat cat, Cat rival where cat.mate = rival.mate
select cat, mate from Cat cat, Cat mate where cat.mate = mate
The special property (lowercase)
id
may be used to reference the unique identifier of an object. (You may also use its mapped identifier property name.). Note that this keyword is specific to HQL.
select cat from Cat as cat where cat.id = 123 select cat from Cat as cat where cat.mate.id = 69
The second query is efficient. No table join is required!
Properties of composite identifiers may also be used. Suppose
Person
has a composite identifier consisting of country
and medicareNumber
.
select person from bank.Person person where person.id.country = 'AU' and person.id.medicareNumber = 123456
select account from bank.Account account where account.owner.id.country = 'AU' and account.owner.id.medicareNumber = 123456
Once again, the second query requires no table join.
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. Once again, this is specific to HQL.
select cat from Cat cat where cat.class = 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>
). Any
is specific to Hibernate
from AuditLog log, Payment payment where log.item.class = 'Payment' and log.item.id = payment.id
Notice that
log.item.class
and payment.class
would refer to the values of completely different database columns in the above query.