Hibernate.orgCommunity Documentation

Chapter 13. HQL and JPQL

Table of Contents

13.1. Case Sensitivity
13.2. Statement types
13.2.1. Select statements
13.2.2. Update statements
13.2.3. Delete statements
13.2.4. Insert statements
13.3. The FROM clause
13.3.1. Identification variables
13.3.2. Root entity references
13.3.3. Explicit joins
13.3.4. Implicit joins (path expressions)
13.3.5. Collection member references
13.3.6. Polymorphism
13.4. Expressions
13.4.1. Identification variable
13.4.2. Path expressions
13.4.3. Literals
13.4.4. Parameters
13.4.5. Arithmetic
13.4.6. Concatenation (operation)
13.4.7. Aggregate functions
13.4.8. Scalar functions
13.4.9. Collection-related expressions
13.4.10. Entity type
13.4.11. CASE expressions
13.5. The SELECT clause
13.6. Predicates
13.6.1. Relational comparisons
13.6.2. Nullness predicate
13.6.3. Like predicate
13.6.4. Between predicate
13.6.5. In predicate
13.6.6. Exists predicate
13.6.7. Empty collection predicate
13.6.8. Member-of collection predicate
13.6.9. NOT predicate operator
13.6.10. AND predicate operator
13.6.11. OR predicate operator
13.7. The WHERE clause
13.8. Grouping
13.9. Ordering
13.10. Query API
13.10.1. Hibernate Query API
13.10.2. JPA Query API

The 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 Chapter 14, Criteria 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.

Note

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.

Important

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


Important

Neither UPDATE nor DELETE statements are allowed to result in what is called an implicit join. Their form already disallows explicit joins.

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.


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.

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!


The FROM clause can also contain explicit relationship joins using the join keyword. These joins can be either inner or left outer style joins.



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.

Important

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.


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


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.

Important

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.


Just as with explicit joins, implicit joins may reference association or component/embedded attributes. For further information about collection-valued association references, see Section 13.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.


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.


VALUE

Refers to the collection value. Same as not specifying a qualifier. Useful to explicitly show intent. Valid for any type of collection-valued reference.

INDEX

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.

KEY

Valid only for Maps. Refers to the map's key. If the key is itself an entity, can be further navigated.

ENTRY

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 13.4.9, “Collection-related expressions” for additional details on collection related expressions.

Essentially expressions are references that resolve to basic or tuple values.

String literals are enclosed in single-quotes. To escape a single-quote within a string literal, use double single-quotes.


Numeric literals are allowed in a few different forms.


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

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.

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.

SIZE

Calculate the size of a collection. Equates to a subquery!

MAXELEMENT

Available for use on collections of basic type. Refers to the maximum value as determined by applying the max SQL aggregation.

MAXINDEX

Available for use on indexed collections. Refers to the maximum index (key/position) as determined by applying the max SQL aggregation.

MINELEMENT

Available for use on collections of basic type. Refers to the minimum value as determined by applying the min SQL aggregation.

MININDEX

Available for use on indexed collections. Refers to the minimum index (key/position) as determined by applying the min SQL aggregation.

ELEMENTS

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.

INDICES

Similar to elements except that indices refers to the collections indices (keys/positions) as a whole.


Elements of indexed collections (arrays, lists, and maps) can be referred to by index operator.


See also Section 13.3.5.1, “Special case - qualified path expressions” as there is a good deal of overlap.

Both the simple and searched forms are supported, as well as the 2 SQL defined abbreviated forms (NULLIF and COALESCE)

The SELECT clause identifies which objects and values to return as the query results. The expressions discussed in Section 13.4, “Expressions” are all valid select expressions, except where otherwise noted. See the section Section 13.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


