JPQL

JPQL语言
JPQL语言，即 Java Persistence Query Language 的简称。
JPQL是一种和 SQL 非常类似的中间性和对象化查询语言，
它最终会被编译成针对不同底层数据库的SQL查询，从而屏蔽不同数据库的差异。
JPQL语言的语句可以是 select 语句、update 语句或delete语句，它们都通过 Query 接口封装执行。
JPQL主要用于JPA查询数据，和SQL语句的语法大同小异；

--最基本的查询：

SELECT p FROM Player p

--查询出所有的player,包括其子类，也可以写成这样

SELECT p From Player as p

--去除重复的元素

SELECT DISTINCT
p
FROM Player p
WHERE p.position = ?1


关键字DISTINCT去除了重复的元素，并且接受参数设置条件过滤

--结合查询关联
--查询所有有team的player

SELECT DISTINCT p
FROM Player p, IN(p.teams) t

--也可以写成如下：

SELECT DISTINCT p
FROM Player p JOIN p.teams t

--或者：

SELECT DISTINCT p
FROM Player p
WHERE p.team IS NOT EMPTY

--关联关系的查询过滤

SELECT t
FROM Team t JOIN t.league l
WHERE l.sport = ’soccer’ OR l.sport =’football’

--查询所有league sports属性的team对象

SELECT DISTINCT p
FROM Player p, IN (p.teams) t
WHERE t.league.sport = :sport

--其他的查询表达式
--LIKE：

SELECT p
FROM Player p
WHERE p.name LIKE ’Mich%’

--IS NULL：

SELECT t
FROM Team t
WHERE t.league IS NULL

--IS EMPTY：

SELECT p
FROM Player p
WHERE p.teams IS EMPTY

--主要用于判断关系实体一对多集合
--在判断日期范围的时候尤其有用；
--BETWEEN

SELECT DISTINCT p
FROM Player p
WHERE p.salary BETWEEN :lowerSalary AND :higherSalary

--等价于：

p.salary >= :lowerSalary AND p.salary <= :higherSalary

--复合条件：
--查找薪水比指定姓名的员工更高的员工

SELECT DISTINCT p1
FROM Player p1, Player p2
WHERE p1.salary > p2.salary AND p2.name = :name

--IN：
o.country IN (’UK’, ’US’, ’France’)
--同时你也可以在In语句中设置参数：
o.country IN (’UK’, ’US’, ’France’, :country)
--子查询：

SELECT c
FROM Customer c
WHERE (SELECT COUNT(o) FROM c.orders o) > 10

--EXISTS子查询：

SELECT DISTINCT emp
FROM Employee emp
WHERE EXISTS (
    SELECT spouseEmp
    FROM Employee spouseEmp
WHERE spouseEmp = emp.spouse)

--ALL和ANY配合=<>=>使用

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

--其他函数：

CONCAT(String, String)String
LENGTH(String)int
LOCATE(String, String [, start])int
SUBSTRING(String, start, length)String
TRIM([[LEADING|TRAILING|BOTH] char) FROM] (String)String
LOWER(String)String
UPPER(String)String

--算法函数：

Function SyntaxReturn Type
ABS(number)int, float, or double
MOD(int, int)int
SQRT(double)double
SIZE(Collection)int

--返回参数：
1） 实体对象

SELECT t
FROM Player p, IN (p.teams) t

2) Objecth或者Object[]

SELECT c.name, c.country.name
FROM customer c
WHERE c.lastname = ’Coss’ AND c.firstname = ’Roxane’

返回一个Object[] list,[0]为name，[1]为country name

--Select语句的聚合函数：

AVGDoubleReturns the mean average of the fields.
COUNTLongReturns the total number of results.
MAXthe type of the fieldReturns the highest value in the result set.
MINthe type of the fieldReturns the lowest value in the result set.
SUMLong (for integral fields)Double (for floating point fields)BigInteger (for BigInteger fields)BigDecimal (forBigDecimal fields)Returns the sum of all the values in the result set.

--如：

SELECT COUNT(l.price)
FROM Order o JOIN o.lineItems l JOIN o.customer c
WHERE c.lastname = ’Incandenza’ AND c.firstname = ’Hal’

--构造语句：

SELECT NEW com.xyz.CustomerDetail(c.name, c.country.name)
FROM customer c
WHERE c.lastname = ’Coss’ AND c.firstname = ’Roxane’

能够利用查出的数据直接构造出对象

--Order By：

SELECT p.product_name
FROM Order o, IN(o.lineItems) l JOIN o.customer c
WHERE c.lastname = ’Faehmel’ AND c.firstname = ’Robert’
ORDER BY o.quantity

--GROUP BY：

SELECT c.country, COUNT(c)
FROM Customer c GROUP BY c.country

--Having：

SELECT c.status, AVG(o.totalPrice)
FROM Order o JOIN o.customer c
GROUP BY c.status HAVING c.status IN (1, 2, 3)