A Guide to Hibernate Query Language

An identifier is a name used to refer to an entity, an attribute of a Java class, an identification variable, or a function.

For example, Book, title, author, and upper are all identifiers, but they refer to different kinds of things. In HQL and JPQL, the case sensitivity of an identifier depends on the kind of thing the identifier refers to.

The rules for case sensitivity are:

We apologize for this inconsistency. In hindsight, it might have been better to define the whole language as case-sensitive.

Just to reiterate these rules:

A quoted identifier is written in backticks. Quoting lets you use a keyword as an identifier.

Actually, in most contexts, HQL keywords are "soft", and don’t need to be quoted. The parser is usually able to distinguish if the reserved word is being used as a keyword or as an identifier.

Comments in HQL look like multiline comments in Java. They’re delimited by /* and */.

Neither SQL-style -- nor Java-style // line-ending comments are allowed.

It’s quite rare to see comments in HQL, but perhaps it will be more common now that Java has text blocks.

Parameters come in two flavors in JPQL, and HQL supports a third flavor for historical reasons:

JDBC-style parameters of form ? are like ordinal parameters where the index is inferred from the position in the text of the query. JDBC-style parameters are deprecated.

Some of the syntax for literal values also departs from the standard syntax in ANSI SQL, especially in the area of date/time literals, but we’ll discuss all that later, in Literals.

The SQL null behaves quite differently to a null value in Java.

This rule is the source of the famous (and controversial) ternary logic of SQL. A logical expression like firstName='Gavin' and team='Hibernate' isn’t restricted to the values true and false. It may also be null.

This can, in principle, lead to some quite unintuitive results: we can’t use the law of the excluded middle to reason about logical expressions in SQL! But in practice, we’ve never once run into a case where this caused us problems.

As you probably know, when a logical predicate occurs as a restriction, rows for which the predicate evaluates to null are excluded from the result set. That is, in this context at least, a logical null is interpreted as "effectively false".

The BNF for an update statement is quite straightforward:

The set clause has a list of assignments to attributes of the given entity.

For example:

Update statements are polymorphic, and affect mapped subclasses of the given entity class. Therefore, a single HQL update statement might result in multiple SQL update statements executed against the database.

An update statement must be executed using Query.executeUpdate().

The integer value returned by executeUpdate() indicates the number of entity instances affected by the operation.

An update statement, by default, does not affect the column mapped by the @Version attribute of the affected entities.

Adding the keyword versioned—writing update versioned—specifies that Hibernate should increment the version number or update the last modification timestamp.

An update statement may not directly join other entities, but it may:

The BNF for a delete statement is even simpler:

For example:

As in SQL, the presence or absence of the from keyword has absolutely no effect on the semantics of the delete statement.

Just like update statements, delete statements are polymorphic, and affect mapped subclasses of the given entity class. Therefore, a single HQL delete statement might result in multiple SQL delete statements executed against the database.

A delete statement is executed by calling Query.executeUpdate().

The integer value returned by executeUpdate() indicates the number of entity instances affected by the operation.

A delete statement may not directly join other entities, but it may:

There are two kinds of insert statement:

The first form inserts a single row in the database, or multiple rows if you provide multiple tuples in the values clause. The second form may insert many new rows, or none at all.

The BNF for an insert statement is:

For example:

As in SQL, the presence or absence of the into keyword has no effect on the semantics of the insert statement.

From these examples we might notice that insert statements are in one respect a bit different to update and delete statements.

The queryExpression in an insert …​ select statement may be any valid select query, with the caveat that the types of the values in the select list must match the types of the target fields.

There are two ways to assign a value to the @Id attribute:

Of course, the second option is only available for entities with database-level id generation (sequences or identity/autoincrement columns). It’s not available for entities whose id generator is implemented in Java, nor for entities whose id is assigned by the application.

The same two options are available for a @Version attribute. When no version is explicitly specified, the version for a new entity instance is used.

