Hibernate HQL 中使用 CAST 函數將字符串轉數字進行排序

問題背景

• 數據庫表 score 欄位類型為 VARCHAR2(2)。
• 實體類對應字段為 String。
• 業務需按數值大小排序，直接按字符串排序會導致 "10" < "2" 錯誤。
• 不可修改表結構與實體類。

HQL 解決方案

String hql = "SELECT u FROM User u ORDER BY CAST(u.score AS INTEGER)";

List users = session.createQuery(hql).list();

關鍵點

• CAST(expr AS TYPE) 是 HQL 標準函數，支持 INTEGER, LONG, DOUBLE, STRING 等類型轉換。
• 轉換發生在數據庫層面，由底層 SQL 方言翻譯 (如 Oracle TO_NUMBER, MySQL CAST(... AS UNSIGNED))。
• ORDER BY 子句可直接使用轉換表達式。

Criteria API 替代方案

CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(User.class);
Root root = cq.from(User.class);

cq.select(root)
  .orderBy(cb.asc(cb.function("CAST", Integer.class, root.get("score"), cb.literal(Integer.class))));

List users = session.createQuery(cq).getResultList();

性能提示

• 每次查詢都轉換類型會增加 CPU 開銷，若頻繁查詢建議：
• 方案 A：資料庫層面添加虛擬列/計算列 score_num AS (CAST(score AS INT)) 並建索引。
• 方案 B：應用層緩存排序結果 (Redis Sorted Set 等)。
• 方案 C：數據清洗階段將字符串轉數值存入新列，逐步遷移。

兼容性驗證