QueryLanguage.adoc

Hibernate Query Language

This chapter describes Hibernate Query Language (HQL) and Jakarta Persistence Query Language (JPQL).

Note

JPQL was inspired by early versions of HQL, and is a subset of modern HQL. Here we focus on describing the complete, more powerful HQL language as it exists today. If strict Jakarta Persistence compliance is desired, use the setting hibernate.jpa.compliance.query=true. With this configuration, any attempt to use HQL features beyond the JPQL subset will result in an exception. We don’t recommend the use of this setting.

HQL (and JPQL) are loosely based on SQL and are easy to learn for anyone familiar with SQL.

Identifiers and case sensitivity

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, Person, name, p, 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:

• keywords and function names are case-insensitive, but

• identification variable names, Java class names, and the names of attributes of Java classes, are case-sensitive.

Note	Incidentally, it’s standard practice to use lowercase keywords in HQL and JPQL. The use of uppercase keywords indicates an endearing but unhealthy attachment to the culture of the 1970’s.

Just to reiterate these rules:

• select, SeLeCT, sELEct, and SELECT are all the same, and also

• upper(name) and UPPER(name) are the same, but

• from BackPack and from Backpack are different, referring to different Java classes, and similarly,

• person.nickName and person.nickname are different, since the path expression element nickName refers to an attribute of an entity defined in Java, and finally,

• person.nickName, Person.nickName, and PERSON.nickName are also all different, since the first element of a path expression is an identification variable.

Note	The JPQL specification defines identification variables as case-insensitive. And so in strict JPA-compliant mode, Hibernate treats person.nickName, Person.nickName, and PERSON.nickName as the same.

A quoted identifier is written in backticks. Quoting lets you use a keyword as an identifier, for example thing.`select` .

Statement types

HQL features four different kinds of statement:

• select queries,

• update statements,

• delete statements, and

• insert …​ values and insert …​ select statements.

Important

The effect of an update or delete statement is not reflected in the persistence context, nor in the state of entity objects held in memory at the time the statement is executed. It is the responsibility of the application to maintain synchronization of state held in memory with the database after execution of an update or delete statement.

Select statements

The full BNF for a select query is quite complicated.

link:extras/statement_select_bnf.txt[role=include]

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 this chapter, but to summarize, a query might have:

• a with clause, specifying named subqueries to be used in the following query,

• a select list, specifying a projection (the things to return from the query),

• a from clause and joins, specifying the entities involved in the query, and how they’re related to each other,

• a where clause, specifying a restriction,

• a group by clause, for aggregation,

• a having clause, specifying a restriction to apply after aggregation,

• set operators applied to the results of multiple subqueries,

• an order by clause, for sorting the results, and even

• a limit/offset clause, for limiting or paginating the results.

Every one of these clauses is optional!

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

We don’t necessarily recommend leaving off the select list.

Note

HQL doesn’t require a select clause, but JPQL does. Naturally, the previous query may be written with a select clause: link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include] When there’s no explicit select clause, the select list is implied by the result type of the query: link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include] For complicated queries, it’s probably best to explicitly specify a select list.

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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

Tip

Looking carefully at the BNF given above, you might notice that the select list may occur either at the beginning of a query, or near the end, right before order by. Of course, standard SQL, and JPQL, require that the select list comes at the beginning. But it’s more natural to put it last: link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include] This form of the query is more readable, because the alias is declared before it’s used, just as God and nature intended.

Update statements

The BNF for an update statement is much easier to understand:

link:extras/statement_update_bnf.txt[role=include]

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

For example:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

An update statement must be executed using Query#executeUpdate(). A single HQL update statement might result in multiple SQL update statements executed against the database.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/../batch/BatchTests.java[role=include]

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/../batch/BatchTests.java[role=include]

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

Note	In a JOINED inheritance hierarchy, multiple rows are required to store a single entity instance. In this case, the update count returned by Hibernate might not be exactly the same as the number of rows affected in the database.

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

Adding the keyword versioned—writing update versioned—specifies that Hibernate should update the version or update timestamp.

Note	update versioned does not work with custom version types defined by implementing UserVersionType, and is not available in JPQL.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/../batch/BatchTests.java[role=include]

Update statements are polymorphic, and affect mapped subclasses of the given entity class.

An update statement may use implicit or explicit joins. Beware that if joins lead to row duplications, e.g. when joining the target row against a non-unique column, it is undefined which row is updated or whether an error is thrown.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/../query/hql/UpdateJoinTests.java[role=include]

Note	With JPA compliance enabled, update or delete statement may not have an implicit (or explicit) join.

Delete statements

The BNF for a delete statement is also quite simple:

link:extras/statement_delete_bnf.txt[role=include]

A delete statement is executed by calling Query#executeUpdate(). A single HQL delete statement might result in multiple SQL delete statements executed against the database.

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

Delete statements are polymorphic, and affect mapped subclasses of the given entity class.

A delete statement may use implicit or explicit joins.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/../query/hql/DeleteJoinTests.java[role=include]

Note	With JPA compliance enabled, update or delete statement may not have an implicit (or explicit) join.

Insert statements

There are two kinds of insert statement:

• insert …​ values, where the attribute values to insert are given directly as tuples, and

• insert …​ select, where the inserted attribute values are sourced from a subquery.

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.

Tip	The first sort of insert statement is not as useful. It’s usually better to just use persist(). On the other hand, you might consider using it to set up test data.

Note	insert statements are not available in JPQL.

The BNF for an insert statement is:

link:extras/statement_insert_bnf.txt[role=include]

For example:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/../batch/BatchTests.java[role=include]

An insert statement must be executed by calling Query#executeUpdate().

Important

An insert statement is inherently not polymorphic! Its list of target fields is of fixed length, whereas each subclass of an entity class might declare additional fields. If the entity is involved in a mapped inheritance hierarchy, only attributes declared directly by the named entity and its superclasses may occur in the list of target fields. Attributes declared by subclasses may not occur.

The queryExpression 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.

Note

This is checked during query compilation rather than allowing the type check to delegate to the database. This may cause problems when two Java types map to the same database type. For example, an attribute of type LocalDateTime and an attribute or type Timestamp both map to the SQL type timestamp, but are not considered assignable by the query compiler.

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

• explicitly specify the id attribute in the list of target fields, and its value in the values assigned to the target fields, or

• omit it, in which case a generated value is used.

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.

To implement "upsert" semantics i.e. insert-or-update, the on conflict clause can be used. Reacting on conflicts can be either based on the name or the list of attribute paths of a unique constraint. Using the unique constraint name as conflict target requires either native database support, which at the time of writing is only available in PostgreSQL, or that the statement is a single row insert. A single row insert can be ensured by specifying only a single values tuple in case of an insert-values statement, or using fetch first 1 rows only in case of an insert-select statement.

Possible conflict actions are to ignore the conflict or update conflicting objects/rows.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/../query/hql/InsertConflictTests.java[role=include]

The special alias excluded is available in the update set clause of the conflict clause and refers to the values that failed insertion due to a unique constraint conflict.

Note

The MySQL/MariaDB implementation leverages the native on duplicate key clause which does not support specifying an explicit column list or constraint name. Beware that this implementation might produce different results than on other databases if a table has more than a single unique constraint. Another quirk of this implementation is that the MySQL/MariaDB JDBC driver returns surprising update counts. For every row that is inserted, the update count is incremented by 1, but for rows that are updated, the update count is incremented by 2. To learn more about this, refer to the MySQL documentation.

Literals

We now switch gears, and begin describing the language from the bottom up. The very bottom of a programming language is its syntax for literal values.

The most important literal value in this language is null. It’s assignable to any other type.

Boolean literals

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

String literals

String literals are enclosed in single quotes.

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Numeric literals

Numeric literals come in several different forms.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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

Postfix	Type	Java type
L or l	long integer	long
D or d	double precision	double
F or f	single precision	float
BI or bi	large integer	BigInteger
BD or bd	exact decimal	BigDecimal

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

Note	In a literal with an exponent, the E is case-insensitive. Similarly, the Java-style postfix is case-insensitive.

Hexadecimal literals may be written using the same syntax as Java: 0X1A2B or 0x1a2b.

Date and time literals

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.

Date/time type	Recommended Java type	JDBC escape syntax	Braced literal syntax	Explicitly typed literal syntax
Date	LocalDate	{d 'yyyy-mm-dd'}	{yyyy-mm-dd}	date yyyy-mm-dd
Time	LocalTime	{t 'hh:mm'}	{hh:mm}	time hh:mm
Time with seconds	LocalTime	{t 'hh:mm:ss'}	{hh:mm:ss}	time hh:mm:ss
Datetime	LocalDateTime	{ts 'yyyy-mm-ddThh:mm:ss'}	{yyyy-mm-dd hh:mm:ss}	datetime yyyy-mm-dd hh:mm:ss
Datetime with milliseconds	LocalDateTime	{ts 'yyyy-mm-ddThh:mm:ss.millis'}	{yyyy-mm-dd hh:mm:ss.millis}	datetime yyyy-mm-dd hh:mm:ss.millis
Datetime with an offset	OffsetDateTime	{ts 'yyyy-mm-ddThh:mm:ss+hh:mm'}	{yyyy-mm-dd hh:mm:ss +hh:mm}	datetime yyyy-mm-dd hh:mm:ss +hh:mm
Datetime with a time zone	OffsetDateTime	{ts 'yyyy-mm-ddThh:mm:ss GMT'}	{yyyy-mm-dd hh:mm:ss GMT}	datetime yyyy-mm-dd hh:mm:ss GMT

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

Date/time type	Java type	Underscore syntax	Spaced syntax
Date	java.time.LocalDate	local_date	local date
Time	java.time.LocalTime	local_time	local time
Datetime	java.time.LocalDateTime	local_datetime	local datetime
Offset datetime	java.time.OffsetDateTime	offset_datetime	offset datetime
Instant	java.time.Instant	instant	instant
Date	java.sql.Date	current_date	current date
Time	java.sql.Time	current_time	current time
Datetime	java.sql.Timestamp	current_timestamp	current timestamp

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

Important

The use of date and time types from the java.sql package is strongly discouraged! Always use java.time types in new code.

Duration literals

There are two sorts of duration in HQL:

• year/quarter/month/week/day durations, and

• week/day/hour/minute/second/nanosecond durations.

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.

Note	A HQL duration is considered to map to a Java java.time.Duration, but semantically they’re perhaps more similar to an ANSI SQL INTERVAL type.

Binary string literals

HQL also provides a choice of formats for binary strings:

• the braced syntax {0xDE, 0xAD, 0xBE, 0xEF}, a list of Java-style hexadecimal byte literals, or

• the quoted syntax X’DEADBEEF' or x’deadbeef', similar to SQL.

Enum literals

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Here, the enum class is inferred from the type of the expression on the left of the relational operator.

Java constants

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Literal entity names

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

Expressions

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

String concatenation

HQL defines two ways to concatenate strings:

• the SQL-style concatenation operator, ||, and

• the JPQL-standard concat() function.

See below for details of the concat() function.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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

Numeric arithmetic

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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)

Many more numeric operations are defined below, in Functions.

Datetime arithmetic

Arithmetic involving dates, datetimes, and durations is quite subtle. Here we list the basic operations.

Operator	Expression type	Example	Resulting type
-	Difference between two dates	your.birthday - local date	year/quarter/month/week/day duration
-	Difference between two datetimes	local datetime - record.lastUpdated	week/day/hour/minute/second/nanosecond duration
+	Sum of a date and a year/quarter/month/week/day duration	local date + 1 week	date
+	Sum of a datetime and a week/day/hour/minute/second/nanosecond duration	record.lastUpdated + 1 second	datetime
*	Product of an integer and a duration	billing.cycles * 30 day	duration
by unit	Convert a duration to an integer	(1 year) by day	integer

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.

Important

Please carefully note the difference between these two operations: by and extract() both evaluate to an integer, but they have very different uses.

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

Identification variables and path expressions

Identification variables, and path expressions beginning with an identification variable are legal expression in almost every context.

See Identification variables and Implicit association joins (path expressions).

Case expressions

Just like in standard SQL, there are two forms of case expression:

• the simple case expression, and

• the so-called searched case expression.

Tip	Case expressions are verbose. It’s often simpler to use the coalesce(), nullif(), or ifnull() functions, as described below in Functions for working with null values.

Simple case expressions

The syntax of the simple form is defined by:

link:extras/simple_case_bnf.txt[role=include]

For example:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Searched case expressions

The searched form has the following syntax:

link:extras/searched_case_bnf.txt[role=include]

For example:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

A case expression may contain complex expression, including operator expressions:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Functions

Both HQL and JPQL define some standard functions and make them portable between databases.

Tip	A program that wishes to remain portable between Jakarta Persistence providers should in principle limit itself to the use of these functions. On the other hand, this is an extremely short list. Any nontrivial program will probably need to look beyond it.

In some cases, the syntax of these functions looks a bit funny at first, for example, cast(number as String), or extract(year from date), or even trim(leading '.' from string). This syntax is inspired by standard ANSI SQL, and we promise you’ll get used to it.

Important

HQL abstracts away from the actual database-native SQL functions, letting you write queries which are portable between databases. For some functions, and always depending on the database, a HQL function invocation translates to a quite complicated SQL expression!

In addition, there are several ways to use a database function that’s not known to Hibernate.

Types and typecasts

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

Special function	Purpose	Signature	JPA standard
type()	The (concrete) entity or embeddable type	type(e)	✓
treat()	Narrow an entity or embeddable type	treat(e as Entity)	✓
cast()	Narrow a basic type	cast(x as Type)	✗
str()	Cast to a string	str(x)	✗

Let’s see what these functions do.

type()

The function type(), applied to an identification variable or to an entity-valued or embeddable-valued path expression, evaluates to the concrete type, that is, the Java Class, of the referenced entity or embeddable. This is mainly useful when dealing with entity inheritance hierarchies.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

treat()

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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.

cast()

The function cast() has a similar syntax, but is used to narrow basic types. Its first argument is usually an attribute of an entity, or a more complex expression involving entity attributes.

The target type is an unqualified Java class name: String, Long, Integer, Double, Float, Character, Byte, BigInteger, BigDecimal, LocalDate, LocalTime, LocalDateTime, etc.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

str()

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

ordinal()

The function ordinal(x) extracts the ordinal value of an enum. It supports both enum fields mapped as ORDINAL and STRING.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/EnumTest.java[role=include]

Functions for working with null values

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

Function	Purpose	Signature	JPA standard
coalesce()	First non-null argument	coalesce(x, y, z)	✓
ifnull()	Second argument if first is null	ifnull(x,y)	✗
nullif()	null if arguments are equal	nullif(x,y)	✓

coalesce()

An abbreviated case expression that returns the first non-null operand.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

ifnull()

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

nullif()

Evaluates to null if its operands are equal, or to its first argument otherwise.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Functions for working with dates and times

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

Special function	Purpose	Signature	JPA standard
extract()	Extract a datetime field	extract(field from x)	✓
format()	Format a datetime as a string	format(datetime as pattern)	✗
trunc() or truncate()	Datetime truncation	truncate(datetime, field)	✗

extract()

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

Field types include: day, month, year, second, minute, hour, day of week, day of month, week of year, date, time, epoch and more. For a full list of field types, see the Javadoc for TemporalUnit.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

The following functions are abbreviations for extract():

Function	Long form using extract()	JPA standard
year(x)	extract(year from x)	✗
month(x)	extract(month from x)	✗
day(x)	extract(day from x)	✗
hour(x)	extract(year from x)	✗
minute(x)	extract(year from x)	✗
second(x)	extract(year from x)	✗

Tip	These abbreviations aren’t part of the JPQL standard, but on the other hand they’re a lot less verbose.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

format()

This function formats a date, time, or datetime according to a pattern.

The syntax is format(datetime as pattern), and 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.

trunc() or truncate()

This function truncates a date, time, or datetime to the temporal unit specified by field.

The syntax is truncate(datetime, field). Supported temporal units are: year, month, day, hour, minute or second.

Truncating a date, time or datetime value translates to 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.

Functions for working with strings

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

Function	Purpose	Syntax	JPA standard / ANSI SQL Standard
upper()	The string, with lowercase characters converted to uppercase	upper(s)	✓ / ✓
lower()	The string, with uppercase characters converted to lowercase	lower(s)	✓ / ✓
length()	The length of the string	length(s)	✓ / ✗
concat()	Concatenate strings	concat(x, y, z)	✓ / ✗
locate()	Location of string within a string	locate(s, d), locate(s, d, i)	✓ / ✗
position()	Similar to locate()	position(pattern in string)	✗ / ✓
substring()	Substring of a string (JPQL-style)	substring(s, i), substring(s, i, l)	✓ / ✗
substring()	Substring of a string (ANSI SQL-style)	substring(string from start), substring(string from start for length)	✗ / ✓
trim()	Trim characters from string	trim(string), trim(leading from string), trim(trailing from string), or trim(leading character from string)	✓ / ✓
overlay()	For replacing a substring	overlay(string placing replacement from start), overlay(string placing replacement from start for length)	✗ / ✓
pad()	Pads a string with whitespace, or with a specified character	pad(string with length), pad(string with length leading), pad(string with length trailing), or pad(string with length leading character)	✗ / ✗
left()	The leftmost characters of a string	left(string, length)	✗ / ✗
right()	The rightmost characters of a string	right(string, length)	✗ / ✗
replace()	Replace every occurrence of a pattern in a string	replace(string, pattern, replacement)	✗ / ✗
repeat()	Concatenate a string with itself multiple times	repeat(string, times)	✗ / ✗
collate()	Select a collation	collate(p.name as collation)	✗ / ✗

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

Important

Contrary to Java, positions of characters within strings are indexed from 1 instead of 0!

concat()

Accepts a variable number of arguments, and produces a string by concatenating them.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

locate()

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

• The optional third argument is used to specify a position at which to start the search.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

position()

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

substring()

Returns a substring of the given string.

• The second argument specifies the position of the first character of the substring.

• The optional third argument specifies the maximum length of the substring.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

trim()

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Its BNF is funky:

trimFunction
    : "TRIM" "(" trimSpecification? trimCharacter? "FROM"? expression ")" ;
trimSpecification
    : "LEADING" | "TRAILING" | "BOTH" ;

collate()

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.

Important

Collations aren’t very portable between databases.

Numeric functions

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

Function	Purpose	Signature	JPA standard
abs()	The magnitude of a number	abs(x)	✓
sign()	The sign of a number	sign(x)	✓
mod()	Remainder of integer division	mod(n,d)	✓
sqrt()	Square root of a number	sqrt(x)	✓
exp()	Exponential function	exp(x)	✓
power()	Exponentiation	power(x,y)	✓
ln()	Natural logarithm	ln(x)	✓
round()	Numeric rounding	round(number), round(number, places)	✓
trunc() or truncate()	Numeric truncation	truncate(number), truncate(number, places)	✗
floor()	Floor function	floor(x)	✓
ceiling()	Ceiling function	ceiling(x)	✓
log10()	Base-10 logarithm	log10(x)	✗
log()	Arbitrary-base logarithm	log(b,x)	✗
pi	π	pi	✗
sin(), cos(), tan(), asin(), acos(), atan()	Basic trigonometric functions	sin(theta), cos(theta)	✗
atan2()	Two-argument arctangent (range (-π,π])	atan2(y, x)	✗
sinh(), cosh(), tanh()	Hyperbolic functions	sinh(x), cosh(x), tanh(x)	✗
degrees()	Convert radians to degrees	degrees(x)	✗
radians()	Convert degrees to radians	radians(x)	✗
least()	Return the smallest of the given arguments	least(x, y, z)	✗
greatest()	Return the largest of the given arguments	greatest(x, y, z)	✗

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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

Functions for dealing with collections

The following functions apply to any identification variable that refers to a joined collection.

Function	Purpose	JPA standard
size()	The size of a collection	✗
element()	The element of a list	✗
index()	The index of a list element	✗
key()	The key of a map entry	✗
value()	The value of a map entry	✗
entry()	The whole entry in a map	✗
elements()	See below	✗
indices()	See below	✗

size()

The number of elements of a collection or to-many association.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

element() and index()

A reference to an element or index of joined list.

key(), value(), and entry()

A reference to a key, value, or entry of a joined map.

elements(), and indices()

Later, in Elements and indices, and in Aggregate functions and collections, we will learn about these special functions for quantifying over the elements or indices of a particular collection.

Functions for working with ids and versions

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

Function	Purpose	JPA standard
id()	The value of the entity @Id attribute.	✗
version()	The value of the entity @Version attribute.	✗
naturalid()	The value of the entity @NaturalId attribute.	✗
fk()	The value of the foreign key column mapped by a @ManyToOne (or logical @ManyToOne) association. Mainly useful with @NotFound mappings.	✗

Functions for dealing with arrays

The following functions deal with SQL array types, which are not supported on every database.

Function	Purpose
array()	Creates an array based on the passed arguments
array_list()	Like array, but returns the result as List<?>
array_agg()	Aggregates row values into an array
array_position()	Determines the position of an element in an array
array_positions()	Determines all positions of an element in an array
array_positions_list()	Like array_positions, but returns the result as List<Integer>
array_length()	Determines the length of an array
array_concat()	Concatenates array with each other in order
array_prepend()	Prepends element to array
array_append()	Appends element to array
array_contains()	Whether an array contains an element
array_contains_nullable()	Whether an array contains an element, supporting null element
array_includes()	Whether an array contains another array
array_includes_nullable()	Whether an array contains another array, supporting null elements
array_intersects()	Whether an array holds at least one element of another array
array_intersects_nullable()	Whether an array holds at least one element of another array, supporting null elements
array_get()	Accesses the element of an array by index
array_set()	Creates array copy with given element at given index
array_remove()	Creates array copy with given element removed
array_remove_index()	Creates array copy with the element at the given index removed
array_slice()	Creates a sub-array of the based on lower and upper index
array_replace()	Creates array copy replacing a given element with another
array_trim()	Creates array copy trimming the last N elements
array_fill()	Creates array filled with the same element N times
array_fill_list()	Like array_fill, but returns the result as List<?>
array_to_string()	String representation of array
unnest()	Turns an array into rows

array() and array_list()

Creates an array based on the passed arguments, and infers the array type from the context if possible. To retrieve the result as List<?>, use the array_list() function.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayConstructorTest.java[role=include]

Alternatively, it’s also possible to construct an array with the shorthand bracket syntax [ and ], which is syntax sugar that translates to the array constructor function.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayConstructorTest.java[role=include]

array_agg()

An ordered set aggregate function that aggregates values to an array.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayAggregateTest.java[role=include]

array_position() or position()

Returns the 1-based position of an element in the array. Returns 0 if the element is not found and null if the array is null.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayPositionTest.java[role=include]

Alternatively, it is also possible to use the position() function, which is overloaded to also accept an array argument.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayPositionTest.java[role=include]

array_positions() and array_positions_list()

Returns an int[] of 1-based positions of matching elements in the array. Returns an empty array if the element is not found and null if the array is null. To retrieve the result as List<Integer>, use the array_positions_list() function.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayPositionsTest.java[role=include]

array_length() or length()

Returns size of the passed array. Returns null if the array is null.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayLengthTest.java[role=include]

Alternatively, it is also possible to use the length() function, which is overloaded to also accept an array argument.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayLengthTest.java[role=include]

array_concat() or ||

Concatenates arrays with each other in order. Returns null if one of the arguments is null.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayConcatTest.java[role=include]

Arrays can also be concatenated with the || (double-pipe) operator.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayConcatTest.java[role=include]

In addition, the || (double-pipe) operator also support concatenating single elements to arrays.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayConcatTest.java[role=include]

array_prepend()

Prepends element to array. Returns null if the array argument is null.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayPrependTest.java[role=include]

array_append()

Appends element to array. Returns null if the array argument is null.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayAppendTest.java[role=include]

array_contains() and array_contains_nullable()

Checks if the first array argument contains the element represented by the second argument. Returns null if the first argument is null. The result of the array_contains function is undefined when the second argument, the element to search, is null.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayContainsTest.java[role=include]

Alternatively, it’s also possible to check for containment with the contains predicate, where the left hand side of the predicate is the array and the right hand side the value to check. This is syntax sugar that translates to the array_contains function.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayContainsTest.java[role=include]

array_includes() and array_includes_nullable()

Checks if the first array argument contains the elements of the second array argument. Returns null if the first argument is null. The result of the array_includes function is undefined when the second argument contains a null.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayIncludesTest.java[role=include]

To search for null elements, the array_includes_nullable function must be used.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayIncludesTest.java[role=include]

Alternatively, it’s also possible to use the includes predicate, where the left hand side of the predicate is the array and the right hand side the array of values to check. This is syntax sugar that translates to the array_includes function.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayIncludesTest.java[role=include]

array_intersects() and array_intersects_nullable()

Checks if the first array argument any of the elements of the second array argument. Returns null if either of the arguments is null. The result of array_intersects is undefined when the second array argument contains a null array element. Only array_intersects_nullable is guaranteed to produce correct results for null array elements.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayIntersectsTest.java[role=include]

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayIntersectsTest.java[role=include]

Alternatively, it’s also possible to check for intersection with the intersects predicate. This is syntax sugar that translates to the array_intersects function.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayIntersectsTest.java[role=include]

array_get()

Returns the element of an array at the given 1-based index. Returns null if either of the arguments is null, and also if the index is bigger than the array length.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayGetTest.java[role=include]

array_set()

Returns an array copy with the given element placed at the given 1-based index, filling up prior slots with null if necessary.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArraySetTest.java[role=include]

array_remove()

Returns an array copy with the given element removed from the array. Allows removal of null elements.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayRemoveTest.java[role=include]

array_remove_index()

Returns an array copy with the element at the given index removed from the array.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayRemoveIndexTest.java[role=include]

array_slice()

Returns the sub-array as specified by the given 1-based inclusive start and end index. Returns null if any of the arguments is null and also if the index is out of bounds.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArraySliceTest.java[role=include]

Alternatively, it’s also possible to slice an array by specifying the lower and upper bound, separated by a colon, as index in the bracket array index syntax array[lowerIndex:upperIndex]. This is syntax sugar that translates to the array_slice function.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArraySliceTest.java[role=include]

array_replace()

Returns an array copy which has elements matching the second argument replaced by the third argument.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayReplaceTest.java[role=include]

array_trim()

Returns an array copy without the last N elements, specified by the second argument. It is an error if any array has a length smaller than the second argument.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayTrimTest.java[role=include]

array_fill() and array_fill_list()

Creates an array filled with the same element N times as specified by the arguments. It is an error to supply an array length smaller than 0. To retrieve the result as List<?>, use the array_fill_list() function.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayFillTest.java[role=include]

array_to_string() or cast(array as String)

Concatenates the array elements with a separator, as specified by the arguments. Null values are filtered, but the optional third argument can be specified to define a default value to use when a null array element is encountered. Returns null if the first argument is null.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayToStringTest.java[role=include]

Alternatively, it is also possible to use cast(array as String), which is a short version of concat('[', array_to_string(array, ',', 'null'), ']').

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayToStringTest.java[role=include]

unnest()

A set-returning function, which turns the single array argument into rows. Returns no rows if the array argument is null or an empty array. The index() function can be used to access the 1-based array index of an array element.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayUnnestStructTest.java[role=include]

The lateral keyword is mandatory if the argument refers to a from node item of the same query level. Basic plural attributes can also be joined directly, which is syntax sugar for lateral unnest(…​).

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayUnnestTest.java[role=include]

Functions for dealing with JSON

The following functions deal with SQL JSON types, which are not supported on every database.

Note	The following functions are incubating/tech-preview and to use them in HQL, it is necessary to enable the hibernate.query.hql.json_functions_enabled configuration setting.

Function	Purpose
json_object()	Constructs a JSON object from pairs of key and value arguments
json_array()	Constructs a JSON array from arguments
json_value()	Extracts a value from a JSON document by JSON path
json_exists()	Checks if a JSON path exists in a JSON document
json_query()	Queries non-scalar values by JSON path in a JSON document
json_arrayagg()	Creates a JSON array by aggregating values
json_objectagg()	Creates a JSON object by aggregating values
json_set()	Inserts/Replaces a value by JSON path within a JSON document
json_remove()	Removes a value by JSON path within a JSON document
json_mergepatch()	Merges JSON documents by performing an RFC 7396 compliant merge
json_array_append()	Appends to a JSON array of a JSON document by JSON path
json_array_insert()	Inserts a value by JSON path to a JSON array within a JSON document
json_table()	Turns a JSON document into rows

json_object()

Constructs a JSON object from pairs of key and value arguments.

link:extras/json_object_bnf.txt[role=include]

Argument count must be even and expressions alternate between keys and values i.e. key1, value1, key2, value2, …​. Alternatively, it is also possible to use a : (colon) to separate keys from values or the value keyword.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonObjectTest.java[role=include]

Although database dependent, usually null values are present in the resulting JSON object. To remove null value entries, use the absent on null clause.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonObjectTest.java[role=include]

json_array()

Constructs a JSON array from arguments.

link:extras/json_array_bnf.txt[role=include]

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonArrayTest.java[role=include]

Although database dependent, usually null values are absent in the resulting JSON array. To retain null elements, use the null on null clause.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonArrayTest.java[role=include]

json_value()

Extracts a scalar value by JSON path from a JSON document.

link:extras/json_value_bnf.txt[role=include]

The first argument is an expression to a JSON document. The second argument is a JSON path as String expression.

Warning

Some databases might also allow extracting non-scalar values. Beware that this behavior is not portable.

Note	It is recommended to only us the dot notation for JSON paths instead of the bracket notation, since most databases support only that.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonValueTest.java[role=include]

The passing clause allows to reuse the same JSON path but pass different values for evaluation.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonValueTest.java[role=include]

The returning clause allows to specify the cast target i.e. the type of value to extract.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonValueTest.java[role=include]

The on error clause defines the behavior when an error occurs while resolving the value for the JSON path. Conditions that classify as errors are database dependent, but usual errors which can be handled with this clause are:

• First argument is not a valid JSON document

• Second argument is not a valid JSON path

• JSON path does not resolve to a scalar value

The default behavior of on error is database specific, but usually, null is returned on an error. It is recommended to specify this clause when the exact error behavior is important.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonValueTest.java[role=include]

The on empty clause defines the behavior when the JSON path does not match the JSON document. By default, null is returned on empty.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonValueTest.java[role=include]

To actually receive an error on empty, it is necessary to also specify error on error. Depending on the database, an error might still be thrown even without that, but that is not portable.

Note	The H2 emulation only supports absolute JSON paths using the dot notation.

json_exists()

Checks if a JSON document contains a JSON path.

link:extras/json_exists_bnf.txt[role=include]

The first argument is an expression to a JSON document. The second argument is a JSON path as String expression.

Note	It is recommended to only us the dot notation for JSON paths instead of the bracket notation, since most databases support only that.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonExistsTest.java[role=include]

The passing clause allows to reuse the same JSON path but pass different values for evaluation.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonExistsTest.java[role=include]

The on error clause defines the behavior when an error occurs while checking for existence with the JSON path. Conditions that classify as errors are database dependent, but usual errors which can be handled with this clause are:

• First argument is not a valid JSON document

• Second argument is not a valid JSON path

The default behavior of on error is database specific, but usually, false is returned on an error. It is recommended to specify this clause when the exact error behavior is important.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonExistsTest.java[role=include]

Note	The H2 emulation only supports absolute JSON paths using the dot notation.

json_query()

Queries non-scalar values from a JSON document by a JSON path.

link:extras/json_query_bnf.txt[role=include]

The first argument is an expression to a JSON document. The second argument is a JSON path as String expression.

Warning

Some databases might also allow querying scalar values. Beware that this behavior is not portable.

Note	It is recommended to only us the dot notation for JSON paths instead of the bracket notation, since most databases support only that.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonQueryTest.java[role=include]

The passing clause allows to reuse the same JSON path but pass different values for evaluation.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonQueryTest.java[role=include]

The wrapper clause allows to specify whether results of a query should be wrapped in brackets [] i.e. an array. The default behavior is to omit an array wrapper i.e. without wrapper. It is an error when a json_query returns more than a single result and without wrapper is used. How an error like this should be handled can be controlled with the on error clause.

Warning

Since the default behavior of on error is database dependent, some databases might return a comma separated list of values even when using without wrapper. This is not portable.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonQueryTest.java[role=include]

The on error clause defines the behavior when an error occurs while querying with the JSON path. Conditions that classify as errors are database dependent, but usual errors which can be handled with this clause are:

• First argument is not a valid JSON document

• Second argument is not a valid JSON path

• Multiple json_query results when without wrapper is used

The default behavior of on error is database specific, but usually, null is returned on an error. It is recommended to specify this clause when the exact error behavior is important.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonQueryTest.java[role=include]

The on empty clause defines the behavior when the JSON path does not match the JSON document. By default, null is returned on empty.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonQueryTest.java[role=include]

To actually receive an error on empty, it is necessary to also specify error on error. Depending on the database, an error might still be thrown even without that, but that is not portable.

Note	The H2 emulation only supports absolute JSON paths using the dot notation.

json_arrayagg()

Creates a JSON array by aggregating values.

link:extras/json_arrayagg_bnf.txt[role=include]

This aggregate function is similar to an ordered set aggregate function since it allows to specify the order in which elements are aggregated, but uses a special syntax.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonArrayAggregateTest.java[role=include]

Although database dependent, usually null values are absent in the resulting JSON array. To retain null elements, use the null on null clause.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonArrayAggregateTest.java[role=include]

The order in which elements are aggregated can be defined by specifying an order by clause.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonArrayAggregateTest.java[role=include]

json_objectagg()

Creates a JSON object by aggregating values.

link:extras/json_objectagg_bnf.txt[role=include]

The arguments represent the key and the value to be aggregated to the JSON object, separated by the value keyword or a : (colon).

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonObjectAggregateTest.java[role=include]

Although database dependent, usually null values are absent in the resulting JSON array. To retain null elements, use the null on null clause.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonObjectAggregateTest.java[role=include]

Duplicate keys usually are retained in the resulting string. Use with unique keys to specify that the encounter of a duplicate key should cause an error.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonObjectAggregateTest.java[role=include]

Warning

Some databases like e.g. MySQL, SAP HANA, DB2 and SQL Server do not support raising an error on duplicate keys.

json_set()

Inserts/Replaces a value by JSON path within a JSON document. The function takes 3 arguments, the json document, the json path and the new value to set/insert.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonSetTest.java[role=include]

Warning

SAP HANA, DB2, H2 and HSQLDB do not support this function.

json_remove()

Removes a value by JSON path within a JSON document. The function takes 2 arguments, the json document and the json path representing what to remove.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonRemoveTest.java[role=include]

Warning

SAP HANA, DB2, H2 and HSQLDB do not support this function.

json_replace()

Replaces a value by JSON path within a JSON document. The function takes 3 arguments, the json document, the json path and the new value to set. A value will not be inserted if the key is missing, only the values of existing keys are replaced.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonReplaceTest.java[role=include]

Warning

SAP HANA, DB2, H2 and HSQLDB do not support this function.

json_insert()

Inserts a value by JSON path in a JSON document. The function takes 3 arguments, the json document, the json path and the value to insert. When the JSON document contains a value for a JSON path, no insertion happens, unless the value is an array, in which case the value will be appended to that array. If no value exists for a JSON path, the value will be inserted under the key as specified through the JSON path.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonInsertTest.java[role=include]

Warning

SAP HANA, DB2, H2 and HSQLDB do not support this function.

json_mergepatch()

Merges JSON documents by performing an RFC 7396 compliant merge, which is

• When the first JSON value is not an object, the result is as if the first argument was an empty object

• When the second JSON value is not an object, the result is the second argument

• When both JSON values are objects, members are merged

  • Retain first JSON object members when the second JSON object has no members with matching keys

  • Retain second JSON object members when the first JSON object has no members with matching keys and the value is not equal to the JSON null literal

  • Recursively merge values that exist in both JSON objects, except if the second JSON object member is a JSON null

In simple terms this means

• The second JSON overrides members of the first, with JSON null values causing members to be removed

• JSON objects are merged recursively

Note	Arrays and hence objects within arrays are not merged, but replaced.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonMergepatchTest.java[role=include]

Warning

SAP HANA, DB2, SQL Server, H2 and HSQLDB do not support this function. On PostgreSQL, this function is emulated.

json_array_append()

Appends a value by JSON path to a JSON array within a JSON document. The function takes 3 arguments, the json document, the json path and the value to append.

If the value within the JSON document as identified by the JSON path is not a JSON array, it is auto-wrapped into an array. When no value exists for a JSON path, the document is not changed.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonArrayAppendTest.java[role=include]

Warning

SAP HANA, DB2, H2 and HSQLDB do not support this function.

json_array_insert()

Inserts a value by JSON path to a JSON array within a JSON document. The function takes 3 arguments, the json document, the json path and the value to append.

Although the exact behavior is database dependent, usually an error will be triggered if the JSON path does not end with an array index access i.e. $.a[0]. The zero based array index represents the position at which an element should be inserted in an array.

If the JSON path without the index does not resolve to a JSON array within the JSON document, the document is not changed.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonArrayInsertTest.java[role=include]

Warning

SAP HANA, DB2, H2 and HSQLDB do not support this function.

json_table()

A set-returning function, which turns a JSON document argument into rows. Returns no rows if the document is null or an empty JSON array.

link:extras/json_table_bnf.txt[role=include]

The first argument is the JSON document. The second optional argument represents the JSON path expression to use in order to obtain JSON nodes for further processing. The default for the optional second argument is $[*] i.e. access of root array elements.

Note	If the root of the JSON document is an object, it is recommended to pass $ as JSON path for portability.

The passing clause can be used to pass values for variables in the JSON path.

Attributes/columns that ought to be accessible via the from node alias are defined in the columns clause, which can be of varying forms:

• Value attributes - denoted by a castTarget after the name, behaves like json_value()

• Query attributes - denoted by the json type after the name, behaves like json_query()

• Exists attributes - denoted by the exists keyword after the name, behaves like json_exists()

• Ordinal attributes - denoted by the for ordinality syntax after the name, gives access to the 1-based index of the currently processed array element

• Nested paths - declare a JSON path for processing of a nested columns clause

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/json/JsonTableTest.java[role=include]

The lateral keyword is mandatory if one of the arguments refer to a from node item of the same query level.

Warning

H2 support is limited and HSQLDB as well as Sybase ASE do not support this function.

Functions for dealing with XML

The following functions deal with SQL XML types, which are not supported on every database.

Note	The following functions are incubating/tech-preview and to use them in HQL, it is necessary to enable the hibernate.query.hql.xml_functions_enabled configuration setting.

Function	Purpose
xmlelement()	Constructs an XML element from arguments
xmlcomment()	Constructs an XML comment from the single argument
xmlforest()	Constructs an XML forest from the arguments
xmlconcat()	Concatenates multiple XML fragments to each other
xmlpi()	Constructs an XML processing instruction
xmlquery()	Extracts content from XML document using XQuery or XPath
xmlexists()	Checks if an XQuery or XPath expression exists in an XML document
xmlagg()	Aggregates XML elements by concatenation
xmltable()	Turns an XML document into rows

xmlelement()

Constructs an XML element from the arguments.

link:extras/xmlelement_bnf.txt[role=include]

The identifier represents the XML element name and can be quoted by using backticks.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/xml/XmlElementTest.java[role=include]

XML element attributes can be defined by using the xmlattributes function as second argument. All following arguments represent the XML content.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/xml/XmlElementTest.java[role=include]

Warning

SAP HANA, MySQL, MariaDB and HSQLDB do not support this function.

xmlcomment()

Constructs an XML comment from the single string argument.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/xml/XmlCommentTest.java[role=include]

Warning

SAP HANA, MySQL, MariaDB and HSQLDB do not support this function.

xmlforest()

Constructs an XML forest from the arguments. A forest is a sequence of XML elements.

link:extras/xmlforest_bnf.txt[role=include]

The optional name specifies the XML element name to use for the content as produced by the expression. The name can be omitted if a path expression is passed, in which case the last attribute name is used as element name.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/xml/XmlForestTest.java[role=include]

Warning

SAP HANA, MySQL, MariaDB and HSQLDB do not support this function.

xmlconcat()

Concatenates multiple XML fragments to each other.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/xml/XmlConcatTest.java[role=include]

Warning

SAP HANA, MySQL, MariaDB and HSQLDB do not support this function.

xmlpi()

Constructs an XML processing instruction from the arguments.

link:extras/xmlpi_bnf.txt[role=include]

The identifier represents the XML processing instruction name and can be quoted by using backticks.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/xml/XmlPiTest.java[role=include]

The optional second argument represents the processing instruction content.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/xml/XmlPiTest.java[role=include]

Warning

SAP HANA, MySQL, MariaDB and HSQLDB do not support this function.

xmlquery()

Extracts content from an XML document using XQuery or XPath.

link:extras/xmlquery_bnf.txt[role=include]

The first argument represents the XQuery or XPath expression. The second argument after the passing keyword represents the XML document.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/xml/XmlQueryTest.java[role=include]

Warning

SAP HANA, MySQL, MariaDB and HSQLDB do not support this function.

xmlexists()

Checks if an XQuery or XPath expression exists in an XML document.

link:extras/xmlexists_bnf.txt[role=include]

The first argument represents the XQuery or XPath expression. The second argument after the passing keyword represents the XML document.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/xml/XmlExistsTest.java[role=include]

Warning

SAP HANA, MySQL, MariaDB and HSQLDB do not support this function.

xmlagg()

Aggregates XML elements by concatenation.

link:extras/xmlagg_bnf.txt[role=include]

This aggregate function is similar to an ordered set aggregate function since it allows to specify the order in which elements are aggregated, but uses a special syntax.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/xml/XmlAggTest.java[role=include]

Warning

SAP HANA, MySQL, MariaDB and HSQLDB do not support this function.

xmltable()

A set-returning function, which turns an XML document argument into rows. Returns no rows if the document is null or the XPath expression resolves to no nodes.

link:extras/xmltable_bnf.txt[role=include]

The first argument is the XPath expression. The second argument represents the XML document expression.

Columns that ought to be accessible via the from node alias are defined in the columns clause, which can be of varying forms:

• Value attributes - denoted by a castTarget after the name, will cast the content of the XML node matching the XPath expression of the column

• Query attributes - denoted by the xml type after the name, returns the XML node matching the XPath expression of the column

• Ordinal attributes - denoted by the for ordinality syntax after the name, gives access to the 1-based index of the currently processed XML node

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/xml/XmlTableTest.java[role=include]

The lateral keyword is mandatory if one of the arguments refer to a from node item of the same query level.

Warning

H2, MySQL, MariaDB and HSQLDB do not support this function.

Native and user-defined functions

The functions we’ve described above are the functions abstracted by HQL and made portable across databases. But, of course, HQL can’t abstract every function in your database.

There are several ways to call native or user-defined SQL functions.

• A native or user-defined function may be called using JPQL’s function syntax, for example, function('sinh', phi). (This is the easiest way, but not the best way.)

• A user-written FunctionContributor may register user-defined functions.

• A custom Dialect may register additional native functions by overriding initializeFunctionRegistry().

Tip	Registering a function isn’t hard, but is beyond the scope of this chapter. (It’s even possible to use the APIs Hibernate provides to make your own portable functions!)

Fortunately, every built-in Dialect already registers many native functions for the database it supports.

Tip	Try setting the log category org.hibernate.HQL_FUNCTIONS to debug. Then at startup Hibernate will log a list of type signatures of all registered functions.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Embedding native SQL in HQL

The special function sql() allows the use of native SQL fragments inside an HQL query.

The signature of this function is sql(pattern[, argN]*), where pattern must be a string literal but the remaining arguments may be of any type. The pattern literal is unquoted and embedded in the generated SQL. Occurrences of ? in the pattern are replaced with the remaining arguments of the function.

-- Cast to some native type
select c from Computer c where c.ipAddress = sql('?::inet', '127.0.0.1')
-- Use some native operator
select h from Human h order by sql('(? <-> ?)', h.workLocation, h.homeLocation)

Predicates

A predicate is an operator which, when applied to some argument, evaluates to true or false. In the world of SQL-style ternary logic, we must expand this definition to encompass the possibility that the predicate evaluates to null. Typically, a predicate evaluates to null when one of its arguments is null.

Predicates occur in the where clause, the having clause and in searched case expressions.

Relational operators

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

Tip	If you prefer, HQL treats != as a synonym for <>.

The operands should be of the same type.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

between

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.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Operators for dealing with null

The following operators make it easier to deal with null values.

Operator	Negation	Type	Semantics
is null	is not null	Unary postfix	true if the value to the left is null
is distinct from	is not distinct from	Binary	true if the value on the left is equal to the value on the right, or if both are null

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Operators for dealing with boolean values

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

Note	The values true and false of the boolean basic type are different to the logical true or false produced by a predicate.

For logical operations on predicates, see [logical-operators] below.

Operator	Negation	Type	Semantics
is true	is not true	Unary postfix	true if the value to the left is true, or false otherwise
is false	is not false	Binary	true if the value to the left is false, or false otherwise

String pattern matching

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

Their syntax is defined by:

link:extras/predicate_like_bnf.txt[role=include]

The expression on the right is a pattern, where:

• _ matches any single character,

• % matches any number of characters, and

• if an escape character is specified, it may be used to escape either of these wildcards.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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

For example, to match all stored procedures prefixed with Dr_, the like criteria could be 'Dr|_%' escape '|':

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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

Elements and indices

There’s two special HQL functions that we mentioned earlier, without giving much of an explanation, since they’re only useful in conjunction with the predicate operators we’re about to meet.

These functions are only allowed in the where clause, and result in a subquery in the generated SQL. Indeed, you can think of them as just a shortcut way to write a subquery.

HQL Function	Applies to	Purpose
elements()	Any collection	Refers to the elements of a collection as a whole.
indices()	Indexed collections (lists and maps)	Similar to elements() but refers to the collections indices (keys/positions) as a whole.

In the next three sections, we’ll see how these two functions are useful.

in

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:

link:extras/predicate_in_bnf.txt[role=include]

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

• a list of values enclosed in parentheses,

• a query parameter,

• a subquery, or

• one of the functions elements() or indices() defined above.

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

Note

JPQL limits the legal types to string, numeric, date/time, and enum types, and in JPQL the left expression must be either: a "state field", which means a simple attribute, excluding associations and embedded attributes, or an entity type expression (see Types and typecasts). HQL is far more permissive. HQL itself does not restrict the type any way, though the database itself might. Even embedded attributes are allowed, although that feature depends on the level of support for tuple or "row value constructors" in the underlying database.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

contains

The contains predicates evaluates to true if the value to its right is contained in the value to its left. Currently, this predicate only works with an array typed expression on the left side.

link:extras/predicate_contains_bnf.txt[role=include]

For further details, refer to the array_contains section.

intersects

The intersects predicates evaluates to true if the value to its left has at least one element common with the value to its right. Currently, this predicate only works with an array typed expressions.

link:extras/predicate_intersects_bnf.txt[role=include]

For further details, refer to the array_intersects section.

Relational operators and subqueries

The binary comparisons we met above in Relational operators may involve a qualifier:

• a qualified subquery, or

• a qualifier applied to one of the functions elements() or indices() defined above.

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

Subquery operator	Synonym	Semantics
every	all	Evaluates to true of the comparison is true for every value in the result set of the subquery.
any	some	Evaluates to true of the comparison is true for at least one value in the result set of the subquery.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Exists operator

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

The thing to its right might be:

• a subquery, or

• one of the functions elements() or indices() defined above.

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Collection operators

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

Operator	Negation	Type	Semantics
is empty	is not empty	Unary postfix	true if the collection or association on the left has no elements
member of	not member of	Binary	true if the value on the left is a member of the collection or association on the right

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Logical operators

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.

Declaring root entities: from and cross join

The from clause is responsible for declaring the entities available in the rest of the query, and assigning them aliases, or, in the language of the JPQL specification, identification variables.

Identification variables

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.

Tip	The identification variable is actually optional, but for queries involving more than one entity it’s almost always a good idea to declare one.

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

Root entity references

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.

Tip	Remember, the entity name is the value of the name member of the @Entity annotation, or the unqualified Java class name by default.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

In this example, Person is the entity name, and p is the identification variable.

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Of course, there may be multiple root entities.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

The previous queries may even be written using the syntax cross join in place of the comma:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Polymorphism

HQL and JPQL queries are inherently polymorphic. Consider:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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

Note	The query from java.lang.Object is completely legal. (But not very useful!) It returns every object of every mapped entity type.

Derived roots

A derived root is an uncorrelated subquery which occurs in the from clause. It must declare an identification variable.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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

Important

We emphasize that a derived root must be an uncorrelated subquery. It may not refer to other roots declared in the same from clause.

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

Common table expressions in from clause

A Common table expression (CTE) is like a derived root with a name. The big difference is, that the name can be referred to multiple times. It must declare an identification variable.

The CTE name can be used for a from clause root or a join, similar to entity names.

Refer to the with clause chapter for details about CTEs.

Set-returning functions in from clause

A set-returning function is a function that produces rows instead of a single scalar value and is exclusively used in the from clause, either as root node or join target.

The index() function can be used to access the 1-based index of a returned row.

The following set-returning functions are available on many platforms:

Function	purpose
unnest()	Turns an array into rows
generate_series()	Creates a series of values as rows
json_table()	Turns a JSON document into rows
xmltable()	Turns an XML document into rows

To use set returning functions defined in the database, it is required to register them in a FunctionContributor:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/srf/CustomSetReturningFunctionTest.java[role=include]

After that, the function can be used in the from clause:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/srf/CustomSetReturningFunctionTest.java[role=include]

Note	The index() function represents the idea of the with ordinality SQL syntax, which is not supported on some databases for user defined functions. Hibernate ORM tries to emulate this feature by wrapping invocations as lateral subqueries and using row_number(), which may lead to worse performance.

generate_series set-returning function

A set-returning function, which generates rows from a given start value (inclusive) up to a given stop value (inclusive). The function has 2 variants:

• generate_series(numeric, numeric [,numeric]) - Arguments are start, stop and step with a default of 1 for the optional step argument

• generate_series(temporal, temporal, duration) - Like the numeric variant, but for temporal types and step is required

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/srf/GenerateSeriesTest.java[role=include]

To obtain the "row number" of a generated value i.e. ordinality, it is possible to use the index() function.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/srf/GenerateSeriesTest.java[role=include]

The step argument can be a negative value and progress from a higher start value to a lower stop value.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/function/srf/GenerateSeriesTest.java[role=include]

Declaring joined entities

Joins allow us to navigate from one entity to another, via its associations, or via explicit join conditions. There are:

• explicit joins, declared within the from clause using the keyword join, and

• implicit joins, which don’t need to be declared in the from clause.

An explicit join may be either:

• an inner join, written as join or inner join,

• a left outer join, written as left join or left outer join,

• a right outer join, written as right join or right outer join, or

• a full outer join, written as full join or full outer join.

Explicit root joins

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Note	This looks nice and familiar, but it’s not the most common sort of join in HQL or JPQL.

Explicit association joins

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

• is usually a @OneToMany, @ManyToMany, @OneToOne, or @ManyToOne association, but

• it could be an @ElementCollection, and

• it might even be an attribute of embeddable type.

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.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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

Explicit association joins with join conditions

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

Note

The specified join conditions are added to the join conditions specified by the foreign key association. That’s why, historically, HQL uses the keword with here: "with" emphasizes that the new condition doesn’t replace the original join conditions. The with keyword is specific to Hibernate. JPQL uses on.

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

The following query is arguably less clear, but semantically equivalent:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

join fetch for association fetching

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.

• A join fetch, or, more explicitly, inner join fetch, only returns base entities with an associated entity.

• A left join fetch, or—for lovers of verbosity—left outer join fetch, returns all the base entities, including those which have no associated joined entity.

Important

This is one of the most important features of Hibernate. To achieve acceptable performance with HQL, you’ll need to use join fetch quite often. Without it, you’ll quickly run into the dreaded "n+1 selects" problem.

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:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

In this example, we used a left outer join because we also wanted to obtain customers with no orders.

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

• it’s perfectly safe to fetch several to-one associations in series or parallel in a single query, and

• a single series of nested fetch joins is also fine, but

• fetching multiple collections or to-many associations in parallel results in a Cartesian product at the database level, and might exhibit very poor performance.

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.

Important

Fetch joins should usually be avoided in limited or paged queries. This includes: queries executed using setFirstResult() or setMaxResults(), as in [jpql-pagination], or queries with a limit or offset declared in HQL, described below in Limits and offsets. Nor should they be used with the scroll() and stream() methods described in [hql-api-incremental].

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

Joins with typecasts

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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().

Subqueries in joins

A join clause may contain a subquery, either:

• an uncorrelated subquery, which is almost the same as a derived root, except that it may have an on restriction, or

• a lateral join, which is a correlated subquery, and may refer to other roots declared earlier in the same from clause.

The lateral keyword just distinguishes the two cases.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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

Tip

Traditional SQL doesn’t allow correlated subqueries in the from clause. A lateral join is essentially just that, but with a different syntax to what you might expect. On some databases, join lateral is written cross apply. And on Postgres it’s plain lateral, without join. It’s almost as if they’re deliberately trying to confuse us.

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

Important

Most databases support some flavor of join lateral, and Hibernate emulates the feature for databases which don’t. But emulation is neither very efficient, nor does it support all possible query shapes, so it’s important to test on your target database.

Set-returning functions in joins

A join clause may contain a set-returning function, either:

• an uncorrelated set-returning function, which is almost the same as a set-returning function in the from clause, except that it may have an on restriction, or

• a lateral join, which is a correlated set-returning function, and may refer to other roots declared earlier in the same from clause.

The lateral keyword just distinguishes the two cases. A lateral join may be an inner or left outer join, but not a right join, nor a full join.

Implicit association joins (path expressions)

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

• if an attribute is of embedded type, or is a to-one association, it may be further navigated, but

• if an attribute is of basic type, is collection-valued, or is a to-many association, it is considered terminal, and may not be further navigated.

It’s clear that:

• A path expression like p.name with only two elements just refers to state held directly by an entity with an alias p defined in from or join.

• But a longer path expression, for example, ph.person.name, might refer to state held by an associated entity. (Alternatively, it might refer to state held by an embedded class.)

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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.

Note that:

• Implicit joins are always treated as inner joins.

• Multiple occurrences of the same implicit join always refer to the same SQL join.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Joining collections and many-valued associations

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

• to the elements of a Set,

• to the elements of a List, not to their indices in the list, or

• to the values of a Map, not to their keys.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

In this example, the identification variable ph is of type Phone, the element type of the list Person#phones. But if we need to refer to the index of a Phone in the list, we need some extra syntax.

You might recall that we mentioned element() and index() and key(), value(), and entry() a bit earlier. These functions may be applied to the identification variable declared in a collection join or many-valued association join.

Function	Applies to	Interpretation	Notes
value() or element()	Any collection	The collection element or map entry value	Often optional.
index()	Any List with an index column	The index of the element in the list	For backward compatibility, it’s also an alternative to key(), when applied to a map.
key()	Any Map	The key of the entry in the list	If the key is of entity type, it may be further navigated.
entry()	Any Map	The map entry, that is, the Map.Entry of key and value.	Only legal as a terminal path, and only allowed in the select clause.

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

link:../../../../../../../../hibernate-testing/src/main/java/org/hibernate/testing/orm/domain/userguide/Phone.java[role=include]

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Implicit joins involving collections

The functions element(), index(), key(), and value() may even be applied to a path expression to express an implicit join.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Projection: select

The select list identifies which objects and values to return as the query results.

Note	This operation is called projection.

Any of the expression types discussed in Expressions may occur in the projection list, unless otherwise noted.

Tip	If a query has no explicit select list, the projection is inferred from the entities and joins occurring in the from clause, together with the result type specified by the call to createQuery(). It’s better to specify the projection explicitly, except in the simplest cases.

There might be multiple items in a projection list, in which case each query result is a tuple, and this poses a problem: Java doesn’t have a good way to represent tuples.

If there’s just one projected item in the select list, then, no sweat, that’s the type of each query result. There’s no need to bother with trying to represent a "tuple of length 1".

But if there are multiple expressions in the select list then:

• by default, each query result is packaged as an array of type Object[], or

• if explicitly requested by passing the class Tuple to createQuery(), the query result is packaged as an instance of jakarta.persistence.Tuple.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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 (positions are numbered from 0).

Unfortunately, neither Object[] nor Tuple lets us access an individual item in a result tuple of an HQL query without explicitly specifying the type of the item. (Using a typecast in the case of Object[], or by passing the class object to get() in the case of Tuple.) But there’s another option, as we’re about to see.

Simplifying slightly, the BNF for a projected item is:

link:extras/select_item_bnf.txt[role=include]

where instantiatiationArgs is essentially a nested projection list.

So there’s a special expression type that’s only legal in the select clause: the instantiation rule in the BNF above. Let’s see what it does.

select new

The select new construct packages the query results into a user-written Java class instead of an array.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/CallStatistics.java[role=include]

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

The class must be specified by its fully qualified name, and it must have a matching constructor.

Important

This class does not need to be mapped or annotated in any way. Even if the class is an entity class, the resulting instances are not managed entities and are not associated with the session.

Alternatively, using the syntax select new map, the query may specify that each result should be packaged as a map:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

The keys of the map are determined by the aliases given to the projected items in the select list. If no aliases are specified, the key of an item is its position in the list (positions are numbered from 0).

Or, using the syntax select new list, the query may specify that each result should be packaged as a list:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Note	This is an older syntax, that predates JPQL. In hindsight, it’s hard to see what advantage List<Object> offers compared to Object[]. On the other hand, Map is a perfectly fine alternative Tuple, but isn’t portable to other persistence providers.

distinct

The distinct keyword helps remove duplicate results from the query result list. It’s only effect is to add distinct to the generated SQL.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/SelectDistinctTest.java[role=include]

Note

As of Hibernate 6, duplicate results arising from the use of join fetch are automatically removed by Hibernate in memory, after reading the database results and materializing entity instances as Java objects. It’s no longer necessary to remove duplicate results explicitly, and, in particular, distinct should not be used for this purpose.

Aggregate functions

It’s common to have aggregate functions like count(), sum(), and max() in a select list. Aggregate functions are special functions that reduce the size of the result set.

The standard aggregate functions defined in both ANSI SQL and JPQL are:

Aggregate function	Argument type	Result type	JPA standard / ANSI SQL standard
count(), including count(distinct), count(all), and count(*)	Any	Long	✓/✓
avg()	Any numeric type	Double	✓/✓
min()	Any numeric type, or string	Same as the argument type	✓/✓
max()	Any numeric type, or string	Same as the argument type	✓/✓
sum()	Any numeric type	See table below	✓/✓
var_pop(), var_samp()	Any numeric type	Double	✗/✓
stddev_pop(), stddev_samp()	Any numeric type	Double	✗/✓

In the case of sum(), the rules for assigning a result type are:

Argument type	Result type
Any integral numeric type except BigInteger	Long
Any floating point numeric type	Double
BigInteger	BigInteger
BigDecimal	BigDecimal

There are also ordered set aggregate functions.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

HQL defines the two additional aggregate functions which accept a logical predicate as an argument, for example, every(p.amount < 1000.0).

Aggregate function	Argument type	Result type	JPA standard
any() or some()	Logical predicate	Boolean	✗
every() or all()	Logical predicate	Boolean	✗

Note	Aggregate functions usually appear in the select clause, but control over aggregation is the responsibility of the group by clause, as described below.

Aggregate functions and collections

The elements() and indices() functions we met earlier let us apply aggregate functions to a collection:

New syntax	Legacy HQL function	Applies to	Purpose
max(elements(x))	maxelement(x)	Any collection with sortable elements	The maximum element or map value
min(elements(x))	minelement(x)	Any collection with sortable elements	The minimum element or map value
sum(elements(x))	—	Any collection with numeric elements	The sum of the elements or map values
avg(elements(x))	—	Any collection with numeric elements	The average of the elements or map values
max(indices(x))	maxindex(x)	Indexed collections (lists and maps)	The maximum list index or map key
min(indices(x))	minindex(x)	Indexed collections (lists and maps)	The minimum list index or map key
sum(indices(x))	—	Indexed collections (lists and maps)	The sum of the list indexes or map keys
avg(indices(x))	—	Indexed collections (lists and maps)	The average of the list indexes or map keys

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Tip	These operations can almost always be written in another way, without the use of these convenience functions.

filter

All aggregate functions support the inclusion of a filter clause, a sort of mini-where applying a restriction to just one item of the select list:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Ordered set aggregate functions: within group

An ordered set aggregate function is a special aggregate function which has:

• not only an optional filter clause, as above, but also

• a within group clause containing a mini-order by specification.

There are two main types of ordered set aggregate function:

• an inverse distribution function calculates a value that characterizes the distribution of values within the group, for example, percentile_cont(0.5) is the median, and percentile_cont(0.25) is the lower quartile.

• a hypothetical set function determines the position of a "hypothetical" value within the ordered set of values.

The following ordered set aggregate functions are available on many platforms:

Type	Functions
Inverse distribution functions	mode(), percentile_cont(), percentile_disc()
Hypothetical set functions	rank(), dense_rank(), percent_rank(), cume_dist()
Other	listagg(), array_agg

Actually, the most widely-supported ordered set aggregate function is one which builds a string by concatenating the values within a group. This function has different names on different databases, but HQL abstracts these differences, and—following ANSI SQL—calls it listagg().

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Window functions: over

A window function is one which also has an over clause, which may specify:

• window frame partitioning, with partition by, which is very similar to group by,

• ordering, with order by, which defines the order of rows within a window frame, and/or

• windowing, with range, rows, or groups, which define the bounds of the window frame within a partition.

The default partitioning and ordering is taken from the group by and order by clauses of the query. Every partition runs in isolation, that is, rows can’t leak across partitions.

Like ordered set aggregate functions, window functions may optionally specify filter or within group.

Window functions are similar to aggregate functions in the sense that they compute some value based on a "frame" comprising multiple rows. But unlike aggregate functions, window functions don’t flatten rows within a window frame.

The windowing clause specifies one of the following modes:

• rows for frame start/end defined by a set number of rows, for example, rows n preceding means that only n preceding rows are part of a frame,

• range for frame start/end defined by value offsets, for example, range n preceding means a preceding row is part of a frame if the abs(value, lag(value) over(..)) ⇐ N, or

• groups for frame start/end defined by group offsets, for example, groups n preceding means n preceding peer groups are part of a frame, a peer group being rows with equivalent values for order by expressions.

The frame exclusion clause allows excluding rows around the current row:

• exclude current row excludes the current row,

• exclude group excludes rows of the peer group of the current row,

• exclude ties excludes rows of the peer group of the current row, except the current row, and

• exclude no others is the default, and does not exclude anything.

Important

Frame clause modes range and groups, as well as frame exclusion modes might not be available on every database.

The default frame is rows between unbounded preceding and current row exclude no others, which means that all rows prior to the "current row" are considered.

The following window functions are available on all major platforms:

Window function	Purpose	Signature
row_number()	The position of the current row within its frame	row_number()
lead()	The value of a subsequent row in the frame	lead(x), lead(x, i, x)
lag()	The value of a previous row in the frame	lag(x), lag(x, i, x)
first_value()	The value of a first row in the frame	first_value(x)
last_value()	The value of a last row in the frame	last_value(x)
nth_value()	The value of the `n`th row in the frame	nth_value(x, n)

In principle every aggregate or ordered set aggregate function might also be used as a window function, just by specifying over, but not every function is supported on every database.

Important

Window functions and ordered set aggregate functions aren’t available on every database. Even where they are available, support for particular features varies widely between databases. Therefore, we won’t waste time going into further detail here. For more information about the syntax and semantics of these functions, consult the documentation for your dialect of SQL.

Restriction: where

The where clause restricts the results returned by a select query or limits the scope of an update or delete query.

Note	This operation is usually called selection, but since that term is often confused with the select keyword, and since both projection and selection involve "selecting" things, here we’ll use the less-ambiguous term restriction.

A restriction is nothing more than a single logical expression, a topic we exhausted above in Predicates.

Aggregation: group by and having

An aggregate query is one with aggregate functions in its projection list.

The group by clause divides the result set into groups, so that a query with aggregate functions in the select list returns not a single result for the whole query, but one result for each group.

Note	In short, grouping controls the effect of aggregation.

A query with aggregation may also have a having clause, a restriction applied to the groups.

Aggregation and grouping: group by

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

• if there’s just one item, then the query will have a single result for each unique value of that item, or

• if there are multiple items, the query will have a result for each unique combination or their values.

The BNF for a grouped item is just:

link:extras/group_by_item_bnf.txt[role=include]

Consider the following queries:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

The first query retrieves the complete total over all orders. The second retrieves the total for each customer, after grouping the orders by customer.

Totals and subtotals: rollup and cube

The special functions rollup() and cube() may be used in the group by clause, when supported by the database. The semantics are identical to SQL.

These functions are especially useful for reporting:

• A group by clause with rollup() is used to produce subtotals and grand totals.

• A group by clause with cube() allows totals for every combination of columns.

Aggregation and restriction: having

In a grouped query, the where clause applies to the non-aggregated values (it determines which rows will make it into the aggregation). The having clause also restricts results, but it operates on the aggregated values.

In an example above, we retrieved Call duration totals for all persons. 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 total of more than 1000:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

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, while the where clause is applied before.

Operations on result sets: union, intersect, and except

These operators apply not to expressions, but to entire result sets:

• union and union all,

• intersect and intersect all, and

• except and except all.

Just like in SQL, all suppresses the elimination of duplicate results.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Sorting: order by

By default, the results of the query are returned in an arbitrary order.

Note

Imposing an order on a set is called sorting. A relation (a database table) is a set, and therefore certain particularly dogmatic purists have argued that sorting has no place in the algebra of relations. We think this is more than a bit silly: practical data analysis almost always involves sorting, which is a perfectly well-defined operation.

The order by clause specifies a list of projected items used to sort the results. Each sorted item may be:

• an attribute of an entity or embeddable class,

• a more complex expression,

• the alias of a projected item declared in the select list, or

• a literal integer indicating the ordinal position of a projected item in the select list.

Of course, in principle, only certain types may be sorted: numeric types, string, and date and time types. But HQL is very permissive here and will allow an expression of almost any type to occur in a sort list. Even the identification variable of an entity with a sortable identifier type may occur as a sorted item.

Note

The JPQL specification requires that every sorted item in the order by clause also occur in the select clause. HQL does not enforce this restriction, but applications desiring database portability should be aware that some databases do. Therefore, you might wish to avoid the use of complex expressions in the sort list.

The BNF for a sorted item is:

link:extras/order_by_item_bnf.txt[role=include]

Each sorted item listed in the order by clause may explicitly specify a direction, either:

• asc for ascending order, or

• desc for descending order.

If no direction is explicitly specified, the results are returned in ascending order.

Of course, there’s an ambiguity with respect to null values. Therefore, the sorting of null values may also be explicitly specified:

• nulls first puts null values at the beginning of the result set, and

• nulls last puts them last.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Queries with an ordered result list may have limits or pagination.

Limits and offsets

It’s often useful to place a hard upper limit on the number of results that may be returned by a query. The limit and offset clauses are an alternative to the use of setMaxResults() and setFirstResult() respectively, and also may be used for [jpql-pagination].

Tip	If the limit or offset is parameterized, it’s much easier to use setMaxResults() or setFirstResult().

The SQL syntax fetch first …​ rows only and fetch next …​ rows only is also allowed.

The BNF is a bit complicated:

link:extras/limit_offset_bnf.txt[role=include]

These two queries are identical:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

These are well-defined limits: the number of results returned by the database will be limited to 50, as promised. But not every query is quite so well-behaved.

Note	Limiting certainly isn’t a well-defined relational operation, and must be used with care. In particular, limits don’t play well with fetch joins.

This next query is accepted by HQL, and no more than 50 results are returned by getResultList(), just as expected:

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

However, if you log the SQL executed by Hibernate, you’ll notice something wrong:

select
    p1_0.id,
    c1_0.phone_id,
    c1_0.calls_ORDER,
    c1_0.id,
    c1_0.duration,
    c1_0.payment_id,
    c1_0.call_timestamp,
    p1_0.phone_number,
    p1_0.person_id,
    p1_0.phone_type
from
    Phone p1_0
join
    phone_call c1_0
        on p1_0.id=c1_0.phone_id
order by 1

What happened to the limit clause?

Important

When limits or pagination are combined with a fetch join, Hibernate must retrieve all matching results from the database and apply the limit in memory! This almost certainly isn’t the behavior you were hoping for, and in general will exhibit terrible performance characteristics.

In the next chapter we’ll see a completely different way to write queries in Hibernate.

With clause

The with clause allows to specify common table expressions (CTEs) which can be imagined like named subqueries. Every uncorrelated subquery can be factored to a CTE in the with clause. The semantics are equivalent.

The with clause offers features beyond naming subqueries though:

• Specify materialization hints

• Recursive querying

Materialization hint

The materialization hint MATERIALIZED or NOT MATERIALIZED can be applied to tell the DBMS whether a CTE should or shouldn’t be materialized. Consult the database manual of the respective database for the exact meaning of the hint.

Usually, one can expect that MATERIALIZED will cause the subquery to be executed separately and saved into a temporary table, whereas NOT MATERIALIZED will cause the subquery to be inlined into every use site and considered during optimizations separately.

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Recursive querying

The main use case for the with clause is to define a name for a subquery, such that this subquery can refer to itself, which ultimately enables recursive querying.

Recursive CTEs must follow a very particular shape, which is

• Base query part

• union or union all

• Recursive query part

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

The base query part represents the initial set of rows. When fetching a tree of data, the base query part usually is the tree root.

The recursive query part is executed again and again until it produces no new rows. The result of such a CTE is the base query part result unioned together with all recursive query part executions. Depending on whether union all or union (distinct) is used, duplicate rows are preserved or not.

Recursive queries additionally can have

• a search clause to hint the DBMS whether to use breadth or depth first searching

• a cycle clause to hint the DBMS how to determine that a cycle was reached

Defining the search clause requires specifying a name for an attribute in the set sub-clause, that will be added to the CTE type and allows ordering results according to the search order.

searchClause
: "SEARCH" ("BREADTH"|"DEPTH") "FIRST BY" searchSpecifications "SET" identifier
;

searchSpecifications
: searchSpecification ("," searchSpecification)*
;

searchSpecification
: identifier sortDirection? nullsPrecedence?
;

A DBMS has two possible orders when executing the recursive query part

• Depth first - handle the newest produced rows by the recursive query part first

• Breadth first - handle the oldest produced rows by the recursive query part first

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Recursive processing can lead to cycles which might lead to queries executing forever. The cycle clause hints the DBMS which CTE attributes to track for the cycle detection. It requires specifying a name for a cycle mark attribute in the set sub-clause, that will be added to the CTE type and allows detecting if a cycle occurred for a result.

By default, the cycle mark attribute will be set to true when a cycle is detected and false otherwise. The values to use can be explicitly specified through the to and default sub-clauses. Optionally, it’s also possible to specify a cycle path attribute name through the using clause The cycle path attribute can be used to understand the traversal path that lead to a result.

cycleClause
	: "CYCLE" cteAttributes "SET" identifier ("TO" literal "DEFAULT" literal)? ("USING" identifier)?
	;

link:../../../../../../../../hibernate-core/src/test/java/org/hibernate/orm/test/hql/HQLTest.java[role=include]

Important

Hibernate merely translates recursive CTEs but doesn’t attempt to emulate the feature. Therefore, this feature will only work if the database supports recursive CTEs. Hibernate does emulate the search and cycle clauses though if necessary, so you can safely use that. Note that most modern database versions support recursive CTEs already.