Using Nested Data 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¶
Let's create a MySQL database with the required data.
- Install the MySQL server.
-
Create the following database: Company
CREATE DATABASE Company;
-
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`));
-
-
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:
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;
Synapse configuration¶
Given below is the data service configuration you need to build. See the instructions on how to build and run this example.
Tip
Be sure to replace the datasource username and password with the correct values for your MySQL instance.
<data name="nested_queries" transports="http https local">
<config enableOData="false" id="Datasource">
<property name="driverClassName">com.mysql.jdbc.Driver</property>
<property name="url">jdbc:mysql://localhost:3306/Company</property>
<property name="username">root</property>
<property name="password">password</property>
</config>
<query id="EmployeeOfficeSQL" useConfig="Datasource">
<sql>select EmployeeNumber, FirstName, LastName, Email, JobTitle, OfficeCode from EMPLOYEES where OfficeCode=:OfficeCode</sql>
<result element="Entries" rowName="Entry">
<element column="EmployeeNumber" name="EmployeeNumber" xsdType="string"/>
<element column="FirstName" name="FirstName" xsdType="string"/>
<element column="LastName" name="LastName" xsdType="string"/>
<element column="Email" name="Email" xsdType="string"/>
<element column="JobTitle" name="JobTitle" xsdType="string"/>
<element column="OfficeCode" name="OfficeCode" xsdType="string"/>
</result>
<param name="OfficeCode" sqlType="STRING"/>
</query>
<query id="listOfficeSQL" useConfig="Datasource">
<sql>select OfficeCode, AddressLine1, AddressLine2, City, State, Country, Phone from OFFICES where OfficeCode=:OfficeCode</sql>
<result element="Entries" rowName="Entry">
<element column="OfficeCode" name="OfficeCode" xsdType="string"/>
<element column="AddressLine1" name="AddressLine1" xsdType="string"/>
<element column="AddressLine2" name="AddressLine2" xsdType="string"/>
<element column="City" name="City" xsdType="string"/>
<element column="State" name="State" xsdType="string"/>
<element column="Country" name="Country" xsdType="string"/>
<element column="Phone" name="Phone" xsdType="string"/>
<call-query href="EmployeeOfficeSQL" requiredRoles="">
<with-param column="OfficeCode" name="OfficeCode"/>
</call-query>
</result>
<param name="OfficeCode" sqlType="STRING"/>
</query>
<operation name="listOfficeSQLOP">
<call-query href="listOfficeSQL">
<with-param name="OfficeCode" query-param="OfficeCode"/>
</call-query>
</operation>
<resource method="GET" path="offices/{OfficeCode}">
<call-query href="listOfficeSQL">
<with-param name="OfficeCode" query-param="OfficeCode"/>
</call-query>
</resource>
</data>
Tip
If you want to map the query output to JSON, select JSON
as the output type. The query result for the listOfficeSQL
query will be as follows:
<query id="listOfficeSQL" useConfig="Datasource">
<sql>select OfficeCode, AddressLine1, AddressLine2, City, State, Country, Phone from OFFICES where OfficeCode=:OfficeCode</sql>
<result outputType="json">{
"Offices":{
"Office":[
{
"OfficeCode":"$OfficeCode(type:integer)",
"City":"$City",
"Country":"$Country",
"Phone":"$Phone",
"@EmployeeOfficeSQL":"$OfficeCode->OfficeCode"
}
]
}
}</result>
<param name="OfficeCode" sqlType="STRING"/>
</query>
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"
Build and run¶
Create the artifacts:
- Set up WSO2 Integration Studio. The path to this folder is referred to as
MI_TOOLING_HOME
throughout this tutorial. -
Download the JDBC driver for MySQL from here and copy it to the
MI_TOOLING_HOME/Contents/Eclipse/runtime/microesb/lib/
(for MacOS) orMI_TOOLING_HOME/runtime/microesb/lib/
(for Windows) directory.Note
If the driver class does not exist in the relevant folders when you create the datasource, you will get an exception such as
Cannot load JDBC driver class com.mysql.jdbc.Driver
. - Create the data service with the configurations given above.
- Deploy the artifacts in your Micro Integrator.
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:8290/services/nested_queries/offices/1
Tip
If you configured the output mapping of the listOfficeSQL
query to be in the JSON format, you need to add the header -H 'Accept: application/json'
to your curl command to get the output in the JSON format.
curl -H 'Accept: application/json' -X GET http://localhost:8290/services/nested_queries/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>[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>
</Entries>
{
"Offices":{
"Office":[
{
"Phone":"+441523624",
"Country":"United Kingdom",
"OfficeCode":1,
"City":"Norwich",
"Entries":{
"Entry":[
{
"EmployeeNumber":"1",
"FirstName":"John",
"LastName":"Gardiner",
"Email":"[email protected]",
"JobTitle":"Manager",
"OfficeCode":"1"
},
{
"EmployeeNumber":"3",
"FirstName":"David",
"LastName":"Green",
"Email":"[email protected]",
"JobTitle":"Manager",
"OfficeCode":"1"
},
{
"EmployeeNumber":"1002",
"FirstName":"Peter",
"LastName":"Parker",
"Email":"[email protected]",
"JobTitle":null,
"OfficeCode":"1"
},
{
"EmployeeNumber":"1003",
"FirstName":"Chris",
"LastName":"Sam",
"Email":"[email protected]",
"JobTitle":null,
"OfficeCode":"1"
},
{
"EmployeeNumber":"1006",
"FirstName":"Chris",
"LastName":"Sam",
"Email":"[email protected]",
"JobTitle":null,
"OfficeCode":"1"
},
{
"EmployeeNumber":"1007",
"FirstName":"John",
"LastName":"Doe",
"Email":"[email protected]",
"JobTitle":null,
"OfficeCode":"1"
},
{
"EmployeeNumber":"1008",
"FirstName":"Peter",
"LastName":"Parker",
"Email":"[email protected]",
"JobTitle":null,
"OfficeCode":"1"
}
]
}
}
]
}
}