How to Use the @Query Annotation in Spring Boot + Spring Data JPA - From JPQL and Native SQL to Update Queries with @Modifying

Here’s the English translation of the article body:

---

Have you ever watched a method name like findByStatusAndCategoryNameOrderByCreatedAtDesc grow longer and longer until you stopped and thought, “this can’t go any further”? Once you need JOINs, aggregations, or bulk UPDATEs, the query method naming convention simply can’t express what you want.

That’s where the @Query annotation comes in. It lets you write JPQL or native SQL directly in a Spring Data JPA repository in Spring Boot, so complex searches and updates all fit inside a single interface.

In this article we’ll cover the basics of JPQL and @Param, when to switch to nativeQuery = true, updates and deletes with @Modifying, countQuery for paging, SpEL, and escaping LIKE searches — all with copy-paste-ready code. The query method naming convention itself is covered in the query methods article, so I’ll leave that to that post.

The assumed setup is Spring Boot 3.x + Spring Data JPA (Hibernate 6). The examples target PostgreSQL but are written to work on H2 as well; wherever DB-specific behavior matters, I’ll call it out on the spot.

When to Use @Query

First, the “which one do I use” decision. When in doubt, use this table.

What you want to do	What to use
Simple conditions (equality, ranges, ORDER BY)	Query methods
JOINs, aggregations, fetching only specific columns	@Query (JPQL)
DB-specific functions, window functions, full control over the SQL	@Query (native SQL)
Conditions that are added or removed depending on user input	Specification / Querydsl

The key is the last row. Searches whose “conditions change dynamically” are a weak spot for @Query, so it’s easier to accept that they belong to Specification or Querydsl territory. @Query shines in cases where “the conditions are fixed, but query methods can’t express them.”

Sample Entities

These are the entities used throughout the rest of the code. It’s a simple setup where a Product has a Category.

@Entity
public class Product {
    @Id @GeneratedValue
    private Long id;
    private String name;
    private BigDecimal price;
    private String status;   // "ACTIVE" / "ARCHIVED"
    private LocalDateTime createdAt;

    @ManyToOne(fetch = FetchType.LAZY)
    private Category category;
    // getter/setter 省略
}

@Entity
public class Category {
    @Id @GeneratedValue
    private Long id;
    private String name;
}

@Query Basics with JPQL

JPQL looks a lot like SQL, but what you write are entity names and property names, not table names and column names. Get this wrong and the app fails at startup, so it’s worth internalizing from the beginning.

public interface ProductRepository extends JpaRepository<Product, Long> {

    // 基本形。Product はエンティティ名、p.status はプロパティ名
    @Query("SELECT p FROM Product p WHERE p.status = :status")
    List<Product> findByStatus(@Param("status") String status);

    // 関連エンティティの条件はパスで辿れる
    @Query("SELECT p FROM Product p WHERE p.category.name = :categoryName ORDER BY p.createdAt DESC")
    List<Product> findByCategoryName(@Param("categoryName") String categoryName);

    // 明示的にJOINしても同じ
    @Query("SELECT p FROM Product p JOIN p.category c WHERE c.name = :categoryName")
    List<Product> findByCategoryNameWithJoin(@Param("categoryName") String categoryName);

    // 集計。COUNTはLong、AVGはDoubleで受ける
    @Query("SELECT COUNT(p) FROM Product p WHERE p.status = :status")
    long countByStatus(@Param("status") String status);

    @Query("SELECT AVG(p.price) FROM Product p WHERE p.category.id = :categoryId")
    Double averagePriceByCategory(@Param("categoryId") Long categoryId);
}

Being able to navigate associations with dots, like p.category.name, is one of the nice things about JPQL. A JOIN is generated automatically behind the scenes.

A small detail: keywords like SELECT are case-insensitive, but entity and property names like Product and createdAt are case-sensitive. Write them as if you were copying the Java class definition verbatim.

Another benefit of JPQL is that syntax errors are detected at startup. If you misspell a property name, the app won’t start, failing with a QueryCreationException (whose message reads Validation failed for query for method ...). That’s far safer than discovering it in production. The error message includes the method name, so look there and fix it. Note that if you’ve set spring.data.jpa.repositories.bootstrap-mode to lazy, the same exception is thrown not at startup but the first time that repository is used (deferred validates on ContextRefreshedEvent when startup completes, so failures still surface at startup).

@Param or Positional Parameters?

There are two ways to pass parameters.

// 位置パラメータ。引数の順番と ?1 ?2 が対応する
@Query("SELECT p FROM Product p WHERE p.status = ?1 AND p.price <= ?2")
List<Product> findCheapPositional(String status, BigDecimal maxPrice);

// 名前付きパラメータ。こちらを推奨
@Query("SELECT p FROM Product p WHERE p.status = :status AND p.price <= :maxPrice")
List<Product> findCheap(@Param("status") String status, @Param("maxPrice") BigDecimal maxPrice);

// IN句にはそのままコレクションを渡せる
@Query("SELECT p FROM Product p WHERE p.status IN :statuses")
List<Product> findByStatuses(@Param("statuses") Collection<String> statuses);

Positional parameters break silently when you reorder the arguments, so in real-world code I’d say named parameters are the only sensible choice.

If the -parameters compiler option is enabled, you can omit @Param and the parameters are resolved by argument name. Maven projects that inherit from spring-boot-starter-parent and the Spring Boot Gradle plugin enable this option by default, but code that depends on build configuration makes me nervous, so I’m in the “always write @Param” camp.

If you misspell a name, Spring Data throws an IllegalStateException. The message takes the form Using named parameters for method ... but parameter ... not found in annotated query ..., listing the method name and the missing parameter name, so you can fix it by comparing the spelling of :name in the JPQL and @Param("name").

Writing Native SQL with nativeQuery=true

When you want to use DB-specific functions or run aggregations that don’t map to an entity, set nativeQuery = true. This time you write table names and column names as they are.

public interface ProductRepository extends JpaRepository<Product, Long> {

    // DATE_TRUNCで月別の登録件数を集計(PostgreSQL想定。H2 2.x以降でも動く)
    @Query(value = """
            SELECT DATE_TRUNC('month', created_at) AS "monthStart", COUNT(*) AS "cnt"
            FROM product
            WHERE status = :status
            GROUP BY DATE_TRUNC('month', created_at)
            ORDER BY "monthStart"
            """, nativeQuery = true)
    List<MonthlyCount> countMonthly(@Param("status") String status);

    // エンティティで受けるなら、全カラムをSELECTする(SELECT * が手軽)
    @Query(value = "SELECT * FROM product WHERE price > :price", nativeQuery = true)
    List<Product> findExpensiveNative(@Param("price") BigDecimal price);
}

// MonthlyCount.java
// 集計結果はインターフェースプロジェクションで受けると読みやすい
public interface MonthlyCount {
    Timestamp getMonthStart();   // java.sql.Timestamp
    Long getCnt();
}

Named parameters work the same way in native SQL. However, if you want the result mapped to an entity, the mapping fails unless you SELECT every column the entity has. If you only need some columns, receive them via an interface like MonthlyCount above, or as Object[].

There’s a reason the aliases are wrapped in double quotes. Interface projections match getter names (cnt for getCnt) against the result set aliases, but unquoted identifiers are normalized to lowercase on PostgreSQL and uppercase (CNT) on H2. This mismatch can cause the getter to return null, so it’s safest to pin the spelling with quotes, as in AS "cnt".

A word on the type of getMonthStart() too. Timestamp columns come back from JDBC as java.sql.Timestamp, so receiving them as Timestamp is the most reliable option. You can receive them as LocalDateTime, but that relies on Spring’s type conversion and may produce a ConverterNotFoundException depending on the environment. If that happens, switch back to Timestamp and call toLocalDateTime() yourself.

The rule for choosing is simple: “if it can be written in JPQL, use JPQL.” Going native means you lose startup-time syntax validation, renamed properties aren’t picked up, and switching databases may break the query. Treating native SQL as “only when needed” is the right stance.

UPDATE/DELETE with @Modifying and @Transactional

This is where people stumble most with @Query. Update and delete queries require both @Modifying and @Transactional, and forgetting either one yields a different error.

public interface ProductRepository extends JpaRepository<Product, Long> {

    // 一括ステータス更新。戻り値は更新件数
    @Modifying(clearAutomatically = true)
    @Query("UPDATE Product p SET p.status = :status WHERE p.id IN :ids")
    int updateStatusByIds(@Param("status") String status, @Param("ids") Collection<Long> ids);

    // 古いレコードの一括削除
    @Modifying
    @Query("DELETE FROM Product p WHERE p.createdAt < :threshold")
    int deleteOlderThan(@Param("threshold") LocalDateTime threshold);
}

@Service
public class ProductService {
    private final ProductRepository repository;
    // コンストラクタ省略

    @Transactional
    public int archive(List<Long> ids) {
        return repository.updateStatusByIds("ARCHIVED", ids);
    }
}

@Modifying tells Spring Data, “this query isn’t a SELECT, so run it with executeUpdate.” If you forget it, Spring Data tries to fetch a result set and Hibernate throws an exception. On Hibernate 6.3 and later the message is Expecting a selection query, but found 'UPDATE Product p ...' (IllegalSelectQueryException); on 6.0–6.2 it reads Expecting a SELECT query.

Here’s what happens when you forget @Transactional.

org.springframework.dao.InvalidDataAccessApiUsageException:
  Executing an update/delete query
Caused by: jakarta.persistence.TransactionRequiredException:
  Executing an update/delete query

Remember that “Executing an update/delete query” means there’s no transaction, and you’ll solve it instantly. @Transactional also works when placed directly on the repository method (propagation defaults to REQUIRED), but then forgetting it in the service layer produces no error — each repository call gets its own short transaction, and the business operation loses its atomicity. Deciding up front that transaction boundaries belong in the service layer leads to fewer accidents. For details on propagation and isolation levels, see the transaction management article.

clearAutomatically and flushAutomatically

There’s a reason the example above has clearAutomatically = true. A JPQL bulk UPDATE bypasses the persistence context and updates the DB directly, so any entities already loaded in the same transaction keep their stale values.

@Transactional
public void archiveAndCheck(Long id) {
    Product before = repository.findById(id).orElseThrow(); // status = "ACTIVE"
    repository.updateStatusByIds("ARCHIVED", List.of(id));
    Product after = repository.findById(id).orElseThrow();
    // clearAutomatically が無いと after.getStatus() は "ACTIVE" のまま
}

clearAutomatically = true clears the persistence context after execution so that the next findById re-reads from the DB. Conversely, flushAutomatically = true flushes pending changes to the DB before execution so the bulk query takes them into account. If you’ll be reading and writing in the same transaction after a bulk update, it’s safest to set both.

One more caveat: bulk queries don’t trigger lifecycle callbacks such as @PreUpdate, nor optimistic locking via @Version. For entities that depend on those, just load them and update via setters.

Use countQuery When Combining with Pageable

Add a Pageable to a @Query method and it can return a Page<Product> directly. Spring Data automatically derives a SELECT COUNT(...) from the original JPQL.

For simple JOINs and conditions, automatic derivation works fine. Once GROUP BY or aggregate functions are involved, a mechanically rewritten count query won’t produce the correct total. DISTINCT and constructor expressions do get derived, but you’ll want to check the SQL log to confirm the count is what you intended. When in doubt, specify countQuery explicitly.

public interface ProductRepository extends JpaRepository<Product, Long> {

    // JPQL。GROUP BYがあるので自動派生では正しい件数が出ない
    @Query(value = """
            SELECT c.name AS name, COUNT(p) AS cnt
            FROM Product p JOIN p.category c
            GROUP BY c.name
            """,
           countQuery = "SELECT COUNT(DISTINCT c.name) FROM Product p JOIN p.category c")
    Page<CategoryCount> countByCategory(Pageable pageable);

    // ネイティブSQL。value / countQuery / nativeQuery をセットで書く
    @Query(value = "SELECT * FROM product WHERE status = :status",
           countQuery = "SELECT COUNT(*) FROM product WHERE status = :status",
           nativeQuery = true)
    Page<Product> findByStatusNative(@Param("status") String status, Pageable pageable);

    // 総件数が要らないなら Slice で count を省略できる
    @Query("SELECT p FROM Product p WHERE p.status = :status")
    Slice<Product> findSliceByStatus(@Param("status") String status, Pageable pageable);
}

// CategoryCount.java
public interface CategoryCount {
    String getName();
    Long getCnt();
}

With native SQL, a count query may be derived for simple SELECTs, but derivation tends to fail once subqueries or UNIONs are involved. I recommend making it a rule to always write countQuery for any paged native query from the start.

