HQL¶

Hibernate allows you to manage entities without having to [write] (basic_entity_mgmt.md) queries. However, more complicated operations require us to write queries manually. Depending on the database implementation (eg MySQL or OracleDB) the syntax of such a query may be slightly different. Hibernate introduces an additional layer of abstraction to overcome these differences, and is Hibernate Query Language, the query language used for Hibernate.

HQL is very similar to SQL, but allows the use of polymorphism, association and other features of the object model in your queries. HQL queries are automatically translated into SQL and the query results are returned as references to the appropriate objects. In HQL we can find keywords that are also in SQL, e.g.:

• clauses: SELECT, GROUP BY, WHERE, ORDER BY, FROM
• aggregate functions: AVG, SUM, MIN, MAX, COUNT

HQL query execution¶

We can execute HQL queries using theEntityManager object and the createQuery method. This method returns a Query object which allows, for example:

• executing the query using the executeUpdate method
• retrieving the returned result using the getFirstResult or getResultList methods
• setting the parameters of the setParameter query

NOTE: If you want to retrieve the query result, we can pass an additional argument to the createQuery method, which is the return type.

FROM¶

The FROM clause is used to indicate the class to be used in the process of finding its instance. It is a kind of shorthand for the SQL SELECT * FROM table query. It also allows you to give an entity an alias (similar to SQL).

The syntax is as below:

FROM entityName [[AS] alias]

NOTE: entityName in the example above it is the class name or the name indicated in the @Entity annotation. If the table name is specified in the @Table annotation, we still use the class name in HQL queries.

NOTE: HQL language is case insensitive.

In the examples in this section, we assume that we have access to a properly initialized EntityManager object. Moreover, in some examples we will base on the following definition of an entity:

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity(name = "cars")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Car {

  @Id
  @GeneratedValue
  private Long id;

  private String modelName;

  private String producer;

  private Double engineVolume;

  public Car(String modelName, String producer, Double engineVolume) {
    this.modelName = modelName;
    this.producer = producer;
    this.engineVolume = engineVolume;
  }
}

Example¶

To retrieve all objects of type Car, we make a query:

List<Car> cars = entityManager.createQuery("FROM cars", Car.class).getResultList();

The next example shows the same query using an alias.

List<Car> cars = entityManager.createQuery("FROM cars c, Car.class).getResultList();

SELECT¶

The SELECT clause is similar to FROM, except that it allows you to specify which of the object's attributes should be returned by the query, i.e.:

SELECT classAfieldName, classBfieldName FROM entityName [[AS] alias]

The following example gets a list of at most 3 producers:

List<String> producers = entityManager.createQuery("SELECT c.producer FROM cars c", String.class)
        .setMaxResults(3)
        .getResultList();

WHERE¶

The WHERE clause allows you to specify conditions that should be met by the elements of the query result. Its syntax, meaning and usable expressions are practically identical to those known from the SQL language, however, instead of using column names, we use class field names:

FROM entityName [[AS] alias] WHERE entityName[alias]=indicated_value

To retrieve all objects of type Car whose modelmodelName has the value Aygo, we execute the query:

List<Car> cars = entityManager.createQuery("FROM cars c WHERE c.modelName = 'Aygo'", Car.class).getResultList();

The following example gets a list of manufacturers that offer a car named Aygo:

List<String> producers = entityManager.createQuery("SELECT c.producer FROM cars c WHERE c.modelName = 'Aygo'", String.class)
        .getResultList();

ORDER BY¶

ORDER BY allows you to order the returned results under any attribute of the returned object. As with native SQL, place ORDER BY at the end of the query. The results can be sorted in ascending order using the ASC keyword or in descending order using DESC.

List<Car> cars = entityManager.createQuery("SELECT c FROM cars c ORDER BY c.engineVolume DESC", Car.class).getResultList();

GROUP BY¶

The GROUP BY clause works exactly like in native SQL. A query using them returns results grouped according to any attribute of the object, e.g .:

List<Object> avgEngineVolumes = entityManager.createQuery("SELECT AVG(c.engineVolume) FROM cars c GROUP BY c.modelName", Object.class).getResultList();

JOIN¶

HQL allows tables to be joined using the JOIN keyword.

Some possible types of joins are:

• LEFT JOIN
• INNER JOIN

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;
import java.util.List;

@Entity(name = "cars")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Car {

  @Id
  @GeneratedValue
  private Long id;

  private String modelName;

  private String producer;

  private Double engineVolume;

  public Car(String modelName, String producer, Double engineVolume) {
    this.modelName = modelName;
    this.producer = producer;
    this.engineVolume = engineVolume;
  }

  @OneToMany
  @JoinColumn(name = "car_id")
  private List<Wheel> wheels;
}

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity(name = "wheels")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Wheel {

  @Id
  @GeneratedValue
  private Long id;

  private Integer diameter;

  private String producer;

  private Long ageInDays;
}

Car car = entityManager.createQuery("SELECT c FROM cars c left join c.wheels w WHERE w.diameter > 15", Car.class).getSingleResult();

In addition, the with clause allows you to specify a condition for the joined table, e.g .:

List<Car> cars = entityManager.createQuery("SELECT c FROM cars c left join c.wheels as w with w.producer = 'Pirelli' WHERE w.diameter > 15", Car.class).getResultList();

NOTE: HQL also allows you to use the fetch keyword, which will fetch objects that can be fetched 'lazy".

Prepared statement¶

HQL, just like JDBC, allows to perform parametrized queries. The value of each parameter should have any unique name preceded by the : character.

List<Car> cars = entityManager.createQuery("SELECT c FROM cars c WHERE c.modelName = :modelName AND c.engineVolume = :engineVolume", Car.class)
        .setParameter("modelName", "Corolla")
        .setParameter("engineVolume", 2.0)
        .getResultList();

Subqueries¶

Hibernate allows to execute subqueries:

FROM Product AS p
WHERE p.price > (
    SELECT AVG(product.price) FROM product
)

Aggregate functions¶

HQL, like SQL, allows you to use aggregate functions, e.g.:

SELECT AVG(p.price), SUM(p.quantity), MAX(p.quantity), COUNT(p) FROM Product p