hibernate2 参考文档之十.txt

Hibernate使用说明书

from eg.Player p where 3 > all elements(p.scores)

from eg.Show show where 'fizard' in indices(show.acts)
请注意这些设施：size,elements,indices,minIndex,maxIndex,minElement,maxElement 都有一些使用限制： 

在where子句中: 只对支持子查询的数据库有效 

在select子句中：只有elements和indices有效 

有序的集合(数组、list、map)的元素可以用索引来进行引用（只限于在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
HQL也对一对多关联或者值集合提供内置的index()函数。 

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

from eg.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)
提示：对应的SQL语句可能是这样的 

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
    )
10.9. order by 子句
查询返回的列表可以按照任何返回的类或者组件的属性排序： 

from eg.DomesticCat cat
order by cat.name asc, cat.weight desc, cat.birthdate
asc和desc是可选的，分别代表升序或者降序。 

10.10. group by 子句
返回统计值的查询可以按照返回的类或者组件的任何属性排序： 

select cat.color, sum(cat.weight), count(cat) 
from eg.Cat cat
group by cat.color

select foo.id, avg( elements(foo.names) ), max( indices(foo.names) ) 
from eg.Foo foo
group by foo.id
请注意：你可以在select子句中使用elements和indices指令，即使你的数据库不支持子查询也可以。 

having子句也是允许的。 

select cat.color, sum(cat.weight), count(cat) 
from eg.Cat cat
group by cat.color 
having cat.color in (eg.Color.TABBY, eg.Color.BLACK)
在having子句中允许出现SQL函数和统计函数，当然这需要底层数据库支持才行。（比如说,MySQL就不支持） 

select cat
from eg.Cat cat
    join cat.kittens kitten
group by cat
having avg(kitten.weight) > 100
order by count(kitten) asc, sum(kitten.weight) desc
注意，group by子句和order by子句都不支持数学表达式。 

10.11. 子查询
对于支持子查询的数据库来说，Hibernate支持在查询中嵌套子查询。子查询必须由圆括号包围（常常是在一个SQL统计函数中）。也允许关联子查询（在外部查询中作为一个别名出现的子查询）。 

from eg.Cat as fatcat 
where fatcat.weight > ( 
    select avg(cat.weight) from eg.DomesticCat cat 
)

from eg.DomesticCat as cat 
where cat.name = some ( 
    select name.nickName from eg.Name as name 
)
    
from eg.Cat as cat 
where not exists ( 
    from eg.Cat as mate where mate.mate = cat 
)

from eg.DomesticCat as cat 
where cat.name not in ( 
    select name.nickName from eg.Name as name 
)
10.12. 示例
Hibernate查询可以非常强大复杂。实际上，强有力的查询语言是Hibernate的主要卖点之一。下面给出的示例与我在近期实际项目中使用的一些查询很类似。请注意你编写的查询大部分等都不会这么复杂！ 

下面的查询对特定的客户，根据给定的最小总计值（minAmount)，查询出所有未付订单，返回其订单号、货品总数、订单总金额，结果按照总金额排序。在决定价格的时候，参考当前目录。产生的SQL查询，在ORDER,ORDER_LINE,PRODUCT,CATALOG和PRICE表之间有四个内部连接和一个没有产生关联的字查询。 

select order.id, sum(price.amount), count(item)
from Order as order
    join order.lineItems as item
    join item.product as product,
    Catalog as catalog
    join catalog.prices as price
where order.paid = false
    and order.customer = :customer
    and price.product = product
    and catalog.effectiveDate < sysdate
    and catalog.effectiveDate >= all (
        select cat.effectiveDate 
        from Catalog as cat
        where cat.effectiveDate < sysdate
    )
group by order
having sum(price.amount) > :minAmount
order by sum(price.amount) desc
好家伙，真长！实际上，在现实生活中我并不是非常热衷于子查询，所以我的查询往往是这样的： 

select order.id, sum(price.amount), count(item)
from Order as order
    join order.lineItems as item
    join item.product as product,
    Catalog as catalog
    join catalog.prices as price
where order.paid = false
    and order.customer = :customer
    and price.product = product
    and catalog = :currentCatalog
