Using a plain JPA EntityManager, how do you execute raw SQL, and what shape are the results depending on which createNativeQuery overload you call?
answer
- Three overloads: untyped / entity class / mapping name
- Single column → bare Object, not Object[]
- Entity mapping needs every mapped column present
- Already-managed id wins; row values discarded
- Positional ?1; never concatenate input
basics
~20 sentityManager.createNativeQuery(sql) returns untyped rows — each row is an Object[] of column values (or a single Object for one column). createNativeQuery(sql, Book.class) maps rows to managed entities. createNativeQuery(sql, "MappingName") uses a declared @SqlResultSetMapping for richer shapes.
solid answer
~50 sThree overloads, three result shapes: ```java // 1. untyped: Object[] per row (or a bare Object for a single column) List<?> rows = em.createNativeQuery( "select id, title from book where author_id = ?1") .setParameter(1, authorId).getResultList(); // 2. entity: rows become managed Book instances List<Book> books = em.createNativeQuery("select * from book where ...", Book.class) .getResultList(); // 3. named @SqlResultSetMapping: entities, scalars, DTOs, or a mix List<?> rows = em.createNativeQuery(sql, "BookWithSales").getResultList(); ``` Points that matter: - The return is a raw `Query`, never a `TypedQuery` — so no compile-time typing, and casting is on you. - Entities produced by overload 2 are **managed**: they join the persistence context, are dirty-checked, and if that id is already loaded, the existing instance is returned and the fresh row values are discarded. - The SQL must supply **every column the entity maps**, or loading fails. - Parameters are positional `?1` style; **never** concatenate user input into the SQL.
go deeper
Be able to write all three overload forms, bind a ?1 parameter, and say that untyped rows come back as Object[] in select order.
Explain the managed-entity consequences: all mapped columns required, results dirty-checked, and the identity map winning over freshly read values.
Add the operational picture — driver-dependent scalar types, no startup validation, DML bypassing the persistence context and caches, and injection risk with dynamic identifiers.
Set the policy for when native SQL is allowed at all, how such queries are tested against the real engine, and how their non-portability is contained in the codebase.
## Why native SQL exists in JPA JPQL is deliberately limited to what the object model can express portably. Window functions, recursive CTEs, vendor-specific operators, index hints, `INSERT ... SELECT`, full-text predicates — sooner or later something is unreachable. `createNativeQuery` is the escape hatch: you hand the provider raw SQL, it binds parameters, executes it through JDBC, and maps the `ResultSet` back according to what you asked for. ## Overload 1 — untyped rows ```java Query q = em.createNativeQuery("select id, title, price from book"); List<Object[]> rows = q.getResultList(); for (Object[] r : rows) { Long id = ((Number) r[0]).longValue(); ... } ``` Each row is an `Object[]` in **select-list order**. A single-column select yields bare objects, not one-element arrays — a special case that breaks naive code written against the array shape. Types come from the JDBC driver: a count may arrive as `BigInteger`, `Long` or `BigDecimal` depending on engine and driver version, which is why defensive code casts to `Number` first. This overload is fine for a quick aggregate and miserable for anything wider, because positional indexing into an untyped array rots the moment someone edits the select list. ## Overload 2 — entity results ```java List<Book> books = em.createNativeQuery("select * from book where price > ?1", Book.class) .setParameter(1, min).getResultList(); ``` The provider maps columns to the entity's mapped columns **by name**, then hydrates managed entities. Three consequences people trip over: **All mapped columns must be present.** `select id, title from book` into `Book.class` fails if `Book` also maps `price` and `author_id` — the provider looks for those columns in the `ResultSet` and does not find them. `select *` is the usual (blunt) answer; a `@SqlResultSetMapping` with explicit field results is the precise one. **Results are managed.** They land in the persistence context and are dirty-checked at flush, exactly like JPQL results. Modifying one writes an UPDATE. **The persistence context wins on conflict.** If an entity with that id is already managed, the provider returns the *existing instance* and drops the freshly read column values. So a native query cannot be used to "refresh" an already-loaded entity; you need `em.refresh(entity)` for that. This surprises people who reach for native SQL specifically to see the current database state. **Lazy associations still behave normally** — the returned entity's lazy relations are proxies as usual, so nothing about native SQL avoids the extra selects. ## Overload 3 — named result-set mapping When the result is a mix — an entity plus an aggregate, two entities per row, or a DTO — you declare a `@SqlResultSetMapping` and pass its name. That is where `@EntityResult`, `@ColumnResult` and `@ConstructorResult` come in. ## Parameters Native queries use **positional** parameters written `?1`, `?2` and bound with `setParameter(1, value)`. (Hibernate additionally tolerates `:name` in native SQL, but positional is the portable spelling.) The security rule is unconditional: never build the SQL by string concatenation of user input. A native query is exactly as injectable as any other SQL, and the ORM provides no protection once you have written the string yourself. Identifiers that genuinely must vary — a sort column, a table name — cannot be bound as parameters, so they must be validated against an allow-list of known-safe values. ## DML through native queries `executeUpdate()` runs native `INSERT`/`UPDATE`/`DELETE` and returns the affected row count. This bypasses the persistence context entirely: entities already loaded keep their old values, cascades and lifecycle callbacks do not run, and second-level caches can be left holding stale entries. Native DML belongs in narrow, deliberate places — a bulk archive job — and typically at the start of a fresh persistence context. ## Things native SQL does not get - **No startup validation.** The SQL is opaque to the provider; errors appear on first execution as a `SQLGrammarException` or a driver-specific message. - **No `TypedQuery`.** The API returns raw `Query` for the SQL-string and mapping-name overloads. - **No portability.** The SQL binds you to the engine you wrote it for, including the type-mapping quirks of its driver. - **No pagination guarantee via the dialect.** `setFirstResult`/`setMaxResults` do work for native queries in modern Hibernate, but the dialect has to rewrite your SQL to apply them — which is another reason to keep the SQL simple. ## Rule of thumb Reach for JPQL first. Drop to native SQL when the query genuinely cannot be expressed otherwise or when the generated SQL is measurably wrong for the workload — and when you do, prefer an explicit result-set mapping over positional `Object[]` indexing, and prefer projecting into a DTO over hydrating entities you only intend to read.
- A native query selects fresh values for a row whose entity is already loaded in the persistence context. Which values does the returned object have?The already-managed instance is returned with its existing state, and the freshly read column values are discarded. The persistence context is an identity map, so it never replaces a managed instance from a query result — the same rule applies to JPQL. To pick up the database's current values you must call em.refresh on the entity, or run the query in a persistence context that has not loaded it.
- Why does createNativeQuery return a raw Query rather than a TypedQuery, and what do you do about it?The provider cannot infer a result type from an arbitrary SQL string plus a mapping name, so the API stays untyped and the cast is yours. In practice you either use the Class overload, which reliably yields entities of that type, or declare a @SqlResultSetMapping with a @ConstructorResult so each row arrives as a DTO you cast once. Hibernate's own Session API additionally exposes typed native-query creation.
saying these in an interview costs you the question
- Expecting each row of a single-column native query to be a one-element Object[]
- Selecting a subset of columns into an entity class and expecting the missing fields to be null or lazily loaded
- Assuming a native query refreshes entities already present in the persistence context
- Concatenating user input into the SQL string because 'the ORM escapes it'
- Believing native DML through executeUpdate updates in-memory entities or the second-level cache