JPQL涓昏鐢ㄤ簬JPA鏌ヨ鏁版嵁錛屽拰SQL璇彞鐨勮娉曞ぇ鍚屽皬寮傦紱
鏈鍩烘湰鐨勬煡璇細
SELECT p
FROM Player p
鏌ヨ鍑烘墍鏈夌殑player,鍖呮嫭鍏跺瓙綾伙紝涔熷彲浠ュ啓鎴愯繖鏍?/span>
From Player as p
鍘婚櫎閲嶅鐨勫厓绱?/font>
SELECT DISTINCT
p
FROM Player p
WHERE p.position = ?1
鍏抽敭瀛?/span>DISTINCT鍘婚櫎浜嗛噸澶嶇殑鍏冪礌錛屽茍涓旀帴鍙楀弬鏁拌緗潯浠惰繃婊?/span>
緇撳悎鏌ヨ鍏寵仈
SELECT DISTINCT p
FROM Player p, IN(p.teams) t
鏌ヨ鎵鏈夋湁team鐨?/span>player
涔熷彲浠ュ啓鎴愬涓嬶細
SELECT DISTINCT p
FROM Player p JOIN p.teams t
鎴栬咃細
SELECT DISTINCT p
FROM Player p
WHERE p.team IS NOT EMPTY
鍏寵仈鍏崇郴鐨勬煡璇㈣繃婊?/span>
SELECT t
FROM Team t JOIN t.league l
WHERE l.sport = ’soccer’ OR l.sport =’football’
鏌ヨ鎵鏈?/span>league sports灞炴х殑team瀵硅薄
SELECT DISTINCT p
FROM Player p, IN (p.teams) t
WHERE t.league.sport = :sport
鍏朵粬鐨勬煡璇㈣〃杈懼紡
LIKE錛?/span>
SELECT p
FROM Player p
WHERE p.name LIKE ’Mich%’
IS NULL錛?/span>
SELECT t
FROM Team t
WHERE t.league IS NULL
IS EMPTY錛?/span>
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
鍦ㄥ垽鏂棩鏈熻寖鍥寸殑鏃跺欏挨鍏舵湁鐢紱
澶嶅悎鏉′歡錛?/span>
SELECT DISTINCT p1
FROM Player p1, Player p2
WHERE p1.salary > p2.salary AND p2.name = :name
鏌ユ壘钖按姣旀寚瀹氬鍚嶇殑鍛樺伐鏇撮珮鐨勫憳宸?/font>
IN錛?/span>
o.country IN (’UK’, ’US’, ’France’)
鍚屾椂浣犱篃鍙互鍦?/span>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鍜?/span>ANY閰嶅悎=<>=>浣跨敤
SELECT emp
FROM Employee emp
WHERE emp.salary > ALL (
SELECT m.salary
FROM Manager m
WHERE m.department = emp.department)
鍏朵粬鍑芥暟錛?/font>
|
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 |
綆楁硶鍑芥暟錛?/font>
|
Function Syntax |
Return Type |
|
ABS(number) |
int, float, or double |
|
MOD(int, int) |
int |
|
SQRT(double) |
double |
|
SIZE(Collection) |
int |
榪斿洖鍙傛暟錛?/font>
1錛?/font> 瀹炰綋瀵硅薄
SELECT t
FROM Player p, IN (p.teams) t
2) Objecth鎴栬?/span>Object[]
SELECT c.name, c.country.name
FROM customer c
WHERE c.lastname = ’Coss’ AND c.firstname = ’Roxane’
榪斿洖涓涓?/span>Object[] list,[0]涓?/span>name錛?/span>[1]涓?/span>country name
Select璇彞鐨勮仛鍚堝嚱鏁幫細
|
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’
鏋勯犺鍙ワ細
SELECT NEW com.xyz.CustomerDetail(c.name, c.country.name)
FROM customer c
WHERE c.lastname = ’Coss’ AND c.firstname = ’Roxane’
鑳藉鍒╃敤鏌ュ嚭鐨勬暟鎹洿鎺ユ瀯閫犲嚭瀵硅薄
Order By錛?/span>
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錛?/span>
SELECT c.country, COUNT(c)
FROM Customer c GROUP BY c.country
Having錛?/span>
SELECT c.status, AVG(o.totalPrice)
FROM Order o JOIN o.customer c
GROUP BY c.status HAVING c.status IN (1, 2, 3)
]]>