What are the query options in Spring Data JPA? | JPA & Hibernate

Q: How does JPQL @Query work?

JPQL @Query lets you write entitybased queries manually. Use it when derived methods become unreadable (more than 23 conditions) or when you need JOINs and aggregations. package com.bank.repository; import com.bank.model.Trade; import org.springframework.data.jpa.repository.JpaRepository; import org

Q: How does @Modifying work for bulk operations?

@Modifying marks UPDATE and DELETE queries that execute bulk operations without loading entities. It returns the count of affected rows. package com.bank.repository; import com.bank.model.Trade; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.reposit

Q: Why does @Modifying need @Transactional?

@Modifying queries modify data, so they require an active transaction. The calling service method must be annotated with @Transactional. package com.bank.service; import com.bank.repository.TradeRepository; import org.springframework.stereotype.Service; import org.springframework.transaction.annotat

Q: What is clearAutomatically and flushAutomatically?

After a @Modifying query, the persistence context may hold stale entities. clearAutomatically=true clears the persistence context after the query executes. flushAutomatically=true flushes pending changes before the query. package com.bank.service; import com.bank.model.Trade; import com.bank.reposit

Q: What are common mistakes with custom queries?

The most common mistake is using named parameters without @Param annotation. package com.bank.repository; import com.bank.model.Trade; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.query.

Definition

Spring Data JPA offers derived methods (name-parsed queries), JPQL @Query (entity-based), native SQL @Query (raw SQL), and @Modifying (bulk UPDATE/DELETE) – start with derived, escalate to @Query when needed.

Derived methods – Spring parses the method name into JPQL. findByStatusAndAccountId(status, accountId) generates WHERE t.status = ?1 AND t.accountId = ?2. Validated at startup.
JPQL @Query – manually written entity-based queries. Use named parameters (:name with @Param) or positional (?1). Supports JOINs, aggregations, and subqueries.
Native SQL @Query – raw SQL with nativeQuery=true. Uses table and column names, not entity and field names. Needed for database-specific functions.
@Modifying – marks UPDATE/DELETE queries. Executes one SQL without loading entities. Returns the count of affected rows.
Named parameters – :paramName with @Param("paramName"). Always preferred over positional ?1 for readability.

Five ways to ask the National Bank of India clerk for data. Derived: "Find settled trades for Rajesh" – the clerk understands the method name. JPQL @Query: write a formal request letter in bank language. Native SQL: write in the database's own language. @Modifying: a batch command like "update ALL pending trades to settled." Named parameters: fill in the blanks on a form.

Understanding the Concepts

What are derived query methods?

Derived methods parse the method name into a JPQL query. Spring Data follows strict naming conventions. These methods are validated at application startup – a typo in the method name causes a startup failure, which is a useful safety net.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
import org.springframework.data.jpa.repository.JpaRepository;
import java.math.BigDecimal;
import java.util.List;

public interface TradeRepository extends JpaRepository<Trade, String> {

    // Method name → generated JPQL:
    List<Trade> findByStatus(String status);
    // → SELECT t FROM Trade t WHERE t.status = ?1

    List<Trade> findByAccountIdAndStatus(String accountId, String status);
    // → WHERE t.accountId = ?1 AND t.status = ?2

    List<Trade> findByAmountGreaterThan(BigDecimal amount);
    // → WHERE t.amount > ?1

    List<Trade> findBySymbolContainingIgnoreCase(String fragment);
    // → WHERE UPPER(t.symbol) LIKE UPPER('%' || ?1 || '%')

    List<Trade> findByStatusOrderByAmountDesc(String status);
    // → WHERE t.status = ?1 ORDER BY t.amount DESC

    long countByStatus(String status);
    // → SELECT COUNT(t) FROM Trade t WHERE t.status = ?1

    boolean existsByAccountIdAndStatus(String accountId, String status);
    // → SELECT CASE WHEN COUNT(t) > 0 THEN TRUE ELSE FALSE END ...

    void deleteByStatus(String status);
    // → DELETE FROM Trade t WHERE t.status = ?1
}

How does JPQL @Query work?

JPQL @Query lets you write entity-based queries manually. Use it when derived methods become unreadable (more than 2-3 conditions) or when you need JOINs and aggregations.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;

public interface TradeRepository extends JpaRepository<Trade, String> {

    // Named parameters with @Param:
    @Query("SELECT t FROM Trade t WHERE t.amount BETWEEN :min AND :max "
         + "AND t.status = :status")
    List<Trade> findByAmountRangeAndStatus(
        @Param("min") BigDecimal min,
        @Param("max") BigDecimal max,
        @Param("status") String status);

