00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

JPQL and Native Queries in Spring Data JPA

Last updated:

Derived query methods such as findByStatus cover simple lookups, but real applications need joins, reports, searches and bulk updates. For those, Spring Data JPA lets you write the query yourself, and there are two kinds: JPQL and native queries. JPQL works with your entities and fields, while native SQL talks to the database directly. Knowing when to use each keeps your data layer correct, fast and portable.

This tutorial explains how both kinds of query are processed, then covers parameters, LIKE and IN conditions, DTO projections, aggregates, native SQL with @NativeQuery, paging and sorting, and bulk updates with @Modifying. You will build a payments reporting API on Spring Boot 4.1, learn the pitfalls that break queries in production, and test every query with @DataJpaTest.

1. What Are JPQL and Native Queries?

  • JPQL (Jakarta Persistence Query Language) looks like SQL, but it queries entities and their fields, not tables and columns. You write select p from Payment p where p.amount > :min, and Hibernate translates it into SQL for whichever database you use. Hibernate's own dialect of it, HQL, accepts everything JPQL does plus some extra features.
  • A native query is plain SQL for your specific database, with real table and column names, such as select * from payments where created_at > :from. Hibernate sends it to the database almost unchanged.

In Spring Data JPA, both are declared on repository methods: JPQL with @Query, and native SQL with @NativeQuery (or @Query(nativeQuery = true)). If you are new to repositories, start with the Spring Data JPA overview.

2. When Derived Query Methods Are Not Enough

Derived queries build SQL from the method name, which is great for findByReference but quickly becomes unreadable or impossible. Write the query yourself when you need to:

  • Join and filter across entities, such as payments of customers whose name contains a search term.
  • Return only some columns as a DTO instead of full entities.
  • Aggregate with count, sum and group by for reports.
  • Use database-specific features such as date_trunc, full-text search or JSON operators. These need native SQL.
  • Update or delete many rows at once without loading each entity.

3. How Spring Data JPA Runs JPQL and Native Queries

The two kinds of query take different paths, and the difference explains most of their pros and cons:

How JPQL and native queries are processedJPQL in @Query is parsed by Hibernate into a query tree, validated against the entity model when the application starts, translated into SQL for the configured database dialect and sent to the database with bind parameters. Native SQL in @NativeQuery is only adjusted by Spring Data for paging and sorting, is not checked against the entity model so errors appear at run time, and is sent to the database almost as written with bind parameters. JPQL @Query @Query JPQL entity names Hibernate parser builds a query tree Validate at startup against entity model Translate to SQL for the DB dialect Database bind parameters Native SQL @NativeQuery @NativeQuery SQL table names Spring Data adds paging, sorting No model check errors at run time Database bind parameters sent almost as written
Figure 1: JPQL is checked against your entities at startup and translated per database; native SQL goes to the database almost unchanged, so mistakes surface only when it runs.
  • JPQL is parsed by Hibernate into an internal query tree and checked against your entity model when the application starts. A misspelled field stops startup with a clear error (section 10.1). At run time, Hibernate translates the tree into SQL for the configured database dialect, and it caches the parsed plan for reuse.
  • Native SQL is not parsed against the entity model. Spring Data only rewrites it for paging and sorting, then Hibernate binds the parameters and sends it to the database. Mistakes appear only when the query runs.

In both cases, values are passed as bind parameters (? placeholders filled in by the JDBC driver), never pasted into the SQL text. That is what protects you from SQL injection.

4. Setup

The example uses Spring Data JPA, Spring MVC and an in-memory H2 database:

pom.xml 
<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-webmvc</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>
spring.application.name=payments
spring.datasource.url=jdbc:h2:mem:payments
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.defer-datasource-initialization=true
spring.jpa.open-in-view=false
spring.data.web.pageable.serialization-mode=via-dto
logging.level.org.hibernate.SQL=debug
  • logging.level.org.hibernate.SQL=debug shows the SQL generated from each query, which is the quickest way to check what a JPQL query really does.
  • spring.data.web.pageable.serialization-mode=via-dto makes Page results serialize to a stable JSON format (section 10.6).
