Spring Boot

Spring Boot Native Queries: @Query, Pagination, Projections and Modifying SQL

Use native SQL queries in Spring Data JPA the safe way: @Query(nativeQuery = true), countQuery pagination, interface and DTO projections, @Modifying with clearAutomatically, EntityManager for dynamic SQL, Testcontainers tests, and fixes for common errors.

What Is a Native Query?

A native query sends your SQL to the database through JDBC without Hibernate parsing it. Hibernate only uses entity metadata to map result columns back to the entity. What you give up compared with JPQL: - **Portability.** Your SQL is tied to one database dialect. - **Entity-aware sorting.** Dynamic `Sort` is not supported (see pagination below). - **Automatic persistence-context and cache handling.** Hibernate can't see which tables a native statement touches. Examples assume **PostgreSQL**, so the CTE, `INTERVAL`, `ILIKE` and `ON CONFLICT` syntax won't run unchanged on MySQL or Oracle. They target Spring Boot 3.2 and Hibernate 6.4. Check newer versions' release notes for changes.

Native Query or JPQL? Decision Table

Default to JPQL or derived queries, and move to native SQL only when a requirement forces it. | Requirement | JPQL / derived | Native SQL | |---|---|---| | Plain filters, joins, `JOIN FETCH` | Yes | Unnecessary | | Switch database later | Yes | Rewrite database-specific parts | | `Pageable` with `Sort` | Yes | No, use a fixed `ORDER BY` | | Window functions (`RANK`, `LAG`, `NTILE`) | No | Yes | | CTEs and recursive CTEs | Limited | Yes | | Upsert (`ON CONFLICT`, `MERGE`) | No | Yes | | Full-text search, JSON operators | No | Yes | | Stored procedures | Via `@Procedure` | Yes | | Hand-tuned SQL | No | Yes (prove it with `EXPLAIN ANALYZE`) | Cost of going native: - You maintain one SQL dialect and must test it on that database. - Result mapping is manual (projections, `@SqlResultSetMapping` or `Object[]`). - Hibernate can't track affected tables, so bulk statements need `clearAutomatically`. - User input must only enter through bind parameters.

Native Queries with @Query

With `nativeQuery = true` the string goes to JDBC untouched, so write **real table and column names** (`created_at`, not `createdAt`). Rules that trip people up: - **Entity return type needs every column.** `SELECT *` works. A partial column list fails with "column name ... was not found in this ResultSet", because Hibernate needs every column the entity maps. - **Enums.** A column mapped with `EnumType.STRING` is bound as a plain string, so pass `role.name()`. For a PostgreSQL native enum type, add `CAST(:role AS role_type)`. - **Casts and operators.** PostgreSQL's `::` cast and the `?` JSON operators collide with parameter syntax. Use `CAST(x AS type)` and functions such as `jsonb_exists` instead. - **Aliases on PostgreSQL.** Unquoted aliases fold to lower case (`signupRank` becomes `signuprank`). Interface projections normally still map, but the projection test below proves it for your setup. The repository snippets on this page are all methods of the same repositories, shown in the section where they matter. The `Order` entity maps to the `orders` table (`order` is a reserved word).

Java

@Entity
@Table(name = "users")
public class User {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String email;
    @Enumerated(EnumType.STRING)
    private Role role;
    private boolean active;
    @Column(name = "last_login_at")
    private LocalDateTime lastLoginAt;
    @Column(name = "created_at")
    private LocalDateTime createdAt;
    // getters and setters omitted
}

