EJB mediator query syntax

When we begin writing the applications to take advantage of the EJB data mediator service (DMS) provided in the product, consider the following items.

• The EJB DMS takes as an input argument a compound EJB query which consists of an array containing EJB query language (QL) statements and an optional XREL command. The XREL command is a list of EJB relationships and must appear last in the array.
• Each EJB QL query returns data in the form of a Service DataObjects (SDO) instance. All of the SDO instances are merged into a DataGraph. The SELECT clause of each query specifies the container-managed persistence (CMP) fields or expressions to return in the SDO. The WHERE clause specifies the filtering conditions and we can define an ORDER BY clause. If two or more SELECTs return the same SDO type, each SELECT must project the same CMP fields and expressions. For updatability, the primary key fields of the EJB must be projected. JOINs, UNIONs, and aggregation are not supported except in subqueries.
• A query in the array can refer to a prior query in the FROM clause using the identification variable defined in the prior query and a relationship name. This relationship can be single or collection valued.
• Relationships are constructed between data object instances in the graph when a relationship is used in either the FROM clause or in the XREL command.
• Collection valued input arguments are supported in FROM clause If ?1 refers to a collection of Dept EJBs then the following query is valid for the mediator. The cast syntax is required to tell the query compiler the collection element type.

  select d.deptno from (Dept) ?1 as d
  

• The collection input argument is useful when it is desired to build a DataGraph from EJB instances that are cached in the EJB Container or persistence manager data cache.

• The SELECT clause can specify a list of CMP fields to retrieve (the wildcard * notation can be used to retrieve all CMP fields) or valid EJB query language expressions. CMP fields and expressions must be one of the following types:
  • Primitive types: boolean, byte, short, integer, long, float, double, char
  • Object wrapper types for the primitive types
  • Java.lang.String
  • Java.math.BigDecimal
  • java.math.BigInteger
  • byte [ ]
  • Java.sql.Date
  • java.sql.Time
  • java.sql.Timestamp
  • java.util.Date
  • java.util.Calendar

• All primary key CMP fields must be retrieved in order for the Service Data Objects (SDO) to be updateable; otherwise, applyChanges returns an exception.
• SDO attributes that come from EJB query language expressions such as e.salary + e.bonus AS TOTAL_PAY cannot be updated. If we try to make an update, applyChanges returns a QueryException.
• Aggregate expressions such as SUM(e.salary) are not allowed even though they are part of the EJB query language. Aggregate expressions can be used in subselects in the WHERE clause.

Developing data access applications