Page Comparison

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

...

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.

...

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

2. Enter the following details:

   Query Name	EmployeeOfficeSQL
   Datasource	Datasource
   SQL	Code Block 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.

...

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

2. Enter the following details:

   Query Name	listOfficeSQL
   Datasource	Datasource
   SQL	Code Block 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:

        Code Block
        language javascript title JSON Mapping with Nested Queries
        { "Offices": { "Office": [ { "OfficeCode": $OfficeCode(type:integer), "City": "$City", "Country": "$Country", "Phone": "$Phone", "@EmployeeOfficeSQL": "$OfficeCode->OfficeCode" } ] } }

        Info

        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.

...

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

   Operation Name	listOfficeSQLOP
   Query ID	listOfficeSQL

2. Save the listOfficeSQLOP operation.

...

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

...