queryhql.html

图象处理

        </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>
            &#22909;&#23478;&#20249;&#65292;&#30495;&#38271;&#65281;&#23454;&#38469;&#19978;&#65292;&#22312;&#29616;&#23454;&#29983;&#27963;&#20013;&#25105;&#24182;&#19981;&#26159;&#38750;&#24120;&#28909;&#34935;&#20110;&#23376;&#26597;&#35810;&#65292;&#25152;&#20197;&#25105;&#30340;&#26597;&#35810;&#24448;&#24448;&#26159;&#36825;&#26679;&#30340;&#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;&#30340;&#26597;&#35810;&#32479;&#35745;&#20184;&#27454;&#35760;&#24405;&#22788;&#20110;&#27599;&#31181;&#29366;&#24577;&#20013;&#30340;&#25968;&#37327;&#65292;&#35201;&#25490;&#38500;&#25152;&#26377;&#22788;&#20110;<tt class="literal">AWAITING_APPROVAL</tt>&#29366;&#24577;&#30340;&#65292;&#25110;&#32773;&#26368;&#36817;&#19968;&#27425;&#29366;&#24577;&#26356;&#25913;&#26159;&#30001;&#24403;&#21069;&#29992;&#25143;&#20570;&#20986;&#30340;&#12290;&#23427;&#32763;&#35793;&#25104;SQL&#26597;&#35810;&#21518;&#65292;&#22312;<tt class="literal">PAYMENT</tt>,<tt class="literal">PAYMENT_STATUS</tt>&#21644;<tt class="literal">PAYMENT_STATUS_CHANGE</tt>&#34920;&#20043;&#38388;&#21253;&#21547;&#20004;&#20010;&#20869;&#37096;&#36830;&#25509;&#21644;&#19968;&#20010;&#29992;&#20110;&#20851;&#32852;&#30340;&#23376;&#26597;&#35810;&#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>
            &#20551;&#33509;&#25105;&#24050;&#32463;&#25226;<tt class="literal">statusChange</tt>&#38598;&#21512;&#26144;&#23556;&#20026;&#19968;&#20010;&#21015;&#34920;&#32780;&#19981;&#26159;&#19968;&#20010;&#38598;&#21512;&#30340;&#35805;&#65292;&#26597;&#35810;&#20889;&#36215;&#26469;&#20250;&#31616;&#21333;&#24456;&#22810;&#12290;
        </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;&#30340;&#26597;&#35810;&#20351;&#29992;&#20102;MS SQL Server&#30340;<tt class="literal">isNull()</tt>&#20989;&#25968;&#65292;&#36820;&#22238;&#24403;&#21069;&#29992;&#25143;&#25152;&#23646;&#30340;&#32452;&#32455;&#25152;&#26377;&#36134;&#25143;&#21644;&#26410;&#20184;&#25903;&#20986;&#12290;&#32763;&#35793;&#20026;SQL&#26597;&#35810;&#21518;&#65292;&#22312;<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> &#21644; <tt class="literal">ORG_USER</tt>&#34920;&#20043;&#38388;&#26377;&#19977;&#20010;&#20869;&#37096;&#36830;&#25509;&#65292;&#19968;&#20010;&#22806;&#37096;&#36830;&#25509;&#21644;&#19968;&#20010;&#23376;&#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;&#26576;&#20123;&#25968;&#25454;&#24211;&#32780;&#35328;&#65292;&#25105;&#20204;&#21487;&#33021;&#19981;&#33021;&#20381;&#36182;&#65288;&#20851;&#32852;&#30340;&#65289;&#23376;&#26597;&#35810;&#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-tipstricks"></a>11.13.&nbsp;&#25552;&#31034;&#21644;&#25216;&#24039;&#65288;Tips &amp; Tricks&#65289;</h2></div></div><div></div></div><p>
            &#20320;&#19981;&#36820;&#22238;&#32467;&#26524;&#38598;&#20063;&#21487;&#20197;&#26597;&#35810;&#32467;&#26524;&#38598;&#30340;&#22823;&#23567;&#65306;
        </p><pre class="programlisting">( (Integer) session.iterate("select count(*) from ....").next() ).intValue()</pre><p>
            &#35201;&#20381;&#25454;&#19968;&#20010;&#38598;&#21512;&#30340;&#22823;&#23567;&#23545;&#32467;&#26524;&#38598;&#25490;&#24207;&#65292;&#21487;&#20197;&#29992;&#19979;&#38754;&#30340;&#26597;&#35810;&#26469;&#23545;&#20184;&#19968;&#23545;&#22810;&#25110;&#22810;&#23545;&#22810;&#30340;&#20851;&#32852;&#65306;
        </p><pre class="programlisting">select usr
from User as usr 
    left join usr.messages as msg
group by usr
order by count(msg)</pre><p>
            &#22914;&#26524;&#20320;&#30340;&#25968;&#25454;&#24211;&#25903;&#25345;&#23376;&#26597;&#35810;&#65292;&#20320;&#21487;&#20197;&#22312;&#26597;&#35810;&#30340;where&#23376;&#21477;&#20013;&#23545;&#36873;&#25321;&#30340;&#22823;&#23567;&#36827;&#34892;&#26465;&#20214;&#38480;&#21046;&#65306;
        </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;&#26597;&#35810;&#65292;&#21487;&#20197;&#20351;&#29992;&#19979;&#21015;&#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;&#20351;&#29992;&#20102;inner join,&#36825;&#20010;&#35299;&#20915;&#26041;&#27861;&#19981;&#33021;&#36820;&#22238;&#27809;&#26377;message&#30340;<tt class="literal">User</tt>.&#19979;&#38754;&#30340;&#26041;&#24335;&#23601;&#21487;&#20197;&#65306;
        </p><pre class="programlisting">select usr
from User as usr
    left join usr.messages as msg
group by usr
having count(msg) = 0</pre><p>
            JavaBean&#30340;&#23646;&#24615;&#21487;&#20197;&#30452;&#25509;&#20316;&#20026;&#21629;&#21517;&#30340;&#26597;&#35810;&#21442;&#25968;&#65306;
        </p><pre class="programlisting">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();</pre><p>
            &#22312;<tt class="literal">Query</tt>&#25509;&#21475;&#20013;&#20351;&#29992;&#36807;&#28388;&#22120;(filter),&#21487;&#20197;&#23545;&#38598;&#21512;&#20998;&#39029;&#65306;
        </p><pre class="programlisting">Query q = s.createFilter( collection, "" ); // the trivial filter
q.setMaxResults(PAGE_SIZE);
q.setFirstResult(PAGE_SIZE * pageNumber);
List page = q.list();</pre><p>
            &#38598;&#21512;&#20803;&#32032;&#21487;&#20197;&#20351;&#29992;&#26597;&#35810;&#36807;&#28388;&#22120;(query filter)&#36827;&#34892;&#25490;&#24207;&#25110;&#32773;&#20998;&#32452;&#65306;
        </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;&#21021;&#22987;&#21270;&#38598;&#21512;&#23601;&#21487;&#20197;&#24471;&#21040;&#20854;&#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="transactions.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;10&nbsp;&#31456;&nbsp;&#20107;&#21153;&#21644;&#24182;&#34892;&#65288;Transactions And Concurrency&#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;12&nbsp;&#31456;&nbsp;&#26465;&#20214;&#26597;&#35810;(Criteria Query)</td></tr></table></div></body></html>