public interface RankedUser {
    Long getId();
    String getName();
    Long getSignupRank();
}

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

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

    // call as: findActiveByRole(Role.ADMIN.name())
    @Query(value = """
            SELECT * FROM users
            WHERE role = :role AND active = true
            ORDER BY created_at DESC
            """, nativeQuery = true)
    List<User> findActiveByRole(@Param("role") String role);

    // Window function: no JPQL equivalent. Alias is signupRank, not rank:
    // RANK is a reserved word on MySQL 8.
    @Query(value = """
            SELECT u.id   AS id,
                   u.name AS name,
                   RANK() OVER (PARTITION BY u.role ORDER BY u.created_at) AS signupRank
            FROM users u
            WHERE u.active = true
            """, nativeQuery = true)
    List<RankedUser> findRankedUsers();

    @Query(value = """
            WITH recent_orders AS (
                SELECT customer_id, COUNT(*) AS order_count
                FROM orders
                WHERE created_at >= NOW() - INTERVAL '30 days'
                GROUP BY customer_id
            )
            SELECT u.* FROM users u
            JOIN recent_orders ro ON ro.customer_id = u.id
            WHERE ro.order_count >= :minOrders
            """, nativeQuery = true)
    List<User> findActiveCustomers(@Param("minOrders") int minOrders);

    // The classic mistake: entity field name instead of column name.
    // SELECT * FROM users ORDER BY createdAt   ->   ERROR: column "createdat" does not exist
}

Pagination: countQuery, Slice and Keyset

**Page needs a count query.** For a `Page` return type, Spring Data needs a second query for the total. For native queries it tries to derive one by rewriting your SQL, which is unreliable with joins, `GROUP BY`, `DISTINCT` or a CTE. A `GROUP BY` query without `countQuery` typically fails with "Query did not return a unique result: N results". Write `countQuery` by hand for anything beyond a single-table select. **Sorting is not dynamic.** The reference documentation says dynamic sorting is not supported for native queries, because Spring Data can't safely rewrite your SQL. Keep a fixed `ORDER BY` in the query and pass an **unsorted** `PageRequest`. This area has changed between Spring Data versions, so confirm the behaviour in yours. **Skip the count when you can.** Return `Slice` for infinite scroll and "load more". **Deep pages.** `OFFSET` gets slower the further you go. Keyset pagination avoids that and is easy in native SQL, as long as the `ORDER BY` matches the comparison exactly and includes a unique tiebreaker such as the id.

Java

public interface OrderRow {
    Long getOrderId();
    BigDecimal getTotal();
    String getCustomerName();
}

public interface RatedProduct {
    Long getProductId();
    String getName();
    Double getAvgRating();
    Long getReviewCount();
}

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query(value = """
            SELECT o.id AS orderId, o.total AS total, c.name AS customerName
            FROM orders o
            JOIN customers c ON c.id = o.customer_id
            WHERE c.country = :country AND o.status = :status
            ORDER BY o.created_at DESC, o.id DESC
            """,
           countQuery = """
            SELECT COUNT(*)
            FROM orders o
            JOIN customers c ON c.id = o.customer_id
            WHERE c.country = :country AND o.status = :status
            """,
           nativeQuery = true)
    Page<OrderRow> findPage(@Param("country") String country,
                            @Param("status") String status,
                            Pageable pageable);

    // No count query is run. Use for infinite scroll and "load more".
    @Query(value = """
            SELECT o.id AS orderId, o.total AS total, c.name AS customerName
            FROM orders o JOIN customers c ON c.id = o.customer_id
            WHERE o.status = :status
            ORDER BY o.created_at DESC, o.id DESC
            """, nativeQuery = true)
    Slice<OrderRow> findSlice(@Param("status") String status, Pageable pageable);

    // Keyset pagination: the client sends back the last (created_at, id) it saw.
    @Query(value = """
            SELECT * FROM orders
            WHERE (created_at, id) < (:lastCreatedAt, :lastId)
            ORDER BY created_at DESC, id DESC
            LIMIT :size
            """, nativeQuery = true)
    List<Order> findNextPage(@Param("lastCreatedAt") LocalDateTime lastCreatedAt,
                             @Param("lastId") Long lastId,
                             @Param("size") int size);
}

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    // GROUP BY + HAVING: the count query must count groups, not rows
    @Query(value = """
            SELECT p.id AS productId, p.name AS name,
                   CAST(COALESCE(AVG(r.rating), 0) AS double precision) AS avgRating,
                   COUNT(r.id) AS reviewCount
            FROM products p
            LEFT JOIN reviews r ON r.product_id = p.id
            WHERE p.category_id = :categoryId
            GROUP BY p.id, p.name
            HAVING COUNT(r.id) >= :minReviews
            ORDER BY avgRating DESC, p.name ASC
            """,
           countQuery = """
            SELECT COUNT(*) FROM (
                SELECT p.id
                FROM products p
                LEFT JOIN reviews r ON r.product_id = p.id
                WHERE p.category_id = :categoryId
                GROUP BY p.id
                HAVING COUNT(r.id) >= :minReviews
            ) grouped
            """,
           nativeQuery = true)
    Page<RatedProduct> findTopRated(@Param("categoryId") Long categoryId,
                                    @Param("minReviews") int minReviews,
                                    Pageable pageable);
}

