兔八哥笔记14(3)：Hibernate 查询语言(HQL)

  兔八哥笔记14(3)：Hibernate 查询语言(HQL) 

10.11 子查询（Subqueries）

       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 例子（Examples）

       Hibernate的查询功能相当强大，而且复杂。事实上，功能强大的查询语言是Hibernate的主要的卖点（selling points。原来这是一个外来词呀，今天才知道。^_^）。

       下面是一些简单的查询例子，这是从我最近的项目中挑出来的，在大多数情况下，你写的查询要比这些更简单。

       下面这个查询使用到的表：order, order_line, product, catalog 和price。有4个内连接和一个没有关系（uncorrelated）的子查询。

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
     
     

简直是怪物！（What a monster!）。现实生活中（in real life），我们对子查询并不是很关心，我们大多数的查询更像下面这样：

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
     
     

下面这个 查询翻译成SQL将需要2个内连接和一个相关联的子查询。它用到的表：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
     
     

       如果我映射tatusChanges为一个List代替set，那个我们的查询将更简单：

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的isnull（）函数，用到3个内连接和1个外联接，用大的表：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 提示和窍门

       你可以不用真正的返回查询结果，而知道查询结果的数量：

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

       按照返回的集合的大小排序，可以使用下面的语句：

select usr.id, usr.name  from User as usr

left join usr.messages as msg

group by usr.id, usr.name

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

上面这个办法因为使用了内连接不能返回拥有0个消息的用户的信息，下面这个查询是有用的：

select usr.id, usr.name

from User as usr

    left join usr.messages as msg

group by usr.id, usr.name

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接口实现分页（pageable）功能：

Query q = s.createFilter( collection, "" ); // the trivial filter

q.setMaxResults(PAGE_SIZE);

q.setFirstResult(PAGE_SIZE * pageNumber);

List page = q.list();

使用查询过滤器，可以实现集合元素的排序和分组：

Collection orderedCollection = s.filter( collection, "order by this.amount" );

Collection counts = s.filter( collection, "select this.type, count(this) group by this.type" );

你可以不用对集合进行初始化，就可以得到它的大小：

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

 

 

                                                         兔八哥

2004年1月4日星期日 17:57