Sunday, July 3, 2016

WSO2 DSS - Working With 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.

You can use the default h2 database tables comes with the WSO2 DSS to run these samples.

Nested Query Sample with XML Output Mapping 

<data name="TestNested" transports="http https local">
   <config enableOData="false" id="test">
      <property name="driverClassName">org.h2.Driver</property>
      <property name="url">jdbc:h2:file:./samples/database/DATA_SERV_SAMP</property>
      <property name="username">wso2ds</property>
      <property name="password">wso2ds</property>
   </config>
   <query id="CustomerNameSQL" useConfig="test">
      <sql>Select c.CUSTOMERNAME from CUSTOMERS c where C.CUSTOMERNUMBER = :customerNumberin</sql>
      <result element="Customer">
         <element column="CUSTOMERNAME" name="Name"/>
      </result>
      <param name="customerNumberin" sqlType="INTEGER"/>
   </query>
   <query id="CustomerOrderSQL" useConfig="test">
      <sql>Select o.ORDERNUMBER,o.ORDERDATE,o.STATUS,o.CUSTOMERNUMBER from ORDERS o</sql>
      <result element="Entries" rowName="Entry">
         <element column="ORDERNUMBER" name="ORDERNUMBER" xsdType="string"/>
         <element column="ORDERDATE" name="ORDERDATE" xsdType="string"/>
         <element column="STATUS" name="STATUS" xsdType="string"/>
         <element column="CUSTOMERNUMBER" name="CUSTOMERNUMBER" xsdType="string"/>
         <call-query href="CustomerNameSQL" requiredRoles="">
            <with-param name="customerNumberin" query-param="customerNumber"/>
         </call-query>
      </result>
   </query>
   <operation name="customerOrders">
      <call-query href="CustomerOrderSQL"/>
   </operation>
   <resource method="GET" path="orderlist">
      <call-query href="CustomerOrderSQL"/>
   </resource>
</data>
http://172.17.0.1:9763/services/TestNested.HTTPEndpoint/orderlist

 

Request:

http://172.17.0.1:9763/services/TestNested.HTTPEndpoint/orderlist

Sample Response:

<Entries xmlns="http://ws.wso2.org/dataservice">
   <Entry>
      <ORDERNUMBER>10100</ORDERNUMBER>
      <ORDERDATE>2003-01-06+06:00</ORDERDATE>
      <STATUS>Shipped</STATUS>
      <CUSTOMERNUMBER>363</CUSTOMERNUMBER>
      <Customer>
         <Name>Online Diecast Creations Co.</Name>
      </Customer>
   </Entry>
   <Entry>
      <ORDERNUMBER>10101</ORDERNUMBER>
      <ORDERDATE>2003-01-09+06:00</ORDERDATE>
      <STATUS>Shipped</STATUS>
      <CUSTOMERNUMBER>128</CUSTOMERNUMBER>
      <Customer>
         <Name>Blauer See Auto, Co.</Name>
      </Customer>
   </Entry>
</Entries>

 
Nested Query Sample with JSON output Mapping

Same above service but with json output mapping is as follows.
The format of the mapping should be as follows.
"@target_query_name": "$nested_query_parameter->outer_Query_result_element_name"

<data name="TestNestedJSON" transports="http https local">
   <config enableOData="false" id="test">
      <property name="driverClassName">org.h2.Driver</property>
      <property name="url">jdbc:h2:file:./samples/database/DATA_SERV_SAMP</property>
      <property name="username">wso2ds</property>
      <property name="password">wso2ds</property>
   </config>
   <query id="CustomerNameSQL" useConfig="test">
      <sql>Select c.CUSTOMERNAME from CUSTOMERS c where C.CUSTOMERNUMBER = :customerNumberin</sql>
      <result outputType="json">{"Customers": {"Customer": [{"Name": "$CUSTOMERNAME"}]}}</result>
      <param name="customerNumberin" sqlType="INTEGER"/>
   </query>
   <query id="CustomerOrderSQL" useConfig="test">
      <sql>Select o.ORDERNUMBER,o.ORDERDATE,o.STATUS,o.CUSTOMERNUMBER from ORDERS o</sql>
      <result outputType="json">{"Entries": {"Entry": [{"ORDERNUMBER": "$ORDERNUMBER","ORDERDATE": "$ORDERDATE","STATUS": "$STATUS","CUSTOMERNUMBER": "$CUSTOMERNUMBER","@CustomerNameSQL": "$customerNumberin-&gt;customerNumber"}]}}</result>
   </query>
   <operation name="customerOrders">
      <call-query href="CustomerOrderSQL"/>
   </operation>
   <resource method="GET" path="orderlist">
      <call-query href="CustomerOrderSQL"/>
   </resource>
</data>


