JPQL

JPA提供的一種查詢語言, 更符合OO。

• SQL 中字串用’ 而非”
• JPQL數值不用引號mf.id = 1234
• JPQL不使用*
• JPQL最末端不要;

聚合函數
JPQL支援許多聚合函數,如COUNT、SUM、AVG、MAX和MIN等,用於執行聚合計算。

子查詢
JPQL支援子查詢,可以在WHERE或HAVING子句中使用。子查詢也可以是關聯子查詢。

函數
JPQL提供了一些內建函數,如CONCAT、SUBSTRING、TRIM等,用於字串操作。還可以調用自訂函數。

分頁
JPQL支援使用LIMIT和OFFSET子句實現分頁查詢,但具體語法可能會因JPA實現而異。

SQL

反白執行
createNativeQuery()
使用Native SQL,用#傳入參數, 但傳入參數不是@Entity會遇到 Missing descriptor
Join A on B

SELECT 
    e.id, 
    e.name, 
    e.email,
    d.name AS department_name,
    COUNT(p.id) AS project_count
FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.id  
LEFT JOIN 
    project_employees pe ON e.id = pe.employee_id
LEFT JOIN 
    projects p ON pe.project_id = p.id
WHERE
    e.status = 'ACTIVE'
GROUP BY 
    e.id, e.name, e.email, d.name
HAVING 
    COUNT(p.id) > 2
ORDER BY 
    project_count DESC;

JPQL

createQuery()
使用JPQL, 用:傳入參數, 所有底線都改成駝峰式命名
Join A B

SELECT 
    e.id,
    e.name, 
    e.email,
    d.name AS departmentName,
    COUNT(pe.project) AS projectCount
FROM 
    Employee e
JOIN 
    e.department d
LEFT JOIN 
    e.projectEmployees pe
LEFT JOIN
    pe.project p
WHERE
    e.status = com.example.Status.ACTIVE  
GROUP BY
    e.id, e.name, e.email, d.name
HAVING
    COUNT(p.id) > 2  
ORDER BY 
    projectCount DESC

---

如果你覺得這篇文章很棒，請你不吝點讚 (ﾟ∀ﾟ)