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.

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.

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

5. Click Next to enter thedatasource connection details.

Follow the steps given below.

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

2. Save the datasource.

3. Click Next to start creating queries.

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:

3. Generate input and output mappings:

   1. Click Generate Input Mapping and an input mapping will be generated automatically for the OfficeCode field:
      

      Open

      

   2. Click Generate Response to automatically generate output mappings for the EmployeeNumber, FirstName, LastName, Email, Job Title, and Office Code fields.
      

      Open

      

4. Save the EmployeeOfficeSQL query.

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:

3. Click Generate Input Mappings to create the input mapping. The office code is the input as shown below.

   Open

   

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.
        

        Open

        

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

        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" } ] } }

5. Save the output mapping for the nested query.

6.  Save the query.

7. Click Next to open the Operations screen.