+

Search Tips   |   Advanced Search

 

Example: Using query arguments with EJB mediator

 

The following examples show how you can fine-tune your EJB mediator query arguments.

 

A simple example

This query returns a DataGraph containing multiple instances of DataObjects of type (Eclass name) Emp. The data object attributes are empid and name and their data types correspond to the container-managed persistence (CMP) field types.

select  e.empid, e.name  from Emp as e  where e.salary > 100

The returned DataGraph serialized in its XML format looks like this:

  <?xml version="1.0" encoding="ASCII"?>
<datagraph:DataGraphSchema xmlns:datagraph="datagraph.ecore">
<root>
<Emp empid="1003" name="Eric" />
<Emp empid="1004" name="Dave" />
</root>
</datagraph:DataGraphSchema>

 

Query parameters

This example shows how parameter markers can be used. Recall that the syntax for parameter markers in an EJB query is a question mark followed by a number (?n). When calling the getGraph ( ) method on the EJBMediator, you can optionally pass an array of values. ?n refers to the value of parm[n-1]. The array of values can also be passed on the factory call to create the EJBMediator. Parameters passed on the getGraph( ) override any parameters passed on the create call.

select  e.empid, e.name  from Emp as e  where e.salary > ?1 

 

Returning expressions and methods

This example illustrates that the data object attributes can be the return values of query expressions. EJB query expressions include arithmetic, date-time, path expressions, and methods. Input arguments and return values from methods are restricted to the list of supported data types (see EJB mediator query syntax). A data object containing an updated attribute derived from an expression causes an exception to occur during the applyChanges process unless the user has provided a MediatorAdapter to handle the change.

select  e.empid as employeeId, 
  e.bonus+e.salary as totalPay,  
  e.dept.mgr.name as managerNam, 
  e.computePension( ) as pension  from Emp as e  where e.salary > 100

Data object attribute names are derived from the CMP field names but can be overridden by using the AS keyword in the query. When specifying an expression, the AS keyword should always be used to give a name to the expression.

 

The * syntax

The notation e.* is a short cut for specifying all the CMP fields (but not container-managed relationships) for an EJB. The following query means the same thing as e.empid, e.name e.salary, e.bonus.

select e.* from Emp as e

 

No primary key in select clause

This example shows a query that does not return the primary key field. However, unless the data object contains all the primary key fields for an EJB, updates to the DataGraph cannot be processed by the mediator. This is because the primary key is required to translate the changes into structured query language (SQL), or to convert DataObject references to EJB references. An exception when applyChanges tries to run.

select e.name, e.salary from Emp as e

 

Order by

DataObjects can be ordered.

select d.* from Dept d  order by d.name
  select e.* from in(d.emps) e order by e.empid desc

This results in the Dept objects being ordered by name and the Emp objects within each Dept being order by empid in descending order.

 

Navigating a multi-valued relationship

This compound query returns a DataGraph with DataObject classes Dept and Emp. The shape of the DataGraph reflects the path expressions used in the FROM clauses.

select d.deptno, d.name, d.budget from Dept d  
      where d.deptno < 10  select e.empid, e.name, e.salary  from in(d.emps) e 
      where e.salary > 10

In this case Dept is the root node in the DataGraph and there is a multivalued reference from Dept to Emp as shown:

<?xml version="1.0" encoding="ASCII" ?> 
<datagraph:DataGraphSchema xmlns:datagraph="datagraph.ecore">
<root>
<Dept deptno="1" name="WAS_Sales" budget="500.0" 
   emps="//@root/@Emp.1 //@root/@Emp.0" /> 
<Dept deptno="2" name="WBI_Sales" budget="450.0" 
   emps="//@root/@Emp.3 //@root/@Emp.2" /> 
