# Native SQL and Bulk Operations — Hibernate / JPA

Source: https://www.skillbyai.com/en/hibernate-jpa/q-native

> Use native queries and bulk updates correctly, aware of their effect on the persistence context.

## When JPQL is not enough

Sometimes you need features JPQL does not express: database-specific functions, window functions, recursive CTEs, full-text search, JSON operators or hand-tuned SQL. **Native queries** (`em.createNativeQuery(sql, Result.class)` or Spring Data `@Query(nativeQuery = true)`) run SQL directly; map results to entities, to DTOs via `@SqlResultSetMapping`, or to Spring Data interface projections. They tie code to one database dialect, so keep them few and well tested. **Bulk operations** (`update Product p set p.price = p.price * 1.1 where p.category = :c`, or `delete from ...`) execute directly in the database, which is far faster than loading and modifying entities one by one. But they **bypass the persistence context**: entities already loaded in the current context keep stale values, dirty checking may later overwrite the bulk change, cascades and lifecycle callbacks do not run, and `@Version` is not incremented unless you do it explicitly. Run bulk operations in their own transaction or **clear** the persistence context afterwards (Spring Data's `@Modifying(clearAutomatically = true)`).

## A native window-function query and a safe bulk update

Native SQL for reporting; a bulk update that clears stale state.

```java
public interface SalesRepository extends JpaRepository<PurchaseOrder, Long> {

    interface TopCustomer { Long getCustomerId(); BigDecimal getSpent(); Integer getRank(); }

    @Query(value = """
        select customer_id as customerId, spent, rank
        from (
          select customer_id, sum(total) as spent,
                 rank() over (order by sum(total) desc) as rank
          from orders where status = 'PAID' group by customer_id
        ) ranked
        where rank <= :n
        """, nativeQuery = true)
    List<TopCustomer> topCustomers(int n);

    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Query("""
        update Product p set p.price = p.price * :factor, p.version = p.version + 1
        where p.category.slug = :category
        """)
    int repriceCategory(String category, BigDecimal factor);
}
```

## Bulk updates skip your entity logic

Validation in setters, `@PreUpdate` callbacks and auditing listeners do not run for bulk JPQL or native updates. If those rules matter, either reapply them in SQL or process entities in batches instead.

**Quiz:** What is a risk of running a JPQL bulk update in the same persistence context as loaded entities?

- [x] Loaded entities keep stale values because bulk operations bypass the persistence context
- [ ] The update is ignored
- [ ] It always deadlocks
- [ ] It disables transactions

*Answer:* Loaded entities keep stale values because bulk operations bypass the persistence context. Bulk statements go straight to the database; clear the context or use a separate transaction.