Using a plain JPA EntityManager, how do you execute raw SQL, and what shape are the results depending on which createNativeQuery overload you call?

## 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.