You lead a team on a JPA codebase where raw SQL through createNativeQuery is spreading. How do you decide which queries legitimately belong in raw SQL, and what do you put in place so those queries do not become a liability?
answer
- Native = local win, global cost
- Lost: startup validation, refactor safety, portability, provider awareness
- Legit: expressiveness, set-based bulk, measured plan problem
- Contain: integration test per query on real engine
- Named + centralised + DTO + declared query spaces
basics
~20 sAllow raw SQL where the object query language genuinely cannot express it or produces measurably wrong SQL — window functions, recursive CTEs, vendor features, set-based bulk work. Contain it: name and centralise the queries, map results into DTOs, and cover each one with integration tests on the real engine, since none of it is validated at startup.
solid answer
~60 s**Legitimate reasons to drop to SQL:** constructs JPQL cannot express (window functions, recursive CTEs, `INSERT ... SELECT`, full-text or geospatial predicates, vendor hints); set-based bulk work that would otherwise load rows to mutate them; and cases where the generated SQL is measurably wrong for the workload and no mapping change fixes it. **Illegitimate reasons:** unfamiliarity with JPQL, wanting a DTO (JPQL constructor expressions do that), or 'SQL is faster' with no measurement — the plan is usually identical. **Containment, because native SQL loses every guarantee JPQL has:** - No startup validation → **an integration test per query against the production engine and version**, in CI. - No portability → decide explicitly whether you support one engine; if not, isolate per-vendar variants behind named queries in mapping files. - Declare **synchronized query spaces** so flushing and cache invalidation stay correct and narrow. - Prefer `@ConstructorResult` DTOs over hydrating entities for read paths. - Parameters bound, never concatenated; dynamic identifiers validated against an allow-list. - Keep them **named and centralised**, not inline, so the inventory is reviewable during schema changes.
go deeper
Know that raw SQL is an escape hatch for things the object query language cannot express, and that it is not automatically faster.
List concrete legitimate cases and the guarantees you lose — startup validation, portability, typing — plus the DTO mapping habit for read paths.
Add the operational containment: integration tests on the real engine, declared query spaces, persistence-context hygiene around native DML, and injection review for dynamic identifiers.
Make it a written policy with a review gate, an explicit portability stance, and a plan for keeping the native-query inventory discoverable through schema migrations.
## Framing the decision Raw SQL inside an ORM is a capability, not a failure — but it is a **local optimisation with global costs**, and a lead's job is to price both. What you give up the moment a query becomes a native string: - **Startup validation.** JPQL is parsed against the mapping model when the persistence unit boots, so a renamed attribute fails the build. Native SQL is opaque and fails on first execution — possibly on a rare branch, in production. - **Refactor safety.** Renaming a column is a mapping change plus a search through SQL strings that no tool verifies. - **Portability.** The query binds to one engine's grammar, functions and driver type mappings. That may be fine; it must be a *decision*, not an accident. - **Provider awareness.** Hibernate no longer knows what the query touches, so its flush and query-cache invalidation go conservative (or wrong, if you declare spaces carelessly). - **Typing.** The API returns raw `Query`; result shapes are your responsibility. ## When it is genuinely right 1. **Expressiveness.** Window functions, recursive CTEs, lateral joins, `INSERT ... SELECT`, `MERGE`, full-text or geospatial predicates, vendor optimizer hints. Some of these have crept into modern HQL, so check before assuming. 2. **Set-based work.** Archiving or back-filling millions of rows by loading entities is the wrong shape entirely; a single statement that never materialises objects is right, with the persistence-context hygiene that implies. 3. **A measured plan problem.** The generated SQL is genuinely bad for this workload and no reasonable mapping or fetch change fixes it. The evidence should be an execution plan and a latency number, not an intuition. 4. **Reading a shape the object model does not have.** A dashboard aggregate joining five tables into fifteen columns is not an entity graph; forcing it through one is worse than writing the SQL. ## When it is not - "JPQL confuses me." That is a training cost, not an architecture decision. - "I need a DTO." JPQL constructor expressions already do that. - "Native SQL is faster." For the same query it usually produces the same plan. Measure first. - "I need to bypass the persistence context." Sometimes true, often a sign the unit of work is badly scoped. ## Containment: what you actually put in place **Integration tests on the real engine.** This is non-negotiable and replaces the startup validation you lost. Each native query gets a test executing it against the same engine *and major version* as production — a container in CI, not an in-memory database, whose dialect differences are exactly what will hide the bug. **Centralise and name them.** Native queries scattered inline are invisible during a schema migration. As named native queries, or in a small number of clearly-named files, they form an inventory you can grep before dropping a column. **DTOs by default.** Use `@SqlResultSetMapping` with `@ConstructorResult` for read paths. Entity results drag in the full persistence-context contract — all mapped columns required, dirty checking, identity-map precedence over freshly read values — for output nobody intends to modify. Pin `type` on numeric column results so a driver upgrade does not produce a `ClassCastException`. **Declare synchronized query spaces.** Otherwise every native query flushes the whole persistence context and churns the query cache; declared too narrowly, it reads stale data. Make the declaration part of the review checklist. **Security posture.** Bound parameters only. Dynamic identifiers — sort columns, table suffixes — validated against an allow-list, never interpolated. A native string is the one place in an ORM codebase where injection is fully in play, so it deserves explicit review attention. **Persistence-context hygiene around native DML.** Flush before, execute, then clear or evict; version columns are not bumped for you and lifecycle callbacks do not run. **A portability decision, written down.** If you support one engine, say so and stop paying for abstraction you do not use. If you support several, native queries need per-vendor variants — externalised in mapping files selected per deployment — and the maintenance cost of that must be accepted deliberately. ## Governance without bureaucracy A workable rule: native SQL is allowed, requires a one-line comment stating *why* JPQL was insufficient, and ships with a test. That single sentence in review kills most of the illegitimate cases without slowing the legitimate ones, and it leaves a trail for the person who reads the query two years later. The failure mode to actually fear is not one hand-written query — it is a codebase where half the data access is opaque strings that no build step checks, no test exercises, and no one dares change.
- What replaces the startup validation you lose when a query moves from JPQL to native SQL?Executing it in an integration test against the same database engine and major version as production, run in CI on every change. In-memory substitutes are actively misleading here because the errors are dialect-specific and are precisely what the substitute hides. Booting the persistence unit still catches a missing @SqlResultSetMapping name, but never the SQL body.
- A developer argues a native query is faster than the JPQL equivalent. How do you evaluate that?Ask for the two generated statements and their execution plans side by side. If the SQL is effectively the same, the plan and the latency will be too, and the real difference is usually elsewhere — hydrating entities instead of projecting a DTO, an N+1 pattern, or a missing index. If the generated SQL is genuinely worse, first try the mapping-level fixes, and accept native SQL when none of them close the gap.
- How do you keep native queries from blocking a schema change?Make them discoverable and testable: named native queries or a small number of clearly-named mapping files rather than strings inline across the codebase, so a column rename starts with a grep over a known inventory. Then rely on the integration tests to fail loudly for anything the grep missed. Without both, a schema migration turns into an archaeology exercise.
saying these in an interview costs you the question
- Treating native SQL as inherently faster than the equivalent generated statement without measuring
- Allowing native queries with no integration test, on the grounds that the code compiles
- Testing native SQL against an in-memory database that has different dialect behaviour from production
- Hydrating full entities for read-only report queries instead of projecting into a DTO
- Ignoring synchronized query spaces, so every native query flushes the whole persistence context and churns caches