So rather than dealing with the Object[] (again, see Section 13.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:


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.


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.


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.


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.

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:

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.


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:


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 13.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:


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.


In Hibernate the HQL/JPQL query is represented as org.hibernate.Query which is obtained from the Session. If the HQL/JPQL is a named query, Session#getNamedQuery would be used; otherwise Session#createQuery would be used.


The Query interface can then be used to control the execution of the query. For example, we may want to specify an execution timeout or control caching.


For complete details, see the Query javadocs.

Important

Query hints here are database query hints. They are added directly to the generated SQL according to Dialect#getQueryHintString. The JPA notion of query hints, on the other hand, refer to hints that target the provider (Hibernate). So even though they are called the same, be aware they have a very different purpose. Also be aware that Hibernate query hints generally make the application non-portable across databases unless the code adding them first checks the Dialect.

Flushing is covered in detail in ???. Locking is covered in detail in ???. The concept of read-only state is covered in Chapter 4, Persistence Contexts.

Hibernate also allows an application to hook into the process of building the query results via the org.hibernate.transform.ResultTransformer contract. See its javadocs as well as the Hibernate-provided implementations for additional details.

The last thing that needs to happen before we can execute the query is to bind the values for any parameters defined in the query. Query defines many overloaded methods for this purpose. The most generic form takes the value as well as the Hibernate Type.


Hibernate generally understands the expected type of the parameter given its context in the query. In the previous example, since we are using the parameter in a LIKE comparison against a String-typed attribute Hibernate would automatically infer the type; so the above could be simplified.


There are also short hand forms for binding common types such as strings, booleans, integers, etc.


In terms of execution, Hibernate offers 4 different methods. The 2 most commonly used are

  • Query#list - executes the select query and returns back the list of results.

  • Query#uniqueResult - executes the select query and returns the single result. If there were more than one result an exception is thrown.


Note

If the unique result is used often and the attributes upon which it is based are unique, you may want to consider mapping a natural-id and using the natural-id loading API. See the Hibernate Domain Mapping Guide for more information on natural-ids.

Hibernate offers 2 additional, specialized methods for performing the query and handling results. Query#scroll works in tandem with the JDBC notion of a scrollable ResultSet. The scroll method is overloaded. Then main form accepts a single argument of type org.hibernate.ScrollMode which indicates the type of scrolling to be used. See the javadocs for ScrollMode for the details on each. The second form accepts no argument and will use the ScrollMode indicated by Dialect#defaultScrollMode. Query#scroll returns a org.hibernate.ScrollableResults which wraps the underlying JDBC (scrollable) ResultSet and provides access to the results. Since this form holds the JDBC ResultSet open, the application should indicate when it is done with the ScrollableResults by calling its close method (as inherited from java.io.Closeable, so that ScrollableResults will work with try-with-resources blocks!). If left unclosed by the application, Hibernate will automatically close the ScrollableResults when the current transaction completes.

Note

If you plan to use Query#scroll with collection fetches it is important that your query explicitly order the results so that the JDBC results contain the the related rows sequentially.

The last is Query#iterate, which is intended for loading entities which the the application feels certain will be in the second-level cache. The idea behind iterate is that just the matching identifiers will be obtained in the SQL query. From these the identifiers are resolved by second-level cache lookup. If these second-level cache lookups fail, additional queries will need to be issued against the database. This operation can perform significantly better for loading large numbers of entities that for certain already exist in the second-level cache. In cases where many of the entities do not exist in the second-level cache, this operation will almost definitely perform worse. The Iterator returned from Query#iterate is actually a specially typed Iterator: org.hibernate.engine.HibernateIterator. It is specialized to expose a close method (again, inherited from java.io.Closeable). When you are done with this Iterator you should close it, either by casting to HibernateIterator or Closeable, or by calling org.hibernate.Hibernate#close

In JPA the query is represented by javax.persistence.Query or javax.persistence.TypedQuery as obtained from the EntityManager. For named queries EntityManager#createNamedQuery is used; otherwise EntityManager#createQuery is used.


Note

This will all sound very familiar. Not only was the JPQL syntax heavily inspired by HQL, but many of the JPA APIs were heavily inspired by Hibernate. The 2 Query contracts are very similar.

The Query interface can then be used to control the execution of the query. For example, we may want to specify an execution timeout or control caching.


For complete details, see the Query javadocs. Many of the settings controlling the execution of the query are defined as hints. JPA defines some standard hints (like timeout in the example), but most are provider specific. Relying on provider specific hints limits your applications portability to some degree.

JPA standardized Query hints

Hibernate specific JPA Query hints

  • org.hibernate.cacheMode - Defines the CacheMode to use. See org.hibernate.Query#setCacheMode.

  • org.hibernate.cacheable - Defines whether the query is cacheable. true/false. See org.hibernate.Query#setCacheable.

  • org.hibernate.cacheRegion For queries that are cacheable, defines a specific cache region to use. See org.hibernate.Query#setCacheRegion.

  • org.hibernate.comment - Defines the comment to apply to the generated SQL. See org.hibernate.Query#setComment.

  • org.hibernate.fetchSize - Defines the JDBC fetch-size to use. See org.hibernate.Query#setFetchSize

  • org.hibernate.flushMode - Defines the Hibernate-specific FlushMode to use. See org.hibernate.Query#setFlushMode. If possible, prefer using javax.persistence.Query#setFlushMode instead.

  • org.hibernate.readOnly - Defines that entities and collections loaded by this query should be marked as read-only. See org.hibernate.Query#setReadOnly

Just as seen in the Hibernate API, the final thing that needs to happen before the query can be executed is to bind the values for any defined parameters. JPA defines a simplified set of parameter binding methods. Essentially it supports setting the parameter value (by name/position) and a specialized form for Calendar/Date types additionally accepting a TemporalType.


Additionally, JPA allows access to read some information about parameters as well.

As far as execution, JPA supports the two main methods discussed above for the Hibernate API. It calls these methods Query#getResultList and Query#getSingleResult. They behave exactly as described for org.hibernate.Query#list and org.hibernate.Query#uniqueResult.