Native SQL when you need it

Key points

• createNativeQuery(sql, Book.class) runs raw SQL and hands back fully managed entities, as long as you select the mapped columns.
• Use it for window functions, CTEs, upserts, full-text search - anything JPQL has no syntax for.
• Without an entity class you get Object[] rows. Map them with @SqlResultSetMapping or a Spring Data interface projection.
• Hibernate cannot tell which tables your SQL touches, so it flushes pending changes first. That is usually exactly what you want.
• Bulk update/delete skips the persistence context. Entities already loaded go stale - clear the context after one.
• Native SQL ties that query to one database dialect. Fine, as long as it is a deliberate choice.

Example

// Raw SQL mapped back to managed Book entities
List<Book> best = em.createNativeQuery(
        "select b.* from book b " +
        "join review r on r.book_id = b.id " +
        "group by b.id having avg(r.stars) >= :min " +
        "order by avg(r.stars) desc limit 10", Book.class)
    .setParameter("min", 4.5)
    .getResultList();

// Spring Data: native SQL into a projection interface, no entities involved
public interface BookRepository extends JpaRepository<Book, Long> {

    @Query(value = "select b.title as title, count(r.id) as reviews " +
                   "from book b left join review r on r.book_id = b.id " +
                   "group by b.title", nativeQuery = true)
    List<TitleReviews> titleReviewCounts();

    interface TitleReviews {
        String getTitle();
        long getReviews();
    }
}

Native SQL is an escape hatch, not a defeat.