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.

Prerequisites

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: 
  

Creating the data service

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. Enter the following name for the data service

   Data Service Name	EmployeesDataService

3. Click Next to enter the datasource connection details.
   

Connecting to the datasource

Follow the steps given below.

1. Click Add New Datasource and enter the following details:

   Datasource ID	Datasource
   Datasource Type	RDBMS
   Datasource Type (Default/External)	Leave Default selected.
   Database Engine	MySQL
   Driver Class	com.mysql.jdbc.Driver
   URL	jdbc:mysql://localhost:3306/Company
   User Name	root

2. Save the datasource.
3. Click Next to start creating queries.

Creating a query to GET employee details by office

Let's create a query that can retrieve employee data, based on the office code. That is, when the office code is provided as an input, the data service should get the relevant employee details and present the result.

1. Click Add New Query to specify the query details.

2. Enter the following details:

   Query Name	EmployeeOfficeSQL
   Datasource	Datasource
   SQL	select EmployeeNumber, FirstName, LastName, Email, JobTitle, OfficeCode from EMPLOYEES where OfficeCode=:OfficeCode

3. 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.
      

4. Save the EmployeeOfficeSQL query.

Creating a nested query to GET office details

Let's create a query that can retrieve details of an office based on the office code. That is, when the query is invoked, the data service should get the relevant details of the office premises. Additionally, we will nest the EmployeeOfficeSQL query that was created previously to make sure that the details of the employees attached to each office code are also included in the office details.

1. Click Add New Query to specify the query details.

2. Enter the following details:

   Query Name	listOfficeSQL
   Datasource	Datasource
   SQL	select OfficeCode, AddressLine1, AddressLine2, City, State, Country, Phone from OFFICES

   
   

3. Now, you need to create the output mapping for the query, which will determine how the output is determined. You can use an XML format, JSON format, or RDF format for the output. Let's look at how to use an XML output or a JSON output:
   • If you want to map the query output to an XML format: 
     1. Click Generate Response, and the required fields will be generated as shown below.
        

   1. 1. Now, let's nest the EmployeeOfficeSQL query in the listOfficeSQL query: Click Add New Output Mapping and specify the following values.

         Mapping Type	query
         Select Query	EmployeeOfficeSQL

         When you specify the Select Query, the query parameters of the selected query will be added by the system as shown below.

         

   • If you want to map the query output to JSON:
     1. Select JSON for the Output Type field.

     2. Enter the following JSON script:

        JSON Mapping with Nested Queries

        {
            "Offices": {
                "Office": [
                    {
                        "OfficeCode": $OfficeCode(type:integer),
                        "City": "$City",
                        "Country": "$Country",
                        "Phone": "$Phone",
                        "@EmployeeOfficeSQL": "$OfficeCode->OfficeCode"
                    }
                ]
            }
        }

        Note the following:

        As shown above, nested queries are mentioned in the JSON mapping by giving the query details as a JSON object attribute. That is, the name of the target query to be called and the property value (the fields in the result mapped with the target query parameters) are included in the JSON mapping as the object attribute name.

        In the above example:

        • The target query name is mentioned by prefixing the query name with "@". Note "@EmployeeOfficeSQL" in the example given above.
        • The parameter mapping is added to the query by giving the following values: The field name in the result prefixed by "$", and the name of the target query parameter.
        • These two values in the parameter mapping are separated by "->". See "$OfficeCode->OfficeCode" in the example given above.
        • Note that the target query name and the parameter mapping are separated by a colon as follows: "@EmployeeOfficeSQL": "$OfficeCode->OfficeCode"
4. Save the output mapping for the nested query.
5. Click Main Configuration to return to the query, and save the query.
6. Click Next to open the Operations screen.

Creating a SOAP operation to invoke the query

Now, let's create SOAP operations to invoke the queries created above. Alternatively, you can create REST resources to invoke the queries. See the next section for instructions.

1. Click Add New Operation and enter the details given below.

   Operation Name	listOfficeSQLOP
   Query ID	listOfficeSQL

2. Save the listOfficeSQLOP operation.

Creating a REST resource to invoke the query

Now, let's create REST resources to invoke the queries created above. Alternatively, you can create SOAP operations to invoke the queries. See the previous section, for instructions.

1. Click Add New Resource and enter the details as shown below.

   Resource Path	offices/{OfficeCode}
   Resource Method	Get
   Query ID	listOfficeSQL

2. Save the resource.

Finish creating the data service

Once you have defined the operation, click  Finish to complete the data service creation process. You will now be taken to the  Deployed Services screen, which shows all the data services deployed on the server.

Invoking the data service using SOAP

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 result.

Invoking the data service using REST

The service can be invoked in REST-style via curl (http://curl.haxx.se). Shown below is the curl command to invoke the GET resource:

curl -X GET http://localhost:8280/services/EmployeesDataService.HTTPEndpoint/offices/1

You will now 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>