    // JOIN FETCH (eager load association):
    @Query("SELECT a FROM Account a LEFT JOIN FETCH a.trades "
         + "WHERE a.accountId = :id")
    Optional<com.bank.model.Account> findAccountWithTrades(
        @Param("id") String accountId);

    // Aggregation:
    @Query("SELECT t.symbol, SUM(t.amount) FROM Trade t "
         + "GROUP BY t.symbol ORDER BY SUM(t.amount) DESC")
    List<Object[]> getTotalAmountPerSymbol();
}

How does native SQL @Query work?

Native SQL uses raw SQL with table and column names. Needed for database-specific features like window functions, JSONB operators, or interval arithmetic.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
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;
import java.math.BigDecimal;
import java.util.List;

public interface TradeRepository extends JpaRepository<Trade, String> {

    // Native query: uses table/column names, not entity/field names
    @Query(value = "SELECT * FROM trades "
         + "WHERE amount > :min AND settlement_date = CURRENT_DATE",
           nativeQuery = true)
    List<Trade> findTodaysLargeTrades(@Param("min") BigDecimal min);

    // Native query with pagination: countQuery is REQUIRED
    @Query(value = "SELECT * FROM trades WHERE status = :status",
           countQuery = "SELECT COUNT(*) FROM trades WHERE status = :status",
           nativeQuery = true)
    Page<Trade> findByStatusNative(
        @Param("status") String status, Pageable pageable);
}

How does @Modifying work for bulk operations?

@Modifying marks UPDATE and DELETE queries that execute bulk operations without loading entities. It returns the count of affected rows.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
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;

public interface TradeRepository extends JpaRepository<Trade, String> {

    // Bulk UPDATE: one SQL, no entity loading
    @Modifying
    @Query("UPDATE Trade t SET t.status = :newStatus "
         + "WHERE t.status = :oldStatus AND t.accountId = :accountId")
    int updateStatusForAccount(
        @Param("oldStatus") String oldStatus,
        @Param("newStatus") String newStatus,
        @Param("accountId") String accountId);
    // Returns: number of rows affected

    // Bulk DELETE:
    @Modifying
    @Query("DELETE FROM Trade t WHERE t.status = 'CANCELLED' "
         + "AND t.accountId = :accountId")
    int deleteCancelledForAccount(@Param("accountId") String accountId);

    // clearAutomatically prevents stale persistence context
    @Modifying(clearAutomatically = true)
    @Query("UPDATE Trade t SET t.status = 'SETTLED' WHERE t.tradeId = :id")
    int settleTrade(@Param("id") String tradeId);
}

Why does @Modifying need @Transactional?

@Modifying queries modify data, so they require an active transaction. The calling service method must be annotated with @Transactional.

JAVA

package com.bank.service;

import com.bank.repository.TradeRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class SettlementBatchService {

    private final TradeRepository tradeRepo;

    public SettlementBatchService(TradeRepository tradeRepo) {
        this.tradeRepo = tradeRepo;
    }

    // WRONG: no @Transactional on calling method
    // public int settleAll(String accountId) {
    //     return tradeRepo.updateStatusForAccount("PENDING", "SETTLED", accountId);
    //     // TransactionRequiredException!
    // }

    // CORRECT: @Transactional on calling method
    @Transactional
    public int settleAll(String accountId) {
        int updated = tradeRepo.updateStatusForAccount(
            "PENDING", "SETTLED", accountId);
        // UPDATE trades SET status='SETTLED'
        // WHERE status='PENDING' AND account_id='SAV2001'
        // Returns: 15 (number of rows updated)
        return updated;
    }
}

What is clearAutomatically and flushAutomatically?

After a @Modifying query, the persistence context may hold stale entities. clearAutomatically=true clears the persistence context after the query executes. flushAutomatically=true flushes pending changes before the query.

JAVA

package com.bank.service;

import com.bank.model.Trade;
import com.bank.repository.TradeRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class StalenessDemo {

    private final TradeRepository tradeRepo;

    public StalenessDemo(TradeRepository tradeRepo) {
        this.tradeRepo = tradeRepo;
    }

    @Transactional
    public void demonstrateStaleness(String tradeId) {
        Trade trade = tradeRepo.findById(tradeId).orElseThrow();
        // trade.status = "PENDING" (in persistence context)

        tradeRepo.settleTrade(tradeId);
        // UPDATE trades SET status='SETTLED' WHERE trade_id = ?
        // Database row now has status='SETTLED'

        // WITHOUT clearAutomatically:
        // trade.getStatus() still returns "PENDING" (stale!)

        // WITH clearAutomatically=true:
        // Persistence context cleared after @Modifying
        Trade fresh = tradeRepo.findById(tradeId).orElseThrow();
        // fresh.getStatus() returns "SETTLED" (correct!)
    }
}