// Call sites: pass an UNSORTED PageRequest.
//   orderRepository.findPage("DE", "SHIPPED", PageRequest.of(0, 20));
//
// Do not do this on a native query:
//   PageRequest.of(0, 20, Sort.by("createdAt"))

Interface Projections

Interface projections are the lowest-effort way to return partial columns. Spring Data reads each getter from the result tuple **by alias**, so the alias must equal the property name. - **A mismatched alias does not throw.** The getter silently returns `null`, the most common cause of "my projection is empty". - **Match types.** `COUNT` returns `Long`. `AVG` returns a numeric that is easiest to `CAST` to `double precision` in SQL. - **Keep projections flat.** Nested interface projections (a getter returning another interface) are not supported for native queries, because a native result set has no object graph.

Java

public interface UserSummary {
    Long getId();
    String getName();
    String getEmail();
    Long getOrderCount();
}

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    @Query(value = """
            SELECT u.id        AS id,
                   u.name      AS name,
                   u.email     AS email,
                   COUNT(o.id) AS orderCount
            FROM users u
            LEFT JOIN orders o ON o.customer_id = u.id
            GROUP BY u.id, u.name, u.email
            """, nativeQuery = true)
    List<UserSummary> findUserSummaries();

    // Alias typo: the getter returns null and no exception is thrown
    //   COUNT(o.id) AS order_count   ->   getOrderCount() == null
}

Class-Based DTOs with @SqlResultSetMapping

When you need a class or record instead of an interface, use `@SqlResultSetMapping` with `@ConstructorResult`, and run the named native query through `EntityManager`. The `@ColumnResult` names must match the column aliases, and the types must match the constructor parameters. Spring Data also supports named native queries on repositories, but wiring a result mapping to a DTO that is not the repository's entity type is version-dependent. The `EntityManager` route below always works.

Java

public record ProductStats(Long productId, String productName,
                           Long reviewCount, Double avgRating) { }

@Entity
@Table(name = "products")
@SqlResultSetMapping(
    name = "ProductStatsMapping",
    classes = @ConstructorResult(
        targetClass = ProductStats.class,
        columns = {
            @ColumnResult(name = "product_id",   type = Long.class),
            @ColumnResult(name = "product_name", type = String.class),
            @ColumnResult(name = "review_count", type = Long.class),
            @ColumnResult(name = "avg_rating",   type = Double.class)
        }))
@NamedNativeQuery(
    name = "Product.findStats",
    query = """
        SELECT p.id AS product_id,
               p.name AS product_name,
               COUNT(r.id) AS review_count,
               CAST(COALESCE(AVG(r.rating), 0) AS double precision) AS avg_rating
        FROM products p
        LEFT JOIN reviews r ON r.product_id = p.id
        GROUP BY p.id, p.name
        """,
    resultSetMapping = "ProductStatsMapping")
public class Product {
    @Id private Long id;
    private String name;
    // remaining fields omitted
}

@Repository
public class ProductStatsDao {

    @PersistenceContext
    private EntityManager em;

    @SuppressWarnings("unchecked")
    public List<ProductStats> findStats() {
        return em.createNamedQuery("Product.findStats").getResultList();
    }
}

Native Modifying Queries (UPDATE, DELETE, INSERT)

