@Query and JPQL

Write JPQL or native SQL on a repository method with @Query, bind parameters safely, and run bulk updates with @Modifying.

Syntax@Query("select b from Book b where b.author.id = :authorId and b.published = true") List<Book> findPublished(@Param("authorId") Long authorId); @Modifying @Query("update Book b set b.price = :p where b.id = :id") int setPrice(Long id, BigDecimal p);

When a method name stops being readable, put the query on the method with @Query. By default the text is JPQL: it looks like SQL but names entities and their fields, not tables and columns. select o from PurchaseOrder o where o.status = :status works whatever the table is called.

Spring Data parses every @Query when the application starts, so a misspelt field fails startup rather than the first request. Native queries are only checked by the database, at run time.

Parameters

Bind values with named parameters (:status) and never by concatenating strings. Each named parameter maps to a method argument with @Param("status"). Spring Boot's parent POM and Gradle plugin compile with -parameters, so the argument name alone also works, but @Param keeps the query correct when someone renames the argument. Parameters cannot replace identifiers: you cannot bind a column name or a sort direction. Use a Sort argument for that.

Native SQL

Set nativeQuery = true, or use the shorter @NativeQuery, when you need database features JPQL lacks: window functions, jsonb operators, ON CONFLICT. The query is now tied to one database, and H2 in tests may not understand it. Run those tests against PostgreSQL with Testcontainers.

Updates and deletes

An update or delete query needs @Modifying and an active transaction. It goes straight to the database and skips the persistence context, so:

  • entities already loaded in this transaction keep their old values. clearAutomatically = true clears the context after the statement so later reads reload.
  • pending changes to loaded entities are not flushed first unless you set flushAutomatically = true.
  • @Version is not incremented, entity listeners do not run and auditing timestamps are not set. Update those columns in the statement yourself.
@Modifying(clearAutomatically = true)
@Query("update Product p set p.price = p.price * :factor, p.version = p.version + 1 where p.category = :category")
int reprice(@Param("category") String category, @Param("factor") BigDecimal factor);

Paging a custom query

A @Query method can take a Pageable. Spring Data derives the count query from yours, which fails or is slow for queries with joins and distinct. Supply countQuery explicitly for anything beyond a simple select ... where.

Keep repository queries about data access. Business rules in a where clause are hard to test and easy to duplicate; if two services need the same filter, put it in one well-named method.

Example

Example · java
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
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.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

@Entity
class Product {
    @Id
    private Long id;
    private String name;
    private String category;
    private BigDecimal price;
    private boolean active;
    private Instant updatedAt;

    protected Product() { }
}

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query("select p from Product p where p.active = true and p.category = :category and p.price <= :max")
    List<Product> findAffordable(@Param("category") String category, @Param("max") BigDecimal max);

    @Query(value = "select p from Product p where lower(p.name) like lower(concat(:prefix, '%'))",
           countQuery = "select count(p) from Product p where lower(p.name) like lower(concat(:prefix, '%'))")
    Page<Product> searchByPrefix(@Param("prefix") String prefix, Pageable pageable);

    @Transactional
    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Query("update Product p set p.active = false, p.updatedAt = :now where p.category = :category")
    int deactivateCategory(@Param("category") String category, @Param("now") Instant now);
}

When to use it

  • Search products by name prefix with paging and a count query that stays fast.
  • Deactivate every product in a discontinued category with one UPDATE from an admin endpoint.
  • Build a leaderboard with a SQL window function that JPQL cannot express.

More examples

A native query for a PostgreSQL feature

Window functions are not part of JPQL, so this ranking is a native query. The column aliases match the getters of the LeaderboardRow interface, and Spring Data maps each row onto it without an entity. PostgreSQL folds unquoted aliases to lower case, so the mixed-case playerId alias is quoted to keep its case. Test it against the real database engine: H2 accepts much of this, but not every PostgreSQL function.

Example · java
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import java.util.List;
import org.springframework.data.jpa.repository.NativeQuery;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

@Entity
class Score {
    @Id
    private Long id;
    private Long playerId;
    private int points;

    protected Score() { }
}

interface LeaderboardRow {
    Long getPlayerId();
    Integer getBest();
    Long getRank();
}

public interface ScoreRepository extends Repository<Score, Long> {

    @NativeQuery("""
        select player_id as "playerId", max(points) as best,
               rank() over (order by max(points) desc) as rank
        from score
        group by player_id
        order by best desc
        limit :size
        """)
    List<LeaderboardRow> leaderboard(@Param("size") int size);
}

Fetching an association in the same query

join fetch loads the customer in the same SQL statement, so reading invoice.getCustomer().getName() later costs nothing and works even after the transaction has ended. A plain join would filter on the customer but still leave it lazy.

Example · java
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.Id;
import jakarta.persistence.ManyToOne;
import java.util.List;
import java.util.Optional;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

@Entity
class Customer {
    @Id
    private Long id;
    private String name;

    protected Customer() { }
}

@Entity
class Invoice {
    @Id
    private Long id;
    private String number;
    @ManyToOne(fetch = FetchType.LAZY)
    private Customer customer;

    protected Invoice() { }
}

public interface InvoiceRepository extends Repository<Invoice, Long> {

    @Query("select i from Invoice i join fetch i.customer where i.number = :number")
    Optional<Invoice> findWithCustomer(@Param("number") String number);

    @Query("select i from Invoice i join fetch i.customer c where c.id = :customerId order by i.id desc")
    List<Invoice> findAllForCustomer(@Param("customerId") Long customerId);
}

Mapping straight into a record

A constructor expression builds the record directly from the query, so no entities enter the persistence context and nothing is dirty-checked. The class name inside new must be fully qualified with its real package. The argument types must match a constructor exactly: sum over an int column is a Long in JPQL.

Example · java
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import java.math.BigDecimal;
import java.util.List;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;

@Entity
class OrderLine {
    @Id
    private Long id;
    private String sku;
    private int quantity;
    private BigDecimal unitPrice;

    protected OrderLine() { }
}

record SkuTotal(String sku, Long units, BigDecimal revenue) { }

public interface SalesRepository extends Repository<OrderLine, Long> {

    @Query("""
        select new com.example.sales.SkuTotal(l.sku, sum(l.quantity), sum(l.unitPrice * l.quantity))
        from OrderLine l
        group by l.sku
        order by sum(l.unitPrice * l.quantity) desc
        """)
    List<SkuTotal> revenueBySku();
}

Related

sb derived query methodssb projectionssb pagination and sortingsb fetching and the n 1 problemsb at query

Discussion

  • Be the first to comment on this lesson.