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

   CREATE DATABASE Company;

4. Create the following tables:

   • Offices table:

     USE company;
     
     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:
  To view the data, you can run the following command: SELECT * FROM Offices;
   
• Employees table: 
  To view the data, you can run the following command:  SELECT * FROM Employees;
  
  If you tried out the previous tutorials, you will have more data than what is shown above.

Creating the data service

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

1. Start the WSO2 ESB profile.
2. Open the ESB profile's Management Console using https://localhost:9443/carbon, and log in using admin as the username and the password.
3. Click Create under Data Service.

4. Enter the following name for the data service

   Data Service Name	EmployeesDataService

5. Click Next to enter thedatasource 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	Enter your MySQL server's username. Example: Root
   Password	Enter your MySQL server's password.  If you have not assigned a password, keep this field empty.

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

Creating a query to GET employee details by the office

Let's create a query that can retrieve employee data, based on the office code. 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. 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 where OfficeCode=:OfficeCode

3. Click Generate Input Mappings to create the input mapping. The office code is the input as shown below.
   
4. 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.
        

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

     3. Click Save and click Main Configuration to return to the query.
   • 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"
5. Save the output mapping for the nested query.
6.  Save the query.
7. 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. Select the listOfficeSQLOP operation.

4. Copy the content given below, and paste it into the TryIt tool to get the details of the office that has the office code 1 and all the employees that belong to office code 1.

   <body>
      <p:listOfficeSQLOP xmlns:p="http://ws.wso2.org/dataservice">
         <!--Exactly 1 occurrence-->
         <xs:OfficeCode xmlns:xs="http://ws.wso2.org/dataservice">1</xs:OfficeCode>
      </p:listOfficeSQLOP>
   </body>

5. Click Send 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.
It gets the details of the office that has the office code 1, and all the employees that belong to office code 1.

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

curl -H 'Accept: application/json' -X GET http://localhost:8280/services/EmployeesDataService.HTTPEndpoint/offices/1

You will now see the following result: