HQL and JPQL

The Hibernate Query Language (HQL) and Java Persistence Query Language (JPQL) are both object model focused query languages similar in nature to SQL. JPQL is a heavily-inspired-by subset of HQL. A JPQL query is always a valid HQL query, the reverse is not true however.

Both HQL and JPQL are non-type-safe ways to perform query operations. Criteria queries offer a type-safe approach to querying. See Criteria for more information.

Query API

Examples domain model

To better understand the further HQL and JPQL examples, it’s time to familiarize the domain model entities that are used in all the examples features in this chapter.

Example 1. Examples domain model

@NamedQueries({
    @NamedQuery(
        name = "get_person_by_name",
        query = "select p from Person p where name = :name"
    )
    ,
    @NamedQuery(
        name = "get_read_only_person_by_name",
        query = "select p from Person p where name = :name",
        hints = {
            @QueryHint(
                name = "org.hibernate.readOnly",
                value = "true"
            )
        }
    )
})
@NamedStoredProcedureQueries(
    @NamedStoredProcedureQuery(
        name = "sp_person_phones",
        procedureName = "sp_person_phones",
        parameters = {
            @StoredProcedureParameter(
                name = "personId",
                type = Long.class,
                mode = ParameterMode.IN
            ),
            @StoredProcedureParameter(
                name = "personPhones",
                type = Class.class,
                mode = ParameterMode.REF_CURSOR
            )
        }
    )
)
@Entity
public class Person {

    @Id
    @GeneratedValue
    private Long id;

    private String name;

    private String nickName;

    private String address;

    @Temporal(TemporalType.TIMESTAMP )
    private Date createdOn;

    @OneToMany(mappedBy = "person", cascade = CascadeType.ALL)
    @OrderColumn(name = "order_id")
    private List<Phone> phones = new ArrayList<>();

    @ElementCollection
    @MapKeyEnumerated(EnumType.STRING)
    private Map<AddressType, String> addresses = new HashMap<>();

    @Version
    private int version;

    //Getters and setters are omitted for brevity

}

public enum AddressType {
    HOME,
    OFFICE
}

@Entity
public class Partner {

	@Id
	@GeneratedValue
	private Long id;

	private String name;

	@Version
	private int version;

	//Getters and setters are omitted for brevity

}

@Entity
public class Phone {

    @Id
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    private Person person;

    @Column(name = "phone_number")
    private String number;

    @Enumerated(EnumType.STRING)
    @Column(name = "phone_type")
    private PhoneType type;

    @OneToMany(mappedBy = "phone", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Call> calls = new ArrayList<>(  );

    @OneToMany(mappedBy = "phone")
    @MapKey(name = "timestamp")
    @MapKeyTemporal(TemporalType.TIMESTAMP )
    private Map<Date, Call> callHistory = new HashMap<>();

    @ElementCollection
    private List<Date> repairTimestamps = new ArrayList<>(  );

    //Getters and setters are omitted for brevity

}

public enum PhoneType {
    LAND_LINE,
    MOBILE;
}

@Entity
@Table(name = "phone_call")
public class Call {

    @Id
    @GeneratedValue
    private Long id;

    @ManyToOne
    private Phone phone;

    @Column(name = "call_timestamp")
    private Date timestamp;

    private int duration;

    //Getters and setters are omitted for brevity

}

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public class Payment {

    @Id
    @GeneratedValue
    private Long id;

    private BigDecimal amount;

    private boolean completed;

    @ManyToOne
    private Person person;

    //Getters and setters are omitted for brevity

}

@Entity
public class CreditCardPayment extends Payment {
}

@Entity
public class WireTransferPayment extends Payment {
}

JPA Query API

In JPA the query is represented by javax.persistence.Query or javax.persistence.TypedQuery as obtained from the EntityManager. The create an inline Query or TypedQuery, you need to use the EntityManager#createQuery method. For named queries, the EntityManager#createNamedQuery method is needed.

Example 2. Obtaining a JPA Query or a TypedQuery reference

Query query = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like :name"
);

TypedQuery<Person> typedQuery = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like :name", Person.class
);

Example 3. Obtaining a JPA Query or a TypedQuery reference for a named query

@NamedQuery(
    name = "get_person_by_name",
    query = "select p from Person p where name = :name"
)

Query query = entityManager.createNamedQuery( "get_person_by_name" );

TypedQuery<Person> typedQuery = entityManager.createNamedQuery(
    "get_person_by_name", Person.class
);

Hibernate offers a specific @NamedQuery annotation which provides ways to configure various query features, like flush mode, cacheability, time out interval.

Example 4. Obtaining a Hibernate Query or a TypedQuery reference for a named query

@NamedQueries({
    @NamedQuery(
        name = "get_phone_by_number",
        query = "select p " +
                "from Phone p " +
                "where p.number = :number",
        timeout = 1,
        readOnly = true
    )
})

Phone phone = entityManager
    .createNamedQuery( "get_phone_by_number", Phone.class )
    .setParameter( "number", "123-456-7890" )
    .getSingleResult();

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

Example 5. Basic JPA Query usage

Query query = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like :name" )
// timeout - in milliseconds
.setHint( "javax.persistence.query.timeout", 2000 )
// flush only at commit time
.setFlushMode( FlushModeType.COMMIT );

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

javax.persistence.query.timeout: Defines the query timeout, in milliseconds.
javax.persistence.fetchgraph: Defines a fetchgraph EntityGraph. Attributes explicitly specified as AttributeNodes are treated as FetchType.EAGER (via join fetch or subsequent select). For details, see the EntityGraph discussions in Fetching.
javax.persistence.loadgraph: Defines a loadgraph EntityGraph. Attributes explicitly specified as AttributeNodes are treated as FetchType.EAGER (via join fetch or subsequent select). Attributes that are not specified are treated as FetchType.LAZY or FetchType.EAGER depending on the attribute’s definition in metadata. For details, see the EntityGraph discussions in Fetching.
org.hibernate.cacheMode: Defines the CacheMode to use. See org.hibernate.query.Query#setCacheMode.
org.hibernate.cacheable: Defines whether the query is cacheable. true/false. See org.hibernate.query.Query#setCacheable.
org.hibernate.cacheRegion: For queries that are cacheable, defines a specific cache region to use. See org.hibernate.query.Query#setCacheRegion.
org.hibernate.comment: Defines the comment to apply to the generated SQL. See org.hibernate.query.Query#setComment.
org.hibernate.fetchSize: Defines the JDBC fetch-size to use. See org.hibernate.query.Query#setFetchSize
org.hibernate.flushMode: Defines the Hibernate-specific FlushMode to use. See org.hibernate.query.Query#setFlushMode. If possible, prefer using javax.persistence.Query#setFlushMode instead.
org.hibernate.readOnly: Defines that entities and collections loaded by this query should be marked as read-only. See org.hibernate.query.Query#setReadOnly

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

Example 6. JPA name parameter binding

Query query = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like :name" )
.setParameter( "name", "J%" );

// For generic temporal field types (e.g. `java.util.Date`, `java.util.Calendar`)
// we also need to provide the associated `TemporalType`
Query query = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.createdOn > :timestamp" )
.setParameter( "timestamp", timestamp, TemporalType.DATE );

JPQL-style positional parameters are declared using a question mark followed by an ordinal - ?1, ?2. The ordinals start with 1. Just like with named parameters, positional parameters can also appear multiple times in a query.

Example 7. JPA positional parameter binding

Query query = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like ?1" )
.setParameter( 1, "J%" );

It’s good practice not to mix forms in a given query.

In terms of execution, JPA Query offers 2 different methods for retrieving a result set.

Query#getResultList() - executes the select query and returns back the list of results.
Query#getSingleResult() - executes the select query and returns a single result. If there were more than one result an exception is thrown.

Example 8. JPA getResultList() result

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like :name" )
.setParameter( "name", "J%" )
.getResultList();

Example 9. JPA getSingleResult()

Person person = (Person) entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like :name" )
.setParameter( "name", "J%" )
.getSingleResult();

Hibernate Query API

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

Example 10. Obtaining a Hibernate Query

org.hibernate.query.Query query = session.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like :name"
);

Example 11. Obtaining a Hibernate Query reference for a named query

org.hibernate.query.Query query = session.getNamedQuery( "get_person_by_name" );

Not only was the JPQL syntax heavily inspired by HQL, but many of the JPA APIs were heavily inspired by Hibernate too. The two Query contracts are very similar.

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

Example 12. Basic Query usage - Hibernate

org.hibernate.query.Query query = session.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like :name" )
// timeout - in seconds
.setTimeout( 2 )
// write to L2 caches, but do not read from them
.setCacheMode( CacheMode.REFRESH )
// assuming query cache was enabled for the SessionFactory
.setCacheable( true )
// add a comment to the generated SQL if enabled via the hibernate.use_sql_comments configuration property
.setComment( "+ INDEX(p idx_person_name)" );

For complete details, see the Query Javadocs.

Query hints here are database query hints. They are added directly to the generated SQL according to Dialect#getQueryHintString.

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

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

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

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

Example 13. Hibernate name parameter binding

org.hibernate.query.Query query = session.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like :name" )
.setParameter( "name", "J%", StringType.INSTANCE );

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

Example 14. Hibernate name parameter binding (inferred type)

org.hibernate.query.Query query = session.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like :name" )
.setParameter( "name", "J%" );

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

Example 15. Hibernate name parameter binding (short forms)

org.hibernate.query.Query query = session.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like :name " +
    "  and p.createdOn > :timestamp" )
.setParameter( "name", "J%" )
.setParameter( "timestamp", timestamp, TemporalType.TIMESTAMP);

HQL-style positional parameters follow JDBC positional parameter syntax. They are declared using ? without a following ordinal. There is no way to relate two such positional parameters as being "the same" aside from binding the same value to each.

Example 16. Hibernate positional parameter binding

org.hibernate.query.Query query = session.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like ? " )
.setParameter( 0, "J%" );

This form should be considered deprecated and may be removed in the near future.

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

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

Example 17. Hibernate list() result

List<Person> persons = session.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like :name" )
.setParameter( "name", "J%" )
.list();

It is also possible to extract a single result from a Query.

Example 18. Hibernate uniqueResult()

Person person = (Person) session.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like :name" )
.setParameter( "name", "J%" )
.uniqueResult();

If the unique result is used often and the attributes upon which it is based are unique, you may want to consider mapping a natural-id and using the natural-id loading API. See the Natural Ids for more information on this topic.

Query scrolling

Hibernate offers additional, specialized methods for scrolling the query and handling results using a server-side cursor.

Query#scroll works in tandem with the JDBC notion of a scrollable ResultSet.

The Query#scroll method is overloaded:

Then main form accepts a single argument of type org.hibernate.ScrollMode which indicates the type of scrolling to be used. See the Javadocs for the details on each.
The second form takes no argument and will use the ScrollMode indicated by Dialect#defaultScrollMode.

Query#scroll returns a org.hibernate.ScrollableResults which wraps the underlying JDBC (scrollable) ResultSet and provides access to the results. Unlike a typical forward-only ResultSet, the ScrollableResults allows you to navigate the ResultSet in any direction.

Example 19. Scrolling through a ResultSet containing entities

try ( ScrollableResults scrollableResults = session.createQuery(
        "select p " +
        "from Person p " +
        "where p.name like :name" )
        .setParameter( "name", "J%" )
        .scroll()
) {
    while(scrollableResults.next()) {
        Person person = (Person) scrollableResults.get()[0];
        process(person);
    }
}

Since this form holds the JDBC ResultSet open, the application should indicate when it is done with the ScrollableResults by calling its close() method (as inherited from java.io.Closeable so that ScrollableResults will work with try-with-resources blocks).

If left unclosed by the application, Hibernate will automatically close the underlying resources (e.g. ResultSet and PreparedStatement) used internally by the ScrollableResults when the current transaction is ended (either commit or rollback).

However, it is good practice to close the ScrollableResults explicitly.

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

Hibernate also supports Query#iterate, which is intended for loading entities when it is known that the loaded entries are already stored in the second-level cache. The idea behind iterate is that just the matching identifiers will be obtained in the SQL query. From these the identifiers are resolved by second-level cache lookup. If these second-level cache lookups fail, additional queries will need to be issued against the database.

This operation can perform significantly better for loading large numbers of entities that for certain already exist in the second-level cache. In cases where many of the entities do not exist in the second-level cache, this operation will almost definitely perform worse.

The Iterator returned from Query#iterate is actually a specially typed Iterator: org.hibernate.engine.HibernateIterator. It is specialized to expose a close() method (again, inherited from java.io.Closeable). When you are done with this Iterator you should close it, either by casting to HibernateIterator or Closeable, or by calling Hibernate#close(java.util.Iterator).

Since 5.2, Hibernate offers support for returning a Stream which can be later used to transform the underlying ResultSet.

Internally, the stream() behaves like a Query#scroll and the underlying result is backed by a ScrollableResults.

Fetching a projection using the Query#stream method can be done as follows:

Example 20. Hibernate stream() using a projection result type

try ( Stream<Object[]> persons = session.createQuery(
    "select p.name, p.nickName " +
    "from Person p " +
    "where p.name like :name" )
.setParameter( "name", "J%" )
.stream() ) {

    persons
    .map( row -> new PersonNames(
            (String) row[0],
            (String) row[1] ) )
    .forEach( this::process );
}

When fetching a single result, like a Person entity, instead of a Stream<Object[]>, Hibernate is going to figure out the actual type, so the result is a Stream<Person>.

Example 21. Hibernate stream() using an entity result type

try( Stream<Person> persons = session.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like :name" )
.setParameter( "name", "J%" )
.stream() ) {

    Map<Phone, List<Call>> callRegistry = persons
            .flatMap( person -> person.getPhones().stream() )
            .flatMap( phone -> phone.getCalls().stream() )
            .collect( Collectors.groupingBy( Call::getPhone ) );

    process(callRegistry);
}

Just like with ScrollableResults, you should always close a Hibernate Stream either explicitly or using a try-with-resources block.

Case Sensitivity

With the exception of names of Java classes and properties, queries are case-insensitive. So SeLeCT is the same as sELEct is the same as SELECT, but org.hibernate.eg.FOO and org.hibernate.eg.Foo are different, as are foo.barSet and foo.BARSET.

This documentation uses lowercase keywords as convention in examples.

Statement types

Both HQL and JPQL allow SELECT, UPDATE and DELETE statements to be performed. HQL additionally allows INSERT statements, in a form similar to a SQL INSERT FROM SELECT.

Care should be taken as to when a UPDATE or DELETE statement is executed.

Caution should be used when executing bulk update or delete operations because they may result in inconsistencies between the database and the entities in the active persistence context. In general, bulk update and delete operations should only be performed within a transaction in a new persistence con text or before fetching or accessing entities whose state might be affected by such operations.

— Section 4.10 of the JPA 2.0 Specification

Select statements

The BNF for SELECT statements in HQL is:

select_statement :: =
    [select_clause]
    from_clause
    [where_clause]
    [groupby_clause]
    [having_clause]
    [orderby_clause]

The simplest possible HQL SELECT statement is of the form:

List<Person> persons = session.createQuery(
    "from Person" )
.list();

The select statement in JPQL is exactly the same as for HQL except that JPQL requires a select_clause, whereas HQL does not.

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p", Person.class )
.getResultList();

Even though HQL does not require the presence of a select_clause, it is generally good practice to include one. For simple queries the intent is clear and so the intended result of the select_clause is east to infer. But on more complex queries that is not always the case.

It is usually better to explicitly specify intent. Hibernate does not actually enforce that a select_clause be present even when parsing JPQL queries, however applications interested in JPA portability should take heed of this.

Update statements

The BNF for UPDATE statements is the same in HQL and JPQL:

update_statement ::=
    update_clause [where_clause]

update_clause ::=
    UPDATE entity_name [[AS] identification_variable]
    SET update_item {, update_item}*

update_item ::=
    [identification_variable.]{state_field | single_valued_object_field} = new_value

new_value ::=
    scalar_expression | simple_entity_expression | NULL

UPDATE statements, by default, do not effect the version or the timestamp attribute values for the affected entities.

However, you can force Hibernate to set the version or timestamp attribute values through the use of a versioned update. This is achieved by adding the VERSIONED keyword after the UPDATE keyword.

This is a Hibernate specific feature and will not work in a portable manner.

Custom version types, org.hibernate.usertype.UserVersionType, are not allowed in conjunction with a update versioned statement.

An UPDATE statement is executed using the executeUpdate() of either org.hibernate.query.Query or javax.persistence.Query. The method is named for those familiar with the JDBC executeUpdate() on java.sql.PreparedStatement.

The int value returned by the executeUpdate() method indicates the number of entities effected by the operation. This may or may not correlate to the number of rows effected in the database. An HQL bulk operation might result in multiple actual SQL statements being executed (for joined-subclass, for example). The returned number indicates the number of actual entities affected by the statement. Using a JOINED inheritance hierarchy, a delete against one of the subclasses may actually result in deletes against not just the table to which that subclass is mapped, but also the "root" table and tables "in between".

Example 22. UPDATE query statements

int updatedEntities = entityManager.createQuery(
    "update Person p " +
    "set p.name = :newName " +
    "where p.name = :oldName" )
.setParameter( "oldName", oldName )
.setParameter( "newName", newName )
.executeUpdate();

int updatedEntities = session.createQuery(
    "update Person " +
    "set name = :newName " +
    "where name = :oldName" )
.setParameter( "oldName", oldName )
.setParameter( "newName", newName )
.executeUpdate();

int updatedEntities = session.createQuery(
    "update versioned Person " +
    "set name = :newName " +
    "where name = :oldName" )
.setParameter( "oldName", oldName )
.setParameter( "newName", newName )
.executeUpdate();

Neither UPDATE nor DELETE statements allow implicit joins. Their form already disallows explicit joins too.

Delete statements

The BNF for DELETE statements is the same in HQL and JPQL:

delete_statement ::=
    delete_clause [where_clause]

delete_clause ::=
    DELETE FROM entity_name [[AS] identification_variable]

A DELETE statement is also executed using the executeUpdate() method of either org.hibernate.query.Query or javax.persistence.Query.

Insert statements

HQL adds the ability to define INSERT statements as well.

There is no JPQL equivalent to this.

The BNF for an HQL INSERT statement is:

insert_statement ::=
    insert_clause select_statement

insert_clause ::=
    INSERT INTO entity_name (attribute_list)

attribute_list ::=
    state_field[, state_field ]*

The attribute_list is analogous to the column specification in the SQL INSERT statement. For entities involved in mapped inheritance, only attributes directly defined on the named entity can be used in the attribute_list. Superclass properties are not allowed and subclass properties do not make sense. In other words, INSERT statements are inherently non-polymorphic.

select_statement can be any valid HQL select query, with the caveat that the return types must match the types expected by the insert. Currently, this is checked during query compilation rather than allowing the check to relegate to the database. This may cause problems between Hibernate Types which are equivalent as opposed to equal. For example, this might cause lead to issues with mismatches between an attribute mapped as a org.hibernate.type.DateType and an attribute defined as a org.hibernate.type.TimestampType, even though the database might not make a distinction or might be able to handle the conversion.

For the id attribute, the insert statement gives you two options. You can either explicitly specify the id property in the attribute_list, in which case its value is taken from the corresponding select expression, or omit it from the attribute_list in which case a generated value is used. This latter option is only available when using id generators that operate "in the database"; attempting to use this option with any "in memory" type generators will cause an exception during parsing.

For optimistic locking attributes, the insert statement again gives you two options. You can either specify the attribute in the attribute_list in which case its value is taken from the corresponding select expressions, or omit it from the attribute_list in which case the seed value defined by the corresponding org.hibernate.type.VersionType is used.

Example 23. INSERT query statements

int insertedEntities = session.createQuery(
    "insert into Partner (id, name) " +
    "select p.id, p.name " +
    "from Person p ")
.executeUpdate();

The `FROM` clause

The FROM clause is responsible defining the scope of object model types available to the rest of the query. It also is responsible for defining all the "identification variables" available to the rest of the query.

Identification variables

Identification variables are often referred to as aliases. References to object model classes in the FROM clause can be associated with an identification variable that can then be used to refer to that type throughout the rest of the query.

In most cases declaring an identification variable is optional, though it is usually good practice to declare them.

An identification variable must follow the rules for Java identifier validity.

According to JPQL, identification variables must be treated as case-insensitive. Good practice says you should use the same case throughout a query to refer to a given identification variable. In other words, JPQL says they can be case-insensitive and so Hibernate must be able to treat them as such, but this does not make it good practice.

Root entity references

A root entity reference, or what JPA calls a range variable declaration, is specifically a reference to a mapped entity type from the application. It cannot name component/ embeddable types. And associations, including collections, are handled in a different manner, as later discussed.

The BNF for a root entity reference is:

root_entity_reference ::=
    entity_name [AS] identification_variable

Example 24. Simple query example

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from org.hibernate.userguide.model.Person p", Person.class )
.getResultList();

We see that the query is defining a root entity reference to the org.hibernate.userguide.model.Person object model type. Additionally, it declares an alias of p to that org.hibernate.userguide.model.Person reference, which is the identification variable.

Usually, the root entity reference represents just the entity name rather than the entity class FQN (fully-qualified name). By default, the entity name is the unqualified entity class name, here Person

Example 25. Simple query using entity name for root entity reference

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p", Person.class )
.getResultList();

Multiple root entity references can also be specified, even when naming the same entity.

Example 26. Simple query using multiple root entity references

List<Object[]> persons = entityManager.createQuery(
    "select distinct pr, ph " +
    "from Person pr, Phone ph " +
    "where ph.person = pr and ph is not null", Object[].class)
.getResultList();

List<Person> persons = entityManager.createQuery(
    "select distinct pr1 " +
    "from Person pr1, Person pr2 " +
    "where pr1.id <> pr2.id " +
    "  and pr1.address = pr2.address " +
    "  and pr1.createdOn < pr2.createdOn", Person.class )
.getResultList();

Explicit joins

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

Example 27. Explicit inner join examples

List<Person> persons = entityManager.createQuery(
    "select distinct pr " +
    "from Person pr " +
    "join pr.phones ph " +
    "where ph.type = :phoneType", Person.class )
.setParameter( "phoneType", PhoneType.MOBILE )
.getResultList();

// same query but specifying join type as 'inner' explicitly
List<Person> persons = entityManager.createQuery(
    "select distinct pr " +
    "from Person pr " +
    "inner join pr.phones ph " +
    "where ph.type = :phoneType", Person.class )
.setParameter( "phoneType", PhoneType.MOBILE )
.getResultList();

Example 28. Explicit left (outer) join examples

List<Person> persons = entityManager.createQuery(
    "select distinct pr " +
    "from Person pr " +
    "left join pr.phones ph " +
    "where ph is null " +
    "   or ph.type = :phoneType", Person.class )
.setParameter( "phoneType", PhoneType.LAND_LINE )
.getResultList();

// functionally the same query but using the 'left outer' phrase
List<Person> persons = entityManager.createQuery(
    "select distinct pr " +
    "from Person pr " +
    "left outer join pr.phones ph " +
    "where ph is null " +
    "   or ph.type = :phoneType", Person.class )
.setParameter( "phoneType", PhoneType.LAND_LINE )
.getResultList();

HQL also defines a WITH clause to qualify the join conditions.

This is specific to HQL. JPQL defines the ON clause for this feature.

Example 29. HQL WITH clause join example

List<Object[]> personsAndPhones = session.createQuery(
    "select pr.name, ph.number " +
    "from Person pr " +
    "left join pr.phones ph with ph.type = :phoneType " )
.setParameter( "phoneType", PhoneType.LAND_LINE )
.list();

Example 30. JPQL ON clause join example

List<Object[]> personsAndPhones = entityManager.createQuery(
    "select pr.name, ph.number " +
    "from Person pr " +
    "left join pr.phones ph on ph.type = :phoneType " )
.setParameter( "phoneType", PhoneType.LAND_LINE )
.getResultList();

The important distinction is that in the generated SQL the conditions of the WITH/ON clause are made part of the ON clause in the generated SQL, as opposed to the other queries in this section where the HQL/JPQL conditions are made part of the WHERE clause in the generated SQL.

The distinction in this specific example is probably not that significant. The with clause is sometimes necessary for more complicated queries.

Explicit joins may reference association or component/embedded attributes. In the case of component/embedded attributes, the join is simply logical and does not correlate to a physical (SQL) join. For further information about collection-valued association references, see Collection member references.

An important use case for explicit joins is to define FETCH JOINS which override the laziness of the joined association. As an example, given an entity named Person with a collection-valued association named phones, the JOIN FETCH will also load the child collection in the same SQL query:

Example 31. Fetch join example

// functionally the same query but using the 'left outer' phrase
List<Person> persons = entityManager.createQuery(
    "select distinct pr " +
    "from Person pr " +
    "left join fetch pr.phones ", Person.class )
.getResultList();

As you can see from the example, a fetch join is specified by injecting the keyword fetch after the keyword join. In the example, we used a left outer join because we also wanted to return customers who have no orders.

Inner joins can also be fetched, but inner joins filter out the root entity. In the example, using an inner join instead would have resulted in customers without any orders being filtered out of the result.

Fetch joins are not valid in sub-queries.

Care should be taken when fetch joining a collection-valued association which is in any way further restricted (the fetched collection will be restricted too). For this reason, it is usually considered best practice not to assign an identification variable to fetched joins except for the purpose of specifying nested fetch joins.

Fetch joins should not be used in paged queries (e.g. setFirstResult() or setMaxResults()), nor should they be used with the scroll() or iterate() features.

Implicit joins (path expressions)

Another means of adding to the scope of object model types available to the query is through the use of implicit joins or path expressions.

Example 32. Simple implicit join example

List<Phone> phones = entityManager.createQuery(
    "select ph " +
    "from Phone ph " +
    "where ph.person.address = :address ", Phone.class )
.setParameter( "address", address )
.getResultList();

// same as
List<Phone> phones = entityManager.createQuery(
    "select ph " +
    "from Phone ph " +
    "join ph.person pr " +
    "where pr.address = :address ", Phone.class )
.setParameter( "address", address)
.getResultList();

An implicit join always starts from an identification variable, followed by the navigation operator ( . ), followed by an attribute for the object model type referenced by the initial identification variable. In the example, the initial identification variable is ph which refers to the Phone entity. The ph.person reference then refers to the person attribute of the Phone entity. person is an association type so we further navigate to its age attribute.

If the attribute represents an entity association (non-collection) or a component/embedded, that reference can be further navigated. Basic values and collection-valued associations cannot be further navigated.

As shown in the example, implicit joins can appear outside the FROM clause. However, they affect the FROM clause.

Implicit joins are always treated as inner joins.

Multiple references to the same implicit join always refer to the same logical and physical (SQL) join.

Example 33. Reused implicit join

List<Phone> phones = entityManager.createQuery(
    "select ph " +
    "from Phone ph " +
    "where ph.person.address = :address " +
    "  and ph.person.createdOn > :timestamp", Phone.class )
.setParameter( "address", address )
.setParameter( "timestamp", timestamp )
.getResultList();

//same as
List<Phone> phones = entityManager.createQuery(
    "select ph " +
    "from Phone ph " +
    "inner join ph.person pr " +
    "where pr.address = :address " +
    "  and pr.createdOn > :timestamp", Phone.class )
.setParameter( "address", address )
.setParameter( "timestamp", timestamp )
.getResultList();

Just as with explicit joins, implicit joins may reference association or component/embedded attributes. For further information about collection-valued association references, see Collection member references.

In the case of component/embedded attributes, the join is simply logical and does not correlate to a physical (SQL) join. Unlike explicit joins, however, implicit joins may also reference basic state fields as long as the path expression ends there.

Distinct

For JPQL and HQL, DISTINCT has two meanings:

It can be passed to the database so that duplicates are removed from a result set
It can be used to filter out the same parent entity references when join fetching a child collection

Using DISTINCT with SQL projections

For SQL projections, DISTINCT needs to be passed to the database because the duplicated entries need to be filtered out before being returned to the database client.

Example 34. Using DISTINCT with projection queries example

List<String> lastNames = entityManager.createQuery(
    "select distinct p.lastName " +
    "from Person p", String.class)
.getResultList();

When running the query above, Hibernate generates the following SQL query:

SELECT DISTINCT
    p.last_name as col_0_0_
FROM person p

For this particular use case, passing the DISTINCT keyword from JPQL/HQL to the database is the right thing to do.

Using DISTINCT with entity queries

DISTINCT can also be used to filter out entity object references when fetching a child association along with the parent entities.

Example 35. Using DISTINCT with entity queries example

List<Person> authors = entityManager.createQuery(
    "select distinct p " +
    "from Person p " +
    "left join fetch p.books", Person.class)
.getResultList();

In this case, DISTINCT is used because there can be multiple Books entities associated to a given Person. If in the database there are 3 Persons in the database and each person has 2 Books, without DISTINCT this query will return 6 Persons since the SQL-level result-set size is given by the number of joined Book records.

However, the DISTINCT keyword is passed to the database as well:

SELECT DISTINCT
    p.id as id1_1_0_,
    b.id as id1_0_1_,
    p.first_name as first_na2_1_0_,
    p.last_name as last_nam3_1_0_,
    b.author_id as author_i3_0_1_,
    b.title as title2_0_1_,
    b.author_id as author_i3_0_0__,
    b.id as id1_0_0__
FROM person p
LEFT OUTER JOIN book b ON p.id=b.author_id

In this case, the DISTINCT SQL keyword is undesirable since it does a redundant result set sorting, as explained in this blog post. To fix this issue, Hibernate 5.2.2 added support for the HINT_PASS_DISTINCT_THROUGH entity query hint:

Example 36. Using DISTINCT with entity queries example

List<Person> authors = entityManager.createQuery(
    "select distinct p " +
    "from Person p " +
    "left join fetch p.books", Person.class)
.setHint( QueryHints.HINT_PASS_DISTINCT_THROUGH, false )
.getResultList();

With this entity query hint, Hibernate will not pass the DISTINCT keyword to the SQL query:

SELECT
    p.id as id1_1_0_,
    b.id as id1_0_1_,
    p.first_name as first_na2_1_0_,
    p.last_name as last_nam3_1_0_,
    b.author_id as author_i3_0_1_,
    b.title as title2_0_1_,
    b.author_id as author_i3_0_0__,
    b.id as id1_0_0__
FROM person p
LEFT OUTER JOIN book b ON p.id=b.author_id

When using the HINT_PASS_DISTINCT_THROUGH entity query hint, Hibernate can still remove the duplicated parent-side entities from the query result.

Collection member references

References to collection-valued associations actually refer to the values of that collection.

Example 37. Collection references example

List<Phone> phones = entityManager.createQuery(
    "select ph " +
    "from Person pr " +
    "join pr.phones ph " +
    "join ph.calls c " +
    "where pr.address = :address " +
    "  and c.duration > :duration", Phone.class )
.setParameter( "address", address )
.setParameter( "duration", duration )
.getResultList();

// alternate syntax
List<Phone> phones = session.createQuery(
    "select ph " +
    "from Person pr, " +
    "in (pr.phones) ph, " +
    "in (ph.calls) c " +
    "where pr.address = :address " +
    "  and c.duration > :duration" )
.setParameter( "address", address )
.setParameter( "duration", duration )
.list();

In the example, the identification variable ph actually refers to the object model type Phone, which is the type of the elements of the Person#phones association.

The example also shows the alternate syntax for specifying collection association joins using the IN syntax. Both forms are equivalent. Which form an application chooses to use is simply a matter of taste.

Special case - qualified path expressions

We said earlier that collection-valued associations actually refer to the values of that collection. Based on the type of collection, there are also available a set of explicit qualification expressions.

Example 38. Qualified collection references example

@OneToMany(mappedBy = "phone")
@MapKey(name = "timestamp")
@MapKeyTemporal(TemporalType.TIMESTAMP )
private Map<Date, Call> callHistory = new HashMap<>();


// select all the calls (the map value) for a given Phone
List<Call> calls = entityManager.createQuery(
    "select ch " +
    "from Phone ph " +
    "join ph.callHistory ch " +
    "where ph.id = :id ", Call.class )
.setParameter( "id", id )
.getResultList();

// same as above
List<Call> calls = entityManager.createQuery(
    "select value(ch) " +
    "from Phone ph " +
    "join ph.callHistory ch " +
    "where ph.id = :id ", Call.class )
.setParameter( "id", id )
.getResultList();

// select all the Call timestamps (the map key) for a given Phone
List<Date> timestamps = entityManager.createQuery(
    "select key(ch) " +
    "from Phone ph " +
    "join ph.callHistory ch " +
    "where ph.id = :id ", Date.class )
.setParameter( "id", id )
.getResultList();

// select all the Call and their timestamps (the 'Map.Entry') for a given Phone
List<Map.Entry<Date, Call>> callHistory = entityManager.createQuery(
    "select entry(ch) " +
    "from Phone ph " +
    "join ph.callHistory ch " +
    "where ph.id = :id " )
.setParameter( "id", id )
.getResultList();

// Sum all call durations for a given Phone of a specific Person
Long duration = entityManager.createQuery(
    "select sum(ch.duration) " +
    "from Person pr " +
    "join pr.phones ph " +
    "join ph.callHistory ch " +
    "where ph.id = :id " +
    "  and index(ph) = :phoneIndex", Long.class )
