skip to content

How do you write a native SQL query with @Query, and what changes compared to JPQL?

level: middleimportance: must knowfreq 70%

answer

  1. nativeQuery=true → raw DB SQL, table/column names
  2. no startup validation, not portable
  3. native paging needs countQuery
  4. Sort not reliably applied to native
  5. use for window fns / vendor SQL / ON CONFLICT

basics

~20 s

Set nativeQuery=true on @Query and write real database SQL using table and column names instead of entity fields. It's useful for database-specific features JPQL can't express, but the query is not validated at startup and is less portable.

solid answer

~40 s

Adding `nativeQuery = true` to `@Query` tells Spring Data to pass the string straight to the database as raw SQL rather than treating it as JPQL. You then use real **table and column names**, and you can use vendor-specific syntax (window functions, hints, `ON CONFLICT`, full-text search) that JPQL doesn't support. Parameter binding still works with `:name`/`@Param` and `?1`. Key trade-offs: native SQL is **not parsed/validated at startup** (errors surface at runtime), it's **not portable** across databases, and native **pagination is limited** — with `Pageable` you usually must supply a `countQuery`, and dynamic sorting via `Sort` isn't reliably applied. The result maps back to the entity if columns match, or to an interface/DTO projection otherwise. Reach for native only when JPQL genuinely can't express what you need.

code

java · 12 lines
java
public interface UserRepository extends JpaRepository<User, Long> {

    @Query(value = "SELECT * FROM users WHERE email_address = :email",
           nativeQuery = true)
    Optional<User> findByEmailNative(@Param("email") String email);

    // Native pagination requires an explicit countQuery
    @Query(value = "SELECT * FROM users WHERE status = :status",
           countQuery = "SELECT count(*) FROM users WHERE status = :status",
           nativeQuery = true)
    Page<User> findByStatus(@Param("status") String status, Pageable pageable);
}

go deeper

for a junior

Knows nativeQuery=true means raw SQL with table/column names.

for a middle

Explains the trade-offs: no startup validation, portability loss, countQuery for paging.

for a senior

Discusses result mapping via projections/@SqlResultSetMapping and when native is justified.

for a principal

Weighs native SQL against JPQL/QueryDSL for portability, maintainability, and DB-feature access at architecture level.

## Turning on native SQL `@Query` has a boolean attribute `nativeQuery`. When `nativeQuery = true`, Spring Data does **not** interpret the string as JPQL — it hands the SQL to the JDBC driver essentially as-is (through Hibernate's native query support). You now write against the **physical schema**: real table names and column names. ```java @Query(value = "SELECT * FROM users WHERE email_address = :email", nativeQuery = true) Optional<User> findByEmailNative(@Param("email") String email); ``` Note `email_address` (column) and `users` (table) — the physical names, not the `User` entity / `emailAddress` field you'd use in JPQL. ## Why use native SQL - **Vendor-specific features** JPQL can't express: window functions (`ROW_NUMBER() OVER (...)`), CTEs, `INSERT ... ON CONFLICT`, database full-text search, optimizer hints, recursive queries. - **Performance tuning** that needs exact SQL control. - **Legacy schemas / complex reporting** where the object model is a poor fit. ## Parameter binding Binding is identical to JPQL: named (`:email` + `@Param("email")`) or positional (`?1`). `IN` clauses with collections work the same. ## What you lose vs JPQL 1. **No startup validation.** JPQL is parsed against the entity metamodel at boot; native SQL is opaque to Hibernate's parser, so a typo or bad column shows up only when the query runs. 2. **Portability.** JPQL is dialect-independent — Hibernate generates the right SQL per database. Native SQL is tied to one vendor's syntax. 3. **Pagination caveats.** With a `Pageable`, JPQL derives the count query automatically. For native queries Spring often needs an explicit `countQuery` attribute, and it cannot reliably inject dynamic `ORDER BY` from a `Sort`. Example: ```java @Query( value = "SELECT * FROM users WHERE status = :status", countQuery = "SELECT count(*) FROM users WHERE status = :status", nativeQuery = true) Page<User> findByStatus(@Param("status") String status, Pageable pageable); ``` 4. **Sorting.** Applying `Sort` to a native query is unreliable/unsupported; you typically hardcode `ORDER BY` or build SQL dynamically. ## Result mapping - If the selected columns line up with the entity's mapped columns, rows map back to the entity (`SELECT *` on the entity's table is the easy path). - For arbitrary column sets, use an **interface-based projection** (getters matching column aliases) or a `@SqlResultSetMapping` / `@NamedNativeQuery` for full control. - Scalar aggregates map to primitives/`Object[]`. ```java public interface UserSummary { Long getId(); String getEmailAddress(); } @Query(value = "SELECT id, email_address FROM users WHERE active = true", nativeQuery = true) List<UserSummary> findActiveSummaries(); ``` ## Gotchas - **Column aliases must match projection getters.** `email_address` column vs `getEmailAddress()` — for interface projections the alias should map to the property; alias explicitly (`AS emailAddress`) when needed. - **SQL injection**: always bind via parameters; never concatenate user input into the SQL string. - **Enums/booleans/dates** are bound as their JDBC representations — be mindful when the DB type differs from the Java type. ## When to choose native Default to JPQL for portability and startup safety. Escalate to native only for database features JPQL can't reach or performance-critical SQL you must control exactly.

  • Why does a native @Query with Pageable often need a countQuery?
    Spring can't reliably derive a count query from arbitrary native SQL the way it can from JPQL, so it needs you to supply the count statement explicitly to compute total pages.
  • Is a native SQL query validated at application startup?
    No. Hibernate parses JPQL against the metamodel at boot, but native SQL is passed through opaquely, so syntax or column errors surface only when the query executes.
  • Can you apply a dynamic Sort to a native query?
    Not reliably — Spring can't safely inject ORDER BY into arbitrary native SQL. You typically hardcode ORDER BY or build the SQL dynamically.

saying these in an interview costs you the question

  • Claiming native queries are validated at startup like JPQL
  • Using entity/field names inside a nativeQuery=true string
  • Assuming Pageable count and Sort work automatically for native queries
  • Concatenating user input into native SQL instead of binding parameters

context

resolving links…