What are @QueryHints?

Q: What are @QueryHints?

The @QueryHints annotation on a repository method passes optimization hints to Hibernate. These hints are applied to the specific Query object, not to the entire transaction. package com.bank.repository; import com.bank.model.Trade; import jakarta.persistence.QueryHint; import org.springframework.da

Q: How do you combine multiple hints?

Use @QueryHints with an array of @QueryHint annotations to apply multiple hints to one method. package com.bank.repository; import com.bank.model.Trade; import jakarta.persistence.QueryHint; import org.springframework.data.domain.Pageable; import org.springframework.data.jpa.repository.JpaRepository

Q: What are common mistakes with @QueryHints?

The most common mistake is confusing @QueryHints(readOnly) with @Transactional(readOnly=true) and expecting writes to work. package com.bank.service; import com.bank.model.Account; import com.bank.repository.AccountRepository; import org.springframework.stereotype.Service; import org.springframework

Definition

@QueryHints passes per-query optimization hints to Hibernate – readOnly skips dirty checking for that query, cacheable uses L2 cache, fetchSize controls JDBC batching, timeout cancels slow queries.

@QueryHints – annotates a Spring Data repository method with JPA/Hibernate hints applied to that specific query, not the entire transaction.
readOnly hint – org.hibernate.readOnly=true skips dirty checking and snapshot storage for entities loaded by this query. Same optimization as @Transactional(readOnly=true) but scoped to one query.
cacheable hint – org.hibernate.cacheable=true stores the result in the second-level query cache. Subsequent identical queries return cached results without hitting the database.
fetchSize hint – org.hibernate.fetchSize sets the JDBC fetch size (rows per network round-trip). Default is often 10-50; increase for large result sets.
timeout hint – jakarta.persistence.query.timeout cancels queries exceeding the specified milliseconds.

At National Bank of India, @Transactional(readOnly=true) tells the entire bank visit: "Rajesh is only here to read his statement – no transfers today." @QueryHints(readOnly=true) tells one specific clerk: "For this particular lookup only, do not prepare transfer forms." Rajesh can visit the balance clerk (readOnly) then the transfer clerk (writable) in the same visit. Hints are per-clerk instructions, not per-visit rules.

Understanding the Concepts

The @QueryHints annotation on a repository method passes optimization hints to Hibernate. These hints are applied to the specific Query object, not to the entire transaction.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
import jakarta.persistence.QueryHint;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.QueryHints;
import java.util.List;

public interface TradeRepository extends JpaRepository<Trade, String> {

    @QueryHints(@QueryHint(name = "org.hibernate.readOnly", value = "true"))
    List<Trade> findByStatus(String status);
    // Entities from THIS query: no snapshot, no dirty checking
}

How does the readOnly hint work?

The readOnly hint tells Hibernate to skip snapshot storage and dirty checking for entities loaded by this specific query. Other queries in the same transaction still have full dirty checking.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
import jakarta.persistence.QueryHint;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.QueryHints;
import java.util.List;

public interface TradeRepository extends JpaRepository<Trade, String> {

    @QueryHints(@QueryHint(name = "org.hibernate.readOnly", value = "true"))
    List<Trade> findByStatus(String status);
    // Entities loaded here: NO snapshot, NO dirty checking
    // Saves memory and CPU for large result sets
}

How does the cacheable hint work?

The cacheable hint stores the query result in Hibernate's second-level query cache. The first call executes SQL; subsequent identical calls return the cached result without a database hit.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
import jakarta.persistence.QueryHint;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.QueryHints;
import java.util.List;

public interface TradeRepository extends JpaRepository<Trade, String> {

    @QueryHints(@QueryHint(name = "org.hibernate.cacheable", value = "true"))
    List<Trade> findBySymbol(String symbol);
    // First call: SQL executes, result cached in L2 query cache
    // Next identical call: returns cached result, NO database hit
    // Requires: hibernate.cache.use_query_cache=true in configuration
}

YAML

# application.yml: enable query cache
spring:
  jpa:
    properties:
      hibernate:
        cache:
          use_second_level_cache: true
          use_query_cache: true
          region:
            factory_class: org.hibernate.cache.jcache.JCacheRegionFactory

How does the fetchSize hint work?

The fetchSize hint controls how many rows the JDBC driver fetches per network round-trip. For large result sets, increasing fetchSize reduces round-trips.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
import jakarta.persistence.QueryHint;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.QueryHints;
import java.util.List;

public interface TradeRepository extends JpaRepository<Trade, String> {

    @QueryHints(@QueryHint(name = "org.hibernate.fetchSize", value = "200"))
    List<Trade> findByAccountId(String accountId);
    // JDBC driver fetches 200 rows per network round-trip
    // Default is often 10-50
    // For 10,000 rows: fetchSize=10 = 1000 round-trips
    //                  fetchSize=200 = 50 round-trips
}

How does the timeout hint work?

The timeout hint cancels queries that exceed a time limit. This prevents runaway queries from holding database connections indefinitely.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
import jakarta.persistence.QueryHint;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.QueryHints;
import java.time.LocalDate;
import java.util.List;

public interface TradeRepository extends JpaRepository<Trade, String> {

    @QueryHints(@QueryHint(
        name = "jakarta.persistence.query.timeout", value = "5000"))
    List<Trade> findBySettlementDateBetween(LocalDate from, LocalDate to);
    // If query takes > 5000ms: QueryTimeoutException thrown
    // Prevents connection pool exhaustion from slow queries
}

How is @QueryHints(readOnly) different from @Transactional(readOnly=true)?

