Definition
- @QueryHints – attaches JPA or Hibernate query hints to a specific repository method, tuning that query's execution without changing what it returns.
- org.hibernate.readOnly – loads entities without dirty-check snapshots, saving memory and guaranteeing no accidental flush.
- org.hibernate.cacheable – routes the query through Hibernate's query cache so repeated identical queries return cached results.
- org.hibernate.fetchSize – sets the JDBC fetch size, controlling how many rows the driver pulls per round trip from the database.
- jakarta.persistence.query.timeout – aborts a query that runs longer than N milliseconds, protecting the system from runaway reports.
- Method vs transaction level – a query hint affects one query; @Transactional(readOnly=true) affects every query in the transaction and sets the JDBC connection read-only.
Query hints are special instructions National Bank of India pins to a single request slip. The readOnly note tells the clerk not to bother snapshotting for changes. The cacheable note says if you fetched this exact list recently, reuse it. The fetchSize note says bring records in bundles of a thousand instead of all at once. The timeout note says if this takes longer than three seconds, give up. Pinning the note to one slip (method level) affects just that request; posting it on the department door (transaction level) affects everything during the whole visit.
Understanding the Concepts
What are @QueryHints?
@QueryHints is an annotation you place on a Spring Data repository method to attach JPA or Hibernate query hints. Each hint is a name-value pair that tunes how that specific query executes. The hints do not change the query's result set; they control performance characteristics like caching, memory, fetch size, and timeouts.
package com.bank.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.QueryHints;
import jakarta.persistence.QueryHint;
import com.bank.model.Account;
import java.util.List;
public interface AccountRepository extends JpaRepository<Account, String> {
@QueryHints({
@QueryHint(name = "org.hibernate.readOnly", value = "true"),
@QueryHint(name = "jakarta.persistence.query.timeout", value = "3000")
})
List<Account> findByBranchCode(String branchCode);
}
Expected behaviour:
- Entities returned by findByBranchCode are loaded read-only (no snapshot).
- If the query takes longer than 3000ms, it is aborted.
- Other queries in the same transaction are NOT affected by these hints.
What does the readOnly hint do?
The org.hibernate.readOnly hint loads the returned entities without taking a dirty-check snapshot. Hibernate will not attempt to flush changes for these entities, even inside a writable transaction. This roughly halves the memory per entity and is more granular than marking the whole transaction read-only.
package com.bank.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.QueryHints;
import jakarta.persistence.QueryHint;
import com.bank.model.Account;
import java.util.List;
public interface AccountRepository extends JpaRepository<Account, String> {
@QueryHints(@QueryHint(name = "org.hibernate.readOnly", value = "true"))
List<Account> findByBranchCode(String branchCode);
}
package com.bank.service;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.bank.repository.AccountRepository;
import com.bank.model.Account;
import java.util.List;
@Service
public class ReportService {
private final AccountRepository accountRepository;
public ReportService(AccountRepository accountRepository) {
this.accountRepository = accountRepository;
}
@Transactional // writable transaction overall
public void generateReport(String branchCode) {
// These accounts are loaded read-only even in a writable TX
List<Account> accounts = accountRepository.findByBranchCode(branchCode);
// accounts are read-only: no snapshot, no dirty check
// Other entities in this transaction ARE still tracked for changes
}
}
Memory comparison for 10,000 Account entities:
- Without readOnly hint: ~80MB (entities + snapshots)
- With readOnly hint: ~45MB (entities only, no snapshots)
What does the cacheable hint do?
The org.hibernate.cacheable hint routes the query through Hibernate's query cache. If the exact same query with the same parameters was executed recently, Hibernate returns the cached result without hitting the database. The query cache must be enabled in configuration, and it works best with a second-level entity cache for rarely-changing data.
package com.bank.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.QueryHints;
import jakarta.persistence.QueryHint;
import com.bank.model.BranchCode;
import java.util.List;
public interface BranchCodeRepository extends JpaRepository<BranchCode, String> {
@QueryHints(@QueryHint(name = "org.hibernate.cacheable", value = "true"))
List<BranchCode> findAll();
}
# application.yml: enable query cache
spring:
jpa:
properties:
hibernate:
cache:
use_query_cache: true
use_second_level_cache: true
javax:
cache:
provider: org.ehcache.jsr107.EhcacheCachingProvider
Query cache behaviour:
- First call: hits database, stores result in cache
- Second call: returns cached result, no database hit
- After entity update: cache invalidated automatically
- Best for: rarely-changing reference data (branch codes, currency codes)
What does the fetchSize hint do?
The org.hibernate.fetchSize hint sets the JDBC fetch size, controlling how many rows the driver pulls per round trip. For large result sets, a fetch size of 1000 means the driver fetches 1000 rows at a time instead of loading everything into memory at once. Combined with a Stream return type and readOnly, it enables memory-safe processing of millions of rows.
package com.bank.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.QueryHints;
import jakarta.persistence.QueryHint;
import com.bank.model.Transaction;
import java.util.stream.Stream;
public interface TransactionRepository extends JpaRepository<Transaction, Long> {
@QueryHints({
@QueryHint(name = "org.hibernate.fetchSize", value = "1000"),
@QueryHint(name = "org.hibernate.readOnly", value = "true")
})
@Query("SELECT t FROM Transaction t WHERE t.branchCode = :branch")
Stream<Transaction> streamByBranchCode(String branch);
}
package com.bank.service;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.bank.repository.TransactionRepository;
@Service
public class ExportService {
private final TransactionRepository transactionRepository;
public ExportService(TransactionRepository transactionRepository) {
this.transactionRepository = transactionRepository;
}
@Transactional(readOnly = true)
public void exportTransactions(String branchCode) {
try (var stream = transactionRepository.streamByBranchCode(branchCode)) {
stream.forEach(txn -> {
// Process each transaction without loading all into memory
writeToFile(txn);
});
}
}
private void writeToFile(Object txn) { /* write logic */ }
}
Fetch size comparison for 1,000,000 transactions:
- Default fetch size (10): 100,000 round trips -> very slow
- fetchSize=1000: 1,000 round trips -> fast, memory flat
- fetchSize=1000000: 1 round trip -> OOM risk!
What does the query timeout hint do?
The jakarta.persistence.query.timeout hint sets a timeout in milliseconds for a single query. If the query exceeds this time, it is aborted and a QueryTimeoutException is thrown. This protects the system from a runaway report locking resources and exhausting the connection pool.
package com.bank.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.QueryHints;
import jakarta.persistence.QueryHint;
import com.bank.model.Account;
import java.util.List;
public interface AccountRepository extends JpaRepository<Account, String> {
@QueryHints(@QueryHint(name = "jakarta.persistence.query.timeout", value = "3000"))
@Query("SELECT a FROM Account a WHERE a.holderName LIKE :pattern")
List<Account> searchByName(String pattern);
}
Timeout behaviour:
- Query completes in < 3000ms -> results returned normally
- Query exceeds 3000ms -> QueryTimeoutException thrown
- Connection is released, not stuck waiting for a slow query
- Essential for customer-facing search endpoints
How do method-level and transaction-level settings differ?
A method-level @QueryHint affects exactly that one query. A transaction-level setting like @Transactional(readOnly=true) or @Transactional(timeout=10) affects every query in the transaction. The two compose: you can run a writable transaction overall but mark one heavy query read-only via a hint.
| Aspect | Method-level @QueryHints | Transaction-level @Transactional |
|---|---|---|
| Scope | One query | All queries in TX |
| readOnly | Only returned entities | All entities + JDBC flag |
| timeout | One query timeout | Entire TX timeout |
| JDBC connection | Not affected | Set to read-only |
| Replica routing | No | Yes (if pool supports it) |
package com.bank.service;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.bank.repository.AccountRepository;
import com.bank.repository.TradeRepository;
@Service
public class SettlementService {
private final AccountRepository accountRepository;
private final TradeRepository tradeRepository;
public SettlementService(AccountRepository accountRepository, TradeRepository tradeRepository) {
this.accountRepository = accountRepository;
this.tradeRepository = tradeRepository;
}
@Transactional(timeout = 30) // TX-level: 30-second overall timeout
public void settleAndReport(String branchCode) {
// Writable operations here...
tradeRepository.settlePending(branchCode);
// Method-level readOnly hint: only this query's entities skip dirty checking
var accounts = accountRepository.findByBranchCode(branchCode);
// accounts are read-only, trades are still tracked for changes
}
}
What are common mistakes with @QueryHints?
The most common mistake is enabling the query cache on a high-churn table, which causes constant cache invalidation and adds overhead instead of saving it. A second mistake is setting a large fetchSize without a streaming return type, which loads all rows into memory anyway.
// WRONG: cacheable hint on a high-churn table
package com.bank.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.QueryHints;
import jakarta.persistence.QueryHint;
import com.bank.model.Transaction;
import java.util.List;
public interface BadCacheRepository extends JpaRepository<Transaction, Long> {
@QueryHints(@QueryHint(name = "org.hibernate.cacheable", value = "true"))
List<Transaction> findByAccountId(String accountId);
// Transactions change constantly -> cache invalidated on every write
// Net effect: overhead of cache management with zero benefit
}
// CORRECT: cacheable hint on rarely-changing reference data
package com.bank.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.QueryHints;
import jakarta.persistence.QueryHint;
import com.bank.model.BranchCode;
import java.util.List;
public interface GoodCacheRepository extends JpaRepository<BranchCode, String> {
@QueryHints(@QueryHint(name = "org.hibernate.cacheable", value = "true"))
List<BranchCode> findAll();
// Branch codes change once a quarter -> cache stays valid for weeks
}
What should a senior engineer know?
A senior engineer always sets a query timeout on customer-facing endpoints because one slow query without a timeout can cascade into a pool exhaustion outage. They know that fetchSize behaviour is driver-specific: PostgreSQL requires autocommit off (which is the case inside a @Transactional) to honour it. They know that the query cache only pays off when the underlying entities are also cached and the data is genuinely read-mostly. They also know that readOnly at the method level is a great default for list endpoints because it halves entity memory.
package com.bank.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.QueryHints;
import jakarta.persistence.QueryHint;
import com.bank.model.Account;
import java.util.List;
public interface AccountRepository extends JpaRepository<Account, String> {
// Senior-level defaults: readOnly + timeout on every list endpoint
@QueryHints({
@QueryHint(name = "org.hibernate.readOnly", value = "true"),
@QueryHint(name = "jakarta.persistence.query.timeout", value = "5000")
})
List<Account> findByBranchCode(String branchCode);
}
Visual Overview
@QueryHints vs @Transactional Scope
============================================
@Transactional(readOnly=true, timeout=30)
+-------------------------------------------------------+
| ALL queries in this transaction: |
| - JDBC connection = read-only |
| - FlushMode = MANUAL (no dirty check for ANY entity) |
| - TX timeout = 30 seconds total |
| |
| @QueryHints(readOnly, timeout=3000) |
| +------------------------------------------+ |
| | THIS query only: | |
| | - Entities loaded without snapshot | |
| | - Query aborts after 3 seconds | |
| +------------------------------------------+ |
| |
| Other queries in same TX: |
| - Use TX-level settings (readOnly, 30s timeout) |
| - NOT affected by method-level hints |
+-------------------------------------------------------+
fetchSize Streaming Flow
============================================
Driver fetches rows in chunks:
Application JDBC Driver Database
| | |
|--- next() -------->| |
| |--- FETCH 1000 ---->|
|<-- 1000 rows ------|<-- 1000 rows ------||
|--- process ------->| |
|--- next() -------->| |
| |--- FETCH 1000 ---->|
|<-- 1000 rows ------|<-- 1000 rows ------|
| (memory flat) | |
Key Takeaway
Query hints let Rajesh tune an individual query's execution at National Bank of India without changing what it returns. The readOnly hint loads entities without dirty-check snapshots, roughly halving their memory footprint and guaranteeing they are never flushed as updates. The cacheable hint routes a query through Hibernate's query cache, ideal for the branch-code lookup that is read constantly and almost never changes. The fetchSize hint controls how many rows the JDBC driver pulls per round trip, essential for the regulatory export that streams millions of transactions without blowing up the heap. The query timeout hint aborts a slow query, protecting the system from a runaway search exhausting the connection pool.
The method-level versus transaction-level distinction matters. A query hint affects exactly one query, while @Transactional(readOnly=true) affects every query in the transaction and additionally sets the JDBC connection read-only for replica routing. They compose cleanly: Rajesh can run a writable transaction overall yet mark one expensive reporting query read-only through a hint, and he can bound a single query with a 3-second timeout while bounding the whole transaction with a 30-second timeout.
The senior cautions Pankaj gives are that the query cache only pays off when the data is genuinely read-mostly, that fetchSize behaviour is driver-specific and PostgreSQL needs autocommit off to honour it, and that a query timeout on every customer-facing endpoint is not optional because one slow wildcard search without a timeout can cascade into a pool exhaustion outage.
Remember one sentence: @QueryHints tunes one query with readOnly, cacheable, fetchSize, or timeout, while @Transactional sets the policy for the whole transaction.
---