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)
标签：原生,String,DISTINCT,Player,sql,JPQL,WHERE,SELECT,name From： https://www.cnblogs.com/sunny3158/p/17045608.html