@Transactional(readOnly=true) applies to the entire transaction – all queries skip dirty checking, and you cannot update any entity. @QueryHints(readOnly=true) applies to one query – only entities from that method skip dirty checking. Other queries in the same transaction work normally.

| Scope | @Transactional(readOnly=true) | @QueryHints(readOnly=true) |
|-------|------------------------------|---------------------------|
| Applies to | Entire transaction | Single query |
| Dirty checking | Skipped for ALL entities | Skipped for THIS query's entities |
| JDBC hint | Connection hinted read-only | No connection hint |
| Write safety | Writes silently ignored | Writes still work on other entities |

JAVA

package com.bank.service;

import com.bank.model.Account;
import com.bank.model.Trade;
import com.bank.repository.AccountRepository;
import com.bank.repository.TradeRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.time.LocalDate;

@Service
public class PortfolioReportService {

    private final TradeRepository tradeRepo;
    private final AccountRepository accountRepo;

    public PortfolioReportService(TradeRepository tradeRepo,
                                   AccountRepository accountRepo) {
        this.tradeRepo = tradeRepo;
        this.accountRepo = accountRepo;
    }

    @Transactional  // NOT readOnly: this transaction writes
    public void generateAndStamp(String accountId) {
        // Trades loaded with @QueryHints(readOnly=true)
        var trades = tradeRepo.findByStatus("SETTLED");
        // These Trade entities: NO snapshot, NO dirty checking

        // Account loaded normally (full dirty checking)
        Account account = accountRepo.findById(accountId).orElseThrow();
        account.setLastReportDate(LocalDate.now());
        // This Account IS dirty-checked. UPDATE generated.

        // If @Transactional(readOnly=true) were used instead:
        // account.setLastReportDate() would be SILENTLY IGNORED!
    }
}

How do you combine multiple hints?

Use @QueryHints with an array of @QueryHint annotations to apply multiple hints to one method.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
import jakarta.persistence.QueryHint;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.QueryHints;
import java.util.List;

public interface TradeRepository extends JpaRepository<Trade, String> {

    @QueryHints({
        @QueryHint(name = "org.hibernate.readOnly", value = "true"),
        @QueryHint(name = "org.hibernate.fetchSize", value = "200"),
        @QueryHint(name = "jakarta.persistence.query.timeout", value = "10000")
    })
    List<Trade> findLargeDataset(Pageable pageable);
    // readOnly: no snapshots
    // fetchSize: 200 rows per round-trip
    // timeout: cancel if > 10 seconds
}

What are common mistakes with @QueryHints?

The most common mistake is confusing @QueryHints(readOnly) with @Transactional(readOnly=true) and expecting writes to work.

JAVA

package com.bank.service;

import com.bank.model.Account;
import com.bank.repository.AccountRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;

@Service
public class QueryHintMistake {

    private final AccountRepository accountRepo;

    public QueryHintMistake(AccountRepository accountRepo) {
        this.accountRepo = accountRepo;
    }

    // WRONG: @Transactional(readOnly=true) but method writes
    @Transactional(readOnly = true)
    public void wrongReadOnly(String accountId, BigDecimal amount) {
        Account account = accountRepo.findById(accountId).orElseThrow();
        account.setBalance(amount);
        // Balance change is SILENTLY IGNORED! (readOnly TX)
    }

    // CORRECT: use @QueryHints(readOnly) for selective read-only
    @Transactional  // writable transaction
    public void correctSelectiveReadOnly(String accountId) {
        // Trades loaded readOnly via @QueryHints on the repo method
        var trades = accountRepo.findAll();  // readOnly hint on this method

        // Account can still be modified
        Account account = accountRepo.findById(accountId).orElseThrow();
        account.setBalance(new BigDecimal("400000.00"));
        // UPDATE generated (writable entity, writable TX)
    }
}

Visual Overview

DIAGRAM

  @QueryHints Scope:
  ┌─────────────────────────────────────────┐
  │  @Transactional (writable)                  │
  │                                               │
  │  ┌───────────────────────────────────┐  │
  │  │  Query 1: findByStatus()            │  │
  │  │  @QueryHints(readOnly=true)         │  │
  │  │  → Entities: NO snapshot            │  │
  │  └───────────────────────────────────┘  │
  │                                               │
  │  ┌───────────────────────────────────┐  │
  │  │  Query 2: findById()                │  │
  │  │  (no hint: full dirty checking)     │  │
  │  │  → Entity: snapshot + dirty check   │  │
  │  │  → account.setBalance() → UPDATE   │  │
  │  └───────────────────────────────────┘  │
  └─────────────────────────────────────────┘

Key Takeaway

@QueryHints applies optimization hints to a specific repository method, not the entire transaction. The readOnly hint tells Hibernate to skip dirty checking and snapshot storage for entities loaded by that query – saving CPU and memory while still allowing other queries in the same transaction to write data.

At National Bank of India, Rajesh's portfolio report service loads 50,000 trades with @QueryHints(readOnly=true) – no snapshots stored. In the same transaction, the account entity is loaded normally and its lastReportDate is updated through dirty checking. If @Transactional(readOnly=true) were used instead, the account update would be silently ignored.

Four key hints: readOnly skips dirty checking per-query, cacheable stores results in the second-level cache, fetchSize controls rows per JDBC round-trip (critical for large result sets), and timeout cancels slow queries to prevent connection pool exhaustion. Multiple hints combine with @QueryHints({@QueryHint(...), @QueryHint(...)}).

Remember one sentence: @QueryHints gives per-query granularity for optimizations that @Transactional(readOnly=true) applies to the entire transaction.

---