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字符实体|。