Version note: Spring Boot 4.1 ships Spring Data JPA 4.1 and Hibernate 7.4, and renames the web starter to spring-boot-starter-webmvc. The @NativeQuery annotation used below arrived in Spring Data JPA 3.4. On older versions, use @Query(value = "...", nativeQuery = true), which still works everywhere.

5. Writing JPQL Queries with @Query

The entities are a Customer and a Payment, which has a lazy @ManyToOne to its customer, an amount, a method (CARD, UPI or NET_BANKING), a status (PENDING, CAPTURED, FAILED or REFUNDED) and a createdAt timestamp. The full classes are in section 9.

5.1 Selecting Entities with Joins

PaymentRepository.java 
@Query("select p from Payment p where p.method in :methods and p.amount >= :minAmount order by p.amount desc")
List<Payment> findLargePayments(@Param("methods") Collection<PaymentMethod> methods,
                                @Param("minAmount") BigDecimal minAmount);

Payment and p.amount are the entity and field names, not the table payments. Joins follow relationships (join p.customer c), so there is no on clause to write.

5.2 Named and Positional Parameters

Named parameters (:status) are matched to method parameters by name, and positional parameters (?1, ?2) by position. Prefer named parameters. They survive reordering and read better in long queries.

Named vs positional parameters 
// Named parameters (preferred)
@Query("select p from Payment p where p.status = :status and p.amount > :min")
List<Payment> findByStatusAbove(@Param("status") PaymentStatus status, @Param("min") BigDecimal min);

// Positional parameters
@Query("select p from Payment p where p.status = ?1 and p.amount > ?2")
List<Payment> findByStatusAbovePositional(PaymentStatus status, BigDecimal min);

@Param is optional when your code is compiled with the -parameters flag, which keeps parameter names in the bytecode. The Spring Boot parent POM and Gradle plugin turn that flag on. The examples keep @Param because it also works in projects built another way.

5.3 IN and LIKE Conditions

Pass a Collection to an IN parameter, and build LIKE patterns inside the query so the caller passes only the search text:

PaymentRepository.java 
@Query("""
        select new com.javabytechie.payments.payment.PaymentResponse(
            p.reference, p.amount, p.method, p.status, p.createdAt, c.email)
        from Payment p
        join p.customer c
        where lower(c.name) like lower(concat('%', :name, '%'))
        order by p.createdAt
        """)
List<PaymentResponse> searchByCustomerName(@Param("name") String name);

Spring Data also understands a shortcut, where c.name like %:name%, and adds the wildcards to the bound value for you. Both forms bind the search text as a parameter.

5.4 DTO Projections with Constructor Expressions

A constructor expression (select new ...) builds a DTO, a record here, directly from the selected columns. Only those columns are read, and no entities enter the persistence context:

PaymentRepository.java and PaymentResponse.java 
@Query("""
        select new com.javabytechie.payments.payment.PaymentResponse(
            p.reference, p.amount, p.method, p.status, p.createdAt, c.email)
        from Payment p
        join p.customer c
        where p.reference = :reference
        """)
Optional<PaymentResponse> findResponseByReference(@Param("reference") String reference);

public record PaymentResponse(String reference, BigDecimal amount, PaymentMethod method,
                              PaymentStatus status, LocalDateTime createdAt, String customerEmail) {
}

Use the fully qualified class name after new. The selected values must match a constructor's parameter types in order.

5.5 Aggregates and GROUP BY

Reports combine projections with aggregate functions. Here, captured revenue per payment method:

PaymentRepository.java 
@Query("""
        select new com.javabytechie.payments.payment.MethodRevenue(p.method, count(p), sum(p.amount))
        from Payment p
        where p.status = :status
        group by p.method
        order by sum(p.amount) desc
        """)
