Definition
- Derived query method – Spring Data parses the method name (e.g., findByStatusAndBalanceGreaterThan) into a JPQL query automatically, trading convenience for readability limits.
- @Query with JPQL – an explicit object-oriented query against entity names with named parameters, giving full control over joins, ordering, and projections.
- @Query(nativeQuery=true) – raw SQL for database-specific features like window functions, CTEs, or array operations that JPQL cannot express.
- @Modifying – an annotation that marks a @Query method as an UPDATE or DELETE, requiring a writable @Transactional and optionally clearing the persistence context afterward.
- clearAutomatically / flushAutomatically – attributes on @Modifying that flush pending changes before the bulk statement and clear the context afterward to avoid stale entities.
Imagine different ways to ask National Bank of India's system for information. Derived methods are like filling a quick standard form where the field names spell out the request: find by status and balance greater than. JPQL is writing a short custom request in the bank's internal object language. Native SQL is going straight to the vault's raw filing system for a special database trick. And @Modifying is the stamp that says this request changes records in bulk, like freeze all dormant accounts in one sweep.
Understanding the Concepts
What are derived query methods?
Derived query methods let Spring Data parse the method name into a JPQL query automatically. You write the method signature, and Spring generates the query from the keywords in the name. Supported keywords include And, Or, Between, LessThan, GreaterThan, Like, OrderBy, Top, First, and many more.
package com.bank.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import com.bank.model.Account;
import java.math.BigDecimal;
import java.util.List;
public interface AccountRepository extends JpaRepository<Account, String> {
// Spring parses this into: SELECT a FROM Account a WHERE a.status = ?1 AND a.balance > ?2
List<Account> findByStatusAndBalanceGreaterThan(String status, BigDecimal minBalance);
// Between, OrderBy
List<Account> findByBalanceBetweenOrderByBalanceDesc(BigDecimal min, BigDecimal max);
// Top N
List<Account> findTop10ByBranchCodeOrderByBalanceDesc(String branchCode);
// Exists
boolean existsByHolderPan(String pan);
}
Generated JPQL for findByStatusAndBalanceGreaterThan("ACTIVE", 100000):
SELECT a FROM Account a WHERE a.status = 'ACTIVE' AND a.balance > 100000
Derived methods are great for 1-3 conditions.
Past 3-4 conditions, the method name becomes unreadable.
What is @Query with JPQL?
The @Query annotation lets you write the query explicitly using JPQL (Java Persistence Query Language). JPQL uses entity names and field names, not table and column names. Named parameters bind with :paramName and @Param.
package com.bank.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import com.bank.model.Account;
import java.math.BigDecimal;
import java.util.List;
public interface AccountRepository extends JpaRepository<Account, String> {
@Query("SELECT a FROM Account a WHERE a.balance > :minBalance AND a.status = :status " +
"ORDER BY a.balance DESC")
List<Account> findHighValueAccounts(
@Param("minBalance") BigDecimal minBalance,
@Param("status") String status);
// JPQL with JOIN
@Query("SELECT a FROM Account a LEFT JOIN FETCH a.trades WHERE a.accountId = :id")
Account findWithTrades(@Param("id") String accountId);
}
JPQL advantages:
- Method name stays clean and descriptive
- Full control over joins, ordering, grouping, projections
- Uses entity names (Account, Trade), not table names (accounts, trades)
- Named parameters prevent SQL injection
What is native SQL with @Query?
Native SQL queries use raw SQL with nativeQuery = true. They are needed when JPQL cannot express what you need: window functions, CTEs, PostgreSQL arrays, vendor-specific hints, or complex joins. The trade-off is you lose database portability and JPQL's entity awareness.
package com.bank.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import com.bank.model.Account;
import java.util.List;
public interface AccountRepository extends JpaRepository<Account, String> {
// Window function: rank accounts by balance within each branch
@Query(value = "SELECT a.*, RANK() OVER (PARTITION BY a.branch_code ORDER BY a.balance DESC) " +
"AS balance_rank FROM accounts a WHERE a.branch_code = :branch",
nativeQuery = true)
List<Object[]> findAccountsRankedByBalance(@Param("branch") String branchCode);
// CTE: find accounts with above-average balance
@Query(value = "WITH avg_bal AS (SELECT AVG(balance) AS avg FROM accounts) " +
"SELECT a.* FROM accounts a, avg_bal WHERE a.balance > avg_bal.avg",
nativeQuery = true)
List<Account> findAboveAverageAccounts();
}
Native SQL trade-offs:
+ Database-specific features (window functions, CTEs, hints)
+ Full SQL power
- Loses database portability
- Must map results by column alias or @SqlResultSetMapping
- JPQL entity awareness lost (use table/column names, not entity names)
What is @Modifying and when is it needed?
By default, @Query methods are read-only SELECTs. For UPDATE or DELETE statements, you must add @Modifying, and the method must run inside a @Transactional. The @Modifying method returns the number of affected rows as an int.
package com.bank.repository;
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 com.bank.model.Account;
import java.time.LocalDate;
public interface AccountRepository extends JpaRepository<Account, String> {
@Modifying(clearAutomatically = true, flushAutomatically = true)
@Query("UPDATE Account a SET a.status = 'FROZEN' WHERE a.lastActivityDate < :cutoff")
int freezeDormantAccounts(@Param("cutoff") LocalDate cutoff);
@Modifying(clearAutomatically = true)
@Query("DELETE FROM Account a WHERE a.status = 'CLOSED' AND a.closedDate < :cutoff")
int purgeClosedAccounts(@Param("cutoff") LocalDate cutoff);
}
package com.bank.service;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.bank.repository.AccountRepository;
import java.time.LocalDate;
@Service
public class ComplianceService {
private final AccountRepository accountRepository;
public ComplianceService(AccountRepository accountRepository) {
this.accountRepository = accountRepository;
}
@Transactional
public int freezeDormant() {
LocalDate cutoff = LocalDate.now().minusYears(2);
int frozenCount = accountRepository.freezeDormantAccounts(cutoff);
// clearAutomatically=true: persistence context cleared after bulk UPDATE
// Any entities loaded before the bulk update are now evicted
return frozenCount;
}
}
Expected SQL: UPDATE accounts SET status = 'FROZEN' WHERE last_activity_date < '2024-06-15'
Result: returns the number of rows updated (e.g., 342 dormant accounts frozen)
How do the four query types compare?
| Aspect | Derived | JPQL @Query | Native SQL | @Modifying |
|---|---|---|---|---|
| Query source | Method name | Explicit JPQL | Explicit SQL | Explicit JPQL/SQL |
| Readability | Good for 1-3 conditions | Always clean | Complex but powerful | Clear with annotation |
| Portability | Full (JPQL generated) | Full (JPQL) | Database-specific | Depends on query |
| Joins / projections | Limited | Full | Full | Full |
| Bulk UPDATE/DELETE | Not supported | Not without @Modifying | Not without @Modifying | Supported |
| Persistence context | Normal | Normal | Normal | Bypassed |
package com.bank.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import com.bank.model.Account;
import java.math.BigDecimal;
import java.util.List;
public interface AccountRepository extends JpaRepository<Account, String> {
// 1. Derived: simple, readable
List<Account> findByStatus(String status);
// 2. JPQL: explicit, clean method name
@Query("SELECT a FROM Account a WHERE a.balance > :min ORDER BY a.balance DESC")
List<Account> findHighValue(BigDecimal min);
// 3. Native: database-specific
@Query(value = "SELECT * FROM accounts WHERE balance > :min FOR UPDATE SKIP LOCKED",
nativeQuery = true)
List<Account> findForProcessing(BigDecimal min);
// 4. Modifying: bulk update
@Modifying
@Query("UPDATE Account a SET a.balance = a.balance + :interest WHERE a.status = 'ACTIVE'")
int applyInterest(BigDecimal interest);
}
What are common mistakes?
The most common mistake is forgetting @Modifying on an UPDATE or DELETE query, which causes Spring Data to treat it as a SELECT and throw an exception. A second mistake is not setting clearAutomatically on @Modifying, which leaves stale entities in the persistence context.
// WRONG: missing @Modifying on an UPDATE query
package com.bank.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import com.bank.model.Account;
public interface BrokenRepository extends JpaRepository<Account, String> {
// This throws an exception at runtime!
@Query("UPDATE Account a SET a.status = 'FROZEN' WHERE a.accountId = :id")
void freezeAccount(String id);
}
// CORRECT: @Modifying + @Transactional
package com.bank.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import com.bank.model.Account;
public interface CorrectRepository extends JpaRepository<Account, String> {
@Modifying(clearAutomatically = true)
@Query("UPDATE Account a SET a.status = 'FROZEN' WHERE a.accountId = :id")
int freezeAccount(String id);
}
When should you use each approach?
Use derived methods for simple finders with one to three conditions. Use JPQL @Query when the method name would be unwieldy or when you need joins, projections, or ordering. Use native SQL when you need database-specific features that JPQL cannot express. Use @Modifying for bulk updates and deletes that should execute as a single efficient statement.
// Real-world National Bank of India repository combining all four
package com.bank.repository;
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 com.bank.model.Account;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;
public interface AccountRepository extends JpaRepository<Account, String> {
// Derived: support staff quick lookup
List<Account> findByHolderNameContaining(String name);
// JPQL: risk team custom filter
@Query("SELECT a FROM Account a WHERE a.balance > :min AND a.branchCode = :branch")
List<Account> findByBalanceAndBranch(@Param("min") BigDecimal min, @Param("branch") String branch);
// Native: regulatory report with window function
@Query(value = "SELECT a.*, RANK() OVER (ORDER BY a.balance DESC) AS rank " +
"FROM accounts a WHERE a.branch_code = :branch",
nativeQuery = true)
List<Object[]> rankByBalance(@Param("branch") String branch);
// Modifying: nightly compliance freeze
@Modifying(clearAutomatically = true, flushAutomatically = true)
@Query("UPDATE Account a SET a.status = 'FROZEN' WHERE a.lastActivityDate < :cutoff")
int freezeDormant(@Param("cutoff") LocalDate cutoff);
}
What should a senior engineer know?
A senior engineer knows that @Modifying bulk operations do not trigger entity lifecycle callbacks (@PreUpdate, @PreRemove) or cascades, and they can leave stale entities in the persistence context. They set clearAutomatically and flushAutomatically on @Modifying to keep the context consistent. They know that native queries returning entities must select all mapped columns. They also know that for genuinely dynamic filters with varying conditions at runtime, they move to the Criteria API or Spring Data Specifications rather than building query strings.
package com.bank.repository;
import org.springframework.data.jpa.domain.Specification;
import com.bank.model.Account;
import java.math.BigDecimal;
// Specification for dynamic filters (alternative to building query strings)
public class AccountSpecs {
public static Specification<Account> hasStatus(String status) {
return (root, query, cb) -> cb.equal(root.get("status"), status);
}
public static Specification<Account> balanceAbove(BigDecimal min) {
return (root, query, cb) -> cb.greaterThan(root.get("balance"), min);
}
}
// Usage: repo.findAll(hasStatus("ACTIVE").and(balanceAbove(new BigDecimal("100000"))));
Visual Overview
Query Type Decision Tree
============================================
Start: What kind of query do I need?
|
v
Is it a simple finder (1-3 conditions)?
YES -> Derived query method
findByStatusAndBalanceGreaterThan()
|
NO
v
Do I need joins, projections, or ordering?
YES -> @Query with JPQL
@Query("SELECT a FROM Account a WHERE ...")
|
NO (need DB-specific feature?)
v
Do I need window functions, CTEs, hints?
YES -> @Query(nativeQuery = true)
Raw SQL, database-specific
|
NO (need bulk update/delete?)
v
Do I need to UPDATE or DELETE in bulk?
YES -> @Modifying + @Query
Bulk operation, bypasses persistence context
@Modifying Flow
============================================
flushAutomatically=true clearAutomatically=true
| |
v v
+--------------+ +--------+ +--------------+
| Flush pending|-->| Bulk |-->| Clear |
| changes to DB| | UPDATE | | persistence |
+--------------+ | DELETE | | context |
+--------+ +--------------+
Key Takeaway
Spring Data JPA gives Rajesh four main ways to write queries at National Bank of India, and the choice depends on complexity and portability. Derived query methods let Spring parse the method name into a query, so findByStatusAndBalanceGreaterThan generates the right JPQL automatically. They are wonderfully readable for simple queries but become unwieldy past three or four conditions. For anything more complex, the @Query annotation with JPQL gives explicit control over joins, ordering, and projections while keeping the method name clean.
When JPQL cannot express what the regulatory team needs, such as a PostgreSQL window function or a CTE, Rajesh drops to a native SQL query by setting nativeQuery=true. The trade-off is that he loses database portability and JPQL's entity awareness. Finally, @Query methods are read-only SELECTs by default, so for the nightly compliance job that freezes dormant accounts in one sweep, the @Modifying annotation is required along with a writable @Transactional.
The senior caution Pankaj always raises is that @Modifying bulk operations bypass the persistence context, entity lifecycle callbacks, and cascades. They can leave stale entities loaded in memory that still show the old status. That is why clearAutomatically and flushAutomatically are set on the @Modifying annotation, flushing pending changes before the bulk statement and clearing the context afterward so no stale version of a frozen account lingers.
Remember one sentence: derived methods handle simple lookups, JPQL handles custom queries, native SQL handles database-specific features, and @Modifying handles bulk updates that bypass the persistence context.
---