queryhql.html

是一个中文的Hibernate库文档

        </p><p>
            &#19979;&#38754;&#30340;&#26597;&#35810;&#23545;&#20110;&#26576;&#20010;&#29305;&#23450;&#30340;&#23458;&#25143;&#30340;&#25152;&#26377;&#26410;&#25903;&#20184;&#30340;&#36134;&#21333;&#65292;&#22312;&#32473;&#23450;&#32473;&#26368;&#23567;&#24635;&#20215;&#20540;&#30340;&#24773;&#20917;&#19979;&#65292;&#36820;&#22238;&#35746;&#21333;&#30340;id&#65292;&#26465;&#30446;&#30340;&#25968;&#37327;&#21644;&#24635;&#20215;&#20540;&#65292;
            &#36820;&#22238;&#20540;&#25353;&#29031;&#24635;&#20215;&#20540;&#30340;&#32467;&#26524;&#36827;&#34892;&#25490;&#24207;&#12290;&#20026;&#20102;&#20915;&#23450;&#20215;&#26684;&#65292;&#26597;&#35810;&#20351;&#29992;&#20102;&#24403;&#21069;&#30446;&#24405;&#12290;&#20316;&#20026;&#36716;&#25442;&#32467;&#26524;&#30340;SQL&#26597;&#35810;&#65292;&#20351;&#29992;&#20102;<tt class="literal">ORDER</tt>, 
            <tt class="literal">ORDER_LINE</tt>, <tt class="literal">PRODUCT</tt>, <tt class="literal">CATALOG</tt> &#21644;<tt class="literal">PRICE</tt>
            &#24211;&#34920;&#12290;
        </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 price
where order.paid = false
    and order.customer = :customer
    and price.product = product
    and catalog.effectiveDate &lt; sysdate
    and catalog.effectiveDate &gt;= all (
        select cat.effectiveDate 
        from Catalog as cat
        where cat.effectiveDate &lt; sysdate
    )
group by order
having sum(price.amount) &gt; :minAmount
order by sum(price.amount) desc</pre><p>
            &#36825;&#31616;&#30452;&#26159;&#19968;&#20010;&#24618;&#29289;&#65281;&#23454;&#38469;&#19978;&#65292;&#22312;&#29616;&#23454;&#29983;&#27963;&#20013;&#65292;&#25105;&#24182;&#19981;&#28909;&#34935;&#20110;&#23376;&#26597;&#35810;&#65292;&#25152;&#20197;&#25105;&#30340;&#26597;&#35810;&#35821;&#21477;&#30475;&#36215;&#26469;&#26356;&#20687;&#36825;&#20010;&#65306;
        </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 price
where order.paid = false
    and order.customer = :customer
    and price.product = product
    and catalog = :currentCatalog
group by order
having sum(price.amount) &gt; :minAmount
order by sum(price.amount) desc</pre><p>
            &#19979;&#38754;&#19968;&#20010;&#26597;&#35810;&#35745;&#31639;&#27599;&#19968;&#31181;&#29366;&#24577;&#19979;&#30340;&#25903;&#20184;&#30340;&#25968;&#30446;&#65292;&#38500;&#21435;&#25152;&#26377;&#22788;&#20110;<tt class="literal">AWAITING_APPROVAL</tt>&#29366;&#24577;&#30340;&#25903;&#20184;&#65292;&#22240;&#20026;&#22312;&#35813;&#29366;&#24577;&#19979;
            &#24403;&#21069;&#30340;&#29992;&#25143;&#20316;&#20986;&#20102;&#29366;&#24577;&#30340;&#26368;&#26032;&#25913;&#21464;&#12290;&#35813;&#26597;&#35810;&#34987;&#36716;&#25442;&#25104;&#21547;&#26377;&#20004;&#20010;&#20869;&#36830;&#25509;&#20197;&#21450;&#19968;&#20010;&#30456;&#20851;&#32852;&#30340;&#23376;&#36873;&#25321;&#30340;SQL&#26597;&#35810;&#65292;&#35813;&#26597;&#35810;&#20351;&#29992;&#20102;&#34920;
            <tt class="literal">PAYMENT</tt>, <tt class="literal">PAYMENT_STATUS</tt> &#20197;&#21450; 
            <tt class="literal">PAYMENT_STATUS_CHANGE</tt>&#12290;
        </p><pre class="programlisting">select count(payment), status.name 
from Payment as payment 
    join payment.currentStatus as status
    join payment.statusChanges as statusChange
where payment.status.name &lt;&gt; PaymentStatus.AWAITING_APPROVAL
    or (
        statusChange.timeStamp = ( 
            select max(change.timeStamp) 
            from PaymentStatusChange change 
            where change.payment = payment
        )
        and statusChange.user &lt;&gt; :currentUser
    )
