14.9. 表达式

在where子句中允许使用的表达式包括 大多数你可以在SQL使用的表达式种类:

  • 数学运算符+, -, *, /

  • 二进制比较运算符=, >=, <=, <>, !=, like

  • 逻辑运算符and, or, not

  • in, not in, between, is null, is not null, is empty, is not empty, member of and not member of

  • "简单的" case, case … when … then … else … end,和 "搜索" case, case when … then … else … end

  • 字符串连接符…||… or concat(…,…)

  • current_date(), current_time(), current_timestamp()

  • second(…), minute(…), hour(…), day(…), month(…), year(…),

  • EJB-QL 3.0定义的任何函数或操作：substring(), trim(), lower(), upper(), length(), locate(), abs(), sqrt(), bit_length()， mod()

  • coalesce() 和 nullif()

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

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

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

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

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

  • JDBC风格的参数传入 ?

  • 命名参数:name, :start_date, :x1

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

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

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

  1. from DomesticCat cat where cat.name between 'A' and 'B'
  1. from DomesticCat cat where cat.name in ( 'Foo', 'Bar', 'Baz' )

而且否定的格式也可以如下书写：

  1. from DomesticCat cat where cat.name not between 'A' and 'B'
  1. from DomesticCat cat where cat.name not in ( 'Foo', 'Bar', 'Baz' )

同样, 子句is null与is not null可以被用来测试空值(null).

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

  1. <property name="hibernate.query.substitutions">true 1, false 0</property>

系统将该HQL转换为SQL语句时，该设置表明将用字符 1 和 0 来 取代关键字true 和 false:

  1. from Cat cat where cat.alive = true

你可以用特殊属性size, 或是特殊函数size()测试一个集合的大小。

  1. from Cat cat where cat.kittens.size > 0
  1. from Cat cat where size(cat.kittens) > 0

对于索引了（有序）的集合，你可以使用minindex 与 maxindex函数来引用到最小与最大的索引序数。 同理，你可以使用minelement 与 maxelement函数来 引用到一个基本数据类型的集合中最小与最大的元素。

  1. from Calendar cal where maxelement(cal.holidays) > current_date
  1. from Order order where maxindex(order.items) > 100
  1. from Order order where minelement(order.items) > 10000

在传递一个集合的索引集或者是元素集(elements与indices 函数) 或者传递一个子查询的结果的时候，可以使用SQL函数any, some, all, exists, in

  1. select mother from Cat as mother, Cat as kit
  2. where kit in elements(foo.kittens)
  1. select p from NameList list, Person p
  2. where p.name = some elements(list.names)
  1. from Cat cat where exists elements(cat.kittens)
  1. from Player p where 3 > all elements(p.scores)
  1. from Show show where 'fizard' in indices(show.acts)

注意，在Hibernate3种，这些结构变量- size, elements, indices, minindex, maxindex, minelement, maxelement - 只能在where子句中使用。

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

  1. from Order order where order.items[0].id = 1234
  1. select person from Person person, Calendar calendar
  2. where calendar.holidays['national day'] = person.birthDay
  3.     and person.nationality.calendar = calendar
  1. select item from Item item, Order order
  2. where order.items[ order.deliveredItemIndices[0] ] = item and order.id = 11
  1. select item from Item item, Order order
  2. where order.items[ maxindex(order.items) ] = item and order.id = 11

在[]中的表达式甚至可以是一个算数表达式。

  1. select item from Item item, Order order
  2. where order.items[ size(order.items) - 1 ] = item

对于一个一对多的关联（one-to-many association）或是值的集合中的元素， HQL也提供内建的index()函数，

  1. select item, index(item) from Order order 
  2.     join order.items item
  3. where index(item) < 5

如果底层数据库支持标量的SQL函数，它们也可以被使用

  1. from DomesticCat cat where upper(cat.name) like 'FRI%'

如果你还不能对所有的这些深信不疑，想想下面的查询。如果使用SQL，语句长度会增长多少，可读性会下降多少：

  1. select cust
  2. from Product prod,
  3.     Store store
  4.     inner join store.customers cust
  5. where prod.name = 'widget'
  6.     and store.location.name in ( 'Melbourne', 'Sydney' )
  7.     and prod = all elements(cust.currentOrder.lineItems)

提示: 会像如下的语句

  1. SELECT cust.name, cust.address, cust.phone, cust.id, cust.current_order
  2. FROM customers cust,
  3.     stores store,
  4.     locations loc,
  5.     store_customers sc,
  6.     product prod
  7. WHERE prod.name = 'widget'
  8.     AND store.loc_id = loc.id
  9.     AND loc.name IN ( 'Melbourne', 'Sydney' )
  10.     AND sc.store_id = store.id
  11.     AND sc.cust_id = cust.id
  12.     AND prod.id = ALL(
  13.         SELECT item.prod_id
  14.         FROM line_items item, orders o
  15.         WHERE item.order_id = o.id
  16.             AND cust.current_order = o.id
  17.     )