JPQL語(yǔ)法總結(jié)
JPQL主要用于JPA查詢數(shù)據(jù)，和SQL語(yǔ)句的語(yǔ)法大同小異；
最基本的查詢：
SELECT p
FROM Player p
查詢出所有的player,包括其子類(lèi)，也可以寫(xiě)成這樣
From Player as p
去除重復(fù)的元素
SELECT DISTINCT
p
FROM Player p
WHERE p.position = ?1
關(guān)鍵字DISTINCT去除了重復(fù)的元素，并且接受參數(shù)設(shè)置條件過(guò)濾
結(jié)合查詢關(guān)聯(lián)
SELECT DISTINCT p
FROM Player p, IN(p.teams) t
查詢所有有team的player
也可以寫(xiě)成如下：
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)系的查詢過(guò)濾
SELECT t
FROM Team t JOIN t.league l
WHERE l.sport = ’soccer’ OR l.sport =’football’
查詢所有league sports屬性的team對(duì)象
SELECT DISTINCT p
FROM Player p, IN (p.teams) t
WHERE t.league.sport = :sport
其他的查詢表達(dá)式
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)系實(shí)體一對(duì)多集合
BETWEEN
SELECT DISTINCT p
FROM Player p
WHERE p.salary BETWEEN :lowerSalary AND :higherSalary
等價(jià)于：
p.salary >= :lowerSalary AND p.salary <= :higherSalary
在判斷日期范圍的時(shí)候尤其有用；
復(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’)
同時(shí)你也可以在In語(yǔ)句中設(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ù)：
|
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
|
算法函數(shù)：
|
Function Syntax
|
Return Type
|
|
ABS(number)
|
int, float, or double
|
|
MOD(int, int)
|
int
|
|
SQRT(double)
|
double
|
|
SIZE(Collection)
|
int
|
返回參數(shù)：
1） 實(shí)體對(duì)象
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’
返回一個(gè)Object[] list,[0]為name，[1]為country name
Select語(yǔ)句的聚合函數(shù)：
|
AVG
|
Double
|
Returns the mean average of the fields.
|
|
COUNT
|
Long
|
Returns the total number of results.
|
|
MAX
|
the type of the field
|
Returns the highest value in the result set.
|
|
MIN
|
the type of the field
|
Returns the lowest value in the result set.
|
|
SUM
|
Long (for integral fields)Double (for floating point fields)BigInteger (for BigInteger fields)BigDecimal (for BigDecimal 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’
構(gòu)造語(yǔ)句：
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)造出對(duì)象
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)