group by status.name, status.sortOrder
order by status.sortOrder</pre><p>
            &#22914;&#26524;&#25105;&#25226;<tt class="literal">statusChanges</tt>&#23454;&#20363;&#38598;&#26144;&#23556;&#20026;&#19968;&#20010;&#21015;&#34920;&#65288;list&#65289;&#32780;&#19981;&#26159;&#19968;&#20010;&#38598;&#21512;&#65288;set&#65289;, 
            &#20070;&#20889;&#26597;&#35810;&#35821;&#21477;&#23558;&#26356;&#21152;&#31616;&#21333;.
        </p><pre class="programlisting">select count(payment), status.name 
from Payment as payment
    join payment.currentStatus as status
where payment.status.name &lt;&gt; PaymentStatus.AWAITING_APPROVAL
    or payment.statusChanges[ maxIndex(payment.statusChanges) ].user &lt;&gt; :currentUser
group by status.name, status.sortOrder
order by status.sortOrder</pre><p>
            &#19979;&#38754;&#19968;&#20010;&#26597;&#35810;&#20351;&#29992;&#20102;MS SQL Server&#30340; <tt class="literal">isNull()</tt>&#20989;&#25968;&#29992;&#20197;&#36820;&#22238;&#24403;&#21069;&#29992;&#25143;&#25152;&#23646;&#32452;&#32455;&#30340;&#32452;&#32455;&#24080;&#21495;&#21450;&#32452;&#32455;&#26410;&#25903;&#20184;&#30340;&#36134;&#12290;
            &#23427;&#34987;&#36716;&#25442;&#25104;&#19968;&#20010;&#23545;&#34920;<tt class="literal">ACCOUNT</tt>, <tt class="literal">PAYMENT</tt>, <tt class="literal">PAYMENT_STATUS</tt>,
            <tt class="literal">ACCOUNT_TYPE</tt>, <tt class="literal">ORGANIZATION</tt> &#20197;&#21450; <tt class="literal">ORG_USER</tt>&#36827;&#34892;&#30340;&#19977;&#20010;&#20869;&#36830;&#25509;&#65292;
            &#19968;&#20010;&#22806;&#36830;&#25509;&#21644;&#19968;&#20010;&#23376;&#36873;&#25321;&#30340;SQL&#26597;&#35810;&#12290;
        </p><pre class="programlisting">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</pre><p>
            &#23545;&#20110;&#19968;&#20123;&#25968;&#25454;&#24211;&#65292;&#25105;&#20204;&#38656;&#35201;&#24323;&#29992;&#65288;&#30456;&#20851;&#30340;&#65289;&#23376;&#36873;&#25321;&#12290;
        </p><pre class="programlisting">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</pre></div><div class="sect1" lang="zh-cn"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="queryhql-bulk"></a>15.13.&nbsp;&#25209;&#37327;&#30340;UPDATE &amp; DELETE&#35821;&#21477;</h2></div></div><div></div></div><p>
            HQL&#29616;&#22312;&#25903;&#25345;UPDATE&#19982;DELETE&#35821;&#21477;.  &#26597;&#38405;
            <a href="batch.html#batch-direct" title="14.3.&nbsp;&#22823;&#25209;&#37327;&#26356;&#26032;/&#21024;&#38500;&#65288;Bulk update/delete&#65289;">&#31532;&nbsp;14.3&nbsp;&#33410; &#8220;&#22823;&#25209;&#37327;&#26356;&#26032;/&#21024;&#38500;&#65288;Bulk update/delete&#65289;&#8221;</a> &#20197;&#33719;&#24471;&#26356;&#22810;&#20449;&#24687;&#12290;
        </p></div><div class="sect1" lang="zh-cn"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="queryhql-tipstricks"></a>15.14.&nbsp;&#23567;&#25216;&#24039; &amp; &#23567;&#31373;&#38376;</h2></div></div><div></div></div><p>
            &#20320;&#21487;&#20197;&#32479;&#35745;&#26597;&#35810;&#32467;&#26524;&#30340;&#25968;&#30446;&#32780;&#19981;&#24517;&#23454;&#38469;&#30340;&#36820;&#22238;&#20182;&#20204;&#65306;
        </p><pre class="programlisting">( (Integer) session.iterate("select count(*) from ....").next() ).intValue()</pre><p>
            &#33509;&#24819;&#26681;&#25454;&#19968;&#20010;&#38598;&#21512;&#30340;&#22823;&#23567;&#26469;&#36827;&#34892;&#25490;&#24207;&#65292;&#21487;&#20197;&#20351;&#29992;&#22914;&#19979;&#30340;&#35821;&#21477;&#65306;
        </p><pre class="programlisting">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)</pre><p>
            &#22914;&#26524;&#20320;&#30340;&#25968;&#25454;&#24211;&#25903;&#25345;&#23376;&#36873;&#25321;&#65292;&#20320;&#21487;&#20197;&#22312;&#20320;&#30340;&#26597;&#35810;&#30340;where&#23376;&#21477;&#20013;&#20026;&#36873;&#25321;&#30340;&#22823;&#23567;&#65288;selection size&#65289;&#25351;&#23450;&#19968;&#20010;&#26465;&#20214;:
        </p><pre class="programlisting">from User usr where size(usr.messages) &gt;= 1</pre><p>
            &#22914;&#26524;&#20320;&#30340;&#25968;&#25454;&#24211;&#19981;&#25903;&#25345;&#23376;&#36873;&#25321;&#35821;&#21477;&#65292;&#20351;&#29992;&#19979;&#38754;&#30340;&#26597;&#35810;&#65306;
        </p><pre class="programlisting">select usr.id, usr.name
