JPQL语法总结
来源:互联网 发布:iphone照片马赛克软件 编辑:程序博客网 时间:2024/05/16 01:43
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 BigInteger fields)BigDecimal (for BigDecimalfields)
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)
- JPQL语法总结
- JPQL语法总结
- JPQL语法总结
- JPQL语法总结
- JPQL 语法
- JAP JPQL相关语法
- JPQL
- JPQL
- JPQL
- JPQL
- JPQL
- JPQL
- JPQL
- JPQL
- JPQL
- JPQL
- JPQL
- JPQL
- 每天都要开心快乐哦
- c++对数组的引用
- UITextField
- android中继承TableActivity后onKeyDown事件失效
- 最长公共子序列问题LCS Longest Common Subsequence
- JPQL语法总结
- pulic继承方式下
- ubuntu14.04和window8.1双系统的安装和一些注意事项
- C语言中常见的内存错误与解决方法
- poj_3468线段树成段更新求区间和
- 基于内容的图像检索(颜色,直方图相交法,)java实现代码
- 2C02-View-Layout-Animation-list-Cascade
- UIButton事件的方法解释(一)
- linux 压缩文件的命令总结