JPA查询语言(3)

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			

			

条件表达式(CONDITIONAL EXPRESSIONS)

条件表达式可以用在一个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										
    
    										

函数与表达式(FUNCTIONS AND EXPRESSIONS)

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 操作符指定一个实体字段的范围。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 操作符上。状态字段的类型必须与列表中的值的类型一致。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

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

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 EMPTY

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					

					

MEMBER

可以用[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();					

					

字符串函数(STRING FUNCTIONS)

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 。

数学函数(ARITHMETIC FUNCTIONS)

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。

时间函数(DATETIME FUNCTIONS)

支持以下时间函数。

• CURRENT_DATE :当时日期，由数据库决定。

• CURRENT_TIME :当时时间，由数据库决定。

• CURRENT_TIMESTAMP :当时日期和时间，由数据库决定