The on conflict clause lets us specify what action should be taken when the database already contains the record we’re attempting to insert.

Note that the on constraint variant accepting the name of a unique constraint only works on certain databases, or when just a single row is being inserted.

Like update and delete statements, an insert statement must be executed by calling Query.executeUpdate().

Now it’s time to look at something much more complicated.

Select statements retrieve and analyse data. This is what we’re really here for.

The full BNF for a select query is quite complicated, but there’s no need to understand it now. We’re displaying it here for future reference.

Most of the complexity here arises from the interplay of set operators (union, intersect, and except) with sorting.

We’ll describe the various clauses of a query later, in Root entities and joins and in Selection, projection, and aggregation, but for now, to summarize, a query might have these bits:

Every one of these clauses is optional!

For example, the simplest query in HQL has no select clause at all:

But we don’t necessarily recommend leaving off the select list.

Naturally, the previous query may be written with a select clause:

But when there’s no explicit select clause, the select list is implied by the result type of the query:

For complicated queries, it’s probably best to explicitly specify a select list.

An alternative "simplest" query has only a select list:

This results in a SQL from dual query (or equivalent).

Naturally, queries are always polymorphic. Indeed, a fairly innocent-looking HQL query can easily translate to a SQL statement with many joins and unions.

When there’s no explicit select clause, a further abbreviation is sometimes possible.

If there’s just one projected item in the select list, then, no sweat, that’s the type of each query result.

There’s really no need to fuss about with trying to represent a "tuple of length 1". We’re not even sure what to call those.

Problems arise as soon as we have multiple items in the select list of a query.

When there are multiple expressions in the select list then, by default, and in compliance with JPA, each query result is packaged as an array of type Object[].

This is bearable, but let’s explore some other options.

JPA lets us specify that we want each query result packaged as an instance of jakarta.persistence.Tuple. All we have to do is pass the class Tuple to createQuery().

The names of the Tuple elements are determined by the aliases given to the projected items in the select list. If no aliases are specified, the elements may be accessed by their position in the list, where the first item is assigned the position zero.

As an extension to JPA, and in a similar vein, Hibernate lets us pass Map or List, and have each result packaged as a map or list:

Unfortunately, not one of the types Object[], List, Map, nor Tuple lets us access an individual item in a result tuple without a type cast. Sure Tuple does the type cast for us when we pass a class object to get(), but it’s logically identical. Fortunately there’s one more option, as we’re about to see.

Hibernate 6 lets us pass an arbitrary class type with an appropriate constructor to createQuery() and will use it to package the query results. This works extremely nicely with record types.

It’s important that the constructor of BookSummary has parameters which exactly match the items in the select list.

We must caution that this still isn’t typesafe. In fact, we’ve just pushed the typecasts down into the call to createQuery(). But at least we don’t have to write them explicitly.

In JPQL, and in older versions of Hibernate, this functionality required more ceremony.

For example, the JPA-standard select new construct packages the query results into a user-written Java class instead of an array.

Simplifying slightly, the BNF for a projected item is:

Where the list of selections in an instantiation is essentially a nested projection list.

The boolean literal values are the (case-insensitive) keywords true and false.

String literals are enclosed in single quotes.

To escape a single quote within a string literal, use a doubled single quote: ''.

Alternatively, Java-style double-quoted strings are also allowed, with the usual Java character escape syntax.

This option is not much used.

Numeric literals come in several different forms:

For example:

The type of a numeric literal may be specified using a Java-style postfix:

It’s not usually necessary to specify the precision explicitly.

According to the JPQL specification, date and time literals may be specified using the JDBC escape syntax. Since this syntax is rather unpleasant to look at, HQL provides not one, but two alternatives.

Literals referring to the current date and time are also provided. Again there is some flexibility.

Of these, only local date, local time, local datetime, current_date, current_time, and current_timestamp are defined by the JPQL specification.

There are two sorts of duration in HQL:

For conceptual reasons, the two kinds of duration cannot be cleanly composed.