Native `UPDATE`, `DELETE` and `INSERT` statements need `@Modifying` and a transaction. They run directly against the database, and Hibernate can't see which tables they touch. That has three consequences: - **Stale entities.** An entity loaded earlier in the same transaction keeps its old state after a bulk update. - **`clearAutomatically = true`** clears the persistence context after the statement, fixing the stale read. But it **detaches every managed entity**, so unflushed changes to them are lost. - **`flushAutomatically = true`** flushes pending changes first. Use it together with `clearAutomatically`. Also: - Put `@Transactional` on the **service** method that owns the unit of work. The annotation on the repository method is a safety net. - Native DML invalidates the second-level cache, because Hibernate can't tell which entities changed. - **Upsert syntax varies:** PostgreSQL uses `ON CONFLICT`, MySQL uses `ON DUPLICATE KEY UPDATE`, and SQL Server and Oracle use `MERGE`. PostgreSQL's `ON CONFLICT` needs a unique constraint on the conflict columns.

Java

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Transactional
    @Query(value = "UPDATE users SET active = false WHERE last_login_at < :cutoff",
           nativeQuery = true)
    int deactivateInactiveUsers(@Param("cutoff") LocalDateTime cutoff);

    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Transactional
    @Query(value = """
            DELETE FROM users
            WHERE active = false AND created_at < :cutoff
            """, nativeQuery = true)
    int deleteOldInactiveUsers(@Param("cutoff") LocalDateTime cutoff);

    // PostgreSQL upsert. user_settings needs a unique constraint on (user_id, setting_key).
    @Modifying
    @Transactional
    @Query(value = """
            INSERT INTO user_settings (user_id, setting_key, setting_value)
            VALUES (:userId, :key, :value)
            ON CONFLICT (user_id, setting_key)
            DO UPDATE SET setting_value = EXCLUDED.setting_value,
                          updated_at    = NOW()
            """, nativeQuery = true)
    int upsertSetting(@Param("userId") Long userId,
                      @Param("key") String key,
                      @Param("value") String value);
}

@Service
public class UserCleanupService {

    private final UserRepository userRepository;

    public UserCleanupService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    @Transactional
    public void demo(Long id, LocalDateTime cutoff) {
        User user = userRepository.findById(id).orElseThrow();   // active = true
        userRepository.deactivateInactiveUsers(cutoff);          // row updated in the database

        // Without clearAutomatically: user.isActive() is still true here.
        // With it: user is detached, so any later user.setX(...) is never written.
        User fresh = userRepository.findById(id).orElseThrow();  // active = false
    }
}

EntityManager: Dynamic SQL

Use `EntityManager` when the SQL itself changes at runtime. The rule: **only structure may be concatenated, never values.** - Every user-supplied **value** goes through `setParameter`. - Anything that can't be a parameter, such as a **sort column**, must come from a whitelist. A column name taken straight from a request is SQL injection. - If the user isn't meant to type wildcards, escape `%` and `_` in `LIKE` input. - Keep sort direction on a whitelist too. Note that `Map.of(...)` rejects a `null` key, so `getOrDefault(null, ...)` throws `NullPointerException`. Check for null before the lookup.

Java

public interface ProductRepositoryCustom {
    List<Product> search(ProductFilter filter);
}

@Repository
public class ProductRepositoryCustomImpl implements ProductRepositoryCustom {

    // Whitelist: request value -> real column. Never append the raw request value.
    private static final Map<String, String> SORTABLE = Map.of(
            "name", "name",
            "price", "price",
            "created", "created_at");

    @PersistenceContext
    private EntityManager em;

    @Override
    @SuppressWarnings("unchecked")
    public List<Product> search(ProductFilter filter) {
        StringBuilder sql = new StringBuilder("SELECT * FROM products WHERE 1=1");
        Map<String, Object> params = new HashMap<>();

        if (filter.getName() != null) {
            sql.append(" AND name ILIKE :name");                  // ILIKE is PostgreSQL-only
            params.put("name", "%" + filter.getName() + "%");
        }
        if (filter.getMinPrice() != null) {
            sql.append(" AND price >= :minPrice");
            params.put("minPrice", filter.getMinPrice());
        }
        if (filter.getCategoryId() != null) {
            sql.append(" AND category_id = :categoryId");
            params.put("categoryId", filter.getCategoryId());
        }

        String sortKey = filter.getSortBy();
        String sortColumn = (sortKey == null) ? "name" : SORTABLE.getOrDefault(sortKey, "name");
        sql.append(" ORDER BY ").append(sortColumn)
           .append(" LIMIT :limit OFFSET :offset");

        Query query = em.createNativeQuery(sql.toString(), Product.class);
        params.forEach(query::setParameter);
        query.setParameter("limit", filter.getPageSize());
        query.setParameter("offset", filter.getOffset());
        return query.getResultList();
    }
}

// ProductRepository must extend ProductRepositoryCustom for Spring Data to pick up the Impl.
// For dynamic filters on many fields, also consider Specifications or Querydsl.

EntityManager: Stored Procedures and JDBC Batching

**Stored procedures.** `StoredProcedureQuery` is the JPA API. Use **positional** parameters: the PostgreSQL JDBC driver does not support named parameters on callable statements. A plain `CALL` string through `createNativeQuery` works on some drivers and fails on others. With PostgreSQL, procedures (as opposed to functions) may need `escapeSyntaxCallMode=callIfNoReturn` on the JDBC URL, so test this against a real database. **Bulk inserts.** For inserts that don't need entities, plain JDBC batching through `Session.doWork` or `JdbcTemplate.batchUpdate` is far cheaper than persisting thousands of managed entities. Remember that `doWork` bypasses the persistence context and Hibernate entirely. `Timestamp.valueOf(LocalDateTime)` uses the JVM time zone, so convert from UTC consistently. With the PostgreSQL driver, `reWriteBatchedStatements=true` on the URL lets it rewrite batches into multi-row inserts.

Java

@Repository
public class AuditLogDao {

    @PersistenceContext
    private EntityManager em;

    @Transactional
    public void recalculateUserStats(Long userId) {
        em.createStoredProcedureQuery("recalculate_user_stats")
          .registerStoredProcedureParameter(1, Long.class, ParameterMode.IN)   // positional
          .setParameter(1, userId)
          .execute();
    }

    @Transactional
    public void bulkInsertAuditLogs(List<AuditLog> logs) {
        String sql = "INSERT INTO audit_logs (user_id, action, created_at) VALUES (?, ?, ?)";
        em.unwrap(Session.class).doWork(connection -> {
            try (PreparedStatement ps = connection.prepareStatement(sql)) {
                int count = 0;
                for (AuditLog entry : logs) {
                    ps.setLong(1, entry.getUserId());
                    ps.setString(2, entry.getAction());
                    ps.setTimestamp(3, Timestamp.valueOf(entry.getCreatedAt()));
                    ps.addBatch();
                    if (++count % 50 == 0) {
                        ps.executeBatch();
                    }
                }
                ps.executeBatch();   // remainder
            }
        });
    }
}

Testing Native Queries: Dependencies

Native SQL is only as portable as your test database. H2 in PostgreSQL compatibility mode still rejects many `ON CONFLICT` cases, window frames, `tsvector` and JSON operators, so a green H2 test tells you very little. Run repository tests against the same engine you deploy to, using Testcontainers. You need `spring-boot-testcontainers`, the `junit-jupiter` module (which provides `@Testcontainers` and `@Container`) and the PostgreSQL module. Let your Boot version's BOM pick the versions. Artifact names changed in Testcontainers 2.x, so check the names it manages.

XML

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-testcontainers</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>junit-jupiter</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>   <!-- testcontainers-postgresql on Testcontainers 2.x -->
    <scope>test</scope>
</dependency>

Testing Native Queries: Test Class

`@ServiceConnection` (Spring Boot 3.1+) wires the container into the `DataSource`, and `@AutoConfigureTestDatabase(replace = NONE)` stops Spring swapping it for an embedded database. **The schema must exist.** `ddl-auto` is only `create-drop` by default for embedded databases, so with a container the tables won't exist unless you create them. Either run your Flyway migrations (the realistic choice) or set `ddl-auto=create-drop` as below, which needs an entity for every table the queries touch (here `orders`). Write two tests for every native query: 1. One proving that **projection aliases map to getters**, to catch the silent-null bug. 2. One proving that **bulk statements leave no stale entities** in the persistence context.

Java

@DataJpaTest(properties = "spring.jpa.hibernate.ddl-auto=create-drop")   // or rely on Flyway migrations
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@Testcontainers
class UserRepositoryNativeTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16");

    @Autowired
    UserRepository userRepository;

    @Test
    void projectionAliasesMapToGetters() {
        userRepository.saveAndFlush(user("a@example.com", "Ada"));

        List<UserSummary> rows = userRepository.findUserSummaries();

        assertThat(rows).hasSize(1);
        assertThat(rows.get(0).getName()).isEqualTo("Ada");
        assertThat(rows.get(0).getOrderCount()).isEqualTo(0L);   // null means an alias mismatch
    }

    @Test
    void bulkUpdateLeavesNoStaleEntity() {
        User saved = userRepository.saveAndFlush(user("b@example.com", "Bob"));
        saved.setLastLoginAt(LocalDateTime.now().minusYears(2));
        userRepository.saveAndFlush(saved);

        int updated = userRepository.deactivateInactiveUsers(LocalDateTime.now().minusYears(1));

        assertThat(updated).isEqualTo(1);
        assertThat(userRepository.findById(saved.getId()).orElseThrow().isActive()).isFalse();
    }

    private static User user(String email, String name) {
        User u = new User();
        u.setEmail(email);
        u.setName(name);
        u.setActive(true);
        u.setRole(Role.USER);
        u.setCreatedAt(LocalDateTime.now());
        return u;
    }
}

Troubleshooting Native Query Errors

Native queries fail at the database, not at startup, so mistakes show up as driver errors or silent nulls. | Symptom | Cause | Fix | |---|---|---| | `column "createdat" does not exist` | Entity field name used in SQL | Use the column name: `created_at` | | `The column name x was not found in this ResultSet` | Entity return type with a partial column list | Select every mapped column, or use a projection | | `Query did not return a unique result: 12 results` | Derived count query on a `GROUP BY` query | Write an explicit `countQuery` that counts groups | | Projection getter returns `null`, no exception | Alias doesn't match the getter (`order_count` vs `getOrderCount`) | Alias exactly as the property: `AS orderCount` | | `TransactionRequiredException: Executing an update/delete query` | `@Modifying` query without a transaction | `@Transactional` on the service method | | `could not determine data type of parameter $1` | Optional filter with a null bind: `(:name IS NULL OR name = :name)` | `(CAST(:name AS text) IS NULL OR name = CAST(:name AS text))` | | Entity shows old values after a bulk update | Native DML doesn't refresh the persistence context | `@Modifying(clearAutomatically = true, flushAutomatically = true)` | | Syntax error near `:` with a `::` cast | PostgreSQL cast collides with parameter syntax | Use `CAST(x AS type)` | | Syntax error with the JSON `?` operators | `?` is read as a positional parameter | Use functions such as `jsonb_exists` | | Sort ignored, or an error mentioning the sort property | `Sort` on `Pageable` with a native query | Fixed `ORDER BY`, whitelist-based dynamic SQL, or a custom repository | | Operator error on a native enum column | A String compared with a PostgreSQL enum | `CAST(:param AS your_enum_type)` | | `Named parameters not supported` for a procedure | PostgreSQL driver and named parameters | Use positional parameters | | `NullPointerException` in the sort whitelist | `Map.of` lookup with a null key | Check for null before the lookup |

Common Mistakes

- Using entity field names instead of column names. - Concatenating user input into SQL instead of using bind parameters. - Taking a sort column straight from the request instead of a whitelist. - Returning `Page` from a join or `GROUP BY` query without an explicit `countQuery`. - Passing a sorted `PageRequest` and expecting it to work. - Running a bulk `UPDATE` or `DELETE` without `clearAutomatically` and then reading stale entities. - Using `clearAutomatically` without `flushAutomatically`, losing pending changes. - Testing native SQL on H2 and trusting the result for PostgreSQL or MySQL. - Using a native query for something JPQL can do. - Forgetting that native SQL ties the code to one database.

When Should You Use Native Queries?

Use native SQL when: - You need **window functions, CTEs, upserts, full-text search or JSON operators** - A **proven-slow** JPQL query needs hand-tuned SQL (measure with `EXPLAIN ANALYZE` first) - You call a **stored procedure** - You do **bulk writes** that would be too slow through managed entities Stay with JPQL, derived queries or Specifications when: - The query is filters and joins - You need dynamic sorting through `Pageable` - The application must stay portable across databases

JPQL

Query Methods