Pro JPA2读书笔记系列(八)-第八章(查询语言)-白红宇

Pro JPA2读书笔记系列(八)-第八章(查询语言)

阅读量：6864 次

发布时间：2019-06-26

本文共 7411 字，大约阅读时间需要 24 分钟。

Pro JPA2 第八章(查询语言)

8.1 简介
JP QL 不是SQL.引入它的原因有可移植性以及针对持久化实体的域模型编程.
- 8.1.1 术语
  查询分为4个类别:选择(select),聚合(aggregate),更新(update)和删除(delete).

8.2 选择查询

选择查询的整体形式如下:

SELECT <SELECT_expression> FROM <from_clause> [WHERE <conditional_expression>] [ORDER BY <order_by_clause>]

8.2.1 SELECT 子句
- 路径表达式
  略
- 实体和对象
  略
- 组合表达式
  返回一个具有零个或者多个Object类型数组的实例集合.
- 构造函数表达式
```
SELECT NEW example.EmployeeDetails(e.name,e.salary,e.department.name)FROM Employee e
```
- 继承和多态性
  JPA支持实体之间的继承.结果是,查询语言支持多态性结果,其中可以通过相同的查询返回实体的多个子类.

8.2.2 FROM子句

标识变量
略

联接
一旦查询转换成SQL,实体之间的联接就会产生类似实体所映射的表之间的联接.
每当查询满足任何下列条件时将会发生联接:
- 两个或多个值域变量声明列在FROM子句中,并且出现在SELECT子句中.
- JOIN操作符用于使用路径表达式来扩展标识变量
- 在查询中任何位置的路径表达式通过一个关联字段导航到相同或不同的实体
- 一个或多个WHERE条件比较不同标识变量的特性
  两个实体之间的内部链接(inner join)返回满足所有联接条件的两种实体类型的对象.从一种实体到另一种实体的路径导航是一种内部链接形式.两个实体的外部联接(outer join)是来自满足链接条件的两种实体类型的对象集,再加上一种实体类型的对象集,它们没有匹配在另一种实体类型中的联接条件.
  如果在两个实体之间缺少联接条件,那么查询将产生一个笛卡尔乘积(Cartesian product).

内部联接

JOIN操作符和集合关联字段

SELECT pFROM Employee e JOIN e.phones p

相当于以传统的联接形式编写:

SELECT p.id,p.phone_num,p.type,p.emp_idFROM emp e,phone pWHERE e.id = p.emp_id

也可以使用IN操作符,这也是查询的推荐操作符:

SELECT DISTINCT pFROM Employee e ,IN(e.phones) p

WHERE子句中的联接条件

SELECT DISTINCT dFROM Department d,Employee eWHERE d = e.department

这样形式的查询通常用来弥补在域模型中两个实体之间缺乏明确的关系的缺陷,例如Department实体与作为部门经理的Employee之间没有关联,我们可以在WHERE子句中使用一个联接条件,使其变为可能:

SELECT d,mFROM Department d,Employee mWHERE d = m.department ANDm.directs IS NOT EMPTY

多个联接

SELECT DISTINCT pFROM Department d JOIN d.employees e JOIN e.project p

映射联接
通过以映射实现的集合值关联进行导航的路径表达式是一个特例.与一个正常的集合不同,映射中没一个项对应两部分信息:键和值

SELECT e.name, pFROM Employee e JOIN e.phones p

这种行为可以通过使用VALUE关键字来显式地突出.

SELECT e.name ,VALUE(p)FROM Employee e JOIN e.phones p

对于一个给定的映射项,为了访问键而不是值,可以使用KEY关键字来重写默认的行文,为一个给定的映射项返回键值.

SELECT e.name,KEY(p),VALUE(p)FROM Employee e JOIN e.phones pWHERE KEY(p) IN ('Work','Cell')

外部联接
两个实体之间的外部联接产生了一个域,其中只有关系的一方必须是完整的.
语法LEFT[OUTER] JOIN [AS]

SELECT e,dFROM Employee e LEFT JOIN e.department d

获取联接
获取联接的目的是帮助应用程序设计人员优化数据库访问以及准备分离查询结果.它们允许查询指定由查询引擎导航和预先获取的一个或多个关系,从而使他们不会再后面的运行时延迟加载.
例如,如果有一个Employee实体,它与其地址存在一种延迟加载关系,那么下面的查询可以用来指示关系应该在查询执行时即时地解析:

SELECT eFROM Employee e JOIN FETCH e.address

为了实现获取联接,提供程序需要把获取关联转换成适当类型的常规联接:默认情况下是内部链接,或者当指定了LEFT关键字时是外部联接.还需要扩展查询的SELECT表达式以包括联接关系.

SELECT e,aFROM Employee e JOIN e.address a

唯一的区别是提供程序实际上并不向调用者返回Address实体.  因为结果是从这个查询处理,所以查询引擎在内存中创建Address实体,并将它分配给Employee实体,但是,随后会将它从客户端构建的结果集中删除.  这将会即时加载address关系,然后一般可以通过Employee实体来访问它.

8.2.3

输入参数
位置定义:
SELECT e FROM Employee e WHERE e.salary > ?1
命名参数定义:
SELECT e FROM Employee e WHERE e.salary > :sal

基本表达式形式
操作符优先级如下:
- 导航操作符(.)
- 一元 + -
- 乘法(*)和除法(/)
- 加法(+)和减法(-)
- 比较操作符:= ,> >=,<,<=,<>,[NOT]BETWEEN,[NOT]LIKE,[NOT]IN,IS[NOT]NULL,IS[NOT]EMPTY,[NOT]MEMBER[OF]
- 逻辑操作符(AND,OR,NOT)

BETWEEN

SELECT e FROM Employee eWHERE e.salary BETWEEN 40000 AND 45000

等价于:

SELECT eFROM Employee eWHERE e.salary >= 4000 AND e.salary <= 45000

SELECT dFROM Department dWHERE d.name LIKE '__Eng%'

子查询
子查询可以用于查询的WHERE和HAVING子句.

SELECT eFROM Employee eWHERE e.salary = (SELECT MAX(emp.salary) FROM Employee emp)

能够在子查询中引用主查询的变量将允许这两个查询相关,考虑下例:

SELECT eFROM Employee eWHERE EXISTS (SELECT 1 FROM Phone p WHERE p.employee = e AND p.type = 'Cell')

这也是一个子查询的示例,它返回值的集合,如果子查询返回任何结果,那么在此示例中EXISTS表达式返回true.从子查询中返回1是关于EXISTS表达式的一种标准做法,因为由子查询选择的实际结果并不重要,只有结果的数量是有关系的.

IN
普通写法:

SELECT eFROM Employee eWHERE e.address.state IN ('NY','CA')

子查询写法:

SELECT eFROM Employee eWHERE e.department IN (SELECT DISTINCT d FROM Department d JOIN d.employees de JOIN de.projects p WHERE p.name LIKE 'QA%')

NOT IT:

SELECT p FROM Phone pWHERE p.type NOT IN ('Office','Home');

集合表达式

SELECT eFROM Employee eWHERE e.directs IS NOT EMPTY

它等价于:

SELECT mFROM Employee mWHERE (SELECT COUNT(e) FROM Employee e WHERE e.manager = m) > 0

MEMBER OF 操作符和其否定形式NOT MEMBER OF是一种快捷方法,用于检查是否是集合关联路径的一个成员.

SELECT eFROM Employee eWHERE e MEMBER OF e.directs

MEMBER OF操作符更典型的应用是结合一个输入参数.

SELECT eFROM Employee eWHERE :project MEMBER OF e.projects

它等价于:

SELECT eFROM Employee eWHERE :project IN (SELECT p FROM e.projects p)

EXISTS
如果一个子查询返回任何行,那么EXISTS条件返回true.

SELECT eFROM Employee eWHERE NOT EXISTS (SELECT p WHERE p.type='Cell')

ANY,ALL,SOME

SELECT eFROM Employee eWHERE e.directs IS NOT EMPTY AND         e.salary < ALL(SELECT d.salary                        FROM e.directs d)

当使用ALL操作符时,若整体条件为真,则方程的左边和所有子查询结果的比较都必须为真  ANY操作符的表现类似,但是总体条件为真时,只要至少有一个表达式和子查询结果的比较为真就可以.  SOME操作符时ANY操作符的一个别名

8.2.4 标量表达式

标量表达式是文字值,算数序列,函数表达式,类型表达式或者解析单一标量值的case表达式.可以在SELECT子句中使用它,以格式化报告查询中的投影字段,或者作为在查询的WHERE或HAVING子句中条件表达式的一部分.

字面量
可以在JP QL中使用许多不同的字面量类型,包括字符串,数字,布尔值,枚举,实体类型和时间类型.
查询可以通过指定完全限定名称的枚举类来应用Java枚举类型:

SELECT eFROM Employee e JOIN e.phoneNumbers pWHERE KEY(p) = com.acme.PhoneType.home

