JPQL语法总结对比原生sql

JPQL语法总结对比原生sql
原文连接：https://blog.csdn.net/u013321164/article/details/17675617

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)

 

 

 

其他函数：

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

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 BigIntegerfields)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：

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)