from User usr.name
    join usr.messages msg
group by usr.id, usr.name
having count(msg) &gt;= 1</pre><p>
            &#22240;&#20026;&#20869;&#36830;&#25509;&#65288;inner join&#65289;&#30340;&#21407;&#22240;&#65292;&#36825;&#20010;&#35299;&#20915;&#26041;&#26696;&#19981;&#33021;&#36820;&#22238;&#21547;&#26377;&#38646;&#20010;&#20449;&#24687;&#30340;<tt class="literal">User</tt>
            &#31867;&#30340;&#23454;&#20363;, &#25152;&#20197;&#36825;&#31181;&#24773;&#20917;&#19979;&#20351;&#29992;&#19979;&#38754;&#30340;&#26684;&#24335;&#23558;&#26159;&#26377;&#24110;&#21161;&#30340;:
        </p><pre class="programlisting">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</pre><p>
            JavaBean&#30340;&#23646;&#24615;&#21487;&#20197;&#34987;&#32465;&#23450;&#21040;&#19968;&#20010;&#21629;&#21517;&#26597;&#35810;&#65288;named query&#65289;&#30340;&#21442;&#25968;&#19978;&#65306;
        </p><pre class="programlisting">Query q = s.createQuery("from foo Foo as foo where foo.name=:name and foo.size=:size");
q.setProperties(fooBean); // fooBean&#21253;&#21547;&#26041;&#27861;getName()&#19982;getSize()
List foos = q.list();</pre><p>
            &#36890;&#36807;&#23558;&#25509;&#21475;<tt class="literal">Query</tt>&#19982;&#19968;&#20010;&#36807;&#28388;&#22120;&#65288;filter&#65289;&#19968;&#36215;&#20351;&#29992;&#65292;&#38598;&#21512;&#65288;Collections&#65289;&#26159;&#21487;&#20197;&#20998;&#39029;&#30340;&#65306;
        </p><pre class="programlisting">Query q = s.createFilter( collection, "" ); // &#19968;&#20010;&#31616;&#21333;&#30340;&#36807;&#28388;&#22120;
q.setMaxResults(PAGE_SIZE);
q.setFirstResult(PAGE_SIZE * pageNumber);
List page = q.list();</pre><p>
            &#36890;&#36807;&#20351;&#29992;&#26597;&#35810;&#36807;&#28388;&#22120;&#65288;query filter&#65289;&#21487;&#20197;&#23558;&#38598;&#21512;&#65288;Collection&#65289;&#30340;&#21407;&#32032;&#20998;&#32452;&#25110;&#25490;&#24207;:
        </p><pre class="programlisting">Collection orderedCollection = s.filter( collection, "order by this.amount" );
Collection counts = s.filter( collection, "select this.type, count(this) group by this.type" );</pre><p>
            &#19981;&#29992;&#36890;&#36807;&#21021;&#22987;&#21270;&#65292;&#20320;&#23601;&#21487;&#20197;&#30693;&#36947;&#19968;&#20010;&#38598;&#21512;&#65288;Collection&#65289;&#30340;&#22823;&#23567;&#65306;
        </p><pre class="programlisting">( (Integer) session.iterate("select count(*) from ....").next() ).intValue();</pre></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="batch.html">&#19978;&#19968;&#39029;</a>&nbsp;</td><td width="20%" align="center"><a accesskey="u" href="index.html">&#19978;&#19968;&#32423;</a></td><td width="40%" align="right">&nbsp;<a accesskey="n" href="querycriteria.html">&#19979;&#19968;&#39029;</a></td></tr><tr><td width="40%" align="left" valign="top">&#31532;&nbsp;14&nbsp;&#31456;&nbsp;&#25209;&#37327;&#22788;&#29702;&#65288;Batch processing&#65289;&nbsp;</td><td width="20%" align="center"><a accesskey="h" href="index.html">&#36215;&#22987;&#39029;</a></td><td width="40%" align="right" valign="top">&nbsp;&#31532;&nbsp;16&nbsp;&#31456;&nbsp;
    &#26465;&#20214;&#26597;&#35810;(Criteria Queries)
    </td></tr></table></div></body></html>