List<MethodRevenue> revenueByMethod(@Param("status") PaymentStatus status);
Console and result 
DEBUG org.hibernate.SQL : select p1_0.method,count(p1_0.id),sum(p1_0.amount) from payments p1_0 where p1_0.status=? group by p1_0.method order by sum(p1_0.amount) desc

[{"method":"CARD","payments":2,"total":7099.00},{"method":"UPI","payments":2,"total":1248.50}]

count returns a long and sum of a BigDecimal field returns a BigDecimal, so MethodRevenue declares exactly those types.

6. Writing Native Queries with @NativeQuery

6.1 When Native SQL Is the Right Tool

Use native SQL when you need something JPQL can't express, or can't express clearly: database functions such as date_trunc, window functions, common table expressions (with ...), full-text search, JSON operators or hand-tuned SQL for a hot path. The monthly revenue report needs date_trunc:

PaymentRepository.java 
@NativeQuery("""
        select date_trunc('month', p.created_at) as monthStart,
               count(*) as payments,
               sum(p.amount) as total
        from payments p
        where p.status = :status
        group by date_trunc('month', p.created_at)
        order by monthStart
        """)
List<MonthlyRevenue> monthlyRevenue(@Param("status") String status);

Native SQL uses table and column names (payments, created_at). An enum stored as text is passed as a String, so the service calls it with PaymentStatus.CAPTURED.name().

6.2 Mapping Native Results

A native query can return three kinds of results:

Return typeHow columns are matchedExample
EntityThe query must select every mapped column, usually with select *Page<Payment> findByStatusNative(...)
Interface projectionColumn aliases match getter names (as total to getTotal())interface MonthlyRevenueView { BigDecimal getTotal(); }
Record (DTO)Column aliases match the record componentsList<MonthlyRevenue> monthlyRevenue(...)
Version note: mapping this native query to a record with a LocalDateTime component works on Spring Boot 4.1. On Spring Boot 3.5, the same query failed in our test with ClassCastException: Cannot cast java.sql.Timestamp to java.time.LocalDateTime. On 3.x, use an interface projection for date columns, or declare the component as java.sql.Timestamp.

6.3 Paging and Sorting Native Queries

Native queries accept Pageable and Sort like JPQL ones. Spring Data adds the order by and the limit, and derives a count query for simple statements. For anything more complex, declare countQuery yourself:

PaymentRepository.java 
@NativeQuery(
        value = "select * from payments where status = :status",
        countQuery = "select count(*) from payments where status = :status")
Page<Payment> findByStatusNative(@Param("status") String status, Pageable pageable);

// usage: page 2 of captured payments, 3 per page, oldest first
paymentRepository.findByStatusNative("CAPTURED", PageRequest.of(1, 3, Sort.by("created_at")));

Sort properties in a native query refer to columns (Sort.by("created_at")), while in JPQL they refer to entity fields (Sort.by("createdAt")). See the pagination and sorting tutorial for more on Pageable.

7. JPQL vs Native SQL

JPQL (@Query)Native SQL (@NativeQuery)
Refers toEntities and fieldsTables and columns
Checked at startupYes, against the entity modelNo, errors appear when it runs
Database portabilityPortable, translated per dialectTied to one database's SQL
Database-specific featuresLimited (HQL adds some functions)Everything the database offers
JoinsFollow relationships (join p.customer)Explicit join ... on
ResultsEntities, constructor-expression DTOs, scalarsEntities, interface projections, records, scalars
Paging and sortingFull supportSupported; complex queries need countQuery
Refactoring safetyRenaming a field breaks startup, so you noticeRenaming a column breaks only at run time

8. Update and Delete Queries with @Modifying

A bulk update changes many rows with one statement instead of loading, changing and saving each entity. Mark the method with @Modifying. It returns the number of affected rows and must run inside a transaction:

PaymentRepository.java and PaymentService.java 
@Modifying(clearAutomatically = true)
@Query("""
        update Payment p
        set p.status = :newStatus
        where p.status = :currentStatus and p.createdAt < :cutoff
        """)
int updateStatusBefore(@Param("currentStatus") PaymentStatus currentStatus,
                       @Param("newStatus") PaymentStatus newStatus,
                       @Param("cutoff") LocalDateTime cutoff);

// in PaymentService
@Transactional
public ExpireResult expirePendingBefore(LocalDateTime cutoff) {
    int expired = paymentRepository.updateStatusBefore(PaymentStatus.PENDING, PaymentStatus.FAILED, cutoff);
    log.info("Marked {} pending payments created before {} as FAILED", expired, cutoff);
    return new ExpireResult(expired);
}
Console 
DEBUG org.hibernate.SQL : update payments p1_0 set status=? where p1_0.status=? and p1_0.created_at<?
INFO  c.j.payments.payment.PaymentService : Marked 1 pending payments created before 2026-10-22T00:00 as FAILED

A bulk statement goes straight to the database and bypasses the persistence context, Hibernate's per-transaction cache of loaded entities. Entities already loaded in the same transaction keep their old values. Two attributes help:

AttributeDefaultEffect
clearAutomaticallyfalseClears the persistence context after the update, so later reads load fresh values from the database
flushAutomaticallyfalseWrites pending entity changes to the database before the update runs

Clearing detaches every managed entity, so any unsaved changes to them are lost. Save first, or set flushAutomatically = true as well. Section 10.2 shows what happens without clearAutomatically. The transaction management tutorial explains how the service's @Transactional provides the transaction.

9. A Complete Example: Payments Reporting API

The API lists payments by status with paging, finds one by reference, searches by customer name, reports revenue per method (JPQL) and per month (native SQL), and expires old pending payments with a bulk update. It runs on Spring Boot 4.1 with Java 21.

9.1 Entities and Enums

package com.javabytechie.payments.customer;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "customers")
public class Customer {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;

    @Column(nullable = false, unique = true)
    private String email;

    @Column(nullable = false)
    private String city;

    protected Customer() {
    }

    public Customer(String name, String email, String city) {
        this.name = name;
        this.email = email;
        this.city = city;
    }

    public Long getId() { return id; }
    public String getName() { return name; }
    public String getEmail() { return email; }
    public String getCity() { return city; }
}

9.2 Repository and DTOs

package com.javabytechie.payments.payment;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.Collection;
import java.util.List;
import java.util.Optional;

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.NativeQuery;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface PaymentRepository extends JpaRepository<Payment, Long> {

    // JPQL: entity and field names, a join through the relationship, bind parameters
    @Query("""
            select new com.javabytechie.payments.payment.PaymentResponse(
                p.reference, p.amount, p.method, p.status, p.createdAt, c.email)
            from Payment p
            join p.customer c
            where p.status = :status
            """)
    Page<PaymentResponse> findByStatus(@Param("status") PaymentStatus status, Pageable pageable);

    @Query("""
            select new com.javabytechie.payments.payment.PaymentResponse(
                p.reference, p.amount, p.method, p.status, p.createdAt, c.email)
            from Payment p
            join p.customer c
            where p.reference = :reference
            """)
    Optional<PaymentResponse> findResponseByReference(@Param("reference") String reference);

    @Query("""
            select new com.javabytechie.payments.payment.PaymentResponse(
                p.reference, p.amount, p.method, p.status, p.createdAt, c.email)
            from Payment p
            join p.customer c
            where lower(c.name) like lower(concat('%', :name, '%'))
            order by p.createdAt
            """)
    List<PaymentResponse> searchByCustomerName(@Param("name") String name);

    @Query("select p from Payment p where p.method in :methods and p.amount >= :minAmount order by p.amount desc")
    List<Payment> findLargePayments(@Param("methods") Collection<PaymentMethod> methods,
                                    @Param("minAmount") BigDecimal minAmount);

    // JPQL aggregate straight into a record
    @Query("""
            select new com.javabytechie.payments.payment.MethodRevenue(p.method, count(p), sum(p.amount))
            from Payment p
            where p.status = :status
            group by p.method
            order by sum(p.amount) desc
            """)
    List<MethodRevenue> revenueByMethod(@Param("status") PaymentStatus status);

    // Native SQL: date_trunc is a database function, so this query uses table and column names
    @NativeQuery("""
            select date_trunc('month', p.created_at) as monthStart,
                   count(*) as payments,
                   sum(p.amount) as total
            from payments p
            where p.status = :status
            group by date_trunc('month', p.created_at)
            order by monthStart
            """)
    List<MonthlyRevenue> monthlyRevenue(@Param("status") String status);

    // Native SQL with paging: the count query is written out
    @NativeQuery(
            value = "select * from payments where status = :status",
            countQuery = "select count(*) from payments where status = :status")
    Page<Payment> findByStatusNative(@Param("status") String status, Pageable pageable);

    // Bulk update: runs in the database, bypassing the persistence context
    @Modifying(clearAutomatically = true)
    @Query("""
            update Payment p
            set p.status = :newStatus
            where p.status = :currentStatus and p.createdAt < :cutoff
            """)
    int updateStatusBefore(@Param("currentStatus") PaymentStatus currentStatus,
                           @Param("newStatus") PaymentStatus newStatus,
                           @Param("cutoff") LocalDateTime cutoff);
}

