3.4.4.4.1. JPQL 函数

CUBA 和标准 JPA 之间 JPQL 的主要区别在于 CUBA 函数总是需要一个实体别名，包括 SELECT 和 UPDATE/DELETE 语句。

CUBAJPA

UPDATE app$DeliveryAddress e SET e.customer = :target WHERE e.customer = :source

UPDATE app$DeliveryAddress SET customer = :target WHERE customer = :source

在开启实体的软删除模式时，如果对已经软删除的实体执行 JPQL DELETE FROM 语句将抛出异常。这样的语句实际上转换为 SQL 来删除那些没有标记为删除的所有实例。默认情况下禁用软删除，可以使用 cuba.enableDeleteStatementInSoftDeleteMode 应用程序属性启用软删除。

下表描述了 CUBA 框架支持和不支持的 JPQL 函数。

函数支持查询

聚合函数

支持

SELECT AVG(o.quantity) FROM app$Order o

不支持: 带标量表达式的聚合函数(EclipseLink 特性)

SELECT AVG(o.quantity)/2.0 FROM app$Order o

SELECT AVG(o.quantity o.price) FROM app$Order o

ALL、 ANY、 SOME

支持

SELECT emp FROM app$Employee emp WHERE emp.salary > ALL (SELECT m.salary FROM app$Manager m WHERE m.department = emp.department)

算术函数 (INDEX 、 SIZE 、 ABS 、 SQRT 、 MOD)

支持

SELECT w.name FROM app$Course c JOIN c.studentWaitlist w WHERE c.name = 'Calculus' AND INDEX(w) = 0

SELECT w.name FROM app$Course c WHERE c.name = 'Calculus' AND SIZE(c.studentWaitlist) = 1

SELECT w.name FROM app$Course c WHERE c.name = 'Calculus' AND ABS(c.time) = 10

SELECT w.name FROM app$Course c WHERE c.name = 'Calculus' AND SQRT(c.time) = 10.5

SELECT w.name FROM app$Course c WHERE c.name = 'Calculus' AND MOD(c.time, c.time1) = 2

UPDATE 查询中的 CASE 表达式

支持

SELECT e.name, f.name, CONCAT(CASE WHEN f.annualMiles > 50000 THEN 'Platinum ' WHEN f.annualMiles > 25000 THEN 'Gold ' ELSE '' END, 'Frequent Flyer') FROM app$Employee e JOIN e.frequentFlierPlan f

不支持: UPDATE 查询中的 CASE

UPDATE app$Employee e SET e.salary = CASE e.rating WHEN 1 THEN e.salary 1.1 WHEN 2 THEN e.salary 1.05 ELSE e.salary 1.01 END

日期 函数(CURRENTDATE、CURRENT_TIME、CURRENT_TIMESTAMP)

支持

SELECT e FROM app$Order e WHERE e.date = CURRENT_DATE

EclipseLink 函数 (CAST、 REGEXP、 EXTRACT)

支持

SELECT EXTRACT(YEAR FROM e.createTs) FROM app$MyEntity e WHERE EXTRACT(YEAR FROM e.createTs) > 2012

SELECT e FROM app$MyEntity e WHERE e.name REGEXP '.*'

SELECT CAST(e.number text) FROM app$MyEntity e WHERE e.path LIKE CAST(:ds$myEntityDs.id text)
不支持: GROUP BY 子句中的 CAST

SELECT e FROM app$Order e WHERE e.amount > 100 GROUP BY CAST(e.orderDate date)

实体类型表达式

支持: 实体类型作为参数

SELECT e FROM app$Employee e WHERE TYPE(e) IN (:empType1, :empType2)
不支持: 直接链接到实体类型

SELECT e FROM app$Employee e WHERE TYPE(e) IN (app$Exempt, app$Contractor)

函数调用

支持: 比较子句中使用函数结果

SELECT u FROM sec$User u WHERE function('DAYOFMONTH', u.createTs) = 1
不支持: 直接使用函数返回值

