Defining Nested Queries

Nested queries help you to use the result of one query as an input parameter of another, and the queries executed in a nested query works in a transactional manner. Follow the steps given below to add a nested query to a data service.

Setting up a datasource

Follow the steps given below to set up a MySQL database for this tutorial.

1. Install the MySQL server.
2. Download the JDBC driver for MySQL from here and copy it to your <EI_HOME>/lib directory.
   
3. Create the following database: Company

4. Create the following tables:

   • Offices table:
     

     CREATE TABLE `OFFICES` (`OfficeCode` int(11) NOT NULL, `AddressLine1` varchar(255) NOT NULL, `AddressLine2` varchar(255) DEFAULT NULL, `City` varchar(255) DEFAULT NULL, `State` varchar(255) DEFAULT NULL, `Country` varchar(255) DEFAULT NULL, `Phone` varchar(255) DEFAULT NULL, PRIMARY KEY (`OfficeCode`)); 

   • Employees table:

     CREATE TABLE `EMPLOYEES` (`EmployeeNumber` int(11) NOT NULL, `FirstName` varchar(255) NOT NULL, `LastName` varchar(255) DEFAULT NULL, `Email` varchar(255) DEFAULT NULL, `JobTitle` varchar(255) DEFAULT NULL, `OfficeCode` int(11) NOT NULL, PRIMARY KEY (`EmployeeNumber`,`OfficeCode`), CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`OfficeCode`) REFERENCES `OFFICES` (`OfficeCode`));

5. Insert the following data into the tables:

   • Add to the Offices table:
     

     INSERT INTO OFFICES VALUES (1,"51","Glen Street","Norwich","London","United Kingdom","+441523624");
     INSERT INTO OFFICES VALUES (2,"72","Rose Street","Pasadena","California","United States","+152346343");

   • Add to the Employees table:
     

     INSERT INTO EMPLOYEES VALUES (1,"John","Gardiner","john@office1.com","Manager",1);
     INSERT INTO EMPLOYEES VALUES (2,"Jane","Stewart","jane@office2.com","Head of Sales",2);
     INSERT INTO EMPLOYEES VALUES (3,"David","Green","david@office1.com","Manager",1); 

You will now have two tables in the Company database as shown below:

•  Offices table:
   
• Employees table: 
  

Writing a nested query for a datasource

Let's create a data service using the Create Data Service wizard:

1. Log into the management console and click Create under Data Service.
2. Add a name for the data service and go to the next step.
3. Connect to the Company database that you defined above. For information on how to connect to a RDBMS, see Adding an RDBMS datasource.
4. Click Next to open the Queries screen.
5. Click Add New Query to specify the query details:

   1. Enter EmployeeOfficeSQL as the query ID.

   2. Enter the following SQL dialect:

      select EmployeeNumber, FirstName, LastName, Email, JobTitle, OfficeCode from EMPLOYEES where OfficeCode=:OfficeCode

6. Generate input and output mappings:
   1. Click Generate Input Mapping and an input mapping will be generated automatically for the OfficeCode field:
      
   2. Click Generate Response to automatically generate output mappings for the EmployeeNumber, FirstName, LastName, Email, Job Title and Office Code fields.
      

7. Save the EmployeeOfficeSQL query.

8. Start creating a new query for the Company datasource:

   1. Enter listOfficeSQL as the query ID.

   2. Enter the following SQL dialect:

      select OfficeCode, AddressLine1, AddressLine2, City, State, Country, Phone from OFFICES

9. Click Generate Response to create output mappings for the fields specified in the above query.
   

10. Click Add New Output Mapping and specify the required values for the fields.

    1. Select Query for the Mapping Type field.

    2. Enter the employeeOfficeSQL query you created previously in the Select Query field.

    3. Save the output mapping

    

11. Save the listOfficeSQL query.

12. Click Next to open the Operations screen.
13. Create an operation for the listOfficeSQL query as shown below:
    

14. Save the listOfficeSQLOP operation.

15. Click Finish to navigate to the Deployed Services window, from where you can manage data services.

Invoking the data service 

You can try the data service you created by using the TryIt tool that is in your product by default. 

1. Go to the Deployed Services screen.
2. Click Try this Service to open the data service from the TryIt tool.

3. Execute the listOfficeSQLOP operation and you will see the following result:
   

   <Entries xmlns="http://ws.wso2.org/dataservice">
      <Entry>
         <OfficeCode>1</OfficeCode>
         <AddressLine1>51</AddressLine1>
         <AddressLine2>Glen Street</AddressLine2>
         <City>Norwich</City>
         <State>London</State>
         <Country>United Kingdom</Country>
         <Phone>+441523624</Phone>
         <Entries>
            <Entry>
               <EmployeeNumber>1</EmployeeNumber>
               <FirstName>John</FirstName>
               <LastName>Gardiner</LastName>
               <Email>john@office1.com</Email>
               <JobTitle>Manager</JobTitle>
               <OfficeCode>1</OfficeCode>
            </Entry>
            <Entry>
               <EmployeeNumber>3</EmployeeNumber>
               <FirstName>David</FirstName>
               <LastName>Green</LastName>
               <Email>david@office1.com</Email>
               <JobTitle>Manager</JobTitle>
               <OfficeCode>1</OfficeCode>
            </Entry>
         </Entries>
      </Entry>
      <Entry>
         <OfficeCode>2</OfficeCode>
         <AddressLine1>72</AddressLine1>
         <AddressLine2>Rose Street</AddressLine2>
         <City>Pasadena</City>
         <State>California</State>
         <Country>United States</Country>
         <Phone>+152346343</Phone>
         <Entries>
            <Entry>
               <EmployeeNumber>2</EmployeeNumber>
               <FirstName>Jane</FirstName>
               <LastName>Stewart</LastName>
               <Email>jane@office2.com</Email>
               <JobTitle>Head of Sales</JobTitle>
               <OfficeCode>2</OfficeCode>
            </Entry>
         </Entries>
      </Entry>
   </Entries>