9.3 Service, Controller and Sample Data

Every "not found" lookup throws PaymentNotFoundException, which returns 404 Not Found. Read methods run in read-only transactions, and the bulk update runs in a read-write one.

package com.javabytechie.payments.payment;

import java.time.LocalDateTime;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class PaymentService {

    private static final Logger log = LoggerFactory.getLogger(PaymentService.class);

    private final PaymentRepository paymentRepository;

    public PaymentService(PaymentRepository paymentRepository) {
        this.paymentRepository = paymentRepository;
    }

    @Transactional(readOnly = true)
    public Page<PaymentResponse> findByStatus(PaymentStatus status, Pageable pageable) {
        return paymentRepository.findByStatus(status, pageable);
    }

    @Transactional(readOnly = true)
    public PaymentResponse findByReference(String reference) {
        return paymentRepository.findResponseByReference(reference)
                .orElseThrow(() -> new PaymentNotFoundException(reference));
    }

    @Transactional(readOnly = true)
    public List<PaymentResponse> searchByCustomerName(String name) {
        return paymentRepository.searchByCustomerName(name);
    }

    @Transactional(readOnly = true)
    public List<MethodRevenue> revenueByMethod() {
        return paymentRepository.revenueByMethod(PaymentStatus.CAPTURED);
    }

    @Transactional(readOnly = true)
    public List<MonthlyRevenue> monthlyRevenue() {
        return paymentRepository.monthlyRevenue(PaymentStatus.CAPTURED.name());
    }

    @Transactional
    public ExpireResult expirePendingBefore(LocalDateTime cutoff) {
        int expired = paymentRepository.updateStatusBefore(PaymentStatus.PENDING, PaymentStatus.FAILED, cutoff);
        log.info("Marked {} pending payments created before {} as FAILED", expired, cutoff);
        return new ExpireResult(expired);
    }
}

9.4 Run It

Start the app with ./mvnw spring-boot:run. The first request pages captured payments, largest first:

Terminal 
curl 'http://localhost:8080/api/payments?status=CAPTURED&page=0&size=2&sort=amount,desc'

