Custom Queries with @Query and JPQL

1. When Derived Queries Aren't Enough: Introducing @Query and JPQL

Derived queries are excellent for their simplicity and "convention over configuration" approach. But they have their limits:

• Complex Logic: Logic involving multiple joins, subqueries, or complex calculations is difficult or impossible to express in a method name.
• Readability: A method like findByCategory_NameAndIsAvailableTrueAndPriceBetweenOrderByPriceAsc is valid, but it's a mouthful and hard to maintain.
• Flexibility: You are limited to the keywords provided by Spring Data JPA.

The @Query annotation is the solution. It allows you to define a query as a String directly on your repository method. This query is typically written in JPQL (Java Persistence Query Language).

Think of JPQL as a sibling to SQL. The key difference is that JPQL operates on your Java entities and their fields, not on database tables and columns. This makes your queries object-oriented and, importantly, database-agnostic. The same JPQL query will work whether you're using MySQL, PostgreSQL, or another database.

2. Writing Your First JPQL Query

Using @Query is straightforward. You annotate a method in your repository interface and provide the JPQL query as a string.

Let's say we have our ProductRepository. A derived query to find a product by its exact name would be findByName(String name). Here's how you'd write the same thing using @Query:

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
// ... other imports

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query("SELECT p FROM Product p WHERE p.name = :name")
    Optional<Product> findProductByName(@Param("name") String name);

}

Let's break this down:

• @Query(...): The annotation that holds our query.
• SELECT p FROM Product p: This is JPQL. Notice we are selecting from Product, the entity class, and assigning it an alias p. We then refer to its fields like p.name.
• WHERE p.name = :name: This is the condition. :name is a named parameter.
• @Param("name") String name: The @Param annotation links the method parameter name to the named parameter :name in the query string. This is the recommended way to pass parameters as it's explicit and not dependent on parameter order.

The image below shows more examples of this syntax in action.

While you can also use indexed parameters (e.g., ?1, ?2), named parameters are strongly preferred because they make your queries more readable and less prone to errors if you change the order of method arguments.

@Query("SELECT p FROM Product p WHERE LOWER(p.name) LIKE LOWER(CONCAT('%', :searchTerm, '%'))")
List<Product> findByNameSearch(@Param("searchTerm") String searchTerm);

3. Modifying Data with @Modifying

The @Query annotation isn't just for reading data. You can also perform UPDATE or DELETE operations. Since these queries modify the state of the database, you must add another annotation: @Modifying.

This is crucial because it tells Spring Data JPA that the query is not a SELECT and requires a different execution path. Modifying queries typically return an int or void. An int return type will indicate the number of rows affected by the query.

Here’s an example of a bulk update to set a new price for all products in a given category:

@Modifying
@Query("UPDATE Product p SET p.price = :newPrice WHERE p.category.id = :categoryId")
int updatePriceForCategory(@Param("newPrice") BigDecimal newPrice, @Param("categoryId") Long categoryId);

Notice two things:

1. The presence of @Modifying above @Query.
2. The JPQL uses UPDATE Product... syntax.

4. Advanced Control: Sorting and Pagination

Just like with derived queries, you can easily apply sorting and pagination to your @Query methods.

• Pagination: To enable pagination, simply add a Pageable parameter to your method and make the return type Page<EntityType>. Spring Data JPA will automatically add the necessary limit and offset clauses to the generated SQL.

  @Query("SELECT p FROM Product p WHERE p.category.name = :categoryName")
  Page<Product> findByCategory(@Param("categoryName") String categoryName, Pageable pageable);
  

• Sorting: You have two options for sorting:

  1. Static Sorting: Hard-code an ORDER BY clause directly in your JPQL string.
     @Query("SELECT p FROM Product p ORDER BY p.price DESC")
  2. Dynamic Sorting: Add a Sort parameter to your method. Spring Data JPA will append the ORDER BY clause for you based on the Sort object you provide when you call the method. This is more flexible.

  @Query("SELECT p FROM Product p WHERE p.isAvailable = true")
  List<Product> findAvailableProducts(Sort sort);
  
  // You could then call it like this:
  // productRepository.findAvailableProducts(Sort.by("price").ascending());
  

Conclusion

You now have the most powerful tool in the Spring Data JPA toolbox at your disposal. While derived queries are perfect for simple cases, the @Query annotation gives you the ultimate control to handle any data access requirement your application might have.

Key Takeaways:

• Use @Query with JPQL for complex or highly specific database queries that are difficult to express with derived query method names.
• JPQL is an object-oriented query language that works with your entities and fields, making it database-independent.
• Always use named parameters (:paramName) with the @Param annotation for clarity and safety.
• For UPDATE or DELETE operations, you must add the @Modifying annotation.
• You can still leverage dynamic sorting (Sort) and pagination (Pageable) by adding them as parameters to your @Query-annotated methods.

What's Next?

So far, we've focused on how to create, read, update, and delete data. But often, we need to perform actions automatically whenever an entity is saved or updated—for example, setting createdAt and updatedAt timestamps. In our next lesson, we will dive into configuring entity lifecycle callbacks using @PrePersist and @PreUpdate to automate this kind of behavior.