Native SQL and Bulk Operations

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.

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.

त्वरित जाँच: What is a risk of running a JPQL bulk update in the same persistence context as loaded entities?

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