group by order
having sum(price.amount) > :minAmount
order by sum(price.amount) desc
下面的查询统计付款记录处于每种状态中的数量，要排除所有处于AWAITING_APPROVAL状态的，或者最近一次状态更改是由当前用户做出的。它翻译成SQL查询后，在PAYMENT,PAYMENT_STATUS和PAYMENT_STATUS_CHANGE表之间包含两个内部连接和一个用于关联的子查询。 

select count(payment), status.name 
from Payment as payment 
    join payment.currentStatus as status
    join payment.statusChanges as statusChange
where payment.status.name <> PaymentStatus.AWAITING_APPROVAL
    or (
        statusChange.timeStamp = ( 
            select max(change.timeStamp) 
            from PaymentStatusChange change 
            where change.payment = payment
        )
        and statusChange.user <> :currentUser
    )
group by status.name, status.sortOrder
order by status.sortOrder
假若我已经把statusChange集合映射为一个列表而不是一个集合的话，查询写起来会简单很多。 

select count(payment), status.name 
from Payment as payment
    join payment.currentStatus as status
where payment.status.name <> PaymentStatus.AWAITING_APPROVAL
    or payment.statusChanges[ maxIndex(payment.statusChanges) ].user <> :currentUser
group by status.name, status.sortOrder
order by status.sortOrder
下面的查询使用了MS SQL Server的isNull()函数，返回当前用户所属的组织所有账户和未付支出。翻译为SQL查询后，在ACCOUNT, PAYMENT, PAYMENT_STATUS,ACCOUNT_TYPE, ORGANIZATION 和 ORG_USER表之间有三个内部连接，一个外部连接和一个子查询。 

select account, payment
from Account as account
    left outer join account.payments as payment
where :currentUser in elements(account.holder.users)
    and PaymentStatus.UNPAID = isNull(payment.currentStatus.name, PaymentStatus.UNPAID)
order by account.type.sortOrder, account.accountNumber, payment.dueDate
对某些数据库而言，我们可能不能依赖（关联的）子查询。 

select account, payment
from Account as account
    join account.holder.users as user
    left outer join account.payments as payment
where :currentUser = user
    and PaymentStatus.UNPAID = isNull(payment.currentStatus.name, PaymentStatus.UNPAID)
order by account.type.sortOrder, account.accountNumber, payment.dueDate
10.13. 提示和技巧（Tips & Tricks）
你不返回结果集也可以查询结果集的大小： 

( (Integer) session.iterate("select count(*) from ....").next() ).intValue()
要依据一个集合的大小对结果集排序，可以用下面的查询来对付一对多或多对多的关联： 

select usr
from User as usr 
    left join usr.messages as msg
group by usr
order by count(msg)
如果你的数据库支持子查询，你可以在查询的where子句中对选择的大小进行条件限制： 

from User usr where size(usr.messages) >= 1
如果你的数据库不支持子查询，可以使用下列查询： 

select usr.id, usr.name
from User usr.name
    join usr.messages msg
group by usr.id, usr.name
having count(msg) >= 1
因为使用了inner join,这个解决方法不能返回没有message的User.下面的方式就可以： 

select usr
from User as usr
    left join usr.messages as msg
group by usr
having count(msg) = 0
JavaBean的属性可以直接作为命名的查询参数： 

Query q = s.createQuery("from foo in class Foo where foo.name=:name and foo.size=:size");
q.setProperties(fooBean); // fooBean has getName() and getSize()
List foos = q.list();
在Query接口中使用过滤器(filter),可以对集合分页： 

Query q = s.createFilter( collection, "" ); // the trivial filter
q.setMaxResults(PAGE_SIZE);
q.setFirstResult(PAGE_SIZE * pageNumber);
List page = q.list();
集合元素可以使用查询过滤器(query filter)进行排序或者分组： 

List orderedCollection = s.filter( collection, "order by this.amount" );
List counts = s.filter( collection, "select this.type, count(this) group by this.type" );
不用初始化集合就可以得到其大小： 

( (Integer) session.iterate("select count(*) from ....").next() ).intValue();

--------------------------------------------------------------------------------
Prev  Up  Next 
Chapter 9. 父子关系(Parent Child Relationships)  Home  Chapter 11. 实例(A Worked Example)