skip to content

You added a closed interface projection but the SQL still selects all columns. What are the likely causes, and how do you guarantee column narrowing?

level: principalimportance: should knowfreq 30%

answer

  1. Narrowing = derived method + fully closed projection only
  2. Explicit @Query selects what you wrote (entity = all columns)
  3. One @Value/open getter kills narrowing
  4. EAGER assoc / @ManyToOne default can add joins
  5. Guarantee via 'select new DTO(...)' + verify SQL logs

basics

~20 s

Column narrowing only happens automatically for derived query methods over a closed projection. A manual @Query (JPQL or native) selects exactly what you wrote, an @Value getter makes it open, or fetching the entity then mapping defeats it. To guarantee narrowing, use a closed projection on a derived method or explicitly select only the needed columns.

solid answer

~50 s

Automatic column narrowing is a feature of derived query methods (or Query-by-Example / Querydsl) combined with a fully closed projection — Spring inspects the projection to build the SELECT. It does NOT apply when: (1) you write an explicit @Query — JPQL/native selects exactly the columns/aliases you specified, and if you select the entity Hibernate fetches all mapped columns; (2) any getter is @Value/open, forcing full-entity load; (3) an EAGER association or @Basic(fetch=EAGER)/large column forces loading; (4) the projection type isn't recognized because getter names don't match properties. To guarantee narrowing: keep the projection strictly closed and use a derived method, OR write an explicit constructor-expression/DTO @Query listing only the columns you want. Always confirm by enabling Hibernate SQL logging. At scale, don't rely on implicit narrowing for hand-written queries — make the SELECT explicit.

code

java · 10 lines
java
// NOT narrowed: selects the whole User entity, projection applied after load
@Query("select u from User u where u.active = true")
List<UserView> loadsEverything();

// Narrowed by construction: exactly two columns
@Query("select new com.example.UserDto(u.username, u.email) from User u where u.active = true")
List<UserDto> onlyTwoColumns();

// Narrowed automatically: derived method + closed projection
List<UserView> findByActiveTrue();   // UserView has only plain getters

go deeper

for a junior

Know that closed projections can fetch fewer columns.

for a middle

Know an @Value getter or explicit @Query can defeat narrowing.

for a senior

Enumerate the narrowing conditions and verify via SQL logs; use constructor expressions for certainty.

for a principal

Treat implicit narrowing as fragile on hot paths; standardize explicit DTO queries plus SELECT-shape tests, and account for EAGER associations.

## The promise and its boundaries Spring Data can generate a `SELECT` limited to the columns a **closed** projection references — but only under specific conditions. Interviewers use this scenario to test whether a candidate understands *where* the optimization applies. ### When narrowing DOES happen - **Derived query methods** (method-name queries like `findByActiveTrue`) returning a **fully closed** projection. Spring introspects the projection's getters, maps them to entity attributes, and emits a SELECT of just those columns. Query-by-Example and Querydsl integrations similarly cooperate with closed projections. ### When narrowing does NOT happen — common causes 1. **Explicit `@Query` selecting the entity.** `@Query("select u from User u where ...")` returns the managed entity; Hibernate fetches all its mapped (non-lazy) columns regardless of the projection return type wrapper. The projection is then applied over a fully-loaded entity, so no column savings. 2. **Native query** (`nativeQuery = true`): the SELECT list is literally what you wrote (`select *` selects everything). Narrowing is your responsibility. 3. **Open projection.** Any `@Value` SpEL getter forces loading the full backing entity. One open getter disables narrowing for the whole projection. 4. **EAGER fetch or forced-loaded columns.** An association mapped `FetchType.EAGER`, `@Basic(fetch = EAGER)` fields, or provider behavior can pull extra columns/joins. 5. **Getter-name mismatch.** If a getter name doesn't resolve to a property, Spring may fall back to loading the entity (or the projection simply won't map as expected). 6. **@ManyToOne default EAGER.** To-one associations are EAGER by default in JPA, so they may be joined/fetched unless the projection strategy prunes them. ### How to GUARANTEE only the columns you want - **Option A — closed projection + derived method.** Keep every getter plain and mapped; let Spring generate the query. Verify the emitted SQL. - **Option B — explicit constructor expression / DTO query.** Write the SELECT yourself so there's no ambiguity: ```java @Query("select new com.example.UserDto(u.username, u.email) from User u where u.active = true") List<UserDto> findActive(); ``` This selects exactly two columns by construction. - **Option C — native query with an explicit column list** and aliases matching the projection getters (never `select *`). - **Always verify** with `spring.jpa.properties.hibernate.format_sql`/`show_sql` or a SQL-logging profile — never assume. ## Terms - **Derived query method**: a repository method whose SQL is generated from its name. - **Constructor expression**: JPQL `select new FQCN(...)` that instantiates a DTO from selected columns. - **EAGER/LAZY**: association fetch timing; EAGER loads immediately (can add joins/columns). - **Managed entity**: fully-loaded, persistence-context-tracked object — loading it fetches all mapped non-lazy columns. ## Principal-level judgment Don't build performance-critical read paths on *implicit* narrowing that a future `@Query` refactor can silently break. Prefer explicit DTO constructor queries for hot paths, keep projections closed, cover the SELECT shape with an assertion or SQL-count test, and treat 'select the entity then wrap it' as a non-optimization.

  • Why does returning a closed projection from an explicit @Query that selects the entity NOT narrow the SELECT?
    Because the query selects the managed entity, Hibernate fetches all its mapped non-lazy columns; the projection is just applied over the already-loaded entity, so no columns are saved.
  • How would you prove to a teammate that narrowing is (or isn't) happening?
    Enable Hibernate SQL logging (show_sql/format_sql or a logging profile), run the method, and inspect the emitted SELECT column list — ideally assert it with a SQL-count/inspection test rather than eyeballing.
  • Give a way to force exactly the columns you want regardless of projection kind.
    Use a JPQL constructor expression: select new com.example.UserDto(u.a, u.b) ... — it selects precisely those columns and instantiates the DTO.

saying these in an interview costs you the question

  • Believing every closed projection always narrows the SQL, even with @Query
  • Not knowing @ManyToOne is EAGER by default
  • Assuming 'select the entity then wrap' saves columns
  • Trusting narrowing without checking the SQL logs

context

resolving links…