HQL & JPQL - Part Ⅱ

Basic HQL and JPQL queries

We apply selection to name the data source, restriction to match records to the criteria, and projection to select the data you want returned from a query.

Eclipse插件Hibernate Tools，可以直接写HQL来查询，可看到生成的SQL及查询结果。
此处只关注SELECT语句，HQL也支持UPDATE,DELETE,INSERT..SELECT; JPQL支持UPDATE,DELETE.
HQL查询语句可以省略select clause，但是JPQL不能省略。

Selection

查询一个实体类：

// HQL，注意JPQL需要加上select clause
from Item

生成的SQL：

select i.ITEM_ID, i.NAME, i.DESCRIPTION, ... from ITEM i

Using aliases

使用别名，as关键字可省略，HQL/JPQL中关键字不区分大小写。

// HQL
from Item as item
from Item item
// JPQL
select item from Item item

Polymorphic queries

多态查询，可以查询父类及其子类。

// CreditCard和BankAccount是BillingDetails的子类。
from BillingDetails

// 查询出所有persistent objects
from java.lang.Object

// 查询出所有实现了Serializable接口的persistent objects
from java.io.Serializable

Restriction

WHERE子句

// 查询条件值用单引号括起
from User u where u.email = 'foo@hibernate.org'

// 还可以使用字面量true false
from Item i where i.isActive = true

Comparison expressions

from Bid bid where bid.amount between 1 and 10
from Bid bid where bid.amount > 100
from User u where u.email in ('foo@bar', 'bar@foo')

// 判断是否为空
from User u where u.email is null
from Item i where i.successfulBid is not null

// 模糊查询
from User u where u.firstname like 'G%'
from User u where u.firstname not like '%Foo B%
// 转义%，返回firstname以%Foo开头的用户
from User u where u.firstname like '\%Foo%' escape='\'

// 运算
from Bid bid where ( bid.amount / 0.71 ) - 100.0 > 0.0

// 逻辑运算
from User user where user.firstname like 'G%' and user.lastname like 'K%'
from User u where ( u.firstname like 'G%' and u.lastname like 'K%' ) or u.email in ('foo@hibernate.org', 'bar@hibernate.org' )

Operator	Description
.	Navigation path expression operator
+, -	Unary positive or negative signing (all unsigned numeric values are considered positive)
*, /	Regular multiplication and division of numeric values
+, -	Regular addition and subtraction of numeric values
=, <>, <, >, >=, <=, [NOT] BETWEEN,[NOT] LIKE, [NOT] IN, IS [NOT] NULL	Binary comparison operators with SQL semantics
IS [NOT] EMPTY, [NOT] MEMBER [OF]	Binary operators for collections in HQL and JPQL
NOT, AND, OR	Logical operators for ordering of expression evaluation

Expressions with collections

// 返回所有bids集合属性不为空的Item对象
from Item i where i.bids is not empty

// 返回主键是123的Item对象，还有此对象所属的Category对象
from Item i, Category c where i.id = '123' and i member of c.items

.id总是指定实体类的主键属性，即使其属性名称不是id。

Another trick you use here is the special .id path; this field always refers to the database identifier of an entity, no matter what the name of the identifier property is.

Calling functions

在WHERE，HAVING子句中调用函数：

from User u where lower(u.email) = 'foo@hibernate.org'

HQL,JPQL提供了统一的字符串拼接函数concat():

from User user where concat(user.firstname, user.lastname) like 'G% K%'

利用size()函数判断集合大小：

from Item i where size(i.bids) > 3

JPA标准中支持的函数：

Function	Applicability
UPPER(s), LOWER(s)	String values; returns a string value
CONCAT(s1, s2)	String values; returns a string value
SUBSTRING(s, offset, length)	String values (offset starts at 1); returns a string value
TRIM( [[BOTH | LEADING | TRAILING] char [FROM]] s)	Trims spaces on BOTH sides of s if no char or other specification is given; returns a string value
LENGTH(s)	String value; returns a numeric value
LOCATE(search, s, offset)	Searches for position of ss in s starting at offset; returns a numeric value
ABS(n), SQRT(n), MOD(dividend,divisor)	Numeric values; returns an absolute of same type as input, square root as double, and the remainder of a division as an integer
SIZE(c)	Collection expressions; returns an integer, or 0 if empty

Hibernate扩展的函数：

Function	Applicability
BIT_LENGTH(s)	Returns the number of bits in s
CURRENT_DATE(), CURRENT_TIME(),CURRENT_TIMESTAMP()	Returns the date and/or time of the database management system machine
SECOND(d), MINUTE(d), HOUR(d),DAY(d), MONTH(d), YEAR(d)	Extracts the time and date from a temporal argument
CAST(t as Type)	Casts a given type t to a Hibernate Type
INDEX(joinedCollection)	Returns the index of joined collection element
MINELEMENT(c), MAXELEMENT(c),MININDEX(c), MAXINDEX(c),ELEMENTS(c), INDICES(c)	Returns an element or index of indexed collections(maps, lists, arrays)
Registered in org.hibernate.Dialect	Extends HQL with other functions in a dialect

以上所有函数都会被Hibernate转换成不同数据库的特定SQL函数。如果在HQL中使用的函数，Hibernate无法识别，Hibernate会直接传递此函数到数据库。

Ordering query results

from User u order by u.username desc

from User u order by u.lastname asc, u.firstname asc

Projection

Projection，投射，其实就是指定要查询的字段。

Simple projection of entities and scalar values

Query q = session.createQuery("from Item i, Bid b");
// Query q = entityManager.createQuery("select i, b from Item i, Bid b");
Iterator pairs = q.list().iterator();
// Iterator pairs = q.getResultList().iterator();
while (pairs.hasNext()) {
    Object[] pair = (Object[]) pairs.next();
    Item item = (Item) pair[0];
    Bid bid = (Bid) pair[1];
}

以上查询返回了 a List of Object[]，List中每个元素是对象数组，数组中包含两个实体对象。

以下查询也返回 a List of Object[]，但是数组元素不是实体对象，而是scalar value，所以这种查询被称为scalar query。

select i.id, i.description, i.initialPrice from Item i where i.endDate > current_date()

Getting distinct results

去重，使用distinct关键字。

select distinct item.description from Item item

Calling functions

在SELECT子句中也可以使用函数，尤其是aggregate functions，后面一节将会讲到。

select item.startDate, current_date() from Item item

select item.startDate, item.endDate, upper(item.name) from Item item

需要注意的是：和WHERE子句不同，当在SELECT子句中，如果使用的函数Hibernate无法识别，是不会传送给数据库的；函数必须在org.hibernate.Dialect中注册。

---

此文是对《Java Persistence with Hibernate》第14章第二部分的归纳。

Markdown表格中如果要显示'|'，可以使用HTML字符实体|。