For cases like infinite scrolling, where all you need to know is “is there a next page,” returning a Slice skips the count query entirely — one query saved right there.

Dynamic Sort on native SQL is a limited feature — even the official reference says it can only rewrite “simple queries” — so don’t trust it too much. If you use it, pass column names rather than property names (Sort.by("created_at")) and check the SQL log to confirm the ORDER BY was appended correctly. If you need reliable control over ordering, it’s safer to hard-code ORDER BY in the SQL or rewrite the query in JPQL.

Making Queries Flexible with SpEL (:#{})

You can use SpEL inside @Query. Covering everything would take too long, so let’s look at just two patterns that are useful in practice. The full list of available expressions is in the official reference under Templated Queries and Expressions.

// 汎用の基底リポジトリ。#{#entityName} が実際のエンティティ名に置き換わる
@NoRepositoryBean
public interface SoftDeleteRepository<T, ID> extends JpaRepository<T, ID> {
    @Query("SELECT e FROM #{#entityName} e WHERE e.status <> 'ARCHIVED'")
    List<T> findAllActive();
}

public interface ProductRepository extends SoftDeleteRepository<Product, Long> {
    // 引数オブジェクトのプロパティを直接バインドできる
    @Query("SELECT p FROM Product p WHERE p.status = :#{#cond.status} AND p.price <= :#{#cond.maxPrice}")
    List<Product> search(@Param("cond") ProductSearchCondition cond);
}

With #{#entityName}, you can consolidate queries shared across multiple entities — such as “fetch only records that aren’t soft-deleted” — into a base interface. :#{#cond.status} lets you pass a search condition object directly, so the method signature doesn’t bloat as arguments are added.

That said, once you start writing conditional logic in SpEL, readability plummets. If you find yourself with two or more “ignore this condition if it’s null” clauses, take that as the signal to move to Specification.

LIKE Searches and Escaping Wildcards

For partial-match searches, there are two approaches: append % on the JPQL side or on the parameter side.

public interface ProductRepository extends JpaRepository<Product, Long> {

    // JPQL側で結合する。Spring Data JPAの拡張構文で %:keyword% と書ける
    @Query("SELECT p FROM Product p WHERE p.name LIKE %:keyword%")
    List<Product> searchByName(@Param("keyword") String keyword);

    // 標準JPQLならCONCATを使う
    @Query("SELECT p FROM Product p WHERE LOWER(p.name) LIKE LOWER(CONCAT('%', :keyword, '%'))")
    List<Product> searchByNameIgnoreCase(@Param("keyword") String keyword);

    // パラメータ側で結合する。呼び出し側が "%" + keyword + "%" を渡す。エスケープ文字は ! にする
    @Query("SELECT p FROM Product p WHERE p.name LIKE :pattern ESCAPE '!'")
    List<Product> searchByPattern(@Param("pattern") String pattern);

    // Spring Data JPA組み込みのSpEL関数でエスケープまで任せる
    @Query("SELECT p FROM Product p WHERE p.name LIKE %?#{escape([0])}% ESCAPE ?#{escapeCharacter()}")
    List<Product> searchByNameEscaped(String keyword);
}

@Service
public class ProductSearchService {
    private final ProductRepository repository;
    // コンストラクタ省略

    public List<Product> search(String keyword) {
        return repository.searchByPattern("%" + escapeLike(keyword) + "%");
    }

    // ! % _ をエスケープ。JPQL側の ESCAPE '!' と対にする
    static String escapeLike(String s) {
        return s.replace("!", "!!")
                .replace("%", "!%")
                .replace("_", "!_");
    }
}

The first two are convenient because the caller just passes the raw keyword. The problem arises when a user types % or _. Since _ means “any single character,” searching for a_c matches both abc and axc.

The standard way to prevent this is to build the pattern on the parameter side and escape it in the service layer before passing it in. That’s what escapeLike above does: it neutralizes three characters — the chosen escape character ! itself, plus % and _. You might be tempted to use a backslash as the escape character, but Hibernate 6’s HQL interprets Java-style escape sequences, so as a Java string literal you’d have to write four backslashes, ESCAPE '\\\\', or the app fails at startup with Validation failed for query. Sticking with a symbol like ! avoids the mishap.

Spring Data JPA actually has built-in support for this too: as in the last method, searchByNameEscaped, combining ?#{escape([0])} with ?#{escapeCharacter()} escapes % and _ for you without any utility method. The escape character defaults to \ and can be changed via @EnableJpaRepositories(escapeCharacter = ...). Just keep in mind that it doesn’t handle DB-specific extra wildcards like SQL Server’s [ ].

If you want case-insensitive matching, applying LOWER() to both sides does the job, but remember that applying a function to a column prevents a regular index from being used.

Incidentally, for simple partial matches, a query method like findByNameContainingIgnoreCase is more than enough. There’s no need to force @Query on it.

Fetching Only the Columns You Need - DTO Projections

Often you only want id and name rather than the whole entity. With JPQL you can use constructor expressions; with native SQL, interface projections.

// ProductSummary.java(com.example.dto パッケージ。JPQL側のFQCNと対応させる)
package com.example.dto;

public record ProductSummary(Long id, String name, BigDecimal price) {}

// ProductNameView.java
package com.example.dto;

public interface ProductNameView {
    Long getId();
    String getName();
}

// ProductRepository.java
public interface ProductRepository extends JpaRepository<Product, Long> {
    // コンストラクタ式。クラス名は完全修飾名(FQCN)で書く
    @Query("SELECT new com.example.dto.ProductSummary(p.id, p.name, p.price) FROM Product p WHERE p.status = :status")
    List<ProductSummary> findSummaries(@Param("status") String status);

    // インターフェースプロジェクション。エイリアスとgetter名を合わせる(クォートで綴りを固定)
    @Query(value = "SELECT id AS \"id\", name AS \"name\" FROM product WHERE status = :status", nativeQuery = true)
    List<ProductNameView> findNames(@Param("status") String status);
}

If a constructor expression fails with Could not locate appropriate constructor, the types of the selected columns don’t match the constructor’s parameter types. Long vs. Integer and BigDecimal vs. Double are the usual suspects. For guidance on choosing between approaches and on nested projections, see the projections article.

Make a Habit of Checking the Generated SQL

Whenever you write a @Query, check the log at least once to confirm the SQL and bound values are what you expect. Derived count queries and LIKE escaping can’t be verified without looking at the log.

logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.orm.jdbc.bind: TRACE   # バインドパラメータの値も出す(Hibernate 6系)

If you spot unexpected extra JOINs or N+1 queries here, consult the JPA performance optimization article and consider fetch joins and similar techniques.

Error Lookup Table

Here’s a summary of the errors covered so far, indexed by symptom.

Symptom	Cause	Fix
QueryCreationException (Validation failed for query) at startup	JPQL syntax error, wrong entity or property name	Use the method name in the message to locate and fix the JPQL
Executing an update/delete query	Missing @Transactional	Add @Transactional in the service layer
Expecting a selection query, but found ...	Missing @Modifying	Add @Modifying to the update/delete method
parameter ... not found in annotated query	Mismatch between the @Param name and :name in the JPQL	Make the spellings match
Could not locate appropriate constructor	Constructor expression parameter types don’t match the SELECT column types	Align the DTO parameter types
ConverterNotFoundException	Can’t convert to the projection’s return type (e.g. Timestamp→LocalDateTime)	Receive the type JDBC returns
Wrong page total, or SQL error in the count	countQuery not specified (native, GROUP BY)	Specify countQuery explicitly
Stale values read after a bulk update	Persistence context is out of date	@Modifying(clearAutomatically = true)

Summary

Let’s wrap up with a recap of when to use @Query.

Case	Approach
Simple conditions	Query methods
JOINs, aggregations, DTO retrieval	@Query + JPQL
DB-specific functions, complex SQL	@Query(nativeQuery = true)
Dynamically changing conditions	Specification / Querydsl

There are only three pitfalls you need to remember: always pair @Modifying with @Transactional for updates and deletes, specify countQuery explicitly when paging GROUP BY or native SQL, and escape user input in LIKE searches with ESCAPE.

With those three covered, you should be able to move to @Query without hesitation the moment query methods stop being enough. And if your conditions still keep growing more dynamic, head over to the Specification and Querydsl articles next.

---

Recap: Translated the full article body into English, preserving all Markdown structure, code blocks (including their Japanese comments, per the “keep code unchanged” rule), tables, and all /ja/ internal links exactly as-is. No files were modified — the translation is delivered inline above.