JPQL主要用于JPA查詢數(shù)據(jù)，和SQL語句的語法大同小異；

最基本的查詢：

SELECT p

FROM Player p

查詢出所有的player,包括其子類，也可以寫成這樣

From Player as p

去除重復(fù)的元素

SELECT DISTINCT

 p

FROM Player p

WHERE p.position = ?1

關(guān)鍵字DISTINCT去 除了重復(fù)的元素，并且接受參數(shù)設(shè)置條件過濾

結(jié)合查詢關(guān)聯(lián)

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

關(guān)聯(lián)關(guān)系的查詢過濾

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

主要用于判斷關(guān)系實體一對多集合

BETWEEN

SELECT DISTINCT p

FROM Player p

WHERE p.salary BETWEEN :lowerSalary AND :higherSalary

等價于：

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

在 判斷日期范圍的時候尤其有用；

復(fù) 合條件：

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語句中設(shè)置參數(shù)：

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)

其他函數(shù)：

算法函數(shù)：

返回參數(shù)：

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語句的聚合函數(shù)：

如：

SELECT COUNT(l.price)

FROM Order o JOIN o.lineItems l JOIN o.customer c

WHERE c.lastname = ’Incandenza’ AND c.firstname = ’Hal’

構(gòu)造語句：

SELECT NEW com.xyz.CustomerDetail(c.name, c.country.name)

 FROM customer c

WHERE c.lastname = ’Coss’ AND c.firstname = ’Roxane’

能夠利用查出的數(shù)據(jù)直接構(gòu)造出對象

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)