Table of contents

• Introduction
• Query basics
• Query simplification
• Projections
• JOIN FETCH
• Modifying queries
• Functions
• Conclusion

Introduction

JPQL is the query language from JPA. It comes with many features from basic to advanced. But even in the basic features, some of them are not well-known or not used often.

In this article, I will describe a selection of simple but useful JPQL features to simplify your applications or improve their performance.

Query basics

Let's start with a quick reminder of how to execute a JPQL query.

To programmatically execute a query from an EntityManager instance (usually injected by your application framework):

List<Car> getCarsByColor(String color) {
    String jpql = "SELECT c FROM Car c WHERE c.color = :color";
    TypedQuery<Car> query = entityManager.createQuery(jpql, Car.class);
    query.setParameter("color", color);
    return query.getResultList();
}

The same method declared in a Spring Data JPA repository:

@Repository
public interface CarRepository extends JpaRepository<Car, Long> {
    @Query("SELECT c FROM Car c WHERE c.color = :color")
    List<Car> findByColor(@Param("color") String color);
}

The @Query annotation here is actually not needed, since Spring Data JPA can infer the JPQL from the method name in that case, but it serves as an example.

Alternative syntax, if you prefer indexed parameters instead of named parameters:

@Query("SELECT c FROM Car c WHERE c.color = ?1")
List<Car> findByColor(String color);

JPQL keywords are case-insensitive, you can use select instead of SELECT for example.

Query simplification

There are ways to simplify this kind of query:

@Query("SELECT c FROM Car c WHERE c.color = :color")
List<Car> findByColor(@Param("color") String color);

Once again, in this specific case, the @Query annotation is not needed, but it serves as an example.

First, the JPQL can be shortened. We can use FROM Car c ... instead of SELECT c FROM Car c .... Only the latter is standard JPQL, but the former is well-supported by Hibernate.

Then, when the named parameter matches the parameter name, the @Param annotation can be removed. This requires the -parameters Java compiler flag to be enabled. This is the case by default for the Maven compiler plugin configuration when using the Spring Boot starter parent POM. If you are not using that parent POM, you can explicitly configure the Maven compiler plugin to enable the -parameters flag:

<plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-compiler-plugin</artifactId>
    <version>3.13.0</version>
    <configuration>
        <parameters>true</parameters>
    </configuration>
</plugin>

The simplified query is:

@Query("FROM Car c WHERE c.color = :color")
List<Car> findByColor(String color);

Projections

Queries usually return entities. This way, when we modify the entities, the changes are automatically synchronized with the database, thanks to the entity state tracking of JPA and proper transaction management.

But sometimes we de not need that, we simply need to read some data from the database. We may also want to prevent loading some large attributes or some eager relations. In these situations, we can use projections.

For example to load a single attribute:

@Query("SELECT c.color FROM Car c WHERE c.id = :id")
String findColorByCarId(long id);

Or to load DTOs instead of entities:

@Query("SELECT new com.adeliosys.mockup.CarDto(c.name, c.color) FROM Car c")
List<CarDto> findCarDescriptions();

This works fine with regular Java beans but also with Java records.

JOIN FETCH

JOIN FETCH and LEFT JOIN FETCH are a powerful yet easy to use JPQL feature to improve the application performances. It enables eagerly loading multiple related entities in a single query. I described it in detail in Faster JPA relations loading.

I will share one example here. Consider this domain model with several entities and @ManyToOne or @OneToOne relations:

To load all cars, their maker, their owner and the owners address, in a single query instead of 1+N+M+P, you can use:

@Query("FROM Car c JOIN FETCH c.maker JOIN FETCH c.owner o JOIN FETCH o.address WHERE c.color = :color")
List<Car> findWithRelationsByColor(String color);

This can greatly improve the application performances.

See linked article for additional information.

Modifying queries

A common backend development pattern is to load entities from the database, modify them in Java, then update the database. JPA and Hibernate can often detect the entity changes and automatically generate and execute the corresponding SQL updates.

While familiar and convenient, this process may be slow due to the number of executed queries: 1 read query (to load the entities) then N write queries (to update the entities).

Instead, when possible, use a JPQL modifying query to directly update the entities in a single query. For example with Spring Data JPA:

@Modifying
@Query("UPDATE Employee e SET e.salary = e.salary * :factor")
int updateSalary(double factor);

The return value is the number of modified rows. If that number is not needed, you can use a void return type.

A modifying query can also perform a DELETE statement.

Functions

JPQL supports several functions. Here is a summary of selected ones, with examples:

Many functions work on strings (UPPER, LOWER, CONCAT, SUBSTRING, TRIM, LENGTH, LOCATE). COALESCE is similar to Optional.orElse(...) from Java. ABS and SQRT can compute respectively one and two dimension distances.

These functions can be useful in queries, to directly return processed data, instead of doing so in Java.

They are also valuable in modifying queries, to support more complex business rules.

Conclusion

In this article I explained some JPQL feature that are quite simple to use but can bring significant benefits to the applications. I hope you enjoyed this selection.