WHERE , GROUP BY , HAVING
这条查询语句会返回一个User
实例和他所创建帖子的数量。为了进一步缩小结果范围,你可以使用HAVING
过滤分组信息(由GROUP BY
标志)。下面的查询会返回User
实例及他所发的帖子数量,前提是用户的密码是8个以上的字符。
SELECT u, count(p) From Post p JOIN p.createdByUser u GROUP BY u
HAVING length(u.password) > 8
条件表达式可以用在一个JPQL 查询的WHERE
和HAVING
语句中。你必须了解使用条件表达式时的一些约束。
-
条件表达式中包括
LOB
状态字段可能无法在数据库之间进行移植。 -
字符串使用单引号字符串包裹,如'this'。如果要在查询中使用单引号,就要两个一起使用。你无法在查询中使用Java 转义方法(例如,/'代表单引号)。布尔(Boolean)字符用
TRUE
和FALSE
表示(不区分大小写),数字字符遵从Java 规范,不支持日期字符串。同样可以支持Enum
,但是你必须使用Enum,
全路径名称,如,com.sourcebeat.jpa.model.FTPType
。 -
标识变量必须出现在一个
SELECT
或DELETE
查询的FROM
语句中。如果使用UPDATE
,那么标识变量就必须在UPDATE
语句中。标识变量往往表示他们所定义的实体类型,而不能表示一个集合中的实体。 -
你可以使用位置或者命名形式的输入参数,但不能在某一查询混合使用这两种形式。输入参数可以出现一个查询的
WHERE
语句和(或者)HAVING
语句中。-
位置形式参数的格式是以一个问号(?)打头紧跟一个以1开始的正整数。例如,?1。你可以在一个查询中多个使用同一位置参数,如下所示。
SELECT u FROM User u WHERE u.dateCreated = ?1 OR u.dateUpdated = ?1
-
命名形式的参数用一个冒号(:)加一个Java 标志符如Java 变量名来表示。命名参数表示如下:
SELECT u FROM User u WHERE u.dateCreated = :aDate OR u.dateUpdated = :aDate
-
JPQL 支持函数功能,多种的IN
,LIKE
和BETWEEN
样式表达式,及面向集合(collection)的条件表达式。这一节详细讨论写查询可用的各种选择。
一个查询语句中操作符的优先级为:
-
导航操作符(.)
-
一元符号(+,-)
-
乘(*),除(/)
-
加(+),减(-)
-
比较操作符,=, >, >=, <, <=,<> (不等), [NOT] BETWEEN, [NOT] LIKE, [NOT] IN, IS [NOT] NULL, IS [NOT] EMPTY, [NOT] MEMBER [OF]
-
逻辑操作符,NOT, AND, OR
你可以用BETWEEN
操作符指定一个实体字段的范围。BETWEEN
的语法是:
expression [NOT] BETWEEN expression AND expression
这里expression
可以是一个字符串,算术或日期时间表达式。这里有几个使用BETWEEN
操作符的实例。
SELECT u FROM User u WHERE u.dateCreated between :startDate AND :endDate
SELECT t FROM Topic t WHERE t.postCount NOT BETWEEN ?1 AND ?2
你可以利用IN
比较操作符指定为一个状态字段指定一系列的值。你可以列出一个或多个字符串或参数值(基于位置或命名的),或者利用子查询动态的生成一系列的值。字符型,数字型,枚举型的状态字段可以用在IN
操作符上。状态字段的类型必须与列表中的值的类型一致。IN
操作符的语法为:
state-field [NOT] in (item {, item2}* | subquery).
这里有几个例子。
SELECT f FROM Forum f WHERE f.type IN (?1, ?2)
SELECT f FROM Forum f WHERE f.type IN (1, 2)
LIKE
允许你根据部分值搜索字符串字段。JPQL 用一个下划线(_)表示你搜索字符字符串中任一字符。在查询语句中可以用百分号(%)表示一系列字符,其它的字符代表他们本身。LIKE
的一般格式为:
string-expression [NOT] LIKE pattern [ESCAPE escape-char]
如果你必须在查询语句中使用下划线或百分号作为字面字符,使用ESCAPE
格式。例如,你可以用forum.description like ‘QA/_%’ ESCAPE ‘/’
。你必须在下划线或百分号前加入反反斜线符号人,并且在搜索字符串后面加入ESCAPE ‘/’
语法。这里列出几个例子:
-
‘tr_ck’可以匹配‘truck’和‘trick’, 但不能匹配‘trucker’。
-
‘tr%’可以匹配‘truck’, ‘tractor’, ‘trick’, 等等。
-
‘tr_ck%’可以匹配‘truck’, ‘trick’, 和‘trucker’。
如果你想搜索字符串_hello,你的查询语句应该是这样的:
‘/_hello’ ESCAPE ‘/’
ESCAPE ‘/’
告诉数据库,“我正在一个转义字符('/')上使用反斜线 ”。下面用代码表示:
em.createQuery("SELECT f FROM Forum f " +
"WHERE f.description LIKE '//_%' ESCAPE '//'");
在这段代码中,你使用了两个反斜线。第一个是为了Java 编译器,第二个是由于JPQL 解析器。
如果在MySQL 数据库上执行上面的ESCAPE
查询语句,你可能会得到一个数据库异常。默认情况下,MySQL 会将反斜线识别成一个转义符号,所以它告诉你像处理转义符号那样处理反斜线是错误的。为了使你的查询能在不同数据库之间进行移植。你必须在JDBC 连接中对所有MySQL 关闭所有MySQL 数据库实例上的反斜线转义功能。要在你的JDBC 连接上禁用反斜线转义,将下面的URL 中的sessionVariables
部分添加到你的JDBC 连接中。
jdbc:mysql://localhost:3306/db?sessionVariables=sql_mode=NO_BACKSLASH_ESCAPES
更多信息,请参考MySQL 文档。
IS NULL
比较操作符能够让检测NULL
字段,不管是单值路径表达式还是输入参数。你可以使用IS NOT NULL
来确保一个单值路径表达式有非空值,或者使用IS NULL
来检测NULL
值。
SELECT p FROM PrivateMessage p WHERE p.dateRead IS NOT NULL
// toUser references a many-to-one relationship, so you can use IS [NOT] NULL
SELECT p FROM PrivateMessage p WHERE p.toUser IS NOT NULL
// this query does not work because we are using a
// collection-value path-expression
SELECT f FROM Forum f WHERE f.topics IS NULL
IS [NOT] EMPTY
操作用于空或者非空的集合值表达式。
// the above query rewritten to use IS EMPTY
SELECT f FROM Forum f WHERE f.topics IS EMPTY
// this query will find all forum entities with topics
// (i.e. the collection is not empty)
SELECT f FROM Forum f WHERE f.topics IS NOT EMPTY
可以用[NOT] MEMBER [OF]
来判断一个实体是否是一个集合的一部分。[OF]
是可选的,不影响MEMBER
比较操作符。你可用可不用。
可以用NOT MEMBER
来判断一个实体不是一个集合的组成部分。MEMBER
的语法如下:
Expression [NOT] MEMBER [OF] collection-valued path-expression
// find the forum instance that contains Topic t
Query q2 = em.createQuery("SELECT f FROM Forum f " +
"WHERE :topic MEMBER f.topics");
q2.setParameter("topic", t);
List results2 = q2.getResultList();
JPQL 查询的WHERE
或HAVING
语句中支持字符串函数作为函数表达式。
-
CONCAT(string 1, string 2)
:将字符串2追加到字符串1。 -
SUBSTRING(string, starting position, length)
:从字符串string
开始位置starting position
截取长度为length
字符。 -
LOWER(string)
:将一个字符串string
转换成小写形式。 -
UPPER(string)
:将一个字符串string
转换成大写形式。 -
LENGTH(string)
:返回字符串string
的长度,为整数。 -
TRIM([[LEADING|TRAILING|BOTH] [char] FROM] string)
:去掉字符串string
头,尾或两者的字符char
。最简形式是TRIM(string)
,可以去掉字符串string
头尾的空格字符。 -
LOCATE(string1, string2 [,start])
:返回string2
在string1
的位置。定位函数有一个可选的起始位置start
。
JPQL 查询的WHERE
或HAVING
语句中支持数学函数作为函数表达式。
-
ABS(arithmetic expression)
:返回算术表达式的绝对值。 -
SQRT(arithmetic expression)
:求算术表达式的方根,返回一个Double。 -
MOD(arithmetic expression 1, arithmetic expression 2)
:求参数1与参数2的模,返回一个整数。 -
SIZE(collection-valued path-expression)
:计算一个集合中元素的数量,并返回一个整数。如果集合为空,返回0。