Hibernate.orgCommunity Documentation
Table of Contents
FROM clauseSELECT clauseWHERE clauseThe Hibernate Query Language (HQL) and Java Persistence Query Language (JPQL) are both object model focused query languages similar in nature to SQL. JPQL is a heavily-inspired-by subset of HQL. A JPQL query is always a valid HQL query, the reverse is not true however.
Both HQL and JPQL are non-type-safe ways to perform query operations. Criteria queries offer a type-safe approach to querying. See ??? for more information.
With the exception of names of Java classes and properties, queries are case-insensitive.
So SeLeCT is the same as sELEct is the same as
SELECT, but
org.hibernate.eg.FOO and org.hibernate.eg.Foo are different, as are
foo.barSet and foo.BARSET.
This documentation uses lowercase keywords as convention in examples.
Both HQL and JPQL allow SELECT, UPDATE and DELETE
statements to be performed. HQL additionally allows INSERT statements, in a form
similar to a SQL INSERT-SELECT.
Care should be taken as to when a UPDATE or DELETE statement is
executed.
Caution should be used when executing bulk update or delete operations because they may result in inconsistencies between the database and the entities in the active persistence context. In general, bulk update and delete operations should only be performed within a transaction in a new persistence con- text or before fetching or accessing entities whose state might be affected by such operations. | ||
| --Section 4.10 of the JPA 2.0 Specification | ||
The BNF for SELECT statements in HQL is:
select_statement :: =
[select_clause]
from_clause
[where_clause]
[groupby_clause]
[having_clause]
[orderby_clause]
The simplest possible HQL SELECT statement is of the form:
from com.acme.Cat
The select statement in JPQL is exactly the same as for HQL except that JPQL requires a
select_clause, whereas HQL does not. Even though HQL does not require the presence
of a select_clause, it is generally good practice to include one. For simple queries
the intent is clear and so the intended result of the select_clause is east to
infer. But on more complex queries that is not always the case. It is usually better to explicitly
specify intent. Hibernate does not actually enforce that a select_clause be present
even when parsing JPQL queries, however applications interested in JPA portability should take heed of
this.
The BNF for UPDATE statements is the same in HQL and JPQL:
update_statement ::= update_clause [where_clause]
update_clause ::= UPDATE entity_name [[AS] identification_variable]
SET update_item {, update_item}*
update_item ::= [identification_variable.]{state_field | single_valued_object_field}
= new_value
new_value ::= scalar_expression |
simple_entity_expression |
NULL
UPDATE statements, by default, do not effect the version
or the timestamp attribute values for the affected entities. However,
you can force Hibernate to set the version or timestamp attribute
values through the use of a versioned update. This is achieved by adding the
VERSIONED keyword after the UPDATE keyword. Note, however, that
this is a Hibernate specific feature and will not work in a portable manner. Custom version types,
org.hibernate.usertype.UserVersionType, are not allowed in conjunction
with a update versioned statement.
An UPDATE statement is executed using the executeUpdate
of either org.hibernate.Query or
javax.persistence.Query. The method is named for those familiar with
the JDBC executeUpdate on java.sql.PreparedStatement.
The int value returned by the executeUpdate() method
indicates the number of entities effected by the operation. This may or may not correlate to the number
of rows effected in the database. An HQL bulk operation might result in multiple actual SQL statements
being executed (for joined-subclass, for example). The returned number indicates the number of actual
entities affected by the statement. Using a JOINED inheritance hierarchy, a delete against one of the
subclasses may actually result in deletes against not just the table to which that subclass is mapped,
but also the "root" table and tables “in between”
Example 11.1. Example UPDATE query statements
String hqlUpdate =
"update Customer c " +
"set c.name = :newName " +
"where c.name = :oldName";
int updatedEntities = session.createQuery( hqlUpdate )
.setString( "newName", newName )
.setString( "oldName", oldName )
.executeUpdate();
String jpqlUpdate =
"update Customer c " +
"set c.name = :newName " +
"where c.name = :oldName";
int updatedEntities = entityManager.createQuery( jpqlUpdate )
.setString( "newName", newName )
.setString( "oldName", oldName )
.executeUpdate();
String hqlVersionedUpdate =
"update versioned Customer c " +
"set c.name = :newName " +
"where c.name = :oldName";
int updatedEntities = s.createQuery( hqlUpdate )
.setString( "newName", newName )
.setString( "oldName", oldName )
.executeUpdate();
Neither UPDATE nor DELETE statements are allowed to
result in what is called an implicit join. Their form already disallows explicit joins.
The BNF for DELETE statements is the same in HQL and JPQL:
delete_statement ::= delete_clause [where_clause] delete_clause ::= DELETE FROM entity_name [[AS] identification_variable]
A DELETE statement is also executed using the executeUpdate
method of either org.hibernate.Query or
javax.persistence.Query.
HQL adds the ability to define INSERT statements as well. There is no JPQL
equivalent to this. The BNF for an HQL INSERT statement is:
insert_statement ::= insert_clause select_statement insert_clause ::= INSERT INTO entity_name (attribute_list) attribute_list ::= state_field[, state_field ]*
The attribute_list is analogous to the column specification in the
SQL INSERT statement. For entities involved in mapped inheritance, only attributes
directly defined on the named entity can be used in the attribute_list. Superclass
properties are not allowed and subclass properties do not make sense. In other words,
INSERT statements are inherently non-polymorphic.
select_statement can be any valid HQL select query, with the caveat that the return
types must match the types expected by the insert. Currently, this is checked during query
compilation rather than allowing the check to relegate to the database. This may cause problems
between Hibernate Types which are equivalent as opposed to
equal. For example, this might cause lead to issues with mismatches between an
attribute mapped as a org.hibernate.type.DateType and an attribute defined as
a org.hibernate.type.TimestampType, even though the database might not make a
distinction or might be able to handle the conversion.
For the id attribute, the insert statement gives you two options. You can either explicitly specify
the id property in the attribute_list, in which case its value is taken from the
corresponding select expression, or omit it from the attribute_list in which case a
generated value is used. This latter option is only available when using id generators that operate
“in the database”; attempting to use this option with any “in memory” type
generators will cause an exception during parsing.
For optimistic locking attributes, the insert statement again gives you two options. You can either
specify the attribute in the attribute_list in which case its value is taken from
the corresponding select expressions, or omit it from the attribute_list in which
case the seed value defined by the corresponding
org.hibernate.type.VersionType is used.
Example 11.2. Example INSERT query statements
String hqlInsert = "insert into DelinquentAccount (id, name) select c.id, c.name from Customer c where ..."; int createdEntities = s.createQuery( hqlInsert ).executeUpdate();
The FROM clause is responsible defining the scope of object model types available to
the rest of the query. It also is responsible for defining all the “identification variables”
available to the rest of the query.
Identification variables are often referred to as aliases. References to object model classes in the FROM clause can be associated with an identification variable that can then be used to refer to that type thoughout the rest of the query.
In most cases declaring an identification variable is optional, though it is usually good practice to declare them.
An identification variable must follow the rules for Java identifier validity.
According to JPQL, identification variables must be treated as case insensitive. Good practice says you should use the same case throughout a query to refer to a given identification variable. In other words, JPQL says they can be case insensitive and so Hibernate must be able to treat them as such, but this does not make it good practice.
A root entity reference, or what JPA calls a range variable declaration, is
specifically a reference to a mapped entity type from the application. It cannot name component/
embeddable types. And associations, including collections, are handled in a different manner
discussed later.
The BNF for a root entity reference is:
root_entity_reference ::= entity_name [AS] identification_variable
We see that the query is defining a root entity reference to the com.acme.Cat
object model type. Additionally, it declares an alias of c to that
com.acme.Cat reference; this is the identification variable.
Usually the root entity reference just names the entity name rather than the
entity class FQN. By default the entity name is the unqualified entity class name,
here Cat
Multiple root entity references can also be specified. Even naming the same entity!
Example 11.5. Simple query using multiple root entity references
// build a product between customers and active mailing campaigns so we can spam! select distinct cust, camp from Customer cust, Campaign camp where camp.type = 'mail' and current_timestamp() between camp.activeRange.start and camp.activeRange.end
// retrieve all customers with headquarters in the same state as Acme's headquarters select distinct c1 from Customer c1, Customer c2 where c1.address.state = c2.address.state and c2.name = 'Acme'
The FROM clause can also contain explicit relationship joins using the
join keyword. These joins can be either inner
or left outer style joins.
Example 11.6. Explicit inner join examples
select c
from Customer c
join c.chiefExecutive ceo
where ceo.age < 25
// same query but specifying join type as 'inner' explicitly
select c
from Customer c
inner join c.chiefExecutive ceo
where ceo.age < 25
Example 11.7. Explicit left (outer) join examples
// get customers who have orders worth more than $5000
// or who are in "preferred" status
select distinct c
from Customer c
left join c.orders o
where o.value > 5000.00
or c.status = 'preferred'
// functionally the same query but using the
// 'left outer' phrase
select distinct c
from Customer c
left outer join c.orders o
where o.value > 5000.00
or c.status = 'preferred'
An important use case for explicit joins is to define FETCH JOINS which override
the laziness of the joined association. As an example, given an entity named Customer
with a collection-valued association named orders
As you can see from the example, a fetch join is specified by injecting the keyword fetch
after the keyword join. In the example, we used a left outer join because we want
to return customers who have no orders also. Inner joins can also be fetched. But inner joins still
filter. In the example, using an inner join instead would have resulted in customers without any orders
being filtered out of the result.
Fetch joins are not valid in sub-queries.
Care should be taken when fetch joining a collection-valued association which is in any way further restricted; the fetched collection will be restricted too! For this reason it is usually considered best practice to not assign an identification variable to fetched joins except for the purpose of specifying nested fetch joins.
Fetch joins should not be used in paged queries (aka, setFirstResult/
setMaxResults). Nor should they be used with the HQL
scroll or iterate features.
HQL also defines a WITH clause to qualify the join conditions. Again, this is
specific to HQL; JPQL does not define this feature.
Example 11.9. with-clause join example
select distinct c
from Customer c
left join c.orders o
with o.value > 5000.00
The important distinction is that in the generated SQL the conditions of the
with clause are made part of the on clause in the generated SQL
as opposed to the other queries in this section where the HQL/JPQL conditions are made part of the
where clause in the generated SQL. The distinction in this specific example is
probably not that significant. The with clause is sometimes necessary in more
complicated queries.
Explicit joins may reference association or component/embedded attributes. For further information about collection-valued association references, see Section 11.3.5, “Collection member references”. In the case of component/embedded attributes, the join is simply logical and does not correlate to a physical (SQL) join.
Another means of adding to the scope of object model types available to the query is through the use of implicit joins, or path expressions.
Example 11.10. Simple implicit join example
select c
from Customer c
where c.chiefExecutive.age < 25
// same as
select c
from Customer c
inner join c.chiefExecutive ceo
where ceo.age < 25
An implicit join always starts from an identification variable, followed by
the navigation operator (.), followed by an attribute for the object model type referenced by the
initial identification variable. In the example, the initial
identification variable is c which refers to the
Customer entity. The c.chiefExecutive reference then refers
to the chiefExecutive attribute of the Customer entity.
chiefExecutive is an association type so we further navigate to its
age attribute.
If the attribute represents an entity association (non-collection) or a component/embedded, that reference can be further navigated. Basic values and collection-valued associations cannot be further navigated.
As shown in the example, implicit joins can appear outside the FROM clause. However,
they affect the FROM clause. Implicit joins are always treated as inner joins.
Multiple references to the same implicit join always refer to the same logical and physical (SQL) join.
Example 11.11. Reused implicit join
select c
from Customer c
where c.chiefExecutive.age < 25
and c.chiefExecutive.address.state = 'TX'
// same as
select c
from Customer c
inner join c.chiefExecutive ceo
where ceo.age < 25
and ceo.address.state = 'TX'
// same as
select c
from Customer c
inner join c.chiefExecutive ceo
inner join ceo.address a
where ceo.age < 25
and a.state = 'TX'
Just as with explicit joins, implicit joins may reference association or component/embedded attributes. For further information about collection-valued association references, see Section 11.3.5, “Collection member references”. In the case of component/embedded attributes, the join is simply logical and does not correlate to a physical (SQL) join. Unlike explicit joins, however, implicit joins may also reference basic state fields as long as the path expression ends there.
References to collection-valued associations actually refer to the values of that collection.
Example 11.12. Collection references example
select c
from Customer c
join c.orders o
join o.lineItems l
join l.product p
where o.status = 'pending'
and p.status = 'backorder'
// alternate syntax
select c
from Customer c,
in(c.orders) o,
in(o.lineItems) l
join l.product p
where o.status = 'pending'
and p.status = 'backorder'
In the example, the identification variable o actually refers to the object model
type Order which is the type of the elements of the
Customer#orders association.
The example also shows the alternate syntax for specifying collection association joins using the
IN syntax. Both forms are equivalent. Which form an application chooses to use is
simply a matter of taste.
We said earlier that collection-valued associations actually refer to the values of that collection. Based on the type of collection, there are also available a set of explicit qualification expressions.
Example 11.13. Qualified collection references example
// Product.images is a Map<String,String> : key = a name, value = file path
// select all the image file paths (the map value) for Product#123
select i
from Product p
join p.images i
where p.id = 123
// same as above
select value(i)
from Product p
join p.images i
where p.id = 123
// select all the image names (the map key) for Product#123
select key(i)
from Product p
join p.images i
where p.id = 123
// select all the image names and file paths (the 'Map.Entry') for Product#123
select entry(i)
from Product p
join p.images i
where p.id = 123
// total the value of the initial line items for all orders for a customer
select sum( li.amount )
from Customer c
join c.orders o
join o.lineItems li
where c.id = 123
and index(li) = 1
Refers to the collection value. Same as not specifying a qualifier. Useful to explicitly show intent. Valid for any type of collection-valued reference.
According to HQL rules, this is valid for both Maps and Lists which specify a
javax.persistence.OrderColumn annotation to refer to
the Map key or the List position (aka the OrderColumn value). JPQL however, reserves
this for use in the List case and adds KEY for the MAP case.
Applications interested in JPA provider portability should be aware of this
distinction.
Valid only for Maps. Refers to the map's key. If the key is itself an entity, can be further navigated.
Only valid only for Maps. Refers to the Map's logical
java.util.Map.Entry tuple (the combination of its key
and value). ENTRY is only valid as a terminal path and only valid
in the select clause.
See Section 11.4.9, “Collection-related expressions” for additional details on collection related expressions.
HQL and JPQL queries are inherently polymorphic.
select p from Payment p
This query names the Payment entity explicitly. However, all subclasses of
Payment are also available to the query. So if the
CreditCardPayment entity and WireTransferPayment entity
each extend from Payment all three types would be available to the query. And
the query would return instances of all three.
The HQL query from java.lang.Object is totally valid! It returns every
object of every type defined in your application.
This can be altered by using either the
org.hibernate.annotations.Polymorphism annotation (global, and
Hibernate-specific) or limiting them using in the query itself using an entity type expression.
Essentially expressions are references that resolve to basic or tuple values.
Again, see Section 11.3, “The FROM clause”.
String literals are enclosed in single-quotes. To escape a single-quote within a string literal, use double single-quotes.
Example 11.14. String literal examples
select c from Customer c where c.name = 'Acme' select c from Customer c where c.name = 'Acme''s Pretzel Logic'
Numeric literals are allowed in a few different forms.
Example 11.15. Numeric literal examples
// simple integer literal select o from Order o where o.referenceNumber = 123 // simple integer literal, typed as a long select o from Order o where o.referenceNumber = 123L // decimal notation select o from Order o where o.total > 5000.00 // decimal notation, typed as a float select o from Order o where o.total > 5000.00F // scientific notation select o from Order o where o.total > 5e+3 // scientific notation, typed as a float select o from Order o where o.total > 5e+3F
In the scientific notation form, the E is case insensitive.
Specific typing can be achieved through the use of the same suffix approach specified by Java. So,
L denotes a long; D denotes a double; F
denotes a float. The actual suffix is case insensitive.
The boolean literals are TRUE and FALSE, again case-insensitive.
Enums can even be referenced as literals. The fully-qualified enum class name must be used. HQL can also handle constants in the same manner, though JPQL does not define that as supported.
Entity names can also be used as literal. See Section 11.4.10, “Entity type”.
Date/time literals can be specified using the JDBC escape syntax: {d 'yyyy-mm-dd'}
for dates, {t 'hh:mm:ss'} for times and
{ts 'yyyy-mm-dd hh:mm:ss[.millis]'} (millis optional) for timestamps. These
literals only work if you JDBC drivers supports them.
HQL supports all 3 of the following forms. JPQL does not support the HQL-specific positional parameters notion. It is good practice to not mix forms in a given query.
Named parameters are declared using a colon followed by an identifier -
:aNamedParameter. The same named parameter can appear multiple times in a query.
Example 11.16. Named parameter examples
String queryString =
"select c " +
"from Customer c " +
"where c.name = :name " +
" or c.nickName = :name";
// HQL
List customers = session.createQuery( queryString )
.setParameter( "name", theNameOfInterest )
.list();
// JPQL
List<Customer> customers = entityManager.createQuery( queryString, Customer.class )
.setParameter( "name", theNameOfInterest )
.getResultList();
JPQL-style positional parameters are declared using a question mark followed by an ordinal -
?1, ?2. The ordinals start with 1. Just like with
named parameters, positional parameters can also appear multiple times in a query.
Example 11.17. Positional (JPQL) parameter examples
String queryString =
"select c " +
"from Customer c " +
"where c.name = ?1 " +
" or c.nickName = ?1";
// HQL - as you can see, handled just like named parameters
// in terms of API
List customers = session.createQuery( queryString )
.setParameter( "1", theNameOfInterest )
.list();
// JPQL
List<Customer> customers = entityManager.createQuery( queryString, Customer.class )
.setParameter( 1, theNameOfInterest )
.getResultList();
HQL-style positional parameters follow JDBC positional parameter syntax. They are declared using
? without a following ordinal. There is no way to relate two such
positional parameters as being "the same" aside from binding the same value to each.
This form should be considered deprecated and may be removed in the near future.
Arithmetic operations also represent valid expressions.
Example 11.18. Numeric arithmetic examples
select year( current_date() ) - year( c.dateOfBirth ) from Customer c select c from Customer c where year( current_date() ) - year( c.dateOfBirth ) < 30 select o.customer, o.total + ( o.total * :salesTax ) from Order o
The following rules apply to the result of arithmetic operations:
If either of the operands is Double/double, the result is a Double;
else, if either of the operands is Float/float, the result is a Float;
else, if either operand is BigDecimal, the result is BigDecimal;
else, if either operand is BigInteger, the result is BigInteger (except for division, in which case the result type is not further defined);
else, if either operand is Long/long, the result is Long (except for division, in which case the result type is not further defined);
else, (the assumption being that both operands are of integral type) the result is Integer (except for division, in which case the result type is not further defined);
Date arithmetic is also supported, albeit in a more limited fashion. This is due partially to
differences in database support and partially to the lack of support for INTERVAL
definition in the query language itself.
HQL defines a concatenation operator in addition to supporting the concatenation
(CONCAT) function. This is not defined by JPQL, so portable applications
should avoid it use. The concatenation operator is taken from the SQL concatenation operator -
||.
Example 11.19. Concatenation operation example
select 'Mr. ' || c.name.first || ' ' || c.name.last from Customer c where c.gender = Gender.MALE
See Section 11.4.8, “Scalar functions” for details on the concat() function
Aggregate functions are also valid expressions in HQL and JPQL. The semantic is the same as their SQL counterpart. The supported aggregate functions are:
COUNT (including distinct/all qualifiers) - The result type is always Long.
AVG - The result type is always Double.
MIN - The result type is the same as the argument type.
MAX - The result type is the same as the argument type.
SUM - The result type of the avg() function depends on
the type of the values being averaged. For integral values (other than BigInteger), the result
type is Long. For floating point values (other than BigDecimal) the result type is Double. For
BigInteger values, the result type is BigInteger. For BigDecimal values, the result type is
BigDecimal.
Example 11.20. Aggregate function examples
select count(*), sum( o.total ), avg( o.total ), min( o.total ), max( o.total )
from Order o
select count( distinct c.name )
from Customer c
select c.id, c.name, sum( o.total )
from Customer c
left join c.orders o
group by c.id, c.name
Aggregations often appear with grouping. For information on grouping see Section 11.8, “Grouping”
Both HQL and JPQL define some standard functions that are available regardless of the underlying database in use. HQL can also understand additional functions defined by the Dialect as well as the application.
Here are the list of functions defined as supported by JPQL. Applications interested in remaining portable between JPA providers should stick to these functions.
String concatenation function. Variable argument length of 2 or more string values to be concatenated together.
Extracts a portion of a string value.
substring( string_expression, numeric_expression [, numeric_expression] )
The second argument denotes the starting position. The third (optional) argument denotes the length.
Upper cases the specified string
Lower cases the specified string
Follows the semantics of the SQL trim function.
Returns the length of a string.
Locates a string within another string.
locate( string_expression, string_expression[, numeric_expression] )
The third argument (optional) is used to denote a position from which to start looking.
Calculates the mathematical absolute value of a numeric value.
Calculates the remainder of dividing the first argument by the second.
Calculates the mathematical square root of a numeric value.
Returns the database current date.
Returns the database current time.
Returns the database current timestamp.
Beyond the JPQL standardized functions, HQL makes some additional functions available regardless of the underlying database in use.
Returns the length of binary data.
Performs a SQL cast. The cast target should name the Hibernate mapping type to use. See the chapter on data types for more information.
Performs a SQL extraction on datetime values. An extraction extracts parts of the datetime (the year, for example). See the abbreviated forms below.
Abbreviated extract form for extracting the second.
Abbreviated extract form for extracting the minute.
Abbreviated extract form for extracting the hour.
Abbreviated extract form for extracting the day.
Abbreviated extract form for extracting the month.
Abbreviated extract form for extracting the year.
Abbreviated form for casting a value as character data.
Hibernate Dialects can register additional functions known to be available for that particular database product. These functions are also available in HQL (and JPQL, though only when using Hibernate as the JPA provider obviously). However, they would only be available when using that database/Dialect. Applications that aim for database portability should avoid using functions in this category.
Application developers can also supply their own set of functions. This would usually represent
either custom SQL functions or aliases for snippets of SQL. Such function declarations are
made by using the addSqlFunction method of
org.hibernate.cfg.Configuration
There are a few specialized expressions for working with collection-valued associations. Generally these are just abbreviated forms or other expressions for the sake of conciseness.
Calculate the size of a collection. Equates to a subquery!
Available for use on collections of basic type. Refers to the maximum value as determined
by applying the max SQL aggregation.
Available for use on indexed collections. Refers to the maximum index (key/position) as
determined by applying the max SQL aggregation.
Available for use on collections of basic type. Refers to the minimum value as determined
by applying the min SQL aggregation.
Available for use on indexed collections. Refers to the minimum index (key/position) as
determined by applying the min SQL aggregation.
Used to refer to the elements of a collection as a whole. Only allowed in the where clause.
Often used in conjunction with ALL, ANY or
SOME restrictions.
Similar to elements except that indices refers to
the collections indices (keys/positions) as a whole.
Example 11.21. Collection-related expressions examples
select cal from Calendar cal where maxelement(cal.holidays) > current_date() select o from Order o where maxindex(o.items) > 100 select o from Order o where minelement(o.items) > 10000 select m from Cat as m, Cat as kit where kit in elements(m.kittens) // the above query can be re-written in jpql standard way: select m from Cat as m, Cat as kit where kit member of m.kittens select p from NameList l, Person p where p.name = some elements(l.names) select cat from Cat cat where exists elements(cat.kittens) select p from Player p where 3 > all elements(p.scores) select show from Show show where 'fizard' in indices(show.acts)
Elements of indexed collections (arrays, lists, and maps) can be referred to by index operator.
Example 11.22. Index operator examples
select o from Order o where o.items[0].id = 1234 select p from Person p, Calendar c where c.holidays['national day'] = p.birthDay and p.nationality.calendar = c select i from Item i, Order o where o.items[ o.deliveredItemIndices[0] ] = i and o.id = 11 select i from Item i, Order o where o.items[ maxindex(o.items) ] = i and o.id = 11 select i from Item i, Order o where o.items[ size(o.items) - 1 ] = i
See also Section 11.3.5.1, “Special case - qualified path expressions” as there is a good deal of overlap.
We can also refer to the type of an entity as an expression. This is mainly useful when dealing
with entity inheritance hierarchies. The type can expressed using a TYPE function
used to refer to the type of an identification variable representing an entity. The name of the
entity also serves as a way to refer to an entity type. Additionally the entity type can be
parametrized, in which case the entity's Java Class reference would be bound as the parameter
value.
Example 11.23. Entity type expression examples
select p from Payment p where type(p) = CreditCardPayment select p from Payment p where type(p) = :aType
HQL also has a legacy form of referring to an entity type, though that legacy form is considered
deprecated in favor of TYPE. The legacy form would have used p.class
in the examples rather than type(p). It is mentioned only for completeness.
Both the simple and searched forms are supported, as well as the 2 SQL defined abbreviated forms
(NULLIF and COALESCE)
The simple form has the following syntax:
CASE {operand} WHEN {test_value} THEN {match_result} ELSE {miss_result} END
Example 11.24. Simple case expression example
select case c.nickName when null then '<no nick name>' else c.nickName end from Customer c // This NULL checking is such a common case that most dbs // define an abbreviated CASE form. For example: select nvl( c.nickName, '<no nick name>' ) from Customer c // or: select isnull( c.nickName, '<no nick name>' ) from Customer c // the standard coalesce abbreviated form can be used // to achieve the same result: select coalesce( c.nickName, '<no nick name>' ) from Customer c
The searched form has the following syntax:
CASE [ WHEN {test_conditional} THEN {match_result} ]* ELSE {miss_result} END
Example 11.25. Searched case expression example
select case when c.name.first is not null then c.name.first
when c.nickName is not null then c.nickName
else '<no first name>' end
from Customer c
// Again, the abbreviated form coalesce can handle this a
// little more succinctly
select coalesce( c.name.first, c.nickName, '<no first name>' )
from Customer c
NULLIF is an abbreviated CASE expression that returns NULL if its operands are considered equal.
Example 11.26. NULLIF example
// return customers who have changed their last name
select nullif( c.previousName.last, c.name.last )
from Customer c
// equivalent CASE expression
select case when c.previousName.last = c.name.last then null
else c.previousName.last end
from Customer c
The SELECT clause identifies which objects and values to return as the query results.
The expressions discussed in Section 11.4, “Expressions” are all valid select expressions, except
where otherwise noted. See the section Section 11.10, “Query API” for information on handling the results
depending on the types of values specified in the SELECT clause.
There is a particular expression type that is only valid in the select clause. Hibernate calls this “dynamic instantiation”. JPQL supports some of that feature and calls it a “constructor expression”
Example 11.27. Dynamic instantiation example - constructor
select new Family( mother, mate, offspr )
from DomesticCat as mother
join mother.mate as mate
left join mother.kittens as offspr
So rather than dealing with the Object[] (again, see Section 11.10, “Query API”) here we are wrapping the values in a type-safe java object that will be returned as the results of the query. The class reference must be fully qualified and it must have a matching constructor.
The class here need not be mapped. If it does represent an entity, the resulting instances are returned in the NEW state (not managed!).
That is the part JPQL supports as well. HQL supports additional “dynamic instantiation” features. First, the query can specify to return a List rather than an Object[] for scalar results:
Example 11.28. Dynamic instantiation example - list
select new list(mother, offspr, mate.name)
from DomesticCat as mother
inner join mother.mate as mate
left outer join mother.kittens as offspr
The results from this query will be a List<List> as opposed to a List<Object[]>
HQL also supports wrapping the scalar results in a Map.
Example 11.29. Dynamic instantiation example - map
select new map( mother as mother, offspr as offspr, mate as mate )
from DomesticCat as mother
inner join mother.mate as mate
left outer join mother.kittens as offspr
select new map( max(c.bodyWeight) as max, min(c.bodyWeight) as min, count(*) as n )
from Cat c
The results from this query will be a List<Map<String,Object>> as opposed to a List<Object[]>. The keys of the map are defined by the aliases given to the select expressions.
Predicates form the basis of the where clause, the having clause and searched case expressions.
They are expressions which resolve to a truth value, generally TRUE or
FALSE, although boolean comparisons involving NULLs generally resolve to
UNKNOWN.
Comparisons involve one of the comparison operators - =, >, >=, <, <=, <>]>. HQL also defines <![CDATA[!= as a comparison operator synonymous with <>. The operands should be of the same type.
Example 11.30. Relational comparison examples
// numeric comparison
select c
from Customer c
where c.chiefExecutive.age < 30
// string comparison
select c
from Customer c
where c.name = 'Acme'
// datetime comparison
select c
from Customer c
where c.inceptionDate < {d '2000-01-01'}
// enum comparison
select c
from Customer c
where c.chiefExecutive.gender = com.acme.Gender.MALE
// boolean comparison
select c
from Customer c
where c.sendEmail = true
// entity type comparison
select p
from Payment p
where type(p) = WireTransferPayment
// entity value comparison
select c
from Customer c
where c.chiefExecutive = c.chiefTechnologist
Comparisons can also involve subquery qualifiers - ALL, ANY,
SOME. SOME and ANY are synonymous.
The ALL qualifier resolves to true if the comparison is true for all of the values in the result of the subquery. It resolves to false if the subquery result is empty.
Example 11.31. ALL subquery comparison qualifier example
// select all players that scored at least 3 points
// in every game.
select p
from Player p
where 3 > all (
select spg.points
from StatsPerGame spg
where spg.player = p
)
The ANY/SOME qualifier resolves to true if the comparison is true for some of (at least one of) the values in the result of the subquery. It resolves to false if the subquery result is empty.
Check a value for nullness. Can be applied to basic attribute references, entity references and parameters. HQL additionally allows it to be applied to component/embeddable types.
Example 11.32. Nullness checking examples
// select everyone with an associated address select p from Person p where p.address is not null // select everyone without an associated address select p from Person p where p.address is null
Performs a like comparison on string values. The syntax is:
like_expression ::=
string_expression
[NOT] LIKE pattern_value
[ESCAPE escape_character]
The semantics follow that of the SQL like expression. The pattern_value is the
pattern to attempt to match in the string_expression. Just like SQL,
pattern_value can use “_” and “%” as wildcards. The
meanings are the same. “_” matches any single character. “%” matches
any number of characters.
The optional escape_character is used to specify an escape character used to
escape the special meaning of “_” and “%” in the
pattern_value. THis is useful when needing to search on patterns including either
“_” or “%”
Example 11.33. Like predicate examples
select p from Person p where p.name like '%Schmidt' select p from Person p where p.name not like 'Jingleheimmer%' // find any with name starting with "sp_" select sp from StoredProcedureMetadata sp where sp.name like 'sp|_%' escape '|'
Analogous to the SQL between expression. Perform a evaluation that a value is within the range of 2 other values. All the operands should have comparable types.
Example 11.34. Between predicate examples
select p
from Customer c
join c.paymentHistory p
where c.id = 123
and index(p) between 0 and 9
select c
from Customer c
where c.president.dateOfBirth
between {d '1945-01-01'}
and {d '1965-01-01'}
select o
from Order o
where o.total between 500 and 5000
select p
from Person p
where p.name between 'A' and 'E'
IN predicates performs a check that a particular value is in a list of values.
Its syntax is:
in_expression ::= single_valued_expression
[NOT] IN single_valued_list
single_valued_list ::= constructor_expression |
(subquery) |
collection_valued_input_parameter
constructor_expression ::= (expression[, expression]*)
The types of the single_valued_expression and the individual values in the
single_valued_list must be consistent. JPQL limits the valid types here
to string, numeric, date, time, timestamp, and enum types. In JPQL,
single_valued_expression can only refer to:
“state fields”, which is its term for simple attributes. Specifically this excludes association and component/embedded attributes.
entity type expressions. See Section 11.4.10, “Entity type”
In HQL, single_valued_expression can refer to a far more broad set of expression
types. Single-valued association are allowed. So are component/embedded attributes, although that
feature depends on the level of support for tuple or “row value constructor syntax” in
the underlying database. Additionally, HQL does not limit the value type in any way, though
application developers should be aware that different types may incur limited support based on
the underlying database vendor. This is largely the reason for the JPQL limitations.
The list of values can come from a number of different sources. In the
constructor_expression and collection_valued_input_parameter, the
list of values must not be empty; it must contain at least one value.
Example 11.35. In predicate examples
select p
from Payment p
where type(p) in (CreditCardPayment, WireTransferPayment)
select c
from Customer c
where c.hqAddress.state in ('TX', 'OK', 'LA', 'NM')
select c
from Customer c
where c.hqAddress.state in ?
select c
from Customer c
where c.hqAddress.state in (
select dm.state
from DeliveryMetadata dm
where dm.salesTax is not null
)
// Not JPQL compliant!
select c
from Customer c
where c.name in (
('John','Doe'),
('Jane','Doe')
)
// Not JPQL compliant!
select c
from Customer c
where c.chiefExecutive in (
select p
from Person p
where ...
)
Exists expressions test the existence of results from a subquery. The affirmative form returns true if the subquery result contains values. The negated form returns true if the subquery result is empty.
The IS [NOT] EMPTY expression applies to collection-valued path expressions. It
checks whether the particular collection has any associated values.
Example 11.36. Empty collection expression examples
select o from Order o where o.lineItems is empty select c from Customer c where c.pastDueBills is not empty
The [NOT] MEMBER [OF] expression applies to collection-valued path expressions. It
checks whether a value is a member of the specified collection.
Example 11.37. Member-of collection expression examples
select p from Person p where 'John' member of p.nickNames select p from Person p where p.name.first = 'Joseph' and 'Joey' not member of p.nickNames
The NOT operator is used to negate the predicate that follows it. If that
following predicate is true, the NOT resolves to false. If the predicate is true, NOT resolves to
false. If the predicate is unknown, the NOT resolves to unknown as well.
The AND operator is used to combine 2 predicate expressions. The result of the
AND expression is true if and only if both predicates resolve to true. If either predicate resolves
to unknown, the AND expression resolves to unknown as well. Otherwise, the result is false.
The WHERE clause of a query is made up of predicates which assert whether values in
each potential row match the predicated checks. Thus, the where clause restricts the results returned
from a select query and limits the scope of update and delete queries.
The GROUP BY clause allows building aggregated results for various value groups. As an
example, consider the following queries:
Example 11.38. Group-by illustration
// retrieve the total for all orders
select sum( o.total )
from Order o
// retrieve the total of all orders
// *grouped by* customer
select c.id, sum( o.total )
from Order o
inner join o.customer c
group by c.id
The first query retrieves the complete total of all orders. The second retrieves the total for each customer; grouped by each customer.
In a grouped query, the where clause applies to the non aggregated values (essentially it determines whether
rows will make it into the aggregation). The HAVING clause also restricts results,
but it operates on the aggregated values. In the Example 11.38, “Group-by illustration” example,
we retrieved order totals for all customers. If that ended up being too much data to deal with,
we might want to restrict the results to focus only on customers with a summed order total of more than
$10,000.00:
Example 11.39. Having illustration
select c.id, sum( o.total )
from Order o
inner join o.customer c
group by c.id
having sum( o.total ) > 10000.00
The HAVING clause follows the same rules as the WHERE clause and is also made up of predicates. HAVING is applied after the groupings and aggregations have been done; WHERE is applied before.
The results of the query can also be ordered. The ORDER BY clause is used to specify
the selected values to be used to order the result. The types of expressions considered valid as part
of the order-by clause include:
state fields
component/embeddable attributes
scalar expressions such as arithmetic operations, functions, etc.
identification variable declared in the select clause for any of the previous expression types
Additionally, JPQL says that all values referenced in the order-by clause must be named in the select clause. HQL does not mandate that restriction, but applications desiring database portability should be aware that not all databases support referencing values in the order-by clause that are not referenced in the select clause.
Individual expressions in the order-by can be qualified with either ASC (ascending) or
DESC (descending) to indicated the desired ordering direction. Null values can be placed
in front or at the end of sorted set using NULLS FIRST or NULLS LAST
clause respectively.
Example 11.40. Order-by examples
// legal because p.name is implicitly part of p
select p
from Person p
order by p.name
select c.id, sum( o.total ) as t
from Order o
inner join o.customer c
group by c.id
order by t