{"content":[{"reference":"PAY-1005","amount":5600.00,"method":"CARD","status":"CAPTURED","createdAt":"2026-10-08T16:20:00","customerEmail":"meera@example.com"},{"reference":"PAY-1001","amount":1499.00,"method":"CARD","status":"CAPTURED","createdAt":"2026-09-03T10:15:00","customerEmail":"asha@example.com"}],"page":{"size":2,"number":0,"totalElements":4,"totalPages":2}}
Console 
DEBUG org.hibernate.SQL : select p1_0.reference,p1_0.amount,p1_0.method,p1_0.status,p1_0.created_at,c1_0.email from payments p1_0 join customers c1_0 on c1_0.id=p1_0.customer_id where p1_0.status=? order by p1_0.amount desc fetch first ? rows only
DEBUG org.hibernate.SQL : select count(*) from payments p1_0 where p1_0.status=?

Spring Data ran the JPQL query with fetch first ? rows only for the page, and a derived count(*) query for the totals. The other endpoints:

Terminal 
curl http://localhost:8080/api/payments/PAY-1001
{"reference":"PAY-1001","amount":1499.00,"method":"CARD","status":"CAPTURED","createdAt":"2026-09-03T10:15:00","customerEmail":"asha@example.com"}

curl -i http://localhost:8080/api/payments/PAY-9999
HTTP/1.1 404
{"timestamp":"2026-10-24T18:38:08.415Z","status":404,"error":"Not Found","path":"/api/payments/PAY-9999"}

curl 'http://localhost:8080/api/payments/search?customer=rav'
[{"reference":"PAY-1003","amount":2200.00,"method":"NET_BANKING","status":"FAILED","createdAt":"2026-09-20T09:05:00","customerEmail":"ravi@example.com"},{"reference":"PAY-1004","amount":899.00,"method":"UPI","status":"CAPTURED","createdAt":"2026-10-02T12:30:00","customerEmail":"ravi@example.com"}]

curl http://localhost:8080/api/payments/reports/by-method
[{"method":"CARD","payments":2,"total":7099.00},{"method":"UPI","payments":2,"total":1248.50}]

curl http://localhost:8080/api/payments/reports/monthly
[{"monthStart":"2026-09-01T00:00:00","payments":2,"total":1848.50},{"monthStart":"2026-10-01T00:00:00","payments":2,"total":6499.00}]

curl -X POST 'http://localhost:8080/api/payments/expire-pending?before=2026-10-22T00:00:00'
{"expired":1}

curl 'http://localhost:8080/api/payments?status=PENDING'
{"content":[{"reference":"PAY-1007","amount":760.00,"method":"CARD","status":"PENDING","createdAt":"2026-10-24T21:45:00","customerEmail":"asha@example.com"}],"page":{"size":20,"number":0,"totalElements":1,"totalPages":1}}

After the bulk update, PAY-1006 (from 20 October) is FAILED, while PAY-1007 (from 24 October) is still PENDING.

10. Common Pitfalls and How to Fix Them

10.1 Typos: Caught at Startup or Only at Run Time

Misspell a field in JPQL (p.amout) and the application refuses to start:

Console 
Caused by: org.springframework.data.repository.query.QueryCreationException: Cannot create query for method [PaymentRepository.findLargePayments(java.util.Collection,java.math.BigDecimal)]; Query validation failed for 'select p from Payment p where p.method in :methods and p.amout >= :minAmount'
Caused by: java.lang.IllegalArgumentException: org.hibernate.query.sqm.UnknownPathException: Could not resolve attribute 'amout' of 'com.javabytechie.payments.payment.Payment'

Misspell a table in native SQL (paymnts) and the application starts normally. The error appears only when that method is first called, possibly in production. How to fix it: prefer JPQL where it is enough, and cover every native query with a repository test.

10.2 Stale Entities After a Bulk Update

Without clearAutomatically, an entity loaded before the update keeps its old value, and so does every later lookup in the same transaction, because Hibernate returns the cached instance:

Console (one transaction, @Modifying without clearAutomatically) 
PAY-1006 loaded            status = PENDING
updateStatusBefore(...)    rows updated = 1
same Java object           status = PENDING
PAY-1006 loaded again      status = PENDING   <- the database row is already FAILED