<Emp empid="1001" name="Rob" salary="100.0" EmpDept="//@root/@Dept.0" /> 
<Emp empid="1002" name="Jason" salary="100.0" EmpDept="//@root/@Dept.0" /> 
<Emp empid="1003" name="Eric" salary="200.0" EmpDept="//@root/@Dept.1" /> 
<Emp empid="1004" name="Dave" salary="500.0" EmpDept="//@root/@Dept.1" /> 
</root>
</datagraph:DataGraphSchema>

 

More on query parameters

Search conditions can be specified on any query. Input arguments are global to the query and can be referenced by number anywhere in the compound query. In the example above, the query arguments passed on the create or getGraph call should be in order { deptno value, salary value, deptno value }.

select d.* from Dept as d 
   where d.deptno between ?1 and ?3  select e.* from in(d.emps) e  
   where e.salary < ?2

 

Navigating a path with multiple relationships

The following query navigates the path composed of EJB relationships Dept.projs and Project.tasks and returns DataObjects for Dept, Emp and Project containing selected CMP fields.

select d.deptno, d.name from Dept as d  select p.projid  from in(d.projects) p  select t.taskid, t.cost from  in (p.tasks) t

The resulting data graph in XML format is shown here.

<?xml version="1.0" encoding="ASCII" ?> 
<datagraph:DataGraphSchema xmlns:datagraph="datagraph.ecore">
<root>
<Dept deptno="1" name="WAS_Sales" projects="//@root/@Project.0" /> 
<Dept deptno="2" name="WBI_Sales" projects="//@root/@Project.1" /> 
<Project projid="1" ProjectDept="//@root/@Dept.0" 
      tasks="//@root/@Task.0   //@root/@Task.2 //@root/@Task.1" /> 
<Project projid="2" ProjectDept="//@root/@Dept.1" 
      tasks="//@root/@Task.3" /> 
<Task taskid="1" cost="50.0" TaskProject="//@root/@Project.0" /> 
<Task taskid="2" cost="60.0" TaskProject="//@root/@Project.0" /> 
<Task taskid="3" cost="900.0" TaskProject="//@root/@Project.0" /> 
<Task taskid="7" cost="20.0" TaskProject="//@root/@Project.1" /> 
</root>
</datagraph:DataGraphSchema>

 

Navigating multiple paths

Here is a mediator query returning a DataGraph with DataObjects for Dept with related employees and a second path that retrieves related projects and tasks.

select d.deptno, d.name from Dept d  select e.empid, e.name  from in(d.emps) e select p.projid from in(d.projects) p  select t.taskid, t.cost from in(p.tasks) where t.cost > 10

The returned DataGraph looks like this:

<?xml version="1.0" encoding="ASCII" ?> 
    <datagraph:DataGraphSchema xmlns:datagraph="datagraph.ecore">
    <root>
        <Dept deptno="1" name="WAS_Sales" projects="//@root/@Project.0"     
 emps="//@root/@Emp.1 //@root/@Emp.0" /> 
        <Dept deptno="2" name="WBI_Sales" projects="//@root/@Project.1" 
     emps="//@root/@Emp.3 //@root/@Emp.2" /> 
        <Project projid="1" ProjectDept = "//@root/@Dept.0"
     tasks="//@root/@Task.0 //@root/@Task.2 //@root/@Task.1" /> 
        <Project projid="2" ProjectDept="//@root/@Dept.1" tasks="//@root/@Task.3" /> 
      <Task taskid="1" cost="50.0" TaskProject="//@root/@Project.0" /> 
        <Task taskid="2" cost="60.0" TaskProject="//@root/@Project.0" /> 
        <Task taskid="3" cost="900.0" TaskProject="//@root/@Project.0" /> 
        <Task taskid="7" cost="20.0" TaskProject="//@root/@Project.1" /> 
        <Emp empid="1001" name="Rob" EmpDept="//@root/@Dept.0" /> 
        <Emp empid="1002" name="Jason" EmpDept="//@root/@Dept.0" /> 
         <Emp empid="1003" name="Eric" EmpDept="//@root/@Dept.1" /> 
         <Emp empid="1004" name="Dave" EmpDept="//@root/@Dept.1" /> 
  </root>
  </datagraph:DataGraphSchema>

 

