| Michael Bouschen 2007-10-23, 7:11 am |
| Hi,
I looked at the sample queries we used when discussion the subquery
proposal. I modified them such that they work with the company model
used in the TCK and adapted them to the proposed API. I propose to add
these queries to a new TCK query test class testing JDOQL subqueries.
(1) Select employees who work more than the average of their department
employees
Single-string JDOQL:
SELECT FROM Employee WHERE this.weeklyhours > (SELECT
AVG(e.weeklyhours) FROM this.department.employees e)
Query API:
Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(this.weeklyhours)");
Query q = pm.newQuery(Employee.class);
q.setFilter("this.weeklyhours> averageWeeklyhours");
q.addSubquery(sub, "double averageWeeklyhours",
"this.department.employees");
(2) Select employees who work more than the average of the employees in
their department having the same manager?
Single-string JDOQL:
SELECT FROM Employee WHERE this.weeklyhours >
(SELECT AVG(e.weeklyhours) FROM this.department.employees e WHERE
e.manager == this.manager)
Query API:
Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(this.weeklyhours)");
sub.setFilter("this.manager == :manager");
Query q = pm.newQuery(Employee.class);
q.setFilter("this.weeklyHours > averageWeeklyhours");
q.addSubquery(sub, "double averageWeeklyhours",
"this.department.employees", "this.manager");
(3) select employees who work more than the average of all employees?
Single-string JDOQL:
SELECT FROM Employee WHERE this.weeklyhours > (SELECT
AVG(e.weeklyhours) FROM Employee e)
Query API:
Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(this.weeklyhours)");
Query q = pm.newQuery(Employee.class);
q.setFilter("this.weeklyHours > averageWeeklyhours");
q.addSubquery(sub, "double averageWeeklyhours", null);
The null value passed as the candidateCollection expression indicates
that we're not overriding the candidates for the subquery and thus it
uses the entire extent of Employee.
(4) Select employees hired after a particular date who work more than
the average of all employees?
Single-string JDOQL:
SELECT FROM Employee WHERE this.hiredate > :hired && this.weeklyhours>
(SELECT AVG(e.weeklyhours) FROM Employee e)
Query API:
Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(this.weeklyhours)");
Query q = pm.newQuery(Employee.class);
q.setFilter("this.hiredate > :hired && this.weeklyhours >
averageWeeklyhours");
q.addSubquery(sub, "double averageWeeklyhours", null);
(5) Select employees hired after a particular date who work more than
the average of all employees of the same manager?
Single-string JDOQL:
SELECT FROM Employee WHERE this.hiredate > :hired && this.weeklyhours >
(SELECT AVG(e.weeklyhours) FROM Employee e WHERE e.manager ==
this.manager)
Query API:
Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(this.weeklyhours)");
sub.setFilter("this.manager == :manager");
Query q = pm.newQuery(Employee.class);
q.setFilter("this.hiredate > :hired && this.weeklyhours>
averageWeeklyhours");
q.addSubquery(sub, "double averageWeeklyhours", null, "this.manager");
(6) Select employees who work more than the average of all employees of
the same manager?
Single-string JDOQL:
SELECT FROM Employee WHERE this.weeklyhours >
(SELECT AVG(e.weeklyhours) FROM Employee e WHERE e.manager ==
this.manager)
Query API:
Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(this.weeklyhours)");
sub.setFilter("this.manager == :manager");
Query q = pm.newQuery(Employee.class);
q.setFilter("this.weeklyHours > averageWeeklyhours");
q.addSubquery(sub, "double averageWeeklyhours", null, "this.manager");
Regards Michael
> It looks like the spec update I just completed forgot to include some
> of the APIs that we discussed here:
>
> addSubquery(Query subquery, String variableDeclaration, String
> candidateCollectionExpr, String parameter);
> addSubqueryWithArray(Query subquery, String variableDeclaration,
> String candidateCollectionExpr, String[] parameters);
>
> In the api2 this becomes
> addSubquery(Query subquery, String variableDeclaration, String
> candidateCollectionExpr, String... parameters);
>
> addSubquery(Query subquery, String variableDeclaration, String
> candidateCollectionExpr, Map parameters);
>
> We also need to describe what happens if the same variableDeclaration
> is re-used. I think it makes sense that the variableDeclaration is the
> key to a map of subqueries in the outer query, and if addSubquery is
> used with the same variableDeclaration then it replaces the previous
> query. And if subquery is null, then that variableDeclaration is reset.
>
> We can explore some of these side issues when we see Michael's test
> cases.
>
> Craig
>
> On Oct 3, 2006, at 9:49 AM, Craig L Russell wrote:
>
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
--
Tech@Spree Engineering GmbH Tel.: +49/(0)30/235 520-33
Buelowstr. 66 Fax.: +49/(0)30/217 520-12
10783 Berlin mailto:mbo.tech@spree.de
Geschaeftsfuehrung: Anna-Kristin Proefrock
Sitz Berlin, Amtsgericht Charlottenburg, HRB 564 52
|