How to fix it: set @Modifying(clearAutomatically = true), as the example does, or run bulk updates in their own transaction before loading entities.

10.3 "No active transaction for update or delete query"

Calling a @Modifying repository method outside a transaction fails:

Console 
org.springframework.dao.InvalidDataAccessApiUsageException: No active transaction for update or delete query
Caused by: jakarta.persistence.TransactionRequiredException: No active transaction for update or delete query

How to fix it: call it from a service method annotated with @Transactional, as PaymentService.expirePendingBefore does.

10.4 Building Queries with String Concatenation

Gluing user input into query text opens the door to SQL injection, where crafted input changes the query itself. This applies to JPQL built by hand too:

Unsafe vs safe 
// UNSAFE: input such as  x' or '1'='1  changes the query
entityManager.createQuery(
        "select p from Payment p where p.reference = '" + reference + "'", Payment.class);

// SAFE: the value is sent separately as a bind parameter
entityManager.createQuery(
        "select p from Payment p where p.reference = :reference", Payment.class)
        .setParameter("reference", reference);

How to fix it: always use bind parameters. For dynamic sorting, accept a Sort or Pageable instead of concatenating an order by clause. For dynamic filters, use Specifications or the Criteria API.

10.5 Reserved Words and Database Differences in Native SQL

Native SQL must suit your exact database. While building this example, the alias month failed on H2 with a syntax error, because MONTH is a reserved word there. The alias monthStart works. Likewise, date_trunc exists in PostgreSQL and H2 but not in MySQL. How to fix it: test native queries against the same database you run in production. Testcontainers makes that easy.

10.6 Returning Page Directly from a Controller

Without extra configuration, Spring Data logs a warning when a controller returns a Page:

Console 
WARN  ...PageModule$WarningLoggingModifier : Serializing PageImpl instances as-is is not supported, meaning that there is no guarantee about the stability of the resulting JSON structure!

The raw JSON format of PageImpl is not guaranteed to stay stable. How to fix it: set spring.data.web.pageable.serialization-mode=via-dto, as in section 4, which produces the stable {"content": [...], "page": {...}} shape shown in section 9.4. Alternatively, map the page into your own response record.

11. Testing Repository Queries

@DataJpaTest starts only the JPA layer with an embedded database and rolls back after each test, so tests can freely change data. The test below checks paging, both reports, the bulk update (before and after) and the native page's count query:

PaymentRepositoryTest.java 
package com.javabytechie.payments.payment;

import static org.assertj.core.api.Assertions.assertThat;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.data.jpa.test.autoconfigure.DataJpaTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;

@DataJpaTest
class PaymentRepositoryTest {

    @Autowired
    private PaymentRepository paymentRepository;

    @Test
    void pagesCapturedPaymentsAsDtos() {
        Page<PaymentResponse> page = paymentRepository.findByStatus(
                PaymentStatus.CAPTURED, PageRequest.of(0, 2, Sort.by("amount").descending()));

        assertThat(page.getTotalElements()).isEqualTo(4);
        assertThat(page.getContent()).extracting(PaymentResponse::reference)
                .containsExactly("PAY-1005", "PAY-1001");
        assertThat(page.getContent().getFirst().customerEmail()).isEqualTo("meera@example.com");
    }

    @Test
    void reportsRevenuePerMethodAndPerMonth() {
        List<MethodRevenue> byMethod = paymentRepository.revenueByMethod(PaymentStatus.CAPTURED);
        List<MonthlyRevenue> byMonth = paymentRepository.monthlyRevenue("CAPTURED");

        assertThat(byMethod).extracting(MethodRevenue::method)
                .containsExactly(PaymentMethod.CARD, PaymentMethod.UPI);
        assertThat(byMethod.getFirst().total()).isEqualByComparingTo("7099.00");
        assertThat(byMonth).extracting(MonthlyRevenue::total)
                .usingElementComparator(BigDecimal::compareTo)
                .containsExactly(new BigDecimal("1848.50"), new BigDecimal("6499.00"));
    }