Navigating a single valued relationship

The important thing to point out here is that even though Emp is the root data object in the graph, multiple Emp data objects will be related to the same Dept data object. So unlike the previous examples, the data graph does not have a tree shape when you look at the data object instances – there are multiple root Emp objects related to the same Dept object. But then after all it is a data graph, not a data tree. Note that mediator queries allow single valued path expressions in the FROM clause. This is a change from the standard EJB query syntax.

select e.empid, e.name  from Emp e  select d.deptno, d.name  from in(e.dept) d 

And the DataGraph in XML format looks like:

<?xml version="1.0" encoding="ASCII" ?> 
   <datagraph:DataGraphSchema xmlns:datagraph="datagraph.ecore">
   <root>
    <Emp empid="1001" name="Rob" dept="//@root/@Dept.0" /> 
    <Emp empid="1002" name="Jason" dept="//@root/@Dept.0" /> 
    <Emp empid="1003" name="Eric" dept="//@root/@Dept.1" /> 
    <Emp empid="1004" name="Dave" dept="//@root/@Dept.1" /> 
    <Dept deptno="1" name="WAS_Sales" 
DeptEmp="//@root/@Emp.1 //@root/@Emp.0" /> 
    <Dept deptno="2" name="WBI_Sales"
 DeptEmp="//@root/@Emp.3 //@root/@Emp.2" /> 
 </root>
 </datagraph:DataGraphSchema>

 

Path expressions in the SELECT clause

This query is similar to the preceding one (both queries return employee data along with department number and name) but note the data graph contains only one data object type in this query (vs. two in the previous query). The fields deptno and name field are read only because they are result of a path expression in the SELECT clause and are not CMP fields of the Emp EJB.

select  e.empid as EmplId , e.name as EmpName ,
     e.dept.deptno as DeptNo , e.dept.name as DeptName from Emp as e

 

Navigating a many: many relationship

The Emp to Task relationship is deemed a many:many relationship. The following query retrieves employees, tasks, and projects. There is only a single occurrence of any particular task DataObject in the DataGraph, even though it can be related to many employees.

select e.empid, e.name from Emp as e select t.taskid, t.description from in(e.tasks) as t select p.projid, p.cost from in(t.proj) as p

 

Multiple links between data objects

The EJB mediator enables you to retrieve data based on relationships and use the XREL command to construct one or more additional relationships based on data already retrieved. The mediator also enables retrieval of data based on ASNname and then construction of one or more relationships based on the data retrieved using the XREL command. The following query retrieves departments, employees that work in the departments, and the employees that manage the departments.

select d.deptno, d.name from Dept d where d.name like ‘%Dev%’ select e.empid, e.name from in (d.emps) as e select m.empid, m.name from in(d.manager) as m

The second and third select clauses both return instances of Emp DataObject. It is possible that the same Emp instance is retrieved through the d.emps relationship and the d.manager relationship. The EJB mediator creates one Emp instance, but creates both relationships. The following query is processed as follows. Dept DataObjects are created from the data in the first query. Emp DataObjects are created from the data in the second query. Relationships in the graph are then constructed for any relationship used in either the FROM clause or an XREL keyword. During relationship construction, no additional data is retrieved. In this example, an employee who works in a department named Dev appears in the DataGraph. If this employee manages a department called Sales, the manages reference is empty. The Dev department was retrieved in the first query, not the Sales department.

select d.deptno, d.name from Dept d where d.name like ‘%Dev%’ select e.empid, e.name from in (d.emps) as e
xrel d.manager

The emps and manager relationship are constructed based on the DataObject instances created from the queries. An employee whose name is ‘Dev’ but works in department ‘Sales’ will have a null dept relationship in the graph.

select d.deptno, d.name from Dept d where d.name like ‘%Dev%’ select e.empid, e.name from Emp e where e.name like ‘Dev%’
xrel d.emps, d.manager