Request:

http://172.17.0.1:9763/services/TestNestedJSON.HTTPEndpoint/orderlist


Response:

with header Accept: applicaton/json

{
    "Entries": {
        "Entry": [{
            "Customers": {
                "Customer": [{
                    "Name": "Online Diecast Creations Co."
                }]
            },
            "ORDERNUMBER": "10100",
            "CUSTOMERNUMBER": "363",
            "STATUS": "Shipped",
            "ORDERDATE": "2003-01-06+06:00"
        }, {
            "Customers": {
                "Customer": [{
                    "Name": "Blauer See Auto, Co."
                }]
            },
            "ORDERNUMBER": "10101",
            "CUSTOMERNUMBER": "128",
            "STATUS": "Shipped",
            "ORDERDATE": "2003-01-09+06:00"
        }]
    }
}





Without Accept:application/json header


<Entries xmlns="http://ws.wso2.org/dataservice/CustomerOrderSQL">
  <Entry>
    <Customers xmlns="http://ws.wso2.org/dataservice/CustomerNameSQL">
      <Customer>
        <Name>Online Diecast Creations Co.</Name>
      </Customer>
    </Customers>
    <ORDERNUMBER>10100</ORDERNUMBER>
    <CUSTOMERNUMBER>363</CUSTOMERNUMBER>
    <STATUS>Shipped</STATUS>
    <ORDERDATE>2003-01-06+06:00</ORDERDATE>
  </Entry>
  <Entry>
    <Customers xmlns="http://ws.wso2.org/dataservice/CustomerNameSQL">
      <Customer>
        <Name>Blauer See Auto, Co.</Name>
      </Customer>
    </Customers>
    <ORDERNUMBER>10101</ORDERNUMBER>
    <CUSTOMERNUMBER>128</CUSTOMERNUMBER>
    <STATUS>Shipped</STATUS>
    <ORDERDATE>2003-01-09+06:00</ORDERDATE>
  </Entry>
  <Entries>



With Multiple Parameters for the nested query:

For this I have modified the two tables by adding a new column as follows.
Alter table CUSTOMERS add ISACTIVE int DEFAULT(1);
Alter table ORDERS add ISACTIVE int DEFAULT(1);


The format of the mapping is as follows.
"@target_query_name": "$nested_query_parameter1->
outer_Query_result_element_name1,$nested_query_parameter2->outer_Query_result_element_name2"

<data name="TestNestedJSON" transports="http https local">
   <config enableOData="false" id="test">
      <property name="driverClassName">org.h2.Driver</property>
      <property name="url">jdbc:h2:file:./samples/database/DATA_SERV_SAMP</property>
      <property name="username">wso2ds</property>
      <property name="password">wso2ds</property>
   </config>
   <query id="CustomerNameSQL" useConfig="test">
      <sql>Select c.CUSTOMERNAME from CUSTOMERS c where C.CUSTOMERNUMBER = :customerNumberin and c.ISACTIVE = :isactivein</sql>
      <result outputType="json">{"Customers": {"Customer": [{"Name": "$CUSTOMERNAME"}]}}</result>
      <param name="customerNumberin" sqlType="INTEGER"/>
      <param name="isactivein" sqlType="INTEGER"/>
   </query>
   <query id="CustomerOrderSQL" useConfig="test">
      <sql>Select o.ORDERNUMBER,o.ORDERDATE,o.STATUS,o.CUSTOMERNUMBER,o.ISACTIVE from ORDERS o</sql>
      <result outputType="json">{"Entries": {"Entry": [{"ORDERNUMBER": "$ORDERNUMBER","ORDERDATE": "$ORDERDATE","STATUS": "$STATUS","CUSTOMERNUMBER": "$CUSTOMERNUMBER","@CustomerNameSQL": "$customerNumberin-&gt;customerNumber,$isactivein-&gt;isactive"}]}}</result>
   </query>
   <operation name="customerOrders">
      <call-query href="CustomerOrderSQL"/>
   </operation>
   <resource method="GET" path="orderlist">
      <call-query href="CustomerOrderSQL"/>
   </resource>
</data>


References:
[1] https://docs.wso2.com/display/DSS350/Defining+Nested+Queries
[2] https://docs.wso2.com/display/DSS350/JSON+Mapping

2 comments:

  1. I have a basic question with Nested query how many database calls will be made. My concern is it will execute a query in the database and come back again to the data service with result and execute the query against the database again. Is my assumption right?

    ReplyDelete
    Replies
    1. Yes. When we are using nested query feature , we call to a some query inside another query. So the inner query takes the input/s from the result of outer query being called and both queries get executed. Other than using the result of one query as input to a another query , the main use-case of nested query feature is to merge two result sets of two queries and build a one result.

      Delete