Literal duration expressions are of form n unit, for example 1 day or 10 year or 100 nanosecond.

The unit may be: day, month, quarter, year, second, minute, hour, or nanosecond.

HQL also provides a choice of formats for binary strings:

Literal values of a Java enumerated type may be written without needing to specify the enum class name:

In the examples above, the enum class is inferred from the type of the expression on the left of the comparison, assignment operator, or in predicate.

In this example the enum class is inferred from the type of the case expression.

HQL allows any Java static constant to be used in HQL, but it must be referenced by its fully-qualified name:

Entity names may also occur as a literal value. They do not need to be qualified.

See Types and typecasts.

HQL defines two ways to concatenate strings:

See below for details of the concat() function.

Many more operations on strings are defined below, in Functions.

The basic SQL arithmetic operators, +,-,*, and / are joined by the remainder operator %.

When both operands of a binary numeric operator have the same type, the result type of the whole expression is the same as the operands.

When the operands are of different type, one of the operands is implicitly converted to wider type, with wideness given, in decreasing order, by the list below:

Many more numeric operations are defined below, in Functions.

Arithmetic involving dates, datetimes, and durations is quite subtle. Among the issues to consider are:

Here we list the basic operations.

The by unit operator converts a duration to an integer, for example: (local date - your.birthday) by day evaluates to the number of days you still have to wait.

The function extract(unit from …​) extracts a field from a date, time, or datetime type, for example, extract(year from your.birthday) produces the year in which you were born, and throws away important information about your birthday.

Additional datetime operations, including the useful format() function, are defined below, in Functions.

The following special functions make it possible to discover or narrow expression types:

Let’s see what these functions do.

The function type(), applied to an identification variable, evaluates to the entity name of the referenced entity. This is mainly useful when dealing with entity inheritance hierarchies.

The function treat() may be used to narrow the type of an identification variable. This is useful when dealing with entity inheritance hierarchies.

The type of the expression treat(p as CreditCardPayment) is the narrowed type, CreditCardPayment, instead of the declared type Payment of p. This allows the attribute cardNumber declared by the subtype CreditCardPayment to be referenced.

The treat() function may even occur in a join.

The function cast() has a similar syntax, but is used to narrow basic types.

The function str(x) is a synonym for cast(x as String).

The following functions make it easy to deal with null values:

The coalesce() function is a sort of abbreviated case expression that returns the first non-null operand.

HQL allows ifnull() as a synonym for coalesce() in the case of exactly two arguments.

On the other hand, nullif() evaluates to null if its operands are equal, or to its first argument otherwise.

There are some very important functions for working with dates and times.

The special function extract() obtains a single field of a date, time, or datetime.

Field types include:

For a full list of field types, see the Javadoc for TemporalUnit.

The following functions are abbreviations for extract():

The format() function formats a date, time, or datetime according to a pattern.

The pattern must be written in a subset of the pattern language defined by Java’s java.time.format.DateTimeFormatter.

For a full list of format() pattern elements, see the Javadoc for Dialect.appendDatetimeFormat.

The truncate() function truncates the precision of a date, time, or datetime to the temporal unit specified by field type.

Supported temporal units are: year, month, day, hour, minute or second.

Truncating a date, time or datetime value means obtaining a value of the same type in which all temporal units smaller than field have been pruned. For hours, minutes and second this means setting them to 00. For months and days, this means setting them to 01.

Naturally, there are a good number of functions for working with strings.

Let’s take a closer look at just some of these.

The JPQL-standard and ANSI SQL-standard concat() function accepts a variable number of arguments, and produces a string by concatenating them.

The JPQL function locate() determines the position of a substring within another string.

The position() function has a similar purpose, but follows the ANSI SQL syntax.

Unsurprisingly, substring() returns a substring of the given string.

The trim() function follows the syntax and semantics of ANSI SQL. It may be used to trim leading characters, trailing characters, or both.

Its BNF is funky:

