HQL vs JPQL vs Native SQL

package io.thecodeforge.repository;

import io.thecodeforge.model.Project;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.Query;
import java.util.List;

public class ForgeQueryService {

    @PersistenceContext
    private EntityManager entityManager;

    public void runQueries() {
        // 1. JPQL: Standardized, entity-based. Highly portable.
        List<Project> jpql = entityManager.createQuery(
            "SELECT p FROM Project p WHERE p.status = 'ACTIVE'", Project.class)
            .getResultList();

        // 2. HQL: Hibernate-specific extensions (like specific JOIN FETCH logic)
        // Note: In modern JPA, many HQL features are now standard JPQL.
        List<Project> hql = entityManager.createQuery(
            "from Project p left join fetch p.tasks where p.id = :id", Project.class)
            .setParameter("id", 1L)
            .getResultList();

        // 3. Native SQL: Production-grade PostgreSQL specific example (JSONB query)
        // io.thecodeforge: Using Native SQL for complex DB-specific types
        Query nativeQuery = entityManager.createNativeQuery(
            "SELECT * FROM forge_projects p WHERE p.metadata->>'priority' = 'HIGH'", 
            Project.class);
        List<Project> nativeResults = nativeQuery.getResultList();
    }
}

package io.thecodeforge.security;

import jakarta.persistence.EntityManager;
import jakarta.persistence.Query;
import java.util.List;

public class QuerySafety {
    
    // ANTI-PATTERN: Vulnerable to SQL Injection via string concatenation
    public void badQuery(EntityManager em, String input) {
        em.createNativeQuery("SELECT * FROM forge_users WHERE name = '" + input + "'").getResultList();
    }

    /**
     * CORRECT: io.thecodeforge best practice - Use Named Parameters
     * This prevents SQLi and allows the DB to cache the execution plan.
     */
    public void goodQuery(EntityManager em, String input) {
        List<?> users = em.createNativeQuery("SELECT * FROM forge_users WHERE name = :userName")
          .setParameter("userName", input)
          .getResultList();
    }

    /**
     * PRO TIP: Native SQL for bulk deletes to bypass session overhead
     */
    public int bulkDeleteOldLogs(EntityManager em) {
        return em.createNativeQuery("DELETE FROM forge_logs WHERE created_at < NOW() - INTERVAL '30 days'")
                 .executeUpdate();
    }
}