Custom Queries with @Query: When `findBy` Isn’t Enough

“Spring Data’s findBy methods are like ordering from a menu. @Query is like going into the kitchen and giving the chef your own custom recipe. 👨‍🍳📝”

Spring Data JPA’s derived query methods—like findByTitle or findByAuthorUsernameOrderByCreatedAtDesc—are magical. You define a method signature, and Spring writes the query for you. It’s like ordering “The Usual” at your favorite diner. But what happens when “The Usual” isn’t enough? What if you need a dish with a complex sauce, a special ingredient, or a cooking method that’s not on the menu? You need to write your own recipe.

In the world of Spring Data JPA, writing your own recipe is done with the @Query annotation. It’s your direct line to the kitchen, allowing you to craft precise, powerful, and sometimes complex queries that go far beyond what simple method names can achieve.

🚩 Prerequisites

1. Java 17+ and a Spring Boot project.
2. Spring Data JPA and a database driver (e.g., PostgreSQL) configured.
3. Basic understanding of JPA @Entity and JpaRepository.
4. Familiarity with writing simple derived query methods (e.g., findBy...).

1️⃣ When Is It Time to Write Your Own Recipe?

Derived queries are great for simple CRUD operations, but you’ll quickly find situations where you need to reach for @Query. This usually happens when:

• The Query is Too Complex for a Method Name: A method name like findAllByStatusAndPublicationDateAfterOrAuthorIsAdminAnd... becomes unreadable and unmaintainable.
• You Need Complex Joins: While Spring can handle simple joins, custom queries give you explicit control over JOIN FETCH to solve N+1 problems or join across unrelated entities.
• You Need Projections or DTOs: You want to return a custom object or DTO with only a subset of an entity’s fields for efficiency.
• You Need Aggregations: You need to perform calculations like COUNT, SUM, AVG, often with a GROUP BY clause.
• You’re Using Non-Standard Logic: The query logic simply doesn’t fit the findBy... pattern, like searching on a calculated value.

2️⃣ Cooking with JPQL: Custom Queries with Named Parameters

The most common way to use @Query is with JPQL (Java Persistence Query Language). JPQL looks a lot like SQL, but it operates on your Java entities and their fields, not on the database tables and columns. This makes it database-independent.

Let’s create a custom query to find all posts with a title containing a certain keyword, written by a specific author. We’ll use named parameters (prefixed with :) for safety and clarity.

// In your PostRepository.java
package com.example.blog.repository;

import com.example.blog.model.Post;
import com.example.blog.model.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Optional;

public interface PostRepository extends JpaRepository<Post, Long> {

    // Standard derived query
    Optional<Post> findByTitle(String title);
    
    // --- Our Custom JPQL Query ---
    /**
     * Finds all posts by a specific author where the title contains a given keyword.
     * Note: We are querying against the 'Post' entity and its fields, not the 'posts' table.
     */
    @Query("SELECT p FROM Post p WHERE p.author = :author AND p.title LIKE %:keyword%")
    List<Post> findPostsByAuthorAndKeyword(
        @Param("author") User author, 
        @Param("keyword") String keyword
    );
}

Breaking down the recipe:

• @Query(...): The annotation holds our JPQL query string.
• SELECT p FROM Post p: This is JPQL. Post is the name of our @Entity class (case-sensitive!), and p is the alias we assign to it.
• p.author and p.title: We refer to the fields of the Post entity, not the database column names.
• :author and :keyword: These are named parameters.
• @Param("author"): This annotation links the method parameter User author to the named parameter :author in the query. It’s essential for clarity and required if your parameter names don’t match or if you’re on an older Java version.

3️⃣ Speaking the Local Dialect: Native SQL Queries

Sometimes, you need to use a feature specific to your database (like PostgreSQL’s JSONB functions or Oracle’s recursive queries). In these cases, JPQL isn’t enough. You need to speak the native language of your database. For this, @Query has a handy flag: nativeQuery = true.

Let’s write a native SQL query for PostgreSQL that uses the ILIKE operator for a case-insensitive search, which is not standard in JPQL.