When should you use each query approach?

Start with derived methods. Escalate to JPQL @Query when derived names become unreadable. Use native SQL only when JPQL cannot express the query.

| Approach | When to use | Validation |
|----------|------------|------------|
| Derived method | Simple queries (1-3 conditions) | Startup (method name parsed) |
| JPQL @Query | Complex JOINs, aggregations, subqueries | Startup (JPQL validated) |
| Native SQL @Query | DB-specific functions, CTEs, window functions | Runtime only |
| @Modifying | Bulk UPDATE/DELETE | Startup |

JAVA

package com.bank.repository;

import com.bank.model.Trade;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.math.BigDecimal;
import java.util.List;

public interface TradeRepository extends JpaRepository<Trade, String> {

    // 1. Derived: simple, validated at startup
    List<Trade> findByStatusOrderByAmountDesc(String status);

    // 2. JPQL: complex, still validated at startup
    @Query("SELECT t FROM Trade t WHERE t.amount BETWEEN :min AND :max")
    List<Trade> findByAmountRange(@Param("min") BigDecimal min,
                                   @Param("max") BigDecimal max);

    // 3. Native: DB-specific, only validated at runtime
    @Query(value = "SELECT *, RANK() OVER (ORDER BY amount DESC) as rnk "
         + "FROM trades WHERE status = :status",
           nativeQuery = true)
    List<Trade> findWithRank(@Param("status") String status);
}

What are common mistakes with custom queries?

The most common mistake is using named parameters without @Param annotation.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.math.BigDecimal;
import java.util.List;

public interface TradeQueryMistakes extends JpaRepository<Trade, String> {

    // WRONG: missing @Param annotation
    // @Query("SELECT t FROM Trade t WHERE t.amount > :min")
    // List<Trade> findLarge(BigDecimal min);
    // Error: "Named parameter not bound: min"

    // CORRECT: @Param binds the method parameter to the JPQL parameter
    @Query("SELECT t FROM Trade t WHERE t.amount > :min")
    List<Trade> findLarge(@Param("min") BigDecimal min);
}

Visual Overview

DIAGRAM

  Query Resolution Order:
  ┌───────────────────────────────────┐
  │  1. Derived Method                  │
  │     findByStatusAndAccountId()      │
  │     → Parsed into JPQL at startup   │
  └─────────────────┬─────────────────┘
                    │ too complex?
                    ▼
  ┌───────────────────────────────────┐
  │  2. JPQL @Query                     │
  │     @Query("SELECT t FROM Trade t") │
  │     → Entity-based, portable        │
  └─────────────────┬─────────────────┘
                    │ need DB-specific?
                    ▼
  ┌───────────────────────────────────┐
  │  3. Native SQL @Query               │
  │     nativeQuery=true                │
  │     → Raw SQL, DB-specific           │
  └───────────────────────────────────┘

Key Takeaway

Spring Data JPA offers four ways to write custom queries. Derived methods parse the method name into JPQL – findByAccountIdAndStatus generates WHERE accountId = ? AND status = ? and is validated at startup. JPQL @Query lets you write entity-based queries manually with named parameters – use it for complex conditions, JOINs, and aggregations. Native SQL with nativeQuery=true writes raw SQL for database-specific features like window functions. @Modifying marks bulk UPDATE/DELETE operations that execute one SQL without loading entities.

At National Bank of India, Rajesh uses derived findByStatusOrderByAmountDesc for the trade listing. He escalates to JPQL @Query for the amount range filter and aggregation dashboard. Native SQL handles the PostgreSQL-specific date interval query. @Modifying powers the end-of-day settlement batch: one UPDATE sets 500 trades to SETTLED in a single SQL instead of loading and saving each entity.

Always start with derived methods for simple queries. Move to JPQL @Query when derived names become unreadable. Use native SQL only when JPQL cannot express the query. Use @Modifying for bulk operations and always pair it with @Transactional on the calling method.

Remember one sentence: start with derived methods, escalate to JPQL @Query for complexity, use native SQL for database-specific features, and use @Modifying for bulk updates.

---