.setParameter( "id", id )
.setParameter( "phoneIndex", phoneIndex )
.getSingleResult();

VALUE: Refers to the collection value. Same as not specifying a qualifier. Useful to explicitly show intent. Valid for any type of collection-valued reference.
INDEX: According to HQL rules, this is valid for both Maps and Lists which specify a javax.persistence.OrderColumn annotation to refer to the Map key or the List position (aka the OrderColumn value). JPQL however, reserves this for use in the List case and adds KEY for the Map case. Applications interested in JPA provider portability should be aware of this distinction.
KEY: Valid only for Maps. Refers to the map’s key. If the key is itself an entity, it can be further navigated.
ENTRY: Only valid for Maps. Refers to the map’s logical java.util.Map.Entry tuple (the combination of its key and value). ENTRY is only valid as a terminal path and it’s applicable to the SELECT clause only.

See Collection-related expressions for additional details on collection related expressions.

Polymorphism

HQL and JPQL queries are inherently polymorphic.

List<Payment> payments = entityManager.createQuery(
    "select p " +
    "from Payment p ", Payment.class )
.getResultList();

This query names the Payment entity explicitly. However, all subclasses of Payment are also available to the query. So, if the CreditCardPayment and WireTransferPayment entities extend the Payment class, all three types would be available to the entity query, and the query would return instances of all three.

This behavior can be altered in two ways:

by limiting the query to select only from the subclass entity
by using either the org.hibernate.annotations.Polymorphism annotation (global, and Hibernate-specific). See the @Polymorphism section for more info about this use case.

The HQL query from java.lang.Object is totally valid (although not very practical from a performance perspective)!

It returns every object of every entity type defined by your application mappings.

Expressions

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

Identification variable

See The FROM clause.

Path expressions

Again, see The FROM clause.

Literals

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

Example 39. String literals examples

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like 'Joe'", Person.class)
.getResultList();

// Escaping quotes
List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like 'Joe''s'", Person.class)
.getResultList();

Numeric literals are allowed in a few different forms.

Example 40. Numeric literal examples

// simple integer literal
Person person = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.id = 1", Person.class)
.getSingleResult();

// simple integer literal, typed as a long
Person person = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.id = 1L", Person.class)
.getSingleResult();

// decimal notation
List<Call> calls = entityManager.createQuery(
    "select c " +
    "from Call c " +
    "where c.duration > 100.5", Call.class )
.getResultList();

// decimal notation, typed as a float
List<Call> calls = entityManager.createQuery(
    "select c " +
    "from Call c " +
    "where c.duration > 100.5F", Call.class )
.getResultList();

// scientific notation
List<Call> calls = entityManager.createQuery(
    "select c " +
    "from Call c " +
    "where c.duration > 1e+2", Call.class )
.getResultList();

// scientific notation, typed as a float
List<Call> calls = entityManager.createQuery(
    "select c " +
    "from Call c " +
    "where c.duration > 1e+2F", Call.class )
.getResultList();

In the scientific notation form, the E is case-insensitive.

Specific typing can be achieved through the use of the same suffix approach specified by Java. So, L denotes a long, D denotes a double, F denotes a float. The actual suffix is case-insensitive.

The boolean literals are TRUE and FALSE, again case-insensitive.

Enums can even be referenced as literals. The fully-qualified enum class name must be used. HQL can also handle constants in the same manner, though JPQL does not define that as supported.

Entity names can also be used as literal. See Entity type.

Date/time literals can be specified using the JDBC escape syntax:

{d 'yyyy-mm-dd'} for dates
{t 'hh:mm:ss'} for times
{ts 'yyyy-mm-dd hh:mm:ss[.millis]'} (millis optional) for timestamps.

These Date/time literals only work if you JDBC drivers supports them.

Arithmetic

Arithmetic operations also represent valid expressions.

Example 41. Numeric arithmetic examples

// select clause date/time arithmetic operations
Long duration = entityManager.createQuery(
    "select sum(ch.duration) * :multiplier " +
    "from Person pr " +
    "join pr.phones ph " +
    "join ph.callHistory ch " +
    "where ph.id = 1L ", Long.class )
.setParameter( "multiplier", 1000L )
.getSingleResult();

// select clause date/time arithmetic operations
Integer years = entityManager.createQuery(
    "select year( current_date() ) - year( p.createdOn ) " +
    "from Person p " +
    "where p.id = 1L", Integer.class )
.getSingleResult();

// where clause arithmetic operations
List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where year( current_date() ) - year( p.createdOn ) > 1", Person.class )
.getResultList();

The following rules apply to the result of arithmetic operations:

If either of the operands is Double/double, the result is a Double
else, if either of the operands is Float/float, the result is a Float
else, if either operand is BigDecimal, the result is BigDecimal
else, if either operand is BigInteger, the result is BigInteger (except for division, in which case the result type is not further defined)
else, if either operand is Long/long, the result is Long (except for division, in which case the result type is not further defined)
else, (the assumption being that both operands are of integral type) the result is Integer (except for division, in which case the result type is not further defined)

Date arithmetic is also supported, albeit in a more limited fashion. This is due partially to differences in database support and partially to the lack of support for INTERVAL definition in the query language itself.

Concatenation (operation)

HQL defines a concatenation operator in addition to supporting the concatenation (CONCAT) function. This is not defined by JPQL, so portable applications should avoid it use. The concatenation operator is taken from the SQL concatenation operator (e.g ||).

Example 42. Concatenation operation example

String name = entityManager.createQuery(
    "select 'Customer ' || p.name " +
    "from Person p " +
    "where p.id = 1", String.class )
.getSingleResult();

See Scalar functions for details on the concat() function

Aggregate functions

Aggregate functions are also valid expressions in HQL and JPQL. The semantic is the same as their SQL counterpart. The supported aggregate functions are:

COUNT (including distinct/all qualifiers): The result type is always Long.
AVG: The result type is always Double.
MIN: The result type is the same as the argument type.
MAX: The result type is the same as the argument type.
SUM: The result type of the SUM() function depends on the type of the values being summed. For integral values (other than BigInteger), the result type is Long.

For floating point values (other than BigDecimal) the result type is Double. For BigInteger values, the result type is BigInteger. For BigDecimal values, the result type is BigDecimal.

Example 43. Aggregate function examples

Object[] callStatistics = entityManager.createQuery(
    "select " +
    "    count(c), " +
    "    sum(c.duration), " +
    "    min(c.duration), " +
    "    max(c.duration), " +
    "    avg(c.duration)  " +
    "from Call c ", Object[].class )
.getSingleResult();

Long phoneCount = entityManager.createQuery(
    "select count( distinct c.phone ) " +
    "from Call c ", Long.class )
.getSingleResult();

List<Object[]> callCount = entityManager.createQuery(
    "select p.number, count(c) " +
    "from Call c " +
    "join c.phone p " +
    "group by p.number", Object[].class )
.getResultList();

Aggregations often appear with grouping. For information on grouping see Group by.

Scalar functions

Both HQL and JPQL define some standard functions that are available regardless of the underlying database in use. HQL can also understand additional functions defined by the Dialect as well as the application.

JPQL standardized functions

Here is the list of functions defined as supported by JPQL. Applications interested in remaining portable between JPA providers should stick to these functions.

CONCAT: String concatenation function. Variable argument length of 2 or more string values to be concatenated together.

List<String> callHistory = entityManager.createQuery(
    "select concat( p.number, ' : ' , cast(c.duration as string) ) " +
    "from Call c " +
    "join c.phone p", String.class )
.getResultList();

SUBSTRING: Extracts a portion of a string value. The second argument denotes the starting position, where 1 is the first character of the string. The third (optional) argument denotes the length.

List<String> prefixes = entityManager.createQuery(
    "select substring( p.number, 1, 2 ) " +
    "from Call c " +
    "join c.phone p", String.class )
.getResultList();

UPPER: Upper cases the specified string

List<String> names = entityManager.createQuery(
    "select upper( p.name ) " +
    "from Person p ", String.class )
.getResultList();

LOWER: Lower cases the specified string

List<String> names = entityManager.createQuery(
    "select lower( p.name ) " +
    "from Person p ", String.class )
.getResultList();

TRIM: Follows the semantics of the SQL trim function.

List<String> names = entityManager.createQuery(
    "select trim( p.name ) " +
    "from Person p ", String.class )
.getResultList();

LENGTH: Returns the length of a string.

List<Integer> lengths = entityManager.createQuery(
    "select length( p.name ) " +
    "from Person p ", Integer.class )
.getResultList();

LOCATE: Locates a string within another string. The third argument (optional) is used to denote a position from which to start looking.

List<Integer> sizes = entityManager.createQuery(
    "select locate( 'John', p.name ) " +
    "from Person p ", Integer.class )
.getResultList();

ABS: Calculates the mathematical absolute value of a numeric value.

List<Integer> abs = entityManager.createQuery(
    "select abs( c.duration ) " +
    "from Call c ", Integer.class )
.getResultList();

MOD: Calculates the remainder of dividing the first argument by the second.

List<Integer> mods = entityManager.createQuery(
    "select mod( c.duration, 10 ) " +
    "from Call c ", Integer.class )
.getResultList();

SQRT: Calculates the mathematical square root of a numeric value.

List<Double> sqrts = entityManager.createQuery(
    "select sqrt( c.duration ) " +
    "from Call c ", Double.class )
.getResultList();

CURRENT_DATE: Returns the database current date.

List<Call> calls = entityManager.createQuery(
    "select c " +
    "from Call c " +
    "where c.timestamp = current_date", Call.class )
.getResultList();

CURRENT_TIME: Returns the database current time.

List<Call> calls = entityManager.createQuery(
    "select c " +
    "from Call c " +
    "where c.timestamp = current_time", Call.class )
.getResultList();

CURRENT_TIMESTAMP: Returns the database current timestamp.

List<Call> calls = entityManager.createQuery(
    "select c " +
    "from Call c " +
    "where c.timestamp = current_timestamp", Call.class )
.getResultList();

HQL functions

Beyond the JPQL standardized functions, HQL makes some additional functions available regardless of the underlying database in use.

BIT_LENGTH: Returns the length of binary data.

List<Number> bits = entityManager.createQuery(
    "select bit_length( c.duration ) " +
    "from Call c ", Number.class )
.getResultList();

CAST: Performs a SQL cast. The cast target should name the Hibernate mapping type to use. See the data types chapter on for more information.

List<String> durations = entityManager.createQuery(
    "select cast( c.duration as string ) " +
    "from Call c ", String.class )
.getResultList();

EXTRACT: Performs a SQL extraction on datetime values. An extraction extracts parts of the datetime (the year, for example).

List<Integer> years = entityManager.createQuery(
    "select extract( YEAR from c.timestamp ) " +
    "from Call c ", Integer.class )
.getResultList();

See the abbreviated forms below.

YEAR: Abbreviated extract form for extracting the year.

List<Integer> years = entityManager.createQuery(
    "select year( c.timestamp ) " +
    "from Call c ", Integer.class )
.getResultList();

MONTH: Abbreviated extract form for extracting the month.
DAY: Abbreviated extract form for extracting the day.
HOUR: Abbreviated extract form for extracting the hour.
MINUTE: Abbreviated extract form for extracting the minute.
SECOND: Abbreviated extract form for extracting the second.
STR: Abbreviated form for casting a value as character data.

List<String> timestamps = entityManager.createQuery(
    "select str( c.timestamp ) " +
    "from Call c ", String.class )
.getResultList();
List<String> timestamps = entityManager.createQuery(
    "select str( cast(duration as float) / 60, 4, 2 ) " +
    "from Call c ", String.class )
.getResultList();

Non-standardized functions

Hibernate Dialects can register additional functions known to be available for that particular database product. These functions are also available in HQL (and JPQL, though only when using Hibernate as the JPA provider obviously). However, they would only be available when using that database Dialect. Applications that aim for database portability should avoid using functions in this category.

Application developers can also supply their own set of functions. This would usually represent either custom SQL functions or aliases for snippets of SQL. Such function declarations are made by using the addSqlFunction() method of org.hibernate.cfg.Configuration.

Collection-related expressions

There are a few specialized expressions for working with collection-valued associations. Generally, these are just abbreviated forms or other expressions for the sake of conciseness.

SIZE: Calculate the size of a collection. Equates to a subquery!
MAXELEMENT: Available for use on collections of basic type. Refers to the maximum value as determined by applying the max SQL aggregation.
MAXINDEX: Available for use on indexed collections. Refers to the maximum index (key/position) as determined by applying the max SQL aggregation.
MINELEMENT: Available for use on collections of basic type. Refers to the minimum value as determined by applying the min SQL aggregation.
MININDEX: Available for use on indexed collections. Refers to the minimum index (key/position) as determined by applying the min SQL aggregation.
ELEMENTS: Used to refer to the elements of a collection as a whole. Only allowed in the where clause. Often used in conjunction with ALL, ANY or SOME restrictions.
INDICES: Similar to elements except that indices refers to the collections indices (keys/positions) as a whole.

Example 44. Collection-related expressions examples

List<Phone> phones = entityManager.createQuery(
    "select p " +
    "from Phone p " +
    "where maxelement( p.calls ) = :call", Phone.class )
.setParameter( "call", call )
.getResultList();

List<Phone> phones = entityManager.createQuery(
    "select p " +
    "from Phone p " +
    "where minelement( p.calls ) = :call", Phone.class )
.setParameter( "call", call )
.getResultList();

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where maxindex( p.phones ) = 0", Person.class )
.getResultList();

// the above query can be re-written with member of
List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where :phone member of p.phones", Person.class )
.setParameter( "phone", phone )
.getResultList();

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where :phone = some elements ( p.phones )", Person.class )
.setParameter( "phone", phone )
.getResultList();

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where exists elements ( p.phones )", Person.class )
.getResultList();

List<Phone> phones = entityManager.createQuery(
    "select p " +
    "from Phone p " +
    "where current_date() > key( p.callHistory )", Phone.class )
.getResultList();

List<Phone> phones = entityManager.createQuery(
    "select p " +
    "from Phone p " +
    "where current_date() > all elements( p.repairTimestamps )", Phone.class )
.getResultList();

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where 1 in indices( p.phones )", Person.class )
.getResultList();

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where size( p.phones ) = 2", Person.class )
.getResultList();

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

Example 45. Index operator examples

// indexed lists
List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.phones[ 0 ].type = 'LAND_LINE'", Person.class )
.getResultList();

// maps
List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.addresses[ 'HOME' ] = :address", Person.class )
.setParameter( "address", address)
.getResultList();

//max index in list
List<Person> persons = entityManager.createQuery(
    "select pr " +
    "from Person pr " +
    "where pr.phones[ maxindex(pr.phones) ].type = 'LAND_LINE'", Person.class )
.getResultList();

See also Special case - qualified path expressions as there is a good deal of overlap.

Entity type

We can also refer to the type of an entity as an expression. This is mainly useful when dealing with entity inheritance hierarchies. The type can expressed using a TYPE function used to refer to the type of an identification variable representing an entity. The name of the entity also serves as a way to refer to an entity type. Additionally, the entity type can be parameterized, in which case the entity’s Java Class reference would be bound as the parameter value.

Example 46. Entity type expression examples

List<Payment> payments = entityManager.createQuery(
    "select p " +
    "from Payment p " +
    "where type(p) = CreditCardPayment", Payment.class )
.getResultList();
List<Payment> payments = entityManager.createQuery(
    "select p " +
    "from Payment p " +
    "where type(p) = :type", Payment.class )
.setParameter( "type", WireTransferPayment.class)
.getResultList();

HQL also has a legacy form of referring to an entity type, though that legacy form is considered deprecated in favor of TYPE. The legacy form would have used p.class in the examples rather than type(p). It is mentioned only for completeness.

CASE expressions

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

Simple CASE expressions

The simple form has the following syntax:

CASE {operand} WHEN {test_value} THEN {match_result} ELSE {miss_result} END

Example 47. Simple case expression example

List<String> nickNames = entityManager.createQuery(
    "select " +
    "    case p.nickName " +
    "    when 'NA' " +
    "    then '<no nick name>' " +
    "    else p.nickName " +
    "    end " +
    "from Person p", String.class )
.getResultList();

// same as above
List<String> nickNames = entityManager.createQuery(
    "select coalesce(p.nickName, '<no nick name>') " +
    "from Person p", String.class )
.getResultList();

Searched CASE expressions

The searched form has the following syntax:

CASE [ WHEN {test_conditional} THEN {match_result} ]* ELSE {miss_result} END

Example 48. Searched case expression example

List<String> nickNames = entityManager.createQuery(
    "select " +
    "    case " +
    "    when p.nickName is null " +
    "    then " +
    "        case " +
    "        when p.name is null " +
    "        then '<no nick name>' " +
    "        else p.name " +
    "        end" +
    "    else p.nickName " +
    "    end " +
    "from Person p", String.class )
.getResultList();

// coalesce can handle this more succinctly
List<String> nickNames = entityManager.createQuery(
    "select coalesce( p.nickName, p.name, '<no nick name>' ) " +
    "from Person p", String.class )
.getResultList();

NULLIF expressions

NULLIF is an abbreviated CASE expression that returns NULL if its operands are considered equal.

Example 49. NULLIF example

List<String> nickNames = entityManager.createQuery(
    "select nullif( p.nickName, p.name ) " +
    "from Person p", String.class )
.getResultList();

// equivalent CASE expression
List<String> nickNames = entityManager.createQuery(
    "select " +
    "    case" +
    "    when p.nickName = p.name" +
    "    then null" +
    "    else p.nickName" +
    "    end " +
    "from Person p", String.class )
.getResultList();

COALESCE expressions

COALESCE is an abbreviated CASE expression that returns the first non-null operand. We have seen a number of COALESCE examples above.

The `SELECT` clause

The SELECT clause identifies which objects and values to return as the query results. The expressions discussed in Expressions are all valid select expressions, except where otherwise noted. See the section Hibernate Query API for information on handling the results depending on the types of values specified in the SELECT clause.

There is a particular expression type that is only valid in the select clause. Hibernate calls this "dynamic instantiation". JPQL supports some of that feature and calls it a "constructor expression".

So rather than dealing with the Object[] (again, see Hibernate Query API) here we are wrapping the values in a type-safe java object that will be returned as the results of the query.

Example 50. Dynamic HQL and JPQL instantiation example

public class CallStatistics {

    private final long count;
    private final long total;
    private final int min;
    private final int max;
    private final double abg;

    public CallStatistics(long count, long total, int min, int max, double abg) {
        this.count = count;
        this.total = total;
        this.min = min;
        this.max = max;
        this.abg = abg;
    }

    //Getters and setters omitted for brevity
}

CallStatistics callStatistics = entityManager.createQuery(
    "select new org.hibernate.userguide.hql.CallStatistics(" +
    "    count(c), " +
    "    sum(c.duration), " +
    "    min(c.duration), " +
    "    max(c.duration), " +
    "    avg(c.duration)" +
    ")  " +
    "from Call c ", CallStatistics.class )
.getSingleResult();

The class reference must be fully qualified and it must have a matching constructor.

The class here need not be mapped. If it does represent an entity, the resulting instances are returned in the NEW state (not managed!).

HQL supports additional "dynamic instantiation" features. First, the query can specify to return a List rather than an Object[] for scalar results:

Example 51. Dynamic instantiation example - list

List<List> phoneCallDurations = entityManager.createQuery(
    "select new list(" +
    "    p.number, " +
    "    c.duration " +
    ")  " +
    "from Call c " +
    "join c.phone p ", List.class )
.getResultList();

The results from this query will be a List<List> as opposed to a List<Object[]>

HQL also supports wrapping the scalar results in a Map.

Example 52. Dynamic instantiation example - map

List<Map> phoneCallTotalDurations = entityManager.createQuery(
    "select new map(" +
    "    p.number as phoneNumber , " +
    "    sum(c.duration) as totalDuration, " +
    "    avg(c.duration) as averageDuration " +
    ")  " +
    "from Call c " +
    "join c.phone p " +
    "group by p.number ", Map.class )
.getResultList();

The results from this query will be a List<Map<String, Object>> as opposed to a List<Object[]>. The keys of the map are defined by the aliases given to the select expressions. If the user doesn’t assign aliases, the key will be the index of each particular result set column (e.g. 0, 1, 2, etc).

Predicates

Predicates form the basis of the where clause, the having clause and searched case expressions. They are expressions which resolve to a truth value, generally TRUE or FALSE, although boolean comparisons involving NULL generally resolve to UNKNOWN.

Relational comparisons

Comparisons involve one of the comparison operators: =, >, >=, <, <=, <>. HQL also defines != as a comparison operator synonymous with <>. The operands should be of the same type.

Example 53. Relational comparison examples

// numeric comparison
List<Call> calls = entityManager.createQuery(
    "select c " +
    "from Call c " +
    "where c.duration < 30 ", Call.class )
.getResultList();

// string comparison
List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like 'John%' ", Person.class )
.getResultList();

// datetime comparison
List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.createdOn > '1950-01-01' ", Person.class )
.getResultList();

// enum comparison
List<Phone> phones = entityManager.createQuery(
    "select p " +
    "from Phone p " +
    "where p.type = 'MOBILE' ", Phone.class )
.getResultList();

// boolean comparison
List<Payment> payments = entityManager.createQuery(
    "select p " +
    "from Payment p " +
    "where p.completed = true ", Payment.class )
.getResultList();

// boolean comparison
List<Payment> payments = entityManager.createQuery(
    "select p " +
    "from Payment p " +
    "where type(p) = WireTransferPayment ", Payment.class )
.getResultList();

// entity value comparison
List<Object[]> phonePayments = entityManager.createQuery(
    "select p " +
    "from Payment p, Phone ph " +
    "where p.person = ph.person ", Object[].class )
.getResultList();

Comparisons can also involve subquery qualifiers: ALL, ANY, SOME. SOME and ANY are synonymous.

The ALL qualifier resolves to true if the comparison is true for all of the values in the result of the subquery. It resolves to false if the subquery result is empty.

Example 54. ALL subquery comparison qualifier example

// select all persons with all calls shorter than 50 seconds
List<Person> persons = entityManager.createQuery(
    "select distinct p.person " +
    "from Phone p " +
    "join p.calls c " +
    "where 50 > all ( " +
    "    select duration" +
    "    from Call" +
    "    where phone = p " +
    ") ", Person.class )
.getResultList();

The ANY/SOME qualifier resolves to true if the comparison is true for some of (at least one of) the values in the result of the subquery. It resolves to false if the subquery result is empty.

Nullness predicate

Check a value for nullness. Can be applied to basic attribute references, entity references and parameters. HQL additionally allows it to be applied to component/embeddable types.

Example 55. Nullness checking examples

// select all persons with a nickname
List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.nickName is not null", Person.class )
.getResultList();

// select all persons without a nickname
List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.nickName is null", Person.class )
.getResultList();

Like predicate

Performs a like comparison on string values. The syntax is:

like_expression ::=
    string_expression
    [NOT] LIKE pattern_value
    [ESCAPE escape_character]

The semantics follow that of the SQL like expression. The pattern_value is the pattern to attempt to match in the string_expression. Just like SQL, pattern_value can use _ and % as wildcards. The meanings are the same. The _ symbol matches any single character and % matches any number of characters.

Example 56. Like predicate examples

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like 'Jo%'", Person.class )
.getResultList();

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.name not like 'Jo%'", Person.class )
.getResultList();

The optional escape 'escape character' is used to specify an escape character used to escape the special meaning of _ and % in the pattern_value. This is useful when needing to search on patterns including either _ or %.

The syntax is formed as follows: 'like_predicate' escape 'escape_symbol' So, if | is the escape symbol and we want to match all stored procedures prefixed with Dr_, the like criteria becomes: 'Dr|_%' escape '|':

Example 57. Like with escape symbol

// find any person with a name starting with "Dr_"
List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.name like 'Dr|_%' escape '|'", Person.class )
.getResultList();

Between predicate

Analogous to the SQL BETWEEN expression, it checks if the value is within boundaries. All the operands should have comparable types.

Example 58. Between predicate examples

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "join p.phones ph " +
    "where p.id = 1L and index(ph) between 0 and 3", Person.class )
.getResultList();

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.createdOn between '1999-01-01' and '2001-01-02'", Person.class )
.getResultList();

List<Call> calls = entityManager.createQuery(
    "select c " +
    "from Call c " +
    "where c.duration between 5 and 20", Call.class )
.getResultList();

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.name between 'H' and 'M'", Person.class )
.getResultList();

In predicate

IN predicates performs a check that a particular value is in a list of values. Its syntax is:

in_expression ::=
    single_valued_expression [NOT] IN single_valued_list

single_valued_list ::=
    constructor_expression | (subquery) | collection_valued_input_parameter

constructor_expression ::= (expression[, expression]*)

The types of the single_valued_expression and the individual values in the single_valued_list must be consistent.

