HQL 表達式

您可以直接查看官方中文手冊，目前中文版的只提供到3.6：

http://docs.jboss.org/hibernate/core/3.6/reference/zh-CN/html/

Hibernate HSQL表达式

1.数学运算符+, -, *, /
//根据订单明细表查询所有书籍的名称和该书籍的每次销售金额
session.createQuery("select ob.books.title,sum(ob.unitPrice*ob.quantity) as totalFund from Orderbook as ob group by ob.books").list();
//生成SQL
select
books1_.Title as col_0_0_,
sum(orderbook0_.UnitPrice*orderbook0_.Quantity) as col_1_0_
from
bookshop.orderbook orderbook0_,
bookshop.books books1_
where
orderbook0_.BookID=books1_.Id
group by
orderbook0_.BookID
--------------------------------------------------------
2.二进制比较运算符=, >=, <=, <>, !=, like
逻辑运算符and, or, not


//查询价格在100元以下书名包含Java的书籍名称
session.createQuery("select bk.title from Books as bk where bk.title like 'Java%' and bk.unitPrice<100").list();


--------------------------------------------------------
3.in, not in, between, is null, is not null, is empty, is not empty, member of and not member of


3.1. memeber of示范
session.createQuery("select bk.title from Books as bk where bk.publishers member of Publishers").list();
生成SQL
session.createQuery("select bk.title from Books as bk where bk.publishers member of Publishers").list();


3.2. in示范
session.createQuery("select id,title from Books as bk where bk.id in (4949,4950)").list();
生成SQL
select
books0_.Id as col_0_0_,
books0_.Title as col_1_0_
from
bookshop.books books0_
where
books0_.Id in (
4949 , 4950
)
3.3. is not null示范
session.createQuery("select id,title from Books as bk where bk.categories is not null").list();
生成SQL
select
books0_.Id as col_0_0_,
books0_.Title as col_1_0_
from
bookshop.books books0_
where
books0_.CategoryId is not null


3.4. is not empty示范
//查询出售过的书籍信息[订单信息不为空]
session.createQuery("select id,title from Books as bk where bk.orderbooks is not empty").list();
生成SQL
select
books0_.Id as col_0_0_,
books0_.Title as col_1_0_
from
bookshop.books books0_
where
exists (
select
orderbooks1_.Id
from
bookshop.orderbook orderbooks1_
where
books0_.Id=orderbooks1_.BookID
)


--------------------------------------------------------
4."简单的" case, case ... when ... then ... else ... end,和 "搜索" case, case when ... then ... else ... end
4.1. 相等比较
查询每个用户的登录Id和角色名称
session.createQuery("select loginId,case u.userroles.id when 1 then '普通用户' when 2 then '会员' when 3 then '管理员' end as rolename from Users as u").list();
生成SQL
select
users0_.LoginId as col_0_0_,
case users0_.UserRoleId
when 1 then '普通用户'
when 2 then '会员'
when 3 then '管理员'
end as col_1_0_
from
bookshop.users users0_
4.2. 非相等比较
查询所有书籍的销量及评价
session.createQuery("select ob.books.title,sum(ob.quantity*ob.unitPrice),case when (sum(ob.quantity*ob.unitPrice)) between 1 and 500 then '销量一般' when (sum(ob.quantity*ob.unitPrice)) between 500 and 1000 then '销量可以' when (sum(ob.quantity*ob.unitPrice)) between 500 and 1000 then '销量很好' end as sellComment from Orderbook as ob group by ob.books").list();
生成SQL
select
books1_.Title as col_0_0_,
sum(orderbook0_.Quantity*orderbook0_.UnitPrice) as col_1_0_,
case
when sum(orderbook0_.Quantity*orderbook0_.UnitPrice) between 1 and 500 then '销量一般'
when sum(orderbook0_.Quantity*orderbook0_.UnitPrice) between 500 and 1000 then '销量可以'
when sum(orderbook0_.Quantity*orderbook0_.UnitPrice) between 500 and 1000 then '销量很好'
end as col_2_0_
from
bookshop.orderbook orderbook0_,
bookshop.books books1_
where
orderbook0_.BookID=books1_.Id
group by
orderbook0_.BookID


--------------------------------------------------------
5.字符串连接符...||... or concat(...,...)
将书Id号和书名用逗号分隔拼接起来
session.createQuery("select bk.id||','||bk.title from Books as bk where bk.title like 'Java%'").list();
生成SQL
select
concat(books0_.Id,
',',
books0_.Title) as col_0_0_
from
bookshop.books books0_
where
books0_.Title like 'Java%'
结果集：
5860,JavaScript
5861,Java编程思想
5862,Java Server Page


--------------------------------------------------------
6.current_date(), current_time(), current_timestamp()
session.createQuery("select current_date(),current_time(),current_timestamp() from Publishers").list();
生成SQL
select
current_date as col_0_0_,
current_time as col_1_0_,
current_timestamp as col_2_0_
from
bookshop.publishers publishers0_


7.second(...), minute(...), hour(...), day(...), month(...), year(...),
session.createQuery("select current_date(),current_time(),current_timestamp(),year(current_date()),month(current_date()),day(current_date()),hour(current_time()),minute(current_time()),second(current_time()) from Publishers").list();
生成SQL
select
current_date as col_0_0_,
current_time as col_1_0_,
current_timestamp as col_2_0_,
year(current_date) as col_3_0_,
month(current_date) as col_4_0_,
day(current_date) as col_5_0_,
hour(current_time) as col_6_0_,
minute(current_time) as col_7_0_,
second(current_time) as col_8_0_
from
bookshop.publishers publishers0_


8.EJB-QL 3.0定义的任何函数或操作
字符串处理函数
LOWER(String):转换成小写
UPPER(String):转换成大家
TRIM([[leading|trailing|both][trim_char]from)]String):去除指定字符trim_char,默认为空格
CONCAT(String1,String2):连接字符串
LENGTH(String):求字符串长度
LOCATE(String1,String2[,start]):String1在String2的什么位置
SUBSTRING(String1,sart,length):截取字符串
数字函数
ABS(number):绝对值
SORT(double):平方根
MOD(int,int):求余数
返回日期和时间的函数
CURRENT_DATE:返回当前日期
CURRENT_TIME:返回当前时间
CURRENT_TIMESTAMP:返回当前时间戳
聚合函数
COUNT():返回查询结果集中的条目数
MAX():找出最大值
MIN():找出最小值
AVG(numeric):平均值
SUM(numerc):求和




9.coalesce() 和 nullif()


10.str() 把数字或者时间值转换为可读的字符串


11.cast(... as ...), 其第二个参数是某Hibernate类型的名字，以及extract(... from ...)，只要ANSI cast() 和 extract() 被底层数据库支持


12.HQL index() 函数，作用于join的有序集合的别名。


13.HQL函数，把集合作为参数:size(), minelement(), maxelement(), minindex(), maxindex(),还有特别的elements() 和indices函数，可以与数量词加以限定：some, all, exists, any, in。


14.任何数据库支持的SQL标量函数，比如sign(), trunc(), rtrim(), sin()


15.JDBC风格的参数传入 ?


16.命名参数:name, :start_date, :x1


17.SQL 直接常量 'foo', 69, 6.66E+2, '1970-01-01 10:00:01.0'


18.Java public static final 类型的常量 eg.Color.TABBY


关键字in与between可按如下方法使用:


from DomesticCat cat where cat.name between 'A' and 'B'
from DomesticCat cat where cat.name in ( 'Foo', 'Bar', 'Baz' )
而且否定的格式也可以如下书写：


from DomesticCat cat where cat.name not between 'A' and 'B'
from DomesticCat cat where cat.name not in ( 'Foo', 'Bar', 'Baz' )
同样, 子句is null与is not null可以被用来测试空值(null).


在Hibernate配置文件中声明HQL“查询替代（query substitutions）”之后， 布尔表达式（Booleans）可以在其他表达式中轻松的使用:


<property name="hibernate.query.substitutions">true 1, false 0</property>
系统将该HQL转换为SQL语句时，该设置表明将用字符 1 和 0 来 取代关键字true 和 false:


from Cat cat where cat.alive = true
你可以用特殊属性size, 或是特殊函数size()测试一个集合的大小。


from Cat cat where cat.kittens.size > 0
from Cat cat where size(cat.kittens) > 0
对于索引了（有序）的集合，你可以使用minindex 与 maxindex函数来引用到最小与最大的索引序数。 同理，你可以使用minelement 与 maxelement函数来 引用到一个基本数据类型的集合中最小与最大的元素。


from Calendar cal where maxelement(cal.holidays) > current_date
from Order order where maxindex(order.items) > 100
from Order order where minelement(order.items) > 10000
在传递一个集合的索引集或者是元素集(elements与indices 函数) 或者传递一个子查询的结果的时候，可以使用SQL函数any, some, all, exists, in


select mother from Cat as mother, Cat as kit
where kit in elements(foo.kittens)
select p from NameList list, Person p
where p.name = some elements(list.names)
from Cat cat where exists elements(cat.kittens)
from Player p where 3 > all elements(p.scores)
from Show show where 'fizard' in indices(show.acts)
注意，在Hibernate3种，这些结构变量- size, elements, indices, minindex, maxindex, minelement, maxelement - 只能在where子句中使用。


一个被索引过的（有序的）集合的元素(arrays, lists, maps)可以在其他索引中被引用（只能在where子句中）：


from Order order where order.items[0].id = 1234
select person from Person person, Calendar calendar
where calendar.holidays['national day'] = person.birthDay
and person.nationality.calendar = calendar
select item from Item item, Order order
where order.items[ order.deliveredItemIndices[0] ] = item and order.id = 11
select item from Item item, Order order
where order.items[ maxindex(order.items) ] = item and order.id = 11
在[]中的表达式甚至可以是一个算数表达式。


select item from Item item, Order order
where order.items[ size(order.items) - 1 ] = item
对于一个一对多的关联（one-to-many association）或是值的集合中的元素， HQL也提供内建的index()函数，


select item, index(item) from Order order
join order.items item
where index(item) < 5
如果底层数据库支持标量的SQL函数，它们也可以被使用


from DomesticCat cat where upper(cat.name) like 'FRI%'
如果你还不能对所有的这些深信不疑，想想下面的查询。如果使用SQL，语句长度会增长多少，可读性会下降多少：


select cust
from Product prod,
Store store
inner join store.customers cust
where prod.name = 'widget'
and store.location.name in ( 'Melbourne', 'Sydney' )
and prod = all elements(cust.currentOrder.lineItems)
提示: 会像如下的语句


SELECT cust.name, cust.address, cust.phone, cust.id, cust.current_order
FROM customers cust,
stores store,
locations loc,
store_customers sc,
product prod
WHERE prod.name = 'widget'
AND store.loc_id = loc.id
AND loc.name IN ( 'Melbourne', 'Sydney' )
AND sc.store_id = store.id
AND sc.cust_id = cust.id
AND prod.id = ALL(
SELECT item.prod_id
FROM line_items item, orders o
WHERE item.order_id = o.id
AND cust.current_order = o.id
)