使用JDBC转义语法指定时间文字,其中定义了包含字面量的大括号.序列中的第一个字符是"d"或者"t",以指示字面量分别是一个日期或时间.如果使用字面量表示时间戳,那么使用"ts":  * {d 'yyyy-mm-dd'} e.g. {d '2009-11-05'}  * {t 'hh-mm-ss'}     e.g. {t '12-45-52'}  * {ts 'yyyy-mm-dd hh-mm-ss.f'} e.g. {ts '2009-11-05 12-45-52.325'}

函数表达式

如图:

函数	描述
ABS(number)	求绝对值
CONCAT(string1,string2)	连接字符串
CURRENT_DATE	返回数据库服务器定义的当前日期
CURRENT_TIME	返回数据库服务器定义的当前时间
CURRENT_TIMESTAMP	返回数据库服务器定义的当前时间戳
INDEX(identification variable)	返回一个实体在有序列表中的位置
LENGTH(string)	返回字符串参数的长度
LOCATE(string1,string2[,start])	返回string1在string2中的位置, 可选择在start指示的位置开始. 如果不能发现字符串,那么结果为零
LOWER(string)	小写
MOD(number1,number2)	求余
SIZE(collection	返回集合中的元素数量,如果为空,则返回零
SQRT(number	求平方,结果为双精度实型
SUBSTRING(string,start,end)	截取字符串,字符串的索引从1开始计算
UPPER(string)	大写
TRIM [[LEADING,TRAILING,BOTH][char]FROM]string	删除字符串前边或者后边的空格(默认), 也可以是其他字符

SIZE函数需要注意的地方:它是聚合子查询的简写符号.

SELECT d FROM Department d WHERE SIZE(d.employees) = 2

INDEX函数的用法:

SELECT e.name,p.number FROM Employee e JOIN e.phones p WHERE INDEX(p) = 0

CASE
语法: CASE {WHEN< cond_expr > THEN < scalar_expr >} + ELSE < scalar_expr > END

SELECT p.name    CASE WHEN TYPE(p) = DesignProject THEN 'Development'         WHEN TYPE(p) = QualityProject THEN 'QA'         ELSE 'Non-Development'    ENDFROM Project pWHERE p.employees IS NOT EMPTY

还有一种NULLIF是排除聚合函数的结果.

SELECT COUNT(1),COUNT(NULLIF(d.name,'QA')) FROM Department d

ORDER BY

SELECT e.name ,e.salary * 0.05 AS bonus,d.name AS deptNameFROM Employee e JOIN e.department dORDER BY deptName,bonus DESC

如果查询的SELECT子句使用状态字段的路径表达式,那么需要把ORDER BY子句限制为在SELECT子句中使用相同路径表达式.例如下列查询是非法的:

SELECT e.nameFROM Employee eORDER BY e.salary DESC

因为查询的结果类型是String,所以其余的Employee状态字段不可在用于排序.

8.3聚合查询
语法:
SELECT <SELECT_expression> FROM <from_clause> [WHERE <condition_expression>] [GROUP BY <group_by_clause>] [HAVING <condition_expression>] [ORDER BY <order_by_clause>]
实例:
SELECT d.name ,AVG(e.salary) FROM Department d JOIN d.employees e WHERE e.directs IS EMPTY GROUP BY d.name HAVING AVG(e.salary) > 50000
- 8.3.1 聚合函数
  - AVG
    求平均值
  - COUNT
    求数量
```
SELECT e,COUNT(p),COUNT(DISTINCT p.type)FROM Employee e JOIN e.phones pGROUP BY e
```
  - MAX
    求最大值
  - MIN
    求最小值
  - SUM
    求和
- 8.3.2 GROUP BY 子句
  通过GROUP BY子句来聚合结果.分组表达式必须是一个单值路径表达式(状态字段或单值关联关联字段)或标志变量.如果使用标志变量,那么实体不应有任何序列化的状态或大型对象字段.
  SELECT d.name ,COUNT(e),AVG(e.salary) FROM Department d JOIN d.employees e GROUP BY d.name
- 8.3.3 HAVING 子句
  HAVING子句只能跟再GROUP BY 之后,并且它的表达式仅限于先前在GROUP BY子句中标志的状态字段或单值关联字段.
  在HAVING子句中还可以使用聚合函数.
  SELECT e,COUNT(p) FROM Employee e JOIN e.projects p GROUP BY e HAVING COUNT(p) >= 2

8.4 更新查询
```
UPDATE Phone p
SET p.number = CONCAT('288',SUBSTRING(p.number,LOCATE(p.number,'-'),4)),
p.type = 'Business'
WHERE p.employee.address.city = 'Ottawa' AND
p.type = 'Office'
```