JPQL

什麼是 JPQL？

JPQL（Java Persistence Query Language）是專門為 JPA（Java Persistence API）設計的查詢語言。它是一種面向對象的查詢語言，讓開發者可以以 Java 對象和屬性的方式編寫查詢，而不是直接操作數據庫表。這意味著 JPQL 查詢的操作對象是 Java 實體類，而不是數據庫中的表。

JPQL 的特點

• 面向對象：查詢基於 Java 的實體對象，而不是基於數據庫表。這樣可以直接以實體的屬性和關聯進行查詢，符合面向對象的設計。
• 跨資料庫兼容：JPQL 的語法與底層資料庫無關，JPA 會自動將 JPQL 轉換為適合特定資料庫的 SQL，因此可以在多種資料庫之間自由切換。
• 支持關聯映射查詢：可以方便地查詢和操作一對多、多對多等關聯關係，使得對象之間的關係操作變得簡單直觀。

@Query("SELECT o FROM Order o JOIN o.user u WHERE u.id = :userId")
List<Order> findOrdersByUserId(@Param("userId") Long userId);

• 支持分頁跟排序：可以方便地查詢和操作一對多、多對多等關聯關係，使得對象之間的關係操作變得簡單直觀。

Pageable pageable = PageRequest.of(0, 10, Sort.by("name").ascending());
List<User> users = userRepository.findActiveUsers(pageable);

JPQL 缺點

• 缺乏部分資料庫特定的 SQL 功能：JPQL 僅提供通用的查詢功能，對於特定資料庫的優化功能（如 MySQL 的 LIMIT、Oracle 的 ROWNUM 等），無法直接使用。需要時仍需借助 nativeQuery。

特定資料庫函數

JPQL 不支援資料庫專有的函數（例如日期、字符串操作）。但在 native query 中，可以使用這些函數。

• mysql

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') FROM orders;

• oracle

SELECT TO_CHAR(order_date, 'YYYY-MM-DD') FROM orders;

UNION/UNION ALL

SELECT name FROM employees
UNION
SELECT name FROM managers;

CASE

SELECT name, 
       CASE 
           WHEN salary > 5000 THEN 'High'
           WHEN salary > 3000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_range
FROM employees;

窗口函數

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

正則表達式

SELECT * FROM users WHERE username REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$';

特定索引提示

SELECT * FROM users USE INDEX (index_name) WHERE email = 'example@example.com';

SQL 與 JPQL 對照表

功能	Oracle SQL 語法	PostgreSQL SQL 語法	MySQL SQL 語法	JPQL 對應寫法
分頁	SELECT * FROM (SELECT u.*, ROWNUM rnum FROM User u WHERE u.active = 1 ORDER BY u.name ASC) WHERE rnum > 20 AND rnum <= 30;	SELECT * FROM User u WHERE u.active = true ORDER BY u.name ASC LIMIT 10 OFFSET 20;	SELECT * FROM User u WHERE u.active = true ORDER BY u.name ASC LIMIT 10 OFFSET 20;	@Query("SELECT u FROM User u WHERE u.active = true") + Pageable pageable
日期格式化	TO_CHAR(date_column, 'YYYY-MM-DD')	TO_CHAR(date_column, 'YYYY-MM-DD')	DATE_FORMAT(date_column, '%Y-%m-%d')	不支持直接格式化，需在應用層處理
字符串連接	`first_name || ‘ ‘ || last_name`	`first_name || ‘ ‘ || last_name`	`CONCAT(first_name, ‘ ‘, last_name)`	`@Query(“SELECT CONCAT(u.firstName, ‘ ‘, u.lastName) FROM User u”)`（若跨資料庫需避免）
自增主鍵	使用 SEQUENCE 配合 NEXTVAL	使用 SERIAL 類型	使用 AUTO_INCREMENT	在 JPQL 中不處理，僅限於資料庫配置
布林值	使用 NUMBER(1) 或 CHAR(1)	使用 BOOLEAN	使用 TINYINT(1)	JPQL 中可以使用布林字段（無需特別處理）
空值判斷	NVL(column, value)	COALESCE(column, value)	IFNULL(column, value)	COALESCE(column, value)
子字符串	SUBSTR(column, start, length)	SUBSTRING(column FROM start FOR length)	SUBSTRING(column, start, length)	SUBSTRING(column, start, length)