calmJava

StillWaterRunsDeep
随笔 - 3, 文章 - 11, 评论 - 0, 引用 - 0
数据加载中……

2011年4月1日

JPQL语法总结

JPQL语法总结

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

查询所有有teamplayer

也可以写成如下:

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)

ALLANY配合=<>=>使用

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

posted @ 2011-04-01 11:06 calmJava 阅读(12656) | 评论 (0)编辑 收藏