@Query

Declares the JPQL or native SQL a Spring Data repository method runs, with named parameters, paging count queries and projections.

Signature
@Query(value = "select b from Book b where b.author = :author",
       countQuery = "", nativeQuery = false)
  • Added in Spring Boot Spring Data JPA 1.0

Parameters

ParameterTypeDescription
$valueStringThe JPQL query, or SQL when nativeQuery is true.
$nativeQueryoptionalboolean= falseTreat value as native SQL instead of JPQL.
$countQueryoptionalString= derived from valueQuery used to count the total for Page results. Needed for most native queries and for joins that the derived count gets wrong.
$countProjectionoptionalString= ""What the derived count query counts, for example a distinct expression.
$nameoptionalString= ""Name of a named query to use instead of value.
$countNameoptionalString= ""Name of a named query to use for counting.
$queryRewriteroptionalClass<? extends QueryRewriter>= QueryRewriter.IdentityQueryRewriterHook that rewrites the query string just before execution.

Return value

n/aReplaces the query Spring Data would derive from the method name with the given JPQL or SQL.

Errors

Invalid JPQL fails when the repository is created, which is application startup unless repositories are initialised lazily; a test that loads the repositories catches it either way. Native SQL is only checked by the database at run time.

See also

@Query and JPQL@ModifyingDerived Query MethodsProjectionsPagination and Sorting

Derived query methods such as findByEmail are fine for simple lookups, but the name becomes unreadable once a query has joins, several conditions or aggregates. @Query lets you write the query yourself and keep a short, meaningful method name.

JPQL by default

The query is JPQL: it names entities and fields, not tables and columns, so select o from Order o where o.customer.id = :id works whatever the table is called. Spring Data JPA creates each @Query when the repository is created and asks the EntityManager to compile it, so with the default eager setup a typo in an entity or field name stops the application at startup, which is far better than a 500 on the first request. With lazy initialisation (spring.main.lazy-initialization=true) or lazy repository bootstrapping (spring.data.jpa.repositories.bootstrap-mode=lazy), that check moves to the first use of the repository, so keep at least one test that loads the repositories, such as a @DataJpaTest, and the mistake still fails the build.

Parameters

Use named parameters (:status). Spring Data matches them to method parameters by name, which requires the code to be compiled with -parameters; Spring Boot's Maven and Gradle plugins set that up. Adding @Param("status") makes it explicit and survives any build setup. Never build the query string by concatenating input: parameters are bound safely, concatenation is SQL injection.

Spring Data extends JPQL with like %:term%, which wraps the bound value in wildcards for you. Binding stops SQL injection, but it does not stop LIKE wildcards: a user who types % matches every row and _ matches any single character. That returns results the user did not ask for and lets anyone force a scan of the whole table. For user input, escape %, _ and the escape character itself, and declare the escape character in the query with like :pattern escape '!' (the search example below does this). Combine it with lower() for case-insensitive search, and remember that a leading wildcard cannot use a normal B-tree index.

Paging and counting

A method taking a Pageable and returning Page<T> runs your query with a limit plus a second, count query that Spring Data derives from yours. For complex JPQL, joins over collections, or non-trivial native queries, supply countQuery yourself: a derived count over a collection join counts duplicate rows, and Spring Data may not be able to rewrite native SQL into a count. If you do not need the total, return Slice or List and skip the count entirely.

Native queries

nativeQuery = true (or the @NativeQuery annotation) sends SQL straight to the database: use it for window functions, jsonb operators or anything JPQL cannot express. You lose startup validation and portability, and the result maps to entities or interface projections only when column aliases match.

Projections

Returning a record or interface instead of the entity selects only the columns you need and gives you an object that cannot be accidentally modified. For list endpoints this is often the biggest single win over loading entities.

@Query("select new com.example.OrderSummary(o.id, o.total) from Order o where o.status = :status")
List<OrderSummary> summaries(@Param("status") Status status);

For updates and deletes, add @Modifying; a @Query alone only runs selects.

Example

Example · java
import jakarta.persistence.Entity;
import jakarta.persistence.EnumType;
import jakarta.persistence.Enumerated;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import java.math.BigDecimal;
import java.time.Instant;
import java.util.List;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

enum Status { NEW, PAID, SHIPPED }

@Entity
@Table(name = "orders")
class Order {
    @Id @GeneratedValue
    private Long id;
    private Long customerId;
    @Enumerated(EnumType.STRING)
    private Status status;
    private BigDecimal total;
    private Instant placedAt;
    protected Order() { }
}

interface OrderTotal {
    Long getCustomerId();
    BigDecimal getRevenue();
}

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("select o from Order o where o.status = :status and o.placedAt >= :since")
    Page<Order> recent(@Param("status") Status status, @Param("since") Instant since, Pageable page);

    @Query("""
            select o.customerId as customerId, sum(o.total) as revenue
            from Order o
            where o.status = :status
            group by o.customerId
            order by sum(o.total) desc
            """)
    List<OrderTotal> revenueByCustomer(@Param("status") Status status);
}

When to use it

  • A reporting endpoint that groups orders by customer and sums revenue in a single query.
  • A search box that matches article titles case-insensitively, treats % and _ in the input literally, and returns only ids and titles.
  • Filtering products by PostgreSQL jsonb attributes with a paged native query.

Frequently asked questions

Do I need @Param on every parameter?

Not if the code is compiled with -parameters, which Spring Boot's build plugins configure. @Param makes the binding explicit and keeps working if the build setup changes, so many teams always use it.

When is my @Query checked?

JPQL queries are compiled by the EntityManager when the repository bean is created, so with the default eager setup mistakes fail startup. Lazy initialisation or lazy repository bootstrapping delays that to first use, so keep a test that loads the repositories. Native queries are passed to the database unparsed and only fail when executed.

Can @Query run an UPDATE or DELETE?

Yes, together with @Modifying and a transaction. Without @Modifying Spring Data executes it as a select and the call fails.

More examples

Case-insensitive search with escaped wildcards

Without escaping, a search for 50% or user_id would treat % and _ as wildcards, and a bare % would match every published article. The default method escapes the escape character first, then the wildcards, and the query declares ! as the escape character. The constructor expression needs the record's fully qualified name, so adjust com.example to your package; only id and title are selected.

Example · java
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import java.util.List;
import java.util.Locale;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

@Entity
class Article {
    @Id @GeneratedValue
    private Long id;
    private String title;
    private String body;
    private boolean published;
    protected Article() { }
}

record ArticleHit(Long id, String title) { }

public interface ArticleRepository extends JpaRepository<Article, Long> {

    @Query("""
            select new com.example.ArticleHit(a.id, a.title)
            from Article a
            where a.published = true and lower(a.title) like :pattern escape '!'
            """)
    List<ArticleHit> findByTitlePattern(@Param("pattern") String pattern);

    default List<ArticleHit> search(String term) {
        String escaped = term.toLowerCase(Locale.ROOT)
                .replace("!", "!!")
                .replace("%", "!%")
                .replace("_", "!_");
        return findByTitlePattern("%" + escaped + "%");
    }
}

A native query with an explicit count

PostgreSQL's jsonb containment operator has no JPQL equivalent, so native SQL is the right tool. The explicit countQuery gives Page its total without Spring Data trying to rewrite the SQL.

Example · java
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

@Entity
class Product {
    @Id
    private Long id;
    private String name;
    protected Product() { }
}

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query(value = """
            select p.* from product p
            where p.attributes @> cast(:filter as jsonb)
            """,
           countQuery = """
            select count(*) from product p
            where p.attributes @> cast(:filter as jsonb)
            """,
           nativeQuery = true)
    Page<Product> findByAttributes(@Param("filter") String jsonFilter, Pageable page);
}

Skipping the count with Slice

Slice fetches one extra row to know whether there is a next page and never runs a count, which on a table with millions of rows can cost more than the page itself. Ideal for infinite scroll.

Example · java
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import java.time.Instant;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

@Entity
class AuditEntry {
    @Id @GeneratedValue
    private Long id;
    private String actor;
    private Instant at;
    protected AuditEntry() { }
}

public interface AuditEntryRepository extends JpaRepository<AuditEntry, Long> {

    @Query("select e from AuditEntry e where e.actor = :actor order by e.at desc")
    Slice<AuditEntry> byActor(@Param("actor") String actor, Pageable page);
}

Related

sb query and jpqlsb derived query methodssb projectionssb pagination and sortingsb at modifying

Discussion

  • Be the first to comment on this lesson.