    @Test
    void bulkUpdateExpiresOnlyOldPendingPayments() {
        LocalDateTime cutoff = LocalDateTime.of(2026, 10, 22, 0, 0);

        // Before: two pending payments, one before the cutoff and one after it
        assertThat(statusOf("PAY-1006")).isEqualTo(PaymentStatus.PENDING);
        assertThat(statusOf("PAY-1007")).isEqualTo(PaymentStatus.PENDING);

        int updated = paymentRepository.updateStatusBefore(PaymentStatus.PENDING, PaymentStatus.FAILED, cutoff);

        // After: only the older one changed, and reads see the new value
        assertThat(updated).isEqualTo(1);
        assertThat(statusOf("PAY-1006")).isEqualTo(PaymentStatus.FAILED);
        assertThat(statusOf("PAY-1007")).isEqualTo(PaymentStatus.PENDING);
    }

    @Test
    void nativePageUsesTheDeclaredCountQuery() {
        Page<Payment> page = paymentRepository.findByStatusNative(
                "CAPTURED", PageRequest.of(1, 3, Sort.by("created_at")));

        assertThat(page.getTotalElements()).isEqualTo(4);
        assertThat(page.getContent()).extracting(Payment::getReference).containsExactly("PAY-1005");
    }

    private PaymentStatus statusOf(String reference) {
        return paymentRepository.findResponseByReference(reference).orElseThrow().status();
    }
}

Because the startup check covers JPQL, a passing test context already proves every JPQL query is valid. The native query tests are what prove the SQL itself works. The Spring Boot testing tutorial covers test slices in more detail.

Version note: in Spring Boot 4.x, @DataJpaTest lives in org.springframework.boot.data.jpa.test.autoconfigure and needs spring-boot-starter-data-jpa-test. In Spring Boot 3.x it is in org.springframework.boot.test.autoconfigure.orm.jpa.

12. When to Use Derived Queries, JPQL or Native SQL

NeedGood choicePoor choice
Simple lookup by one or two fieldsDerived query (findByReference)A hand-written @Query for the same thing
Joins, filters across entities, DTOs, aggregatesJPQL with @QueryA derived method name with eight parts
Database functions, window functions, CTEs, JSONNative SQL with @NativeQueryLoading everything and computing in Java
Updating or deleting many rows@Modifying JPQLA loop of save() calls
Filters chosen at run timeSpecifications or the Criteria APIConcatenating query strings

13. Best Practices

  • Start with derived queries, move to JPQL when the method name gets long, and use native SQL only for features JPQL lacks.
  • Always use bind parameters, never string concatenation, and prefer named parameters over positional ones.
  • Return DTO records for read-only results with constructor expressions, so queries load only the columns you need.
  • Pass enum values as parameters instead of hard-coding literals, so queries stay readable and refactoring-safe.
  • Declare countQuery for complex native pages rather than relying on a derived count.
  • Use @Modifying(clearAutomatically = true) for bulk updates, and run them in a @Transactional service method.
  • Serialize pages through a stable DTO with spring.data.web.pageable.serialization-mode=via-dto or your own response type.
  • Test every native query against your production database type, since it isn't checked at startup.
  • Watch the generated SQL while developing, to catch unexpected joins and missing indexes early.

14. Summary

JPQL and native queries give Spring Data JPA repositories full control when derived queries run out. Use JPQL with @Query for joins, DTO projections, aggregates and bulk updates: it works with entities, is checked at startup and stays portable across databases. Use native SQL with @NativeQuery when you need database-specific features, map its results to entities, interface projections or records, and declare a countQuery for complex pages. Always bind parameters, mark bulk updates with @Modifying(clearAutomatically = true) inside a transaction, and cover every query with a @DataJpaTest. The main recommendation: prefer JPQL with DTO projections for most custom queries, and keep native SQL for the cases that truly need it.