?? queryhql.html
字號:
如果你还不能对所有的这些深信不疑，想想下面的查询。如果使用SQL，语句长度会增长多少，可读性会下降多少： </p><pre class="programlisting">select custfrom Product prod, Store store inner join store.customers custwhere prod.name = 'widget' and store.location.name in ( 'Melbourne', 'Sydney' ) and prod = all elements(cust.currentOrder.lineItems)</pre><p> <span class="emphasis"><em>提示:</em></span> 会像如下的语句 </p><pre class="programlisting">SELECT cust.name, cust.address, cust.phone, cust.id, cust.current_orderFROM customers cust, stores store, locations loc, store_customers sc, product prodWHERE 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 )</pre></div><div class="sect1" lang="zh-cn"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="queryhql-ordering"></a>14.10. order by子句</h2></div></div><div></div></div><p> 查询返回的列表(list)可以按照一个返回的类或组件（components)中的任何属性（property）进行排序： </p><pre class="programlisting">from DomesticCat catorder by cat.name asc, cat.weight desc, cat.birthdate</pre><p> 可选的<tt class="literal">asc</tt>或<tt class="literal">desc</tt>关键字指明了按照升序或降序进行排序. </p></div><div class="sect1" lang="zh-cn"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="queryhql-grouping"></a>14.11. group by子句</h2></div></div><div></div></div><p> 一个返回聚集值(aggregate values)的查询可以按照一个返回的类或组件（components)中的任何属性（property）进行分组： </p><pre class="programlisting">select cat.color, sum(cat.weight), count(cat) from Cat catgroup by cat.color</pre><pre class="programlisting">select foo.id, avg(name), max(name) from Foo foo join foo.names namegroup by foo.id</pre><p> <tt class="literal">having</tt>子句在这里也允许使用. </p><pre class="programlisting">select cat.color, sum(cat.weight), count(cat) from Cat catgroup by cat.color having cat.color in (eg.Color.TABBY, eg.Color.BLACK)</pre><p> 如果底层的数据库支持的话(例如不能在MySQL中使用)，SQL的一般函数与聚集函数也可以出现 在<tt class="literal">having</tt>与<tt class="literal">order by</tt> 子句中。 </p><pre class="programlisting">select catfrom Cat cat join cat.kittens kittengroup by cathaving avg(kitten.weight) > 100order by count(kitten) asc, sum(kitten.weight) desc</pre><p> 注意<tt class="literal">group by</tt>子句与 <tt class="literal">order by</tt>子句中都不能包含算术表达式（arithmetic expressions）. </p></div><div class="sect1" lang="zh-cn"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="queryhql-subqueries"></a>14.12. 子查询</h2></div></div><div></div></div><p> 对于支持子查询的数据库，Hibernate支持在查询中使用子查询。一个子查询必须被圆括号包围起来（经常是SQL聚集函数的圆括号）。 甚至相互关联的子查询（引用到外部查询中的别名的子查询）也是允许的。 </p><pre class="programlisting">from Cat as fatcat where fatcat.weight > ( select avg(cat.weight) from DomesticCat cat )</pre><pre class="programlisting">from DomesticCat as cat where cat.name = some ( select name.nickName from Name as name )</pre><pre class="programlisting">from Cat as cat where not exists ( from Cat as mate where mate.mate = cat )</pre><pre class="programlisting">from DomesticCat as cat where cat.name not in ( select name.nickName from Name as name )</pre><pre class="programlisting">select cat.id, (select max(kit.weight) from cat.kitten kit) from Cat as cat</pre><p> 注意，HQL自查询只可以在select或者where子句中出现。 </p><p> 在select列表中包含一个表达式以上的子查询，你可以使用一个元组构造符（tuple constructors）： </p><pre class="programlisting">from Cat as cat where not ( cat.name, cat.color ) in ( select cat.name, cat.color from DomesticCat cat )</pre><p> 注意在某些数据库中（不包括Oracle与HSQL），你也可以在其他语境中使用元组构造符， 比如查询用户类型的组件与组合： </p><pre class="programlisting">from Person where name = ('Gavin', 'A', 'King')</pre><p> 该查询等价于更复杂的： </p><pre class="programlisting">from Person where name.first = 'Gavin' and name.initial = 'A' and name.last = 'King')</pre><p> 有两个很好的理由使你不应当作这样的事情：首先，它不完全适用于各个数据库平台；其次，查询现在依赖于映射文件中属性的顺序。 </p></div><div class="sect1" lang="zh-cn"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="queryhql-examples"></a>14.13. HQL示例</h2></div></div><div></div></div><p> Hibernate查询可以非常的强大与复杂。实际上，Hibernate的一个主要卖点就是查询语句的威力。这里有一些例子，它们与我在最近的 一个项目中使用的查询非常相似。注意你能用到的大多数查询比这些要简单的多！ </p><p> 下面的查询对于某个特定的客户的所有未支付的账单，在给定给最小总价值的情况下，返回订单的id，条目的数量和总价值， 返回值按照总价值的结果进行排序。为了决定价格，查询使用了当前目录。作为转换结果的SQL查询，使用了<tt class="literal">ORDER</tt>, <tt class="literal">ORDER_LINE</tt>, <tt class="literal">PRODUCT</tt>, <tt class="literal">CATALOG</tt> 和<tt class="literal">PRICE</tt> 库表。 </p><pre class="programlisting">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 pricewhere 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?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -