CSDN博客

img ltf_ty

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

发表于2004/1/7 18:16:00  1336人阅读

分类: J2EE相关

  兔八哥笔记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 SQLisnull()函数,用到3个内连接和1个外联接,用大的表:account, payment, payment_status, account_type, organizationorg_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();

 

 

                                                         兔八哥

200414日星期日 17:57

0 0

相关博文

我的热门文章

img
取 消
img