// In your PostRepository.java

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;

public interface PostRepository extends JpaRepository<Post, Long> {
    
    // --- Our Custom Native SQL Query ---
    /**
     * Finds posts where the title contains a case-insensitive keyword.
     * This uses native SQL, so we refer to actual table and column names.
     */
    @Query(
        value = "SELECT * FROM posts p WHERE p.title ILIKE %:keyword%", 
        nativeQuery = true
    )
    List<Post> findByTitleCaseInsensitive(@Param("keyword") String keyword);
}

• nativeQuery = true: This tells Spring Data to pass this query directly to the database via JDBC.
• SELECT * FROM posts: Notice we are now using the actual table name (posts) and column names from our database schema, not the entity names.
• ILIKE: This is a PostgreSQL-specific operator. This query would fail on a MySQL or H2 database.

4️⃣ Changing the State of the Jungle: @Modifying Queries

What if you want to write a custom UPDATE or DELETE query? If you try to do this with @Query alone, Spring will throw an exception. This is a safety feature because modifying queries are different—they don’t just fetch data, they change it.

To signal that a query will change data, you must add the @Modifying annotation. This is perfect for bulk operations, which can be much more efficient than fetching a list of entities and updating them one by one.

Let’s write a query to delete all posts by a specific user.

// In your PostRepository.java

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface PostRepository extends JpaRepository<Post, Long> {

    /**
     * Deletes all posts belonging to a specific user.
     * This method must be transactional (@Transactional) where it's called.
     * The @Modifying annotation is required for any UPDATE or DELETE operation.
     */
    @Modifying
    @Query("DELETE FROM Post p WHERE p.author.id = :authorId")
    void deletePostsByAuthorId(@Param("authorId") Long authorId);
}

Key points for @Modifying:

• It’s Required: You MUST use it for any JPQL or native query that performs an UPDATE, DELETE, or DDL operation.
• Transactional Context: The service method that calls a @Modifying query MUST be wrapped in a transaction (@Transactional).
• Return Type: A modifying query can only return void or an int/Integer representing the number of rows affected.
• Persistence Context Beware: Modifying queries operate directly on the database. This can cause the JPA persistence context (the in-memory cache of entities) to become stale. Spring Data can clear the context for you automatically if you add @Modifying(clearAutomatically = true).

💡 Monkey-Proof Tips

• Prefer JPQL over Native SQL: Always start with JPQL. It keeps your data access layer portable across different relational databases. Only drop down to native SQL when you absolutely need a database-specific feature or have a performance-critical query that the JPA provider isn’t optimizing well.
• Use Named Parameters: Always use named parameters (:paramName) instead of indexed parameters (?1). It makes your queries infinitely more readable and less error-prone when you need to reorder parameters.
• Externalize Your Queries: For very large or complex queries, you can store them in a jpa-named-queries.properties file and reference them by name in your repository, keeping your annotations clean.
• Test Your Queries: Use @DataJpaTest to write focused integration tests for your repositories. This is the best way to ensure your custom queries are correct and behave as you expect.

🚀 Challenge

Time to write your own five-star recipe!

1. Create a UserRepository that extends JpaRepository for your User entity.
2. Inside this repository, write a custom JPQL query using @Query that finds all User entities who have written more than a specified number of posts.
3. The method signature should look like: List<User> findUsersWithMoreThanXPosts(long postCount);
4. Hint: You will need to use a subquery with COUNT in your WHERE clause or join with the posts and use GROUP BY and HAVING. The GROUP BY approach is generally more efficient.

// Your challenge solution might look something like this:
@Query("SELECT u FROM User u JOIN u.posts p GROUP BY u HAVING COUNT(p) > :postCount")
List<User> findUsersWithMoreThanXPosts(@Param("postCount") long postCount);

👏 You’ve now graduated from ordering off the menu to being the head chef of your data layer! With @Query, you have the power to craft any data request imaginable, giving you full control and solving complex problems with elegance and precision. Keep cooking!