skip to content

What does @QueryHints do on a repository method, and what are the common hints (fetch size, readOnly, cacheable)?

level: middleimportance: should knowfreq 40%

answer

  1. @QueryHints wraps @QueryHint name/value pairs
  2. fetchSize = JDBC rows per round trip
  3. readOnly = no dirty-check snapshot
  4. cacheable = query cache (needs it enabled)
  5. forCounting flag for Page count query

basics

~10 s

@QueryHints attaches JPA/Hibernate query hints to a repository query — like JDBC fetch size, marking results read-only, or enabling the query cache. It tunes how the query executes without changing what it returns.

solid answer

~40 s

`@QueryHints` (Spring Data) wraps one or more JPA `@QueryHint` key/value pairs and applies them to the query behind a repository method. The common Hibernate hints are: `org.hibernate.fetchSize` — the JDBC fetch size, i.e. how many rows the driver pulls per round trip (important for large reads/streaming); `org.hibernate.readOnly` — load entities in read-only mode so Hibernate keeps no dirty-checking snapshot, saving memory and skipping flush for those entities; and `org.hibernate.cacheable` — allow the query to use the second-level query cache (only effective if the query cache is actually configured). Hibernate 6 exposes these as constants on `AvailableHints` (e.g. `HINT_FETCH_SIZE`, `HINT_READ_ONLY`, `HINT_CACHEABLE`). `@QueryHints` also has a `forCounting` flag controlling whether the hints apply to the count query used during pagination (default true).

code

java · 13 lines
java
public interface OrderRepository extends JpaRepository<Order, Long> {

    @QueryHints(value = {
            @QueryHint(name = "org.hibernate.fetchSize", value = "1000"),
            @QueryHint(name = "org.hibernate.readOnly", value = "true")
        },
        forCounting = false)
    @Query("select o from Order o where o.status = :status")
    List<Order> findAllByStatusReadOnly(@Param("status") OrderStatus status);

    @QueryHints(@QueryHint(name = "org.hibernate.cacheable", value = "true"))
    List<Order> findByRegion(String region); // only helps if query cache is enabled
}

go deeper

for a junior

Know @QueryHints exists to tune query execution (fetch size, read-only, cache).

for a middle

Explain each of the three hints and pair readOnly with @Transactional(readOnly=true).

for a senior

Reason about forCounting on paged queries and the MySQL Integer.MIN_VALUE fetch-size quirk for streaming.

for a principal

Set fetch-size/read-only policy for large-read code paths and decide when query caching genuinely pays off versus causing churn.

## What a query hint is A **query hint** is a non-semantic instruction to the JPA provider (Hibernate) about *how* to run a query — it never changes the result set, only execution behavior (fetching, caching, dirty-checking). In plain JPA you call `query.setHint(key, value)`. Spring Data lets you declare them on the repository method. ## `@QueryHints` and `@QueryHint` - `jakarta.persistence.QueryHint` is a single `name`/`value` pair. - `org.springframework.data.jpa.repository.QueryHints` is the Spring Data container annotation holding an array of `@QueryHint` and applied to a repository method (works with derived queries, `@Query`, etc.). - Attribute **`forCounting`** (default `true`): when a method returns a `Page`, Spring Data issues a separate `count(*)` query. `forCounting` decides whether the same hints are applied to that count query too; set `false` to skip them there. ## The three headline Hibernate hints 1. **`org.hibernate.fetchSize`** (`AvailableHints.HINT_FETCH_SIZE`) — sets JDBC `Statement.setFetchSize(n)`: how many rows the driver buffers per network round trip. Default driver behavior varies (e.g. Oracle=10). Larger values reduce round trips for big reads; it is essential when **streaming** large result sets so the driver doesn't try to materialize everything. Note: on **MySQL**, true row-by-row streaming requires `Integer.MIN_VALUE`, not a positive number — a driver-specific quirk. 2. **`org.hibernate.readOnly`** (`HINT_READ_ONLY`) — loads returned entities into the persistence context as **read-only**: Hibernate stores no loaded-state snapshot, so there is no dirty checking and no automatic UPDATE at flush. This cuts memory and CPU for read-heavy/reporting queries. Combine with `@Transactional(readOnly = true)`. 3. **`org.hibernate.cacheable`** (`HINT_CACHEABLE`) — marks the query eligible for the **query cache** (which caches result-*id* lists keyed by query+params). It only helps if the second-level cache provider AND the query cache are enabled (`hibernate.cache.use_query_cache=true`); otherwise the hint is a silent no-op. Best for small, rarely-changing, frequently-run queries; wrong use invalidates the cache constantly. ## Hibernate 6 constants Older code used `org.hibernate.jpa.QueryHints`/`org.hibernate.annotations.QueryHints`; Hibernate 6 consolidates them on `org.hibernate.jpa.AvailableHints` (e.g. `HINT_FETCH_SIZE = "org.hibernate.fetchSize"`). The string values are stable, so hardcoded strings still work. ## Gotchas - Hints are **advisory**: an unrecognized or unsupported hint is typically ignored, not an error — so a typo silently does nothing. - `cacheable` without a configured query cache does nothing. - `readOnly` entities cannot be modified in that context; attempting to persist changes won't flush them. - `fetchSize` interacts with the DB driver; the ideal value is driver/workload specific. ## When to use Use `fetchSize` + `readOnly` for large exports/streaming, `readOnly` for any pure read to reduce overhead, and `cacheable` only for hot, stable lookup queries with the query cache configured.

  • What does the forCounting attribute of @QueryHints control?
    Whether the same hints are also applied to the separate count query Spring Data runs for Page-returning methods. Default true; set false to apply hints only to the main query, not the count.
  • Why might the cacheable hint have no effect?
    The Hibernate query cache must be explicitly enabled (second-level cache provider plus hibernate.cache.use_query_cache=true). Without it, the hint is silently ignored.

saying these in an interview costs you the question

  • Thinking a query hint changes which rows are returned
  • Believing cacheable works without configuring the query cache
  • Assuming a typo'd hint name raises an error rather than being silently ignored
  • Confusing fetchSize (JDBC rows per round trip) with the SQL result limit / page size

context

resolving links…