JPQL主要用于JPA查询数据，和SQL语句的语法大同小异；

最基本的查询：

SELECT p

FROM Player p

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

From Player as p

去除重复的元素

SELECT DISTINCT

 p

FROM Player p

WHERE p.position = ?1

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

结合查询关联

SELECT DISTINCT p

FROM Player p, IN(p.teams) t

查询所有有team的player

也可以写成如下：

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)

其他函数：

算法函数：

返回参数：

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语句的聚合函数：

如：

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)