How to use Data Service nested query feature?

Introduction

Data Service configuration Language (http://wso2.org/wiki/display/wsf/Data+Services+and+Resources)  allows you to call a query using results returned from prior. (i.e. nested query support). What follows is a small example demonstrating how to use nested query feature.

Step 1 : Creating sample Database

Let's create a database for our sample. I am using MySQL 5 as my database.

Our database 'DATASERVICE_SAMPLE' has two tables,
Employee - containing employee records
Salary - containing employee salary information.

Two tables can be linked using employeeNumber column.

Given bellow are the SQL for creating & populating our database.

DROP DATABASE IF EXISTS DATASERVICE_SAMPLE;
CREATE DATABASE DATASERVICE_SAMPLE;
GRANT ALL ON DATASERVICE_SAMPLE.* TO 'dsuser'@'localhost' IDENTIFIED BY 'user123';

USE DATASERVICE_SAMPLE;

DROP TABLE IF EXISTS Employees;

CREATE TABLE Employees(
        employeeNumber INTEGER,
        lastName VARCHAR(50),
        firstName VARCHAR(50),
        extension VARCHAR(10),
        email VARCHAR(100),
        officeCode VARCHAR(10),
        reportsTo INTEGER,
        jobTitle VARCHAR(50)
);


insert into Employees values (1002,'Murphy','Diane','x5800','dmurphy@classicmodelcars.com','1',null,'President');
insert into Employees values (1056,'Patterson','Mary','x4611','mpatterso@classicmodelcars.com','1',1002,'VP Sales');
insert into Employees values (1076,'Firrelli','Jeff','x9273','jfirrelli@classicmodelcars.com','1',1002,'VP Marketing');
insert into Employees values (1088,'Patterson','William','x4871','wpatterson@classicmodelcars.com','6',1056,'Sales Manager (APAC)');
insert into Employees values (1102,'Bondur','Gerard','x5408','gbondur@classicmodelcars.com','4',1056,'Sale Manager (EMEA)');
insert into Employees values (1143,'Bow','Anthony','x5428','abow@classicmodelcars.com','1',1056,'Sales Manager (NA)');
insert into Employees values (1165,'Jennings','Leslie','x3291','ljennings@classicmodelcars.com','1',1143,'Sales Rep');


DROP TABLE IF EXISTS  Salary;

CREATE TABLE Salary(
        employeeNumber INTEGER,
        salary DOUBLE,
        lastRevisedDate DATE
);

INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1002,13000,'2007/11/30');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1056,30000,'2007/01/20');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1076,17500,'2008/01/01');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1088,7000,'2007/05/20');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1102,25000,'2006/12/01');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1143,40500,'2006/03/20');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1165,12000,'2007/02/01');

Step 2 : Configuration File

Listed bellow is the configuration file demonstrating nested query feature.

<data name="NestedQuerySample">
   <config>
      <property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>
      <property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/DATASERVICE_SAMPLE</property>
      <property name="org.wso2.ws.dataservice.user">dsuser</property>
      <property name="org.wso2.ws.dataservice.password">user123</property>
      <property name="org.wso2.ws.dataservice.minpoolsize">1</property>
      <property name="org.wso2.ws.dataservice.maxpoolsize">25</property>
   </config>

   <query id="employeeQuery">
      <sql>select employeeNumber,firstName,lastName,email from Employees</sql>
      <result element="Employees" rowName="Employee">
         <element name="EmpNo" column="employeeNumber" />
         <element name="FirstName" column="firstName" />
         <element name="LastName" column="lastName" />
         <element name="Email" column="email" />

         <call-query href="salaryQuery">
            <with-param name="employeeNumber" query-param="employeeNumber" />
         </call-query>
      </result>
   </query>

   <operation name="getEmployees">
      <call-query href="employeeQuery" />
   </operation>

   <query id="salaryQuery">
      <sql>select salary,lastRevisedDate from Salary where employeeNumber = ?</sql>
      <result element="Salaries" rowName="Salary" defaultNamespace="http://salary.abc.com">
         <element name="Amount" column="salary" />
         <element name="LastRevisedDate" column="lastRevisedDate" />
      </result>

      <param name="employeeNumber" sqlType="INTEGER" type="" ordinal="" />
   </query>

   <operation name="getSalary">
      <call-query href="salaryQuery">
         <with-param name="employeeNumber" query-param="employeeNumber" />
      </call-query>
   </operation>
</data>

Step 3 :  Understanding nested query

You will notice there are two queries here.
employeeQuery - being used by operation 'getEmployees()'
salaryQuery - being used by operation 'getSalary(int employeeNumber)'

Inside employeeQuery, salaryQuery is being called using the employeeNumber retrieved from former. (i.e. employeeQuery).

<result element="Employees" rowName="Employee">
   <element name="EmpNo" column="employeeNumber" />
   <element name="FirstName" column="firstName" />
   <element name="LastName" column="lastName" />
   <element name="Email" column="email" />

   <call-query href="salaryQuery">
      <with-param name="employeeNumber" query-param="employeeNumber" />
   </call-query>
</result>

Related examples