Hibernate.orgCommunity Documentation
The Java Persistence Query Language (JP-QL) has been heavily inspired by HQL, the native Hibernate Query Language. Both are therefore very close to SQL, but portable and independent of the database schema. People familiar with HQL shouldn't have any problem using JP-QL. In fact HQL is a strict superset of JP-QL and you use the same query API for both types of queries. Portable JPA applications however should stick to JP-QL.
For a type-safe approach to query, we highly recommend you to use the Criteria query, see Chapter 9, Criteria Queries.
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
org.hibernate.eg.FOO
is not
org.hibernate.eg.Foo
and foo.barSet
is not foo.BARSET
.
This manual uses lowercase JP-QL keywords. Some users find queries with uppercase keywords more readable, but we find this convention ugly when embedded in Java code.
The simplest possible JP-QL query is of the form:
select c from eg.Cat c
which simply returns all instances of the class
eg.Cat
. Unlike HQL, the select clause is not optional
in JP-QL. We don't usually need to qualify the class name, since the
entity name defaults to the unqualified class name
(@Entity
). So we almost always just write:
select c from Cat c
As you may have noticed you can assign aliases to classes, the
as
keywork is optional. An alias allows you to refer to
Cat
in other parts of the query.
select cat from Cat as cat
Multiple classes may appear, resulting in a cartesian product or "cross" join.
select formula, parameter from Formula as formula, Parameter as parameter
It is considered good practice to name query aliases using an
initial lowercase, consistent with Java naming standards for local
variables (eg. domesticCat
).
You may also assign aliases to associated entities, or even to
elements of a collection of values, using a
join
.
select cat, mate, kitten from Cat as cat inner join cat.mate as mate left outer join cat.kittens as kitten
select cat from Cat as cat left join cat.mate.kittens as kittens
The supported join types are borrowed from ANSI SQL
inner join
left outer join
The inner join
, left outer
join
constructs may be abbreviated.
select cat, mate, kitten from 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 fetching options in the associations and collection mapping metadata. See the Performance chapter of the Hibernate reference guide for more information.
select cat from Cat as cat inner join fetch cat.mate left join fetch cat.kittens
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. The only reason we might need an alias is if we are
recursively join fetching a further collection:
select cat from Cat as cat inner join fetch cat.mate left join fetch cat.kittens child left join fetch child.kittens
Note that the fetch
construct may not be used in
queries called using scroll()
or
iterate()
. Nor should fetch
be used
together with setMaxResults()
or
setFirstResult()
. It is possible to create a cartesian
product by join fetching more than one collection in a query (as in the
example above), be careful the result of this product isn't bigger than
you expect. Join fetching multiple collection roles gives unexpected
results for bag mappings as it is impossible for Hibernate to
differentiate legit duplicates of a given bag from artificial duplicates
created by the multi-table cartesian product.
If you are using property-level lazy fetching (with bytecode
instrumentation), it is possible to force Hibernate to fetch the lazy
properties immediately (in the first query) using fetch all
properties
. This is Hibernate specific option:
select doc from Document doc fetch all properties order by doc.name
select doc from Document doc fetch all properties where lower(doc.name) like '%cats%'
The select
clause picks which objects and
properties to return in the query result set. Consider:
select mate from Cat as cat inner join cat.mate as mate
The query will select mate
s of other
Cat
s. Actually, you may express this query more
compactly as:
select cat.mate from Cat cat
Queries may return properties of any value type including properties of component type:
select cat.name from 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[]
,
select mother, offspr, mate.name from DomesticCat as mother inner join mother.mate as mate left outer join mother.kittens as offspr
or as a List
(HQL specific feature)
select new list(mother, offspr, mate.name) from DomesticCat as mother inner join mother.mate as mate left outer join mother.kittens as offspr
or as an actual type-safe Java object (often called a view object),
select new Family(mother, mate, offspr) from DomesticCat as mother join mother.mate as mate left join mother.kittens as offspr
assuming that the class Family
has an appropriate
constructor.
You may assign aliases to selected expressions using
as
:
select max(bodyWeight) as max, min(bodyWeight) as min, count(*) as n from Cat cat
This is most useful when used together with select new
map
(HQL specific feature):
select new map( max(bodyWeight) as max, min(bodyWeight) as min, count(*) as n ) from Cat cat
This query returns a Map
from aliases to selected
values.
HQL queries may even return the results of aggregate functions on properties:
select avg(cat.weight), sum(cat.weight), max(cat.weight), count(cat) from Cat cat
The supported aggregate functions are
avg(...), avg(distinct ...), sum(...), sum(distinct
...), min(...), max(...)
count(*)
count(...), count(distinct ...),
count(all...)
You may use arithmetic operators, concatenation, and recognized SQL functions in the select clause (depending on configured dialect, HQL specific feature):
select cat.weight + sum(kitten.weight) from Cat cat join cat.kittens kitten group by cat.id, cat.weight
select firstName||' '||initial||' '||upper(lastName) from Person
The distinct
and all
keywords
may be used and have the same semantics as in SQL.
select distinct cat.name from Cat cat select count(distinct cat.name), count(cat) from Cat cat
A query like:
select cat from 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 (portable JP-QL queries should only name
mapped entities). 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 // HQL only
The interface Named
might be implemented by
various persistent classes:
from Named n, Named m where n.name = m.name // HQL only
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()
.)
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 identifer 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.
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
exists
, all
,
any
, some
(taking
subqueries)
"Simple" case, case ... when ... then ... else ...
end
, and "searched" case, case when ... then ...
else ... end
string concatenation ...||...
or
concat(...,...) (use concat() for portable JP-QL
queries)
current_date()
,
current_time()
,
current_timestamp()
second(...)
, minute(...)
,
hour(...)
, day(...)
,
month(...)
, year(...)
, (specific
to HQL)
Any function or operator: substring(), trim(), lower(),
upper(), length(), locate(), abs(), sqrt(),
bit_length()
coalesce()
and
nullif()
TYPE ... in ...
, where the first argument is
an identifier variable and the second argument is the subclass to
restrict polymorphism to (or a list of subclasses surrounded by
parenthesis)
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'
JDBC escape syntax for dates (dependent on your JDBC driver
support) (eg. where date = {d '2008-12-31'}
)
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 in ( 'Foo', 'Bar', 'Baz' )
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 in ( 'Foo', 'Bar', 'Baz' )
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
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
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 size(cat.kittens) > 0
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 order from Order order where maxindex(order.items) > 100
select order 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). While subqueries are supported by
JP-QL, elements
and indices
are
specific HQL features.
select mother from Cat as mother, Cat as kit where kit in elements(foo.kittens)
select p from NameList list, Person p where p.name = some elements(list.names)
select cat from Cat cat where exists elements(cat.kittens)
select cat from Player p where 3 > all elements(p.scores)
select cat from Show show where 'fizard' in indices(show.acts)
Note that these constructs - size
,
elements
, indices
,
minindex
, maxindex
,
minelement
, maxelement
- may only be
used in the where clause in Hibernate.
JP-QL lets you access the key or the value of a map by using the
KEY()
and VALUE()
operations (even
access the Entry object using ENTRY()
)
SELECT i.name, VALUE(p) FROM Item i JOIN i.photos p WHERE KEY(p) LIKE ‘%egret’
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 person from Person person, Calendar calendar where calendar.holidays['national day'] = person.birthDay and person.nationality.calendar = calendar
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[ maxindex(order.items) ] = item and order.id = 11
The expression inside []
may even be an
arithmetic expression.
select item from Item item, Order order where order.items[ size(order.items) - 1 ] = item
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
Scalar SQL functions supported by the underlying database may be used
select cat from DomesticCat cat where upper(cat.name) like 'FRI%'
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)
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 )
The list returned by a query may be ordered by any property of a returned class or components:
select cat from DomesticCat cat order by cat.name asc, cat.weight desc, cat.birthdate
The optional asc
or desc
indicate ascending or descending order respectively.
A query that returns aggregate values may be grouped by any property of a returned class or components:
select cat.color, sum(cat.weight), count(cat) from Cat cat group by cat.color
select foo.id, avg(name), max(name) from Foo foo join foo.names name group by foo.id
A having
clause is also allowed.
select cat.color, sum(cat.weight), count(cat) from Cat cat group by cat.color having cat.color in (eg.Color.TABBY, eg.Color.BLACK)
SQL functions and aggregate functions are allowed in the
having
and order by
clauses, if
supported by the underlying database (eg. not in MySQL).
select cat from Cat cat join cat.kittens kitten group by cat having avg(kitten.weight) > 100 order by count(kitten) asc, sum(kitten.weight) desc
Note that neither the group by
clause nor the
order by
clause may contain arithmetic
expressions.
For databases that support subselects, JP-QL 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.
select fatcat from Cat as fatcat where fatcat.weight > ( select avg(cat.weight) from DomesticCat cat )
select cat from DomesticCat as cat where cat.name = some ( select name.nickName from Name as name )
select cat from Cat as cat where not exists ( from Cat as mate where mate.mate = cat )
select cat from DomesticCat as cat where cat.name not in ( select name.nickName from Name as name )
For subqueries with more than one expression in the select list, you can use a tuple constructor:
select cat from Cat as cat where not ( cat.name, cat.color ) in ( select cat.name, cat.color from DomesticCat cat )
Note that on some databases (but not Oracle or HSQLDB), you can use tuple constructors in other contexts, for example when querying components or composite user types:
select cat from Person where name = ('Gavin', 'A', 'King')
Which is equivalent to the more verbose:
select cat from Person where name.first = 'Gavin' and name.initial = 'A' and name.last = 'King')
There are two good reasons you might not want to do this kind of thing: first, it is not completely portable between database platforms; second, the query is now dependent upon the ordering of properties in the mapping document.
Hibernate queries can be quite powerful and complex. In fact, the power of the query language is one of Hibernate's main selling points (and now JP-QL). 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.
select count(payment), status.name from Payment as payment join payment.currentStatus as status join payment.statusChanges as statusChange where payment.status.name <> PaymentStatus.AWAITING_APPROVAL or ( statusChange.timeStamp = ( select max(change.timeStamp) from PaymentStatusChange change where change.payment = payment ) and statusChange.user <> :currentUser ) group by status.name, status.sortOrder order by status.sortOrder
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
However the query would have been HQL specific.
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 join account.holder.users as user left outer join account.payments as payment where :currentUser = user and PaymentStatus.UNPAID = isNull(payment.currentStatus.name, PaymentStatus.UNPAID) order by account.type.sortOrder, account.accountNumber, payment.dueDate
Hibernate now supports UPDATE and DELETE statements in HQL/JP-QL. See Section 7.1, “Bulk update/delete” for details.
To order a result by the size of a collection, use the following query:
select usr.id, usr.name from User as usr left join usr.messages as msg group by usr.id, usr.name order by count(msg)
If your database supports subselects, you can place a condition upon selection size in the where clause of your query:
from User usr where size(usr.messages) >= 1
If your database doesn't support subselects, use the following query:
select usr.id, usr.name from User usr.name join usr.messages msg group by usr.id, usr.name having count(msg) >= 1
As this solution can't return a User
with zero
messages because of the inner join, the following form is also
useful:
select usr.id, usr.name from User as usr left join usr.messages as msg group by usr.id, usr.name having count(msg) = 0
Copyright © 2005 Red Hat Inc. and the various authors