The pad() function has a syntax inspired by trim().

Its BNF is given by:

The collate() function selects a collation to be used for its string-valued argument. Collations are useful for binary comparisons with < or >, and in the order by clause.

For example, collate(p.name as ucs_basic) specifies the SQL standard collation ucs_basic.

Of course, we also have a number of functions for working with numeric values.

We haven’t included aggregate functions, ordered set aggregate functions, or window functions in this list, because their purpose is more specialized, and because they come with extra special syntax.

The functions described in this section are especially useful when dealing with @ElementCollection mappings, or with collection mappings involving an @OrderColumn or @MapKeyColumn.

The following functions accept either:

In case 2, application of the function produces an implicit join.

The next group of functions always accept a compound path referring to a collection or many-valued association of an entity. They’re interpreted as referring to the collection as a whole.

Application of one of these function produces an implicit subquery or implicit join.

This query has an implicit join:

This query has an implicit subquery:

The size() function returns the number of elements of a collection or to-many association.

The element() function returns a reference to an element of a joined set or list. For an identification variable (case 1 above), this function is optional. For a compound path (case 2), it’s required.

The index() function returns a reference to the index of a joined list.

In this example, element() is optional, but index() is required:

The key() function returns a reference to a key of a joined map. The value() function returns a reference to its value.

Finally, the following functions evaluate the id, version, or natural id of an entity, or the foreign key of a to-one association:

The following special functions let us embed a call to a native SQL function, refer directly to a column, or evaluate an expression written in native SQL.

The binary comparison operators are borrowed from SQL: =, >, >=, <, <=, <>.

The operands should be of the same type.

The ternary between operator, and its negation, not between, determine if a value falls within a range.

Of course, all three operands must be of compatible type.

The following operators make it easier to deal with null values. These predicates never evaluate to null.

These operators perform comparisons on values of type boolean. These predicates never evaluate to null.

For logical operations on predicates, see Logical operators below.

The following operators apply to collection-valued attributes and to-many associations.

The like operator performs pattern matching on strings. Its friend ilike performs case-insensitive matching.

Their syntax is defined by:

The expression on the right is a pattern, where:

The optional escape character allows a pattern to include a literal _ or % character.

As you can guess, not like and not ilike are the enemies of like and ilike, and evaluate to the exact opposite boolean values.

The in predicates evaluates to true if the value to its left is in …​ well, whatever it finds to its right.

Its syntax is unexpectedly complicated:

This less-than-lovely fragment of the HQL ANTLR grammar tells us that the thing to the right might be:

The type of the expression on the left, and the types of all the values on the right must be compatible.

It’s quite common to have a parameterized list of values.

We may even "vectorize" an in predicate, using a tuple constructor and a subquery with multiple selection items:

The binary comparisons we met above may involve a quantifier, either:

The quantifiers are unary prefix operators: all, every, any, and some.

The unary prefix exists operator evaluates to true if the thing to its right is nonempty.

The thing to its right might be:

As you can surely guess, not exists evaluates to true if the thing to the right is empty.

The logical operators are binary infix and and or, and unary prefix not.

Just like SQL, logical expressions are based on ternary logic. A logical operator evaluates to null if it has a null operand.

An identification variable is just a name we can use to refer to an entity and its attributes from expressions in the query. It may be any legal Java identifier. According to the JPQL specification, identification variables must be treated as case-insensitive language elements.

Identification variables may be declared with the as keyword, but this is optional.

A root entity reference, or what the JPQL specification calls a range variable declaration, is a direct reference to a mapped @Entity type by its entity name.

In this example, Book is the entity name, and book is the identification variable. The as keyword is optional.

Alternatively, a fully-qualified Java class name may be specified. Then Hibernate will query every entity which inherits the named type.

Of course, there may be multiple root entities.

This query may even be written using the syntax cross join in place of the commas:

Of course, it’s possible to write old-fashioned pre-ANSI-era joins:

But we never write HQL this way.

