Crafting Custom Queries with @Query

When to Use @Query Over Derived Queries

In the previous lesson, we saw that derived queries are excellent for readability when the logic is simple (e.g., findByNameAndCategory). But you should switch to @Query when:

• The query logic is too complex for a method name (e.g., involves multiple OR conditions, subqueries, or intricate JOINs).
• You need to fine-tune performance, for instance, by using JOIN FETCH to solve the N+1 problem (we'll cover this!).
• You need to use database-specific features not supported by the database-agnostic JPQL.
• The method name would become excessively long and harm code clarity.

Defining Custom JPQL Queries

The most common way to use @Query is with JPQL. JPQL is similar to SQL but operates on your Java entities and their fields, not on database tables and columns. This maintains a level of abstraction and database independence.

The article below provides a comprehensive guide. We'll start by focusing on defining a basic query and how to handle parameters.

Key Takeaways on Parameter Binding

As you read, you will have noticed two ways to bind method arguments to your query:

1. Positional Parameters (?1, ?2, ...): These are simple to write, as they map to method arguments by their position (1-indexed).

   @Query("SELECT p FROM Product p WHERE p.category = ?1 AND p.price < ?2")
   List<Product> findByCategoryAndPriceLessThan(String category, BigDecimal maxPrice);
   

   However, they are brittle. If you reorder the method parameters, you will introduce a bug that the compiler won't catch.

2. Named Parameters (:name): These are referenced by name. You link the method argument to the named parameter using the @Param("name") annotation.

   @Query("SELECT p FROM Product p WHERE p.category = :category AND p.price < :price")
   List<Product> findByCategoryAndPriceLessThan(@Param("category") String category, @Param("price") BigDecimal price);
   

   This is the highly recommended approach. It's more verbose but makes the code far more readable and robust against refactoring. Your 15 years of software engineering experience will surely attest to the value of maintainability, and this is a clear case where verbosity wins.

Defining Native SQL Queries

Sometimes, JPQL isn't enough. You might need to use a specific feature of your database (like PostgreSQL's full-text search or Oracle's hierarchical queries) or write a query so complex that it's easier to express in raw SQL. For this, you can use a native query.

When you use a native query, you lose database portability, but you gain the full power of your chosen database.

Let's return to the article to see how this is done.

Here's an example. Assuming a products table with a stock_level column:

@Query(value = "SELECT * FROM products WHERE stock_level < :minStock", nativeQuery = true)
List<Product> findLowStockProducts(@Param("minStock") int minStock);

Modifying Data with @Modifying

@Query isn't limited to SELECT statements. You can also use it for UPDATE, DELETE, or INSERT operations, which is particularly efficient for bulk operations.

When you define a query that modifies the state of the database, you must add the @Modifying annotation. This tells Spring Data two things:

1. The query doesn't return a ResultSet of entities but an int representing the number of affected rows.
2. The query needs to be executed within a proper transaction context that allows modification.

The article explains this clearly.

For example, to discontinue all products in a certain category:

@Modifying
@Query("UPDATE Product p SET p.discontinued = true WHERE p.category = :category")
int discontinueProductsByCategory(@Param("category") String category);

Advanced Use Case: Solving the N+1 Problem

Now for a topic that directly relates to your system design background: performance optimization. The "N+1 selects" problem is a common performance pitfall when using ORMs. It occurs when you fetch a list of entities (1 query) and then lazily access a related entity for each of them, triggering N additional queries.

@Query provides an elegant solution using the JOIN FETCH clause in JPQL. This tells the JPA provider to retrieve the related entities in the same initial query.

The following video clip gives a concise and practical demonstration of this exact scenario.

This is a powerful demonstration of why having full control via @Query is essential for building high-performance, scalable applications.

Other Capabilities

It's worth noting that the flexible features we saw with derived queries also work with @Query:

• Pagination and Sorting: You can add Pageable and Sort parameters to your @Query-annotated methods, and Spring Data will correctly apply pagination and ordering to your custom query.
• Projections: You can design your query to return DTOs or interface projections directly, fetching only the data you need and avoiding the overhead of loading full entities.

These are more advanced topics, but it's good to know that the functionality is there when you need it.

Conclusion

In this lesson, you've gained a powerful tool for your data access layer. While derived queries are convenient, @Query provides the control and flexibility required for real-world application development.

Key Takeaways:

• Use @Query for complex queries, performance tuning, or when derived method names become unreadable.
• Write database-agnostic queries with JPQL, which operates on entities and fields.
• Use native queries (nativeQuery = true) for database-specific features, at the cost of portability.
• Always prefer named parameters (:name) with @Param over positional parameters (?1) for readable and maintainable code.
• Use the @Modifying annotation in combination with @Query for UPDATE, DELETE, or INSERT operations.
• @Query is a key tool for performance optimizations, such as solving the N+1 problem with JOIN FETCH.

Now that we've covered the essentials of data persistence with Spring Data JPA, we're going to zoom back out and look at a fundamental concept of the Spring Framework itself.

In the next lesson, we will explore the Spring bean lifecycle. You'll learn how Spring creates, configures, and destroys the objects (beans) that form the backbone of your application. This is crucial for understanding how the Spring IoC container manages your application components.