JPQL is a query language defined by JPA to query entities and their relationships. It is similar to SQL but operates on entities and their attributes instead of tables and columns. JPQL can be used to write queries independent of the underlying database provider.

JPQL Statements

The following statements are available in JPQL:

  • SELECT: Retrieves data from the database.
  • UPDATE: Modifies data in the database.
  • DELETE: Removes data from the database.

SELECT Statement

The SELECT statement retrieves data from the database. The basic syntax is:

  SELECT [DISTINCT] select_expression
FROM identification_variable
[WHERE where_condition]
[GROUP BY group_by_expression]
[HAVING having_condition]
[ORDER BY order_by_expression]

Examples:

  SELECT c FROM Customer c  -- Selects all customers
SELECT c.name FROM Customer c  -- Selects the name of all customers
SELECT c FROM Customer c WHERE c.age > 18  -- Selects all customers older than 18
SELECT NEW ch.fhnw.edu.PersonDto(c.name, c.age) FROM Customer c  -- Selects a custom DTO
SELECT pk FROM PriceCategory pk  -- Polymorphic query

Parameters

Parameters can be used in JPQL queries to make them dynamic. There are two types of parameters:

  • Positional Parameters: Referenced by their position (e.g., ?1, ?2).
  • Named Parameters: Referenced by their name (e.g., :name, :age).
  TypedQuery<Movie> q1 = em.createQuery("SELECT m FROM Movie m WHERE m.title = ?1", Movie.class);
q1.setParameter(1, "No Time To Die");
// -----
TypedQuery<Movie> q2 = em.createQuery("SELECT m FROM Movie m WHERE m.title = :title", Movie.class);
query.setParameter("title", "No Time To Die");

Subqueries

Subqueries can be used in the WHERE or HAVING clause to filter results.

  SELECT c FROM Customer c WHERE c.age IN (SELECT MAX(c2.age) FROM Customer c2)

Named Queries

Named queries are defined in the entity class and can be referenced by their name.

  @Entity
@NamedQuery(name = Movie.FIND_ALL, query = "SELECT m FROM Movie m")
@NamedQuery(name = Movie.FIND_BY_TITLE, query = "SELECT m FROM Movie m WHERE m.title = :title")
public class Customer {
    public static final String FIND_ALL = "Movie.all";
    public static final String FIND_BY_TITLE = "Movie.byTitle";
    // ...
}

Bulk Update and Delete

Bulk update and delete operations can be performed using JPQL. The benefit of using JPQL for these operations is that the entities are not required to be loaded into the persistence context.

  Query q1 = em.createQuery("DELETE FROM Movie m WHERE m.id > 1000");
q1.executeUpdate();
// -----
Query q2 = em.createQuery("UPDATE User u SET u.name = 'TOO YOUNG' WHERE u.age < 18");
q2.executeUpdate();

Joins

Joins can be used to retrieve data from multiple entities.

Criteria API

The Criteria API is a type-safe way to build queries programmatically.

Example:

  public List<Movie> getByTitle(String title) {
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Movie> query = builder.createQuery(Movie.class);
    Root<Movie> root = query.from(Movie.class);
    // SELECT m FROM Movie m WHERE m.title = :title
    query.select(root).where(builder.equal(root.get("title"), title)); // still requires string for attribute
    return em.createQuery(query).getResultList();
}

To prevent usage of strings for attributes, the Metamodel API can be used.

Example:

  public List<Movie> getByTitle(String title) {
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Movie> query = builder.createQuery(Movie.class);
    Root<Movie> root = query.from(Movie.class);
    // SELECT m FROM Movie m WHERE m.title = :title
    query.select(root).where(builder.equal(root.get(Movie_.title), title)); // access to attribute via Metamodel
    return em.createQuery(query).getResultList();
}

The Metamodel classes can be generated by the JPA provider or they can be written manually. If they are written manually, they also need to be updated when the entity changes.

To automatically generate the metamodel, use the following code in build.gradle

  dependencies {
  ...
  annotationProcessor 'org.hibernate:hibernate-jpamodelgen-jakarta:5.6.15.Final'
}

tasks.withType(JavaCompile).configureEach {
  options.generatedSourceOutputDirectory = layout.buildDirectory.dir("generated").get().asFile
}

sourceSets {
  main {
    java {
      srcDirs += [layout.buildDirectory.dir("generated").get().asFile]
    }
  }
}