HQL and JPQL queries are inherently polymorphic. Consider:

This query names the Payment entity explicitly. But the CreditCardPayment and WireTransferPayment entities inherit Payment, and so payment ranges over all three types. Instances of all these entities are returned by the query.

A derived root is an uncorrelated subquery which occurs in the from clause.

The derived root may declare an identification variable.

This feature can be used to break a more complicated query into smaller pieces.

A subquery may also occur in a join, in which case it may be a correlated subquery.

A common table expression (CTE) is like a derived root with a name. We’ll discuss CTEs later.

An explicit root join works just like an ANSI-style join in SQL.

The join condition is written out explicitly in the on clause.

Every explicit association join specifies an entity attribute to be joined. The specified attribute:

In the case of an association or collection, the generated SQL will have a join of the same type. (For a many-to-many association it will have two joins.) In the case of an embedded attribute, the join is purely logical and does not result in a join in the generated SQL.

An explicit join may assign an identification variable to the joined entity.

For an outer join, we must write our query to accommodate the possibility that the joined association is missing.

For further information about collection-valued association references, see Joining collections and many-valued associations.

The with or on clause allows explicit qualification of the join conditions.

Join conditions occurring in the with or on clause are added to the on clause in the generated SQL.

A fetch join overrides the laziness of a given association, specifying that the association should be fetched with a SQL join. The join may be an inner or outer join.

For example, if Person has a one-to-many association named phones, the use of join fetch in the following query specifies that the collection elements should be fetched in the same SQL query:

In this example, we used a left outer join for book.publisher because we also wanted to obtain books with no publisher, but a regular inner join for book.authors because every book has at least one author.

A query may have more than one fetch join, but be aware that:

HQL doesn’t disallow it, but it’s usually a bad idea to apply a restriction to a join fetched entity, since the elements of the fetched collection would be incomplete. Indeed, it’s best to avoid even assigning an identification variable to a fetched joined entity except for the purpose of specifying a nested fetch join.

Fetch joins are disallowed in subqueries, where they would make no sense.

An explicit join may narrow the type of the joined entity using treat().

Here, the identification variable ccp declared to the right of treat() has the narrowed type CreditCardPayment, instead of the declared type Payment. This allows the attribute cardNumber declared by the subtype CreditCardPayment to be referenced in the rest of the query.

See Types and typecasts for more information about treat().

A join clause may contain a subquery, either:

The lateral keyword just distinguishes the two cases.

This query may also be expressed using a lateral join:

A lateral join may be an inner or left outer join, but not a right join, nor a full join.

Lateral joins are particularly useful for computing top-N elements of multiple groups.

It’s not necessary to explicitly join every entity that occurs in a query. Instead, entity associations may be navigated, just like in Java:

It’s clear that:

In the second case, Hibernate with automatically add a join to the generated SQL if necessary.

As in this example, implicit joins usually appear outside the from clause of the HQL query. However, they always affect the from clause of the SQL query.

The example above is equivalent to:

Note that:

This query:

results in just one SQL join, and is just a different way to write:

When a join involves a collection or many-valued association, the declared identification variable refers to the elements of the collection, that is:

In this example, the identification variable author is of type Author, the element type of the list Book.authors. But if we need to refer to the index of an Author in the list, we need some extra syntax.

You might recall that we mentioned List indexes and Map keys and values a bit earlier. These functions may be applied to the identification variable declared in a collection join or many-valued association join.

In particular, index() and key() obtain a reference to a list index or map key.

A path expression like book.authors.name is not considered legal. We can’t just navigate a many-valued association with this syntax.

Instead, the functions element(), index(), key(), and value() may be applied to a path expression to express an implicit join. So we must write element(book.authors).name or index(book.authors).

An element of an indexed collection (an array, list, or map) may even be identified using the index operator:

The group by clause looks quite similar to the select clause—it has a list of grouped items, but:

The BNF for a grouped item is just:

Consider the following queries: