Lunski's Clutter

This is a place to put my clutters, no matter you like it or not, welcome here.

0%

JPQL

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

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

聚合函數
JPQL支援許多聚合函數,如COUNTSUMAVGMAXMIN等,用於執行聚合計算。

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

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

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

SQL

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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

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

Welcome to my other publishing channels