user manual

184 BES Developer’s Guide
Sub-Queries
We can get the average salary of each department using a single query
method:
SELECT e.DEPARTMENT, AVG(e.SALARY) FROM EMPLOYEE e GROUP BY e.DEPARTMENT
The results are:
Sub-Queries
Sub-queries are permitted as deep as the database implementation being
queried allows. For example, you could use the following sub-query (in bold)
specified in ejb-jar.xml. Note that the sub-query includes ORDER BY as well,
and the results are to be returned in descending (DESC) order.
<query>
<query-method>
<method-name>findApStatisticsWithGreaterThanAverageValue</method-name>
<method-params />
</query-method>
<ejb-ql>SELECT Object(s1) FROM ApStatistics s1 WHERE s1.averageValue >
SELECT AVG(s2.averageValue) FROM ApStatistics s2 ORDER BY s1.averageValue
DESC</ejb-ql>
</query>
See your database implementation documentation for details on the
appropriate use of sub-queries.
Dynamic Queries
There are situations where you may need to search dynamically for data,
based on variable criteria. Unfortunately EJB-QL queries do not support this
scenario. Since EJB-QL queries are specified in the deployment descriptor,
any changes to the queries require re-deployment of the bean. The Borland
Enterprise Server offers a Dynamic Query feature which allows you to
construct and execute EJB-QL queries dynamically and programmatically in
the bean code.
Dynamic queries offer these benefits:
■
allow you to create and execute new queries without having to update and
deploy an EJB.
■
reduce the size of the EJB's deployment descriptor file because finder
queries can be dynamically created instead of statically defined in the
deployment descriptors.
DEPARTMENT AVG(SALARY)
Coffee Room 950
Mail Room 1100
The Closet with the Light Out 850