The next example shows the retrieval of data objects for all the employees, projects, and tasks for a given department, and the linkage of employees with tasks.

select d.deptno from Dept d where d.deptno = 42 select e.empid from in(d.emps) e   select p.projid from in(d.projs) p  select t.* from in(p.tasks) t
  xrel e.tasks

If a task is assigned to an employee in department 42 then that link appears in the data graph. If the task is assigned to an employee not in department 42, then that link does not appear in the data graph because the data object was filtered out by the query. An XREL keyword can be followed by one or more EJB relationships. Bidirectional relationships can refer to either role name. Both source and target of the relationship must be retrieved by one or more queries.

 

Retrieving unrelated objects

The following query retrieves Dept and Task.

select d.deptno, d.name from Dept d where d.name like ‘%Dev%’ select t.taskid, t.startDate from Task t where t.startDate > ‘2005’

The following query retrieves Dept and Emps. Even though there are relationships between Dept and Emp (namely mgr and emps), neither relationship is used in FROM or XREL and so the resulting graph does not contain the relationship values.

select d.deptno, d.name from Dept d where d.name like ‘%Dev%’ select e.empid, e.name from Emp e where e.dept.name like ‘%Dev%’

 

Retrieving null or empty relationships

This query returns departments that have no employees and employees with no department. Presumably the application wants to assign the employees to one of the departments. The purpose of xrel is to define the e.dept relationship (and the inverse role d.emps) into the graph schema.

select d.deptno, d.name from Dept d where d.emps is empty select e.empid, e.name from Emp e where e.dept is null
xrel e.dept

 

Collection Input Parameter

A collection of enterprise beans can be passed as an input argument to the ejb mediator and referenced in the FROM clause. Using a collection parameter satisfies the requirement to construct a data graph from a user collection of already activated enterprise beans.

select d.deptno, d.name from ((Dept) ?1) as d select e.empid, e.name from in(d.emps) as e where e.salary > 10

The above query will iterate through the collection of Dept beans and related Emp beans applying the query predicates and constructing the data graph. Values will be obtained from current values of the beans. An example of a program using an ejb collection parameter.

// this method runs in an EJB context and within a transaction scope    public DataGraph myServiceMethod() { 
      InitialContext ic = new InitialContext();
      DeptLocalHome deptHome = ic.lookup("java:comp/env/ejb/Dept");
      Integer deptKey = new Integer(10);
      DeptEJB dept = deptHome.findByPrimaryKey( deptKey));
      Iterator i = dept.getEmps().iterator();
      while (i.hasNext()) {
       EmpEJB e = (EmpEJB)i.next();
       e.setSalary( e.getSalary() * 1.10); // give everyone a 10% raise 
      }

      // create the query collection parameter
      Collection c = new LinkedList();
      c.add(dept);
      Object[] parms = new Object[] { c};  // put ejb collection in parm array.

      // collection containing the dept EJB is passed to EJB Mediator

      String[] query = new String[] 
          { "select d.deptno, d.name from ((Dept)?1 ) as d", 
             "select e.empid, e.name, e.salary " +
                 " from in (d.employees) as e", 
            "select p.projno, p.name from in (d.projects) as p" };

      Mediator m = EJBMediatorFactory.getInstance().createMediator(
 query, parms);
      DataGraph dg = m.getGraph();
      return dg;
      // the DataGraph contains the updated and as yet uncommitted 
//  salary information.  Dept and Emp data 
      // is fetched through EJB instances active in the EJBContainer. 
      //  Project data is retrieved from database using 
      //   container managed relationships.     }





 

Related concepts


XREL keyword

 

Related tasks


Use the Enterprise JavaBeans data mediator service for data access

 

Related Reference


EJB data mediator service programming considerations
EJB data mediator service data retrieval
EJB data mediator service data update
EJB mediator query syntax
DataGraph schema

 

Reference topic