SELECT u FROM sec$User u WHERE function('hasRoles', u.createdBy, u.login)

IN

支持

SELECT e FROM Employee e, IN(e.projects) p WHERE p.budget > 1000000

IS EMPTY 集合

支持

SELECT e FROM Employee e WHERE e.projects IS EMPTY

键/值 KEY/VALUE
 不支持

SELECT v.location.street, KEY(i).title, VALUE(i) FROM app$VideoStore v JOIN v.videoInventory i WHERE v.location.zipcode = '94301' AND VALUE(i) > 0

字面量

支持

SELECT e FROM app$Employee e WHERE e.name = 'Bob'

SELECT e FROM app$Employee e WHERE e.id = 1234

SELECT e FROM app$Employee e WHERE e.id = 1234L

SELECT s FROM app$Stat s WHERE s.ratio > 3.14F

SELECT s FROM app$Stat s WHERE s.ratio > 3.14e32D

SELECT e FROM app$Employee e WHERE e.active = TRUE
不支持: 时间和日期字符串

SELECT e FROM app$Employee e WHERE e.startDate = {d'2012-01-03'}

SELECT e FROM app$Employee e WHERE e.startTime = {t'09:00:00'}

SELECT e FROM app$Employee e WHERE e.version = {ts'2012-01-03 09:00:00.000000001'}

MEMBER OF

支持: 字段或者查询结果

SELECT d FROM app$Department d WHERE (select e from app$Employee e where e.id = :eParam) MEMBER OF e.employees
不支持: 字面量

SELECT e FROM app$Employee e WHERE 'write code' MEMBER OF e.codes

SELECT 中使用 NEW

支持

SELECT NEW com.acme.example.CustomerDetails(c.id, c.status, o.count) FROM app$Customer c JOIN c.orders o WHERE o.count > 100

NULLIF/COALESCE

支持

SELECT NULLIF(emp.salary, 10) FROM app$Employee emp

SELECT COALESCE(emp.salary, emp.salaryOld, 10) FROM app$Employee emp

order by 中使用 NULLS FIRST, NULLS LAST

支持

SELECT h FROM sec$GroupHierarchy h ORDER BY h.level DESC NULLS FIRST

字符串函数 (CONCAT、 SUBSTRING 、 TRIM 、 LOWER 、 UPPER 、 LENGTH 、 LOCATE)

支持

SELECT x FROM app$Magazine x WHERE CONCAT(x.title, 's') = 'JDJs'

SELECT x FROM app$Magazine x WHERE SUBSTRING(x.title, 1, 1) = 'J'

SELECT x FROM app$Magazine x WHERE LOWER(x.title) = 'd'

SELECT x FROM app$Magazine x WHERE UPPER(x.title) = 'D'

SELECT x FROM app$Magazine x WHERE LENGTH(x.title) = 10

SELECT x FROM app$Magazine x WHERE LOCATE('A', x.title, 4) = 6

SELECT x FROM app$Magazine x WHERE TRIM(TRAILING FROM x.title) = 'D'
不支持: 带特定字符的 TRIM

SELECT x FROM app$Magazine x WHERE TRIM(TRAILING 'J' FROM x.title) = 'D'

子查询

支持

SELECT goodCustomer FROM app$Customer goodCustomer WHERE goodCustomer.balanceOwed < (SELECT AVG(c.balanceOwed) FROM app$Customer c)
不支持: 子查询语句 FROM 中使用路径表达式而不是实体名称

SELECT c FROM app$Customer c WHERE (SELECT AVG(o.price) FROM c.orders o) > 100

TREAT

支持

SELECT e FROM app$Employee e JOIN TREAT(e.projects AS app$LargeProject) p WHERE p.budget > 1000000
不支持: WHERE 从句中使用 TREAT_

SELECT e FROM Employee e JOIN e.projects p WHERE TREAT(p as LargeProject).budget > 1000000