JPA - Jakarta Persistence Query Language - JPQL

JPQL

The Jakarta Persistence Query Language (JPQL; formerly Java Persistence Query Language) is a platform-independent object-oriented query language defined as part of the Jakarta Persistence (JPA; formerly Java Persistence API) specification – Wikipedia

JPQL is inspired by SQL, and its queries resemble SQL queries in syntax, but operate against JPA entity objects stored in a relational database rather than directly with database tables.

JPQL only supports a subset of SQL standard. If you want to make complex queries, we have to use Native SQL Query.

JPQL is a powerful query language that allows you to define database queries based on your entity model. Its structure and syntax are very similar to SQL. But there is an important difference that I want to point out before I walk you through the different parts of a JPQL query.

JPQL uses the entity object model instead of database tables to define a query. That makes it very comfortable for us Java developers, but you have to keep in mind that the database still uses SQL. Hibernate, or any other JPA implementation, has to transform the JPQL query into SQL. It is, therefore, a good practice to activate the logging of the SQL statements during development to check the generated SQL statements.

You need to note that:

Spring Data JPA don’t adjust the query to database’s specific SQL dialect, so ensure that the provided statement is supported by RDBMS.
Spring Data JPA does not currently support dynamic sorting for native queries, because it would have to manipulate the actual query declared, which it cannot do reliably for native SQL.

What you can do with JPQL

Define the attributes you want to select
Join related entities in the FROM clause
Join unrelated entities in the FROM clause
Use conditional expressions in the WHERE and HAVING clause
Use subqueries in the WHERE and HAVING clause
Group your query results with GROUP BY and apply additional conditional expressions with HAVING
Order the query results with ORDER BY
Limit the number of records in your result set
Use standard functions
Use non-standard and database specific functions
Call stored procedures

What you can’t do with JPQL

Use subqueries outside of WHERE and HAVING clauses
Perform set operations
Use database specific hints
Write recursive queries
Use window functions

Selection – The FROM clause

The model

Joining multiple entities

Inner Joins

SELECT a, b FROM Author a JOIN a.books b

The definition of the Author entity provides all information Hibernate needs to join it to the Book entity, and you don’t have to provide an additional ON statement.

JOINs of unrelated entities are not supported by the JPA specification, but you can use a theta join which creates a cartesian product and restricts it in the WHERE clause to the records with matching foreign and primary keys. I use this approach in the following example to join the Book with the Publisher entities.

SELECT b, p FROM Book b, Publisher p WHERE b.fk_publisher = p.id

More details about this approach: You can read more about this workaround and Hibernates proprietary support for JOINs of unrelated entities in: https://thorben-janssen.com/how-to-join-unrelated-entities/

Left Outer Joins

INNER JOINs, like the one in the previous example, require that the selected entities fulfill the join condition. The query returned only the Author entities with associated Book entities but not the ones for which the database doesn’t contain a Book entity. If you want to include the authors without published books, you have to use a LEFT JOIN, like in the following code snippet.

SELECT a, b FROM Author a LEFT JOIN a.books b

Additional Join Conditions

The previous examples use the defined association to join the entities. But sometimes you only want to join the related entities which fulfill additional conditions. Since JPA 2.1, you can do this for INNER JOINs, and LEFT JOINs with an additional ON statement.

SELECT a, p FROM Author a JOIN a.publications p ON p.publishingDate > ?1

Path expressions or implicit joins

Path expressions create implicit joins and are one of the benefits provided by the entity model. You can use the ‘.’ operator to navigate to related entities as I do in the following code snippet.

SELECT b FROM Book b WHERE b.publisher.name LIKE ‘%es%

As you can see, I use the ‘.’ operator to navigate via the publisher attribute of the Book entity b to the related Publisher entities. That creates an implicit join between the Book and Publisher entity which will be translated into an additional join statement in the SQL query.

Polymorphism and Downcasting

Polymorphism

When you choose an inheritance strategy that supports polymorphic queries, your query selects all instances of the specified class and its subclasses. With the model in the example for this blog post, you can, for example, select all Publication entities, which are either Book or BlogPost entities.

SELECT p FROM Publication p

Or you can select a specific subtype of a Publication, like a BlogPost.

SELECT b FROM BlogPost b

Downcasting

Since JPA 2.1, you can also use the TREAT operator for downcasting in FROM and WHERE clauses. I use that in the following code snippet to select all Author entities with their related Book entities. As you can see in the model, the publications association defines an association between the Author and the Publication entity. So without the TREAT operator, the query would return all Author entities with their associated Book or BlogPost entities.

SELECT a, p FROM Author a JOIN treat (a.publications AS Book) p

ATTENTION: There are several issues with the implementation of TREAT in Hibernate 5.1. Based on my experiments, Hibernate 5.1 handles TREAT only, if it is written in lower case and used in the WHERE clause. The treat operator in this example is ignored by Hibernate 5.1.0.Final.

TODO https://thorben-janssen.com/hibernate-tips-downcast-entities-jpql-queries/

Restriction – The WHERE clause

JPQL supports a set of basic operators to define comparison expressions. Most of them are identical to the comparison operators supported by SQL, and you can combine them with the logical operators AND, OR and NOT into more complex expressions.

Operators for single-valued expressions:

Equal: author.id = 10
Not equal: author.id <> 10
Greater than: author.id > 10
Greater or equal then: author.id => 10
Smaller than: author.id < 10
Smaller or equal then: author.id <= 10
Between: author.id BETWEEN 5 and 10
Like: author.firstName LIKE ‘%and%’ The % character represents any character sequence. This example restricts the query result to all Authors with a firstName that contains the String ‘and’, like Alexander or Sandra. You can use an _ instead of % as a single character wildcard. You can also negate the operator with NOT to exclude all Authors with a matching firstName.
Is null: author.firstName IS NULL You can negate the operator with NOT to restrict the query result to all Authors who’s firstName IS NOT NULL.
In: author.firstName IN (‘John’, ‘Jane’) Restricts the query result to all Authors with the first name John or Jane.

Operators for collection expressions:

Is empty: author.books IS EMPTY Restricts the query result to all Authors that don’t have any associated Book entities. You can negate the operator (IS NOT EMPTY) to restrict the query result to all Authors with associated Book entities.
Size: size(author.books) > 2 Restricts the query result to all Authors who are associated with more than 2 Book entities.
Member of: :myBook member of author.books Restricts the query result to all Authors who are associated with a specific Book entity.

You can use one or more of the operators to restrict your query result. The following query returns all Author entities with a firstName attribute that contains the String “and” and an id attribute greater or equal 20 and who have written at least 5 books.

SELECT a FROM Author a WHERE a.firstName like ‘%and%’ and a.id >= 20 and size(author.books) >= 5

Projection – The SELECT clause

The projection of your query defines which information you want to retrieve from the database. This part of the query is very different from SQL. In SQL, you specify a set of database columns and functions as your projection. You can do the same in JPQL by selecting a set of entity attributes or functions as scalar values, but you can also define entities or constructor calls as your projection. Hibernate, or any other JPA implementation, maps this information to a set of database columns and function calls to define the projection of the generated SQL statement.

Let’s have a look at the different options. You can use any combination of them in your queries.

Entities

Entities are the most common projection in JPQL queries. Hibernate uses the mapping information of the selected entities to determine the database columns it has to retrieve from the database. It then maps each row of the result set to the selected entities.

SELECT a FROM Author a

It’s comfortable to use entities as your projection. But you should always keep in mind that all entities are managed by the persistence context which creates overhead for read-only use cases. In these situations, it’s better to use scalar values or a constructor reference as a projection.

Scalar values

Scalar value projections are very similar to the projections you know from SQL. Instead of database columns, you select one or more entity attributes or the return value of a function call with your query.

SELECT a.firstName, a.lastName FROM Author a

Constructor references

Constructor references are a good projection for read-only use cases. They’re more comfortable to use than scalar value projections and avoid the overhead of managed entities.

JPQL allows you to define a constructor call in the SELECT clause. You can see an example of it in the following code snippet. You just need to provide the fully qualified class name and specify the constructor parameters of an existing constructor. Similar to the entity projection, Hibernate generates an SQL query which returns the required database columns and uses the constructor reference to instantiate a new object for each record in the result set.

SELECT new org.thoughts.on.java.model.AuthorValue(a.id, a.firstName, a.lastName) FROM Author a

Distinct query results

SELECT DISTINCT a.lastName FROM Author a

Functions

Functions are another powerful feature of JPQL that you probably know from SQL. It allows you to perform basic operations in the WHERE and SELECT clause. You can use the following functions in your query:

upper(String s): transforms String s to upper case
lower(String s): transforms String s to lower case
current_date(): returns the current date of the database
current_time(): returns the current time of the database
current_timestamp(): returns a timestamp of the current date and time of the database
substring(String s, int offset, int length): returns a substring of the given String s
trim(String s): removes leading and trailing whitespaces from the given String s
length(String s): returns the length of the given String s
locate(String search, String s, int offset): returns the position of the String search in s. The search starts at the position offset
abs(Numeric n): returns the absolute value of the given number
sqrt(Numeric n): returns the square root of the given number
mod(Numeric dividend, Numeric divisor): returns the remainder of a division
treat(x as Type): downcasts x to the given Type
size(c): returns the size of a given Collection c
index(orderedCollection): returns the index of the given value in an ordered Collection

Grouping – The GROUP BY and HAVING clause

When you use aggregate functions, like count(), in your SELECT clause, you need to reference all entity attributes that are not part of the function in the GROUP BY clause.

The following code snippet shows an example that uses the aggregate function count() to count how often each last name occurs in the Author table.

SELECT a.lastName, COUNT(a) FROM Author a GROUP BY a.lastName

The HAVING clause is similar to the WHERE clause and allows you to define additional restrictions for your query. The main difference is that the restrictions specified in a HAVING clause are applied to a group and not to a row.

I use it in the following example to select all last names that start with a ‘B’ and count how often each of them occurs in the Author table.

SELECT a.lastName, COUNT(a) AS cnt FROM Author a GROUP BY a.lastName HAVING a.lastName LIKE ‘B%’

Ordering – The ORDER BY clause

You can define the order in which the database shall return your query results with an ORDER BY clause. Its definition in JPQL is similar to SQL. You can provide one or more entity attributes to the ORDER BY clause and specify an ascending (ASC) or a descending (DESC) order.

The following query selects all Author entities from the database in the ascending order of their lastName attributes. All Authors with the same lastName are returned in descending order of their firstName.

SELECT a FROM Author a ORDER BY a.lastName ASC, a.firstName DESC

Subselects

A subselect is a query embedded into another query. It’s a powerful feature you probably know from SQL. Unfortunately, JPQL supports it only in the WHERE clause and not in the SELECT or FROM clause.

Subqueries can return one or multiple records and can use the aliases defined in the outer query. The following example shows a query that uses a subquery to count all Books written by an Author and returns only the Authors who’ve written more than 1 Book.

SELECT a FROM Author a WHERE (SELECT count(b) FROM Book b WHERE a MEMBER OF b.authors ) > 1

References

https://thorben-janssen.com/jpql

Links to this note

JPA - Jakarta Persistence API