Due to a known issue do not use JDK1.8.0_151 with WSO2 products. Use JDK 1.8.0_144 until JDK 1.8.0_162-ea is released.
This documentation is for WSO2 Data Services Server version 3.5.1. For the latest documentation, see the documentation for WSO2 Enterprise Integrator.
||
Skip to end of metadata
Go to start of metadata

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 <DSS_HOME>/repository/components/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","[email protected]","Manager",1);
      INSERT INTO EMPLOYEES VALUES (2,"Jane","Stewart","[email protected]","Head of Sales",2);
      INSERT INTO EMPLOYEES VALUES (3,"David","Green","[email protected]","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 of WSO2 DSS and click Create in the Data Service menu.
  2. Add a name for the data service and go to the next step.
  3. Connect to the Company database that you defined above.
  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 EmployeeNumberFirstNameLastNameEmailJob Title and Office Code fields.
  7. 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
  8. Click Generate Response to create output mappings for the fields specified in the above query.
  9. Create a new output mapping.

    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

  10. Save the listOfficeSQL query.

  11. Create an operation for the listOfficeSQL query as shown below.
  12. Save the listOfficeSQLOP operation.

  13. 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>[email protected]</Email>
                <JobTitle>Manager</JobTitle>
                <OfficeCode>1</OfficeCode>
             </Entry>
             <Entry>
                <EmployeeNumber>3</EmployeeNumber>
                <FirstName>David</FirstName>
                <LastName>Green</LastName>
                <Email>[email protected]</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>[email protected]</Email>
                <JobTitle>Head of Sales</JobTitle>
                <OfficeCode>2</OfficeCode>
             </Entry>
          </Entries>
       </Entry>
    </Entries>
  • No labels