Native Queries

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

    // ── Basic native query — returns mapped entities: ─────────────────
    @Query(value = "SELECT * FROM users WHERE email = :email",
           nativeQuery = true)
    Optional<User> findByEmailNative(@Param("email") String email);

    // ── Native query with multiple conditions: ────────────────────────
    @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);
    // Note: enum values must be passed as strings in native queries

    // ── Database-specific SQL — window function (not in JPQL): ────────
    @Query(value = """
            SELECT u.*,
                   RANK() OVER (PARTITION BY u.role ORDER BY u.created_at) AS rank
            FROM users u
            WHERE u.active = true
            """,
           nativeQuery = true)
    List<Object[]> findUsersWithRank();

    // ── CTE (Common Table Expression): ────────────────────────────────
    @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);

    // ── Full-text search (PostgreSQL): ────────────────────────────────
    @Query(value = """
            SELECT * FROM products
            WHERE to_tsvector('english', name || ' ' || description)
                  @@ plainto_tsquery('english', :query)
            ORDER BY ts_rank(
                to_tsvector('english', name || ' ' || description),
                plainto_tsquery('english', :query)
            ) DESC
            """,
           nativeQuery = true)
    List<Product> fullTextSearch(@Param("query") String query);
}

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

    // ── Native query with pagination — countQuery is required: ────────
    @Query(
        value = """
            SELECT o.* 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
            """,
        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<Order> findByCountryAndStatus(
        @Param("country") String country,
        @Param("status") String status,
        Pageable pageable);

    // ── Native query with complex pagination: ─────────────────────────
    @Query(
        value = """
            SELECT p.*,
                   COALESCE(AVG(r.rating), 0) AS avg_rating,
                   COUNT(r.id) AS review_count
            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
            ORDER BY avg_rating 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
            ) subquery
            """,
        nativeQuery = true
    )
    Page<Object[]> findTopRatedProducts(
        @Param("categoryId") Long categoryId,
        @Param("minReviews") int minReviews,
        Pageable pageable);

    // ── IMPORTANT: Sort in native queries ─────────────────────────────
    // Passing a Pageable with Sort to a native query is unreliable —
    // Spring Data maps Sort field names from entity fields to column names
    // but native queries use column names directly.
    // For native queries with dynamic sort: use a fixed ORDER BY clause,
    // or build the query string dynamically in a custom repository implementation.
}

// ── Interface projection — method names must match column aliases: ────
public interface UserSummary {
    Long getId();
    String getName();
    String getEmail();
    Long getOrderCount();
}

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

    // Column aliases must exactly match the getter names (case-insensitive):
    @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();
    // Spring Data generates a proxy implementing UserSummary —
    // each getter reads the corresponding alias from the result set.

    // ── Nested projection (Hibernate 6+): ─────────────────────────────
    public interface OrderSummary {
        Long getId();
        BigDecimal getTotal();
        CustomerInfo getCustomer();

        interface CustomerInfo {
            Long getId();
            String getName();
        }
    }
    // Requires Hibernate-specific @NestedConfigurationProperty — use
    // class-based DTOs with @SqlResultSetMapping for complex nested results.
}

// ── Class-based DTO via constructor expression in native query: ────────
public record ProductStats(
    Long productId,
    String productName,
    long reviewCount,
    double avgRating
) { }

// Use @SqlResultSetMapping for class-based DTOs with native queries:
@Entity
@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, AVG(r.rating) 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 { }

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

    // ── Native bulk UPDATE: ────────────────────────────────────────────
    @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);

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

    // ── Database-specific UPSERT (PostgreSQL ON CONFLICT): ────────────
    @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)
    void upsertSetting(
        @Param("userId") Long userId,
        @Param("key") String key,
        @Param("value") String value);

    // ── MySQL INSERT ON DUPLICATE KEY: ────────────────────────────────
    @Modifying
    @Transactional
    @Query(value = """
            INSERT INTO page_views (page_url, view_count, last_viewed_at)
            VALUES (:url, 1, NOW())
            ON DUPLICATE KEY UPDATE
                view_count = view_count + 1,
                last_viewed_at = NOW()
            """,
           nativeQuery = true)
    void incrementPageView(@Param("url") String url);
}

@Repository
@RequiredArgsConstructor
public class ProductRepositoryCustomImpl implements ProductRepositoryCustom {

    private final EntityManager em;

    // ── Dynamic native query: ─────────────────────────────────────────
    @Override
    public List<Product> searchProducts(ProductFilter filter) {
        StringBuilder sql = new StringBuilder(
            "SELECT * FROM products WHERE 1=1");

        if (filter.getName() != null)
            sql.append(" AND LOWER(name) LIKE LOWER(:name)");
        if (filter.getMinPrice() != null)
            sql.append(" AND price >= :minPrice");
        if (filter.getCategory() != null)
            sql.append(" AND category_id = :categoryId");

        sql.append(" ORDER BY name LIMIT :limit OFFSET :offset");

        Query query = em.createNativeQuery(sql.toString(), Product.class);

        if (filter.getName() != null)
            query.setParameter("name", "%" + filter.getName() + "%");
        if (filter.getMinPrice() != null)
            query.setParameter("minPrice", filter.getMinPrice());
        if (filter.getCategory() != null)
            query.setParameter("categoryId", filter.getCategory());

        query.setParameter("limit", filter.getPageSize());
        query.setParameter("offset", filter.getOffset());

        return query.getResultList();
    }

    // ── Stored procedure: ─────────────────────────────────────────────
    @Override
    @Transactional
    public void recalculateUserStats(Long userId) {
        em.createNativeQuery("CALL recalculate_user_stats(:userId)")
          .setParameter("userId", userId)
          .executeUpdate();
    }

    // ── Batch insert with JDBC-level control: ─────────────────────────
    @Override
    @Transactional
    public void bulkInsert(List<AuditLog> logs) {
        Session session = em.unwrap(Session.class);
        session.doWork(connection -> {
            PreparedStatement ps = connection.prepareStatement(
                "INSERT INTO audit_logs (user_id, action, created_at) " +
                "VALUES (?, ?, ?)");
            for (int i = 0; i < logs.size(); i++) {
                ps.setLong(1, logs.get(i).getUserId());
                ps.setString(2, logs.get(i).getAction());
                ps.setTimestamp(3, Timestamp.valueOf(logs.get(i).getCreatedAt()));
                ps.addBatch();
                if (i % 50 == 0) ps.executeBatch();
            }
            ps.executeBatch();
        });
    }
}

# ── Use JPQL when: ───────────────────────────────────────────────────
# - The query is expressible in standard SQL constructs
# - Database portability matters (switching MySQL ↔ PostgreSQL)
# - You want Hibernate to manage JOIN and alias mapping
# - The query uses entity associations (JOIN FETCH, navigation)
# - You want compile-time validation via @NamedQuery

# Example — JPQL is better here:
@Query("SELECT o FROM Order o JOIN FETCH o.customer WHERE o.status = :status")
List<Order> findByStatusWithCustomer(@Param("status") Order.Status status);

# ── Use native SQL when: ──────────────────────────────────────────────
# - The query uses database-specific features:
#     Window functions (ROW_NUMBER, RANK, LEAD, LAG)
#     CTEs (WITH ... AS)
#     UPSERT (ON CONFLICT, ON DUPLICATE KEY)
#     Full-text search (PostgreSQL tsvector, MySQL FULLTEXT)
#     JSON operators (PostgreSQL ->>, MySQL JSON_EXTRACT)
#     Lateral joins, recursive CTEs
# - Hibernate's generated SQL is provably slower than a hand-tuned query
# - You are calling stored procedures or database functions
# - Bulk operations that must bypass the persistence context entirely

# Example — native SQL is better here:
@Query(value = """
    SELECT u.*,
           NTILE(4) OVER (ORDER BY u.total_spent DESC) AS spending_quartile
    FROM users u
    WHERE u.active = true
    """, nativeQuery = true)
List<Object[]> findUsersWithSpendingQuartile();

# ── Pitfalls to avoid: ────────────────────────────────────────────────
# - Native queries bypass Hibernate's entity cache — stale data risk
# - Column aliases must match projection getter names exactly
# - Enum parameters must be passed as strings (not enum instances)
# - Native pagination with Sort is unreliable — use fixed ORDER BY
# - Always use @Modifying(clearAutomatically=true) for bulk operations