JPQL limits the valid types here to string, numeric, date, time, timestamp, and enum types, and , in JPQL, single_valued_expression can only refer to:

"state fields", which is its term for simple attributes. Specifically this excludes association and component/embedded attributes.
entity type expressions. See Entity type

In HQL, single_valued_expression can refer to a far more broad set of expression types. Single-valued association are allowed, and so are component/embedded attributes, although that feature depends on the level of support for tuple or "row value constructor syntax" in the underlying database. Additionally, HQL does not limit the value type in any way, though application developers should be aware that different types may incur limited support based on the underlying database vendor. This is largely the reason for the JPQL limitations.

The list of values can come from a number of different sources. In the constructor_expression and collection_valued_input_parameter, the list of values must not be empty; it must contain at least one value.

Example 59. In predicate examples

List<Payment> payments = entityManager.createQuery(
    "select p " +
    "from Payment p " +
    "where type(p) in ( CreditCardPayment, WireTransferPayment )", Payment.class )
.getResultList();

List<Phone> phones = entityManager.createQuery(
    "select p " +
    "from Phone p " +
    "where type in ( 'MOBILE', 'LAND_LINE' )", Phone.class )
.getResultList();

List<Phone> phones = entityManager.createQuery(
    "select p " +
    "from Phone p " +
    "where type in :types", Phone.class )
.setParameter( "types", Arrays.asList( PhoneType.MOBILE, PhoneType.LAND_LINE ) )
.getResultList();

List<Phone> phones = entityManager.createQuery(
    "select distinct p " +
    "from Phone p " +
    "where p.person.id in (" +
    "    select py.person.id " +
    "    from Payment py" +
    "    where py.completed = true and py.amount > 50 " +
    ")", Phone.class )
.getResultList();

// Not JPQL compliant!
List<Phone> phones = entityManager.createQuery(
    "select distinct p " +
    "from Phone p " +
    "where p.person in (" +
    "    select py.person " +
    "    from Payment py" +
    "    where py.completed = true and py.amount > 50 " +
    ")", Phone.class )
.getResultList();

// Not JPQL compliant!
List<Payment> payments = entityManager.createQuery(
    "select distinct p " +
    "from Payment p " +
    "where ( p.amount, p.completed ) in (" +
    "    (50, true )," +
    "    (100, true )," +
    "    (5, false )" +
    ")", Payment.class )
.getResultList();

Exists predicate

Exists expressions test the existence of results from a subquery. The affirmative form returns true if the subquery result contains values. The negated form returns true if the subquery result is empty.

Empty collection predicate

The IS [NOT] EMPTY expression applies to collection-valued path expressions. It checks whether the particular collection has any associated values.

Example 60. Empty collection expression examples

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.phones is empty", Person.class )
.getResultList();

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where p.phones is not empty", Person.class )
.getResultList();

Member-of collection predicate

The [NOT] MEMBER [OF] expression applies to collection-valued path expressions. It checks whether a value is a member of the specified collection.

Example 61. Member-of collection expression examples

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where 'Home address' member of p.addresses", Person.class )
.getResultList();

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "where 'Home address' not member of p.addresses", Person.class )
.getResultList();

NOT predicate operator

The NOT operator is used to negate the predicate that follows it. If that following predicate is true, the NOT resolves to false.

If the predicate is true, NOT resolves to false. If the predicate is unknown (e.g. NULL), the NOT resolves to unknown as well.

AND predicate operator

The AND operator is used to combine 2 predicate expressions. The result of the AND expression is true if and only if both predicates resolve to true. If either predicates resolves to unknown, the AND expression resolves to unknown as well. Otherwise, the result is false.

OR predicate operator

The OR operator is used to combine 2 predicate expressions. The result of the OR expression is true if one predicate resolves to true. If both predicates resolve to unknown, the OR expression resolves to unknown. Otherwise, the result is false.

The `WHERE` clause

The WHERE clause of a query is made up of predicates which assert whether values in each potential row match the current filtering criteria. Thus, the where clause restricts the results returned from a select query and limits the scope of update and delete queries.

Group by

The GROUP BY clause allows building aggregated results for various value groups. As an example, consider the following queries:

Example 62. Group by example

Long totalDuration = entityManager.createQuery(
    "select sum( c.duration ) " +
    "from Call c ", Long.class )
.getSingleResult();

List<Object[]> personTotalCallDurations = entityManager.createQuery(
    "select p.name, sum( c.duration ) " +
    "from Call c " +
    "join c.phone ph " +
    "join ph.person p " +
    "group by p.name", Object[].class )
.getResultList();

//It's even possible to group by entities!
List<Object[]> personTotalCallDurations = entityManager.createQuery(
    "select p, sum( c.duration ) " +
    "from Call c " +
    "join c.phone ph " +
    "join ph.person p " +
    "group by p", Object[].class )
.getResultList();

The first query retrieves the complete total of all orders. The second retrieves the total for each customer, grouped by each customer.

In a grouped query, the where clause applies to the non-aggregated values (essentially it determines whether rows will make it into the aggregation). The HAVING clause also restricts results, but it operates on the aggregated values. In the Group by example, 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:

Example 63. Having example

List<Object[]> personTotalCallDurations = entityManager.createQuery(
    "select p.name, sum( c.duration ) " +
    "from Call c " +
    "join c.phone ph " +
    "join ph.person p " +
    "group by p.name " +
    "having sum( c.duration ) > 1000", Object[].class )
.getResultList();

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.

Order by

The results of the query can also be ordered. The ORDER BY clause is used to specify the selected values to be used to order the result. The types of expressions considered valid as part of the ORDER BY clause include:

state fields
component/embeddable attributes
scalar expressions such as arithmetic operations, functions, etc.
identification variable declared in the select clause for any of the previous expression types

Additionally, JPQL says that all values referenced in the ORDER BY clause must be named in the SELECT clause. HQL does not mandate that restriction, but applications desiring database portability should be aware that not all databases support referencing values in the ORDER BY clause that are not referenced in the select clause.

Individual expressions in the order-by can be qualified with either ASC (ascending) or DESC (descending) to indicated the desired ordering direction. Null values can be placed in front or at the end of the sorted set using NULLS FIRST or NULLS LAST clause respectively.

Example 64. Order by example

List<Person> persons = entityManager.createQuery(
    "select p " +
    "from Person p " +
    "order by p.name", Person.class )
.getResultList();

List<Object[]> personTotalCallDurations = entityManager.createQuery(
    "select p.name, sum( c.duration ) as total " +
    "from Call c " +
    "join c.phone ph " +
    "join ph.person p " +
    "group by p.name " +
    "order by total", Object[].class )
.getResultList();

Read-only entities

As explained in entity immutability section, fetching entities in read-only mode is much more efficient than fetching read-write entities. Even if the entities are mutable, you can still fetch them in read-only mode, and benefit from reducing the memory footprint and speeding up the flushing process.

Read-only entities are skipped by the dirty checking mechanism as illustrated by the following example:

Example 65. Read-only entities query example

List<Call> calls = entityManager.createQuery(
    "select c " +
    "from Call c " +
    "join c.phone p " +
    "where p.number = :phoneNumber ", Call.class )
.setParameter( "phoneNumber", "123-456-7890" )
.setHint( "org.hibernate.readOnly", true )
.getResultList();

calls.forEach( c -> c.setDuration( 0 ) );

SELECT c.id AS id1_5_ ,
       c.duration AS duration2_5_ ,
       c.phone_id AS phone_id4_5_ ,
       c.call_timestamp AS call_tim3_5_
FROM   phone_call c
INNER JOIN phone p ON c.phone_id = p.id
WHERE   p.phone_number = '123-456-7890'

As you can see, there is no SQL UPDATE being executed.

You can also pass the read-only hint to named queries using the JPA @QueryHint annotation.

Example 66. Fetching read-only entities using a named query and the read-only hint

@NamedQuery(
    name = "get_read_only_person_by_name",
    query = "select p from Person p where name = :name",
    hints = {
        @QueryHint(
            name = "org.hibernate.readOnly",
            value = "true"
        )
    }
)

The Hibernate native API offers a Query#setReadOnly method, as an alternative to using a JPA query hint:

Example 67. Read-only entities native query example

List<Call> calls = entityManager.createQuery(
    "select c " +
    "from Call c " +
    "join c.phone p " +
    "where p.number = :phoneNumber ", Call.class )
.setParameter( "phoneNumber", "123-456-7890" )
.unwrap( org.hibernate.query.Query.class )
.setReadOnly( true )
.getResultList();