Update of /cvsroot/hibernate/Hibernate2/doc/reference/src
In directory sc8-pr-cvs1:/tmp/cvs-serv858/reference/src
Modified Files:
query_language.xml
Log Message:
example queries
Index: query_language.xml
===================================================================
RCS file: /cvsroot/hibernate/Hibernate2/doc/reference/src/query_language.xml,v
retrieving revision 1.7
retrieving revision 1.8
diff -C2 -d -r1.7 -r1.8
*** query_language.xml 30 Mar 2003 06:40:17 -0000 1.7
--- query_language.xml 31 Mar 2003 02:25:30 -0000 1.8
***************
*** 307,314 ****
<programlisting><![CDATA[from bank.Person person
! where person.id.country = 'AU' and person.id.medicareNumber = 123456
from bank.Account account
! where account.owner.id.country = 'AU' and account.owner.id.medicareNumber = 123456]]></programlisting>
<para>
--- 307,316 ----
<programlisting><![CDATA[from bank.Person person
! where person.id.country = 'AU'
! and person.id.medicareNumber = 123456
from bank.Account account
! where account.owner.id.country = 'AU'
! and account.owner.id.medicareNumber = 123456]]></programlisting>
<para>
***************
*** 589,596 ****
</para>
! <programlisting><![CDATA[select cat.color, sum(cat.weight), count(cat) from eg.Cat cat
group by cat.color
! select foo.id, avg( elements(foo.names) ), max( indices(foo.names) ) from eg.Foo foo
group by foo.id]]></programlisting>
--- 591,600 ----
</para>
! <programlisting><![CDATA[select cat.color, sum(cat.weight), count(cat)
! from eg.Cat cat
group by cat.color
! select foo.id, avg( elements(foo.names) ), max( indices(foo.names) )
! from eg.Foo foo
group by foo.id]]></programlisting>
***************
*** 600,605 ****
<para>A <literal>having</literal> clause is also allowed.</para>
! <programlisting><![CDATA[select cat.color, sum(cat.weight), count(cat) from eg.Cat cat
! group by cat.color having cat.color in (eg.Color.TABBY, eg.Color.BLACK)]]></programlisting>
</sect1>
--- 604,611 ----
<para>A <literal>having</literal> clause is also allowed.</para>
! <programlisting><![CDATA[select cat.color, sum(cat.weight), count(cat)
! from eg.Cat cat
! group by cat.color
! having cat.color in (eg.Color.TABBY, eg.Color.BLACK)]]></programlisting>
</sect1>
***************
*** 628,631 ****
--- 634,714 ----
</sect1>
+
+ <sect1 id="query-language-s9">
+ <title>Examples</title>
+
+ <para>
+ Hibernate queries can be quite powerful and complex. In fact, the power of the query language
+ is one of Hibernate's main selling points. Here are some example queries very similar to queries
+ that I used on a recent project.
+ </para>
+
+ <para>
+ This query counts the number of payments in each status, excluding all payments in the
+ <literal>AWAITING_APPROVAL</literal> status where the most recent status change was made by the
+ current user. It translates to an SQL query with two inner joins and a subselect against the
+ <literal>PAYMENT</literal>, <literal>PAYMENT_STATUS</literal> and
+ <literal>PAYMENT_STATUS_CHANGE</literal> tables.
+ </para>
+
+ <programlisting><![CDATA[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]]></programlisting>
+
+ <para>
+ If I would have mapped the <literal>statusChanges</literal> collection as a list, instead of a set,
+ the query would have been much simpler to write.
+ </para>
+
+ <programlisting><![CDATA[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]]></programlisting>
+
+ <para>
+ The next query uses the MS SQL Server <literal>isNull()</literal> function to return all
+ the accounts and unpaid payments for the organization to which the current user belongs.
+ It translates to an SQL query with three inner joins, an outer join and a subselect against
+ the <literal>ACCOUNT</literal>, <literal>PAYMENT</literal>, <literal>PAYMENT_STATUS</literal>,
+ <literal>ACCOUNT_TYPE</literal>, <literal>ORGANIZATION</literal> and
+ <literal>ORG_USER</literal> tables.
+ </para>
+
+ <programlisting><![CDATA[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]]></programlisting>
+
+ <para>
+ For some databases, we would need to do away with the subselect.
+ </para>
+
+ <programlisting><![CDATA[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]]></programlisting>
+
+ </sect1>
+
</chapter>
|
