A developer turns on Hibernate's query cache and marks a list query cacheable, but observes that on a cache hit the application still issues one SELECT per row. Explain why that happens and how to fix it.
answer
- Query cache stores ids, entity cache stores state
- Hit → resolve ids: context → L2 → database
- Uncached entity + query hit = N+1 selects
- Scalar projections cache values, no hydration
- Size entity region ≥ the query results backing it
basics
~20 sFor entity queries the query cache stores only identifiers. On a hit Hibernate must rebuild each entity, taking it from the entity second-level cache if present, otherwise loading it by id. Mark the returned entity types cacheable to remove those SELECTs.
solid answer
~50 sThe query cache stores the **identifiers** a query returned, not the row data. On a hit, Hibernate has a list of ids and must materialise entities for the current session, in this order: already in the persistence context, else the entity second-level cache region, else a database load **by id**. If the returned entity is not itself cached in L2, that third branch fires for every id — an N+1 pattern where the original query was a single round trip. Caching "worked" and made things worse. The fix is to cache the entity too: annotate it `@Cacheable` plus `@Cache(usage = ...)`, size its region for the working set, and confirm with statistics that the entity region shows hits. If caching that entity is not acceptable — too big, too volatile, correctness-sensitive — then the query should not be cacheable either. Scalar projections behave differently: they store the values themselves and need no hydration.
code
java · 9 lines@Entity
@Cacheable
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Product { @Id private Long id; private String name; }
List<Product> page = em.createQuery(
"select p from Product p where p.active = true", Product.class)
.setHint("org.hibernate.cacheable", true)
.getResultList();go deeper
Know that the query cache stores identifiers and that entities must also be cacheable, or Hibernate loads them one by one.
Walk the resolution order — persistence context, entity region, database — and explain why the design stores ids rather than rows.
Diagnose from SQL shape plus statistics, and reason about relative region sizing and independent eviction between the two regions.
Recognise the coupling as a design constraint: adopting the query cache commits you to caching the underlying entities, which is a correctness decision, not just a tuning one.
## The two-layer design Hibernate deliberately keeps result caching and state caching separate. The query results region maps a query key to a list of **identifiers** (plus a timestamp). The entity region maps an identifier to **dehydrated state**. Nothing in the query region contains column values for entity queries. The motivation is consistency and memory. If the query cache stored full rows, the same entity appearing in five cached queries would be duplicated five times, and updating it would require finding and rewriting every copy. Storing ids means there is exactly one authoritative cached copy of an entity's state — in the entity region — and query results are just pointers into it. ## What a hit actually does On a cacheable query execution: 1. Build the cache key from the query, its bind parameters, paging and related context. 2. Look it up. If absent → miss, run SQL, store ids + timestamp. 3. If present, validate it against the update-timestamps region; if a queried table changed after the stored timestamp, treat it as a miss. 4. If still valid, **resolve each identifier into a managed entity**: persistence context first, then the entity second-level cache, then a database load by id. Step 4 is where the surprise lives. With the entity not cached in L2, a hit on a 200-row query produces 200 `select … where id = ?` statements. The original uncached path was one statement. Adding a cache made the workload strictly worse, plus serialization and memory overhead. ## Fixing it **Cache the returned entity.** Add `@Cacheable` and `@Cache(usage = …)` to every entity type a cacheable query can return, and size its region to hold the rows those queries touch. If the query returns 5,000 distinct products but the region holds 500, most ids still miss and you have simply moved the N+1 problem into the cache layer. **Or use a scalar projection.** `select p.id, p.name from Product p` caches the actual values, so a hit needs no hydration at all. This is a good fit for read-only list views that do not need managed entities — and it sidesteps the entity-region requirement entirely. **Or do not cache that query.** If the entity is too volatile or too large to cache, the query cache is not the right tool for it. ## Diagnosis The symptom is easy to confuse with a lazy-loading problem, so check the shape of the SQL. Hydration misses look like a burst of identical `select … from product where id=?` statements with different bind values, immediately after a query that emitted no SQL of its own — that absence is the tell that the query cache hit while the entity region missed. Statistics confirm it: `getQueryCacheHitCount()` climbing while the entity region's `getMissCount()` climbs in lockstep. Fix the entity region and the second counter drops toward zero. ## Related traps in the same area - **Collections.** A cacheable query returning entities with cached collections still needs those collection regions enabled; otherwise the collections load individually on access. Collection regions cache the member identifiers, so the same id-resolution logic applies one level down. - **Paging.** `firstResult`/`maxResults` are part of the query cache key, so page 1 and page 2 are separate entries and neither helps the other. - **Eviction skew.** The query region and entity region evict independently. A query result can survive while the entities it points to have been evicted, silently reintroducing the per-id loads. That is a sizing question: the entity region must be at least as generously sized as the query results it backs. ## The rule to remember The query cache accelerates *finding* rows; the entity cache accelerates *reading* them. Enabling the first without the second buys the lookup and pays for the read many times over.
- Why does Hibernate store identifiers rather than full rows in the query cache?To keep a single authoritative copy of each entity's state. If rows were duplicated into every cached query result, one update would have to locate and rewrite every copy, and two cached queries could disagree about the same entity. Storing ids means query results are pointers into the entity region, so entity state is maintained in exactly one place and memory is not multiplied by the number of queries returning the same row.
- How would you get caching benefit for a read-only list screen without caching the entity at all?Use a scalar or DTO projection — select the specific columns rather than the entity — and mark that query cacheable. Projections store the actual values in the query results region, so a hit needs no hydration and no entity region. The trade-off is that you get detached values rather than managed entities, which is usually exactly right for a read-only screen.
Caching the search results as a list of book call numbers helps only if the books are still on the nearby shelf; otherwise you make one trip to the basement per book.
saying these in an interview costs you the question
- Believing the query cache stores full result rows for entity queries
- Enabling the query cache without caching the returned entity types
- Blaming lazy loading for the per-id SELECT burst
- Sizing the entity region smaller than the query results that point into it
- Assuming paged queries share one cache entry