Using Multiple Expressions

Expressions support subqueries (SQL subselects) and parallel selects. To create a subquery, use a single expression builder. With parallel selects, use multiple expression builders when you define a single query. This lets you specify joins for unrelated objects at the object level.

Subselects and Subqueries

Some queries compare the results of other, contained queries (or subqueries). SQL supports this comparison through subselects. TopLink expressions provide subqueries to support subselects.

Subqueries lets you define complex expressions that query on aggregated values (counts, min, max) and unrelated objects (exists, in, comparisons). To obtain a subquery, pass an instance of a report query to any expression comparison operation, or use the subQuery operation on an expression builder. The subquery is not required to have the same reference class as the parent query, and it must use its own expression builder.

You can nest subqueries, or use them in parallel. Subqueries can also make use of custom SQL.

For expression comparison operations that accept a single value (equal, greaterThan, lessThan), the subquery result must return a single value. For expression comparison operations that accept a set of values (in, exists), the subquery result must return a set of values.

Example 97-17 illustrates how to create an expression that matches all employees with more than five managed employees.

Example 97-17 A Subquery Expression Using a Comparison and Count Operation

ExpressionBuilder emp = new ExpressionBuilder();
ExpressionBuilder managedEmp = new ExpressionBuilder();
ReportQuery subQuery =new ReportQuery(Employee.class, managedEmp);
subQuery.addCount();
subQuery.setSelectionCriteria(managedEmp.get("manager") .equal(emp));
Expression exp = emp.subQuery(subQuery).greaterThan(5);

Example 97-18 illustrates how to create an expression that matches the employee with the highest salary in the city of Ottawa.

Example 97-18 A Subquery Expression Using a Comparison and Max Operation

ExpressionBuilder emp = new ExpressionBuilder();
ExpressionBuilder ottawaEmp = new ExpressionBuilder();
ReportQuery subQuery = new ReportQuery(Employee.class, ottawaEmp);
subQuery.addMax("salary");
subQuery.setSelectionCriteria(ottawaEmp.get("address").get("city").equal("Ottawa"));
Expression exp =   emp.get("salary").equal(subQuery).and(emp.get("address").get("city").equal("Ottawa"));

Example 97-19 illustrates how to create an expression that matches all employees that have no projects.

Example 97-19 A Subquery Expression Using a Not Exists Operation

ExpressionBuilder emp = new ExpressionBuilder();
ExpressionBuilder proj = new ExpressionBuilder();
ReportQuery subQuery = new ReportQuery(Project.class, proj);
subQuery.addAttribute("id");
subQuery.setSelectionCriteria(proj.equal(emp.anyOf("projects"));
Expression exp = emp.notExists(subQuery);

Parallel Expressions

Parallel expressions enable you to compare unrelated objects. Parallel expressions require multiple expression builders, but do not require the use of report queries. Each expression must have its own expression builder, and you must use the constructor for expression builder that takes a class as an argument. The class does not have to be the same for the parallel expressions, and you can create multiple parallel expressions in a single query.

Only one of the expression builders is considered the primary expression builder for the query. This primary builder makes use of the zero argument expression constructor, and TopLink obtains its class from the query.

Example 97-20 illustrates how to create an expression that matches all employees with the same last name as another employee of different gender, and accounts for the possibility that returned results could be a spouse.

Example 97-20 A Parallel Expression on Two Independent Employees

ExpressionBuilder emp = new ExpressionBuilder();
ExpressionBuilder spouse = new ExpressionBuilder(Employee.class);
Expression exp = emp.get("lastName").equal(spouse.get("lastName"))
  .and(emp.get("gender").notEqual(spouse.get("gender"));