HQL vs JPQL vs Native SQL — $50k L1 Cache Pitfall

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.util;

// Decision logic — not actual code, just a reference
public class QueryDecision {
    /*
    if (need_portability && no_vendor_features) {
        use JPQL;
    } else if (need_hibernate_feature) {
        use HQL;
    } else if (need_vendor_specific || need_max_performance) {
        use Native SQL;
        // always use named parameters and @SqlResultSetMapping
    }
    */
}

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();
    }
}

package io.thecodeforge.entity;

import jakarta.persistence.*;
import java.math.BigDecimal;

@SqlResultSetMapping(
    name = "OrderSummaryMapping",
    classes = @ConstructorResult(
        targetClass = OrderSummaryDTO.class,
        columns = {
            @ColumnResult(name = "order_id", type = Long.class),
            @ColumnResult(name = "total", type = BigDecimal.class)
        }
    )
)
@Entity
public class Order {
    // entity fields...
}

// Usage in repository:
// List<OrderSummaryDTO> summaries = em.createNativeQuery(
//     "SELECT o.id AS order_id, SUM(oi.price) AS total FROM orders o ...",
//     "OrderSummaryMapping").getResultList();

package io.thecodeforge.repository;

import io.thecodeforge.model.Project;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import java.util.List;

public interface ProjectRepository extends CrudRepository<Project, Long> {

    // JPQL — portable across databases
    @Query("SELECT p FROM Project p WHERE p.status = :status")
    List<Project> findActiveProjects(@Param("status") String status);

    // Native SQL — PostgreSQL specific, documented
    @Query(value = "SELECT * FROM forge_projects WHERE metadata->>'priority' = :priority",
           nativeQuery = true)
    List<Project> findHighPriorityProjects(@Param("priority") String priority);
}