Using WSO2 DSS retrieve data from multiple databases in a single Query

I came across an issue of having same table in multiple databases and having to write data multiple times when syncing these data with external systems. Therefore I thought of transferring these duplicated tables into one single common database. Then this common database will to be used by several of our internal systems. However I recently came across an issue that I needed to retrieve data from multiple databases using a single query. Since I'm already using WSO2 DSS, I found a solution to tackle this problem.

Here's how to do it. For the ease of understanding I have divided the steps into two parts.

Part 1 - Configure MySQL Database

Login to your local MySQL db and create two databases.
Then create two tables and insert some test data as follows.

mysql> create database db1;
mysql> create database db2;

mysql> use db1; 

mysql> CREATE TABLE employees ( EmployeeID int(11) NOT NULL AUTO_INCREMENT, FirstName varchar(255) DEFAULT NULL, LastName varchar(255) DEFAULT NULL, Team varchar(255) DEFAULT NULL, PRIMARY KEY (EmployeeID));

mysql> insert into employees (FirstName, LastName, Team) values('Suhan', 'Dharmasuriya', 'InternalIT');

mysql> insert into employees (FirstName, LastName, Team) values('Thilina', 'Perera', 'Finance');

mysql> select * from employees;

+------------+-----------+--------------+------------+

| EmployeeID | FirstName | LastName     | Team       |

+------------+-----------+--------------+------------+

|          1 | Suhan     | Dharmasuriya | InternalIT |

|          2 | Thilina   | Perera       | Finance    |

+------------+-----------+--------------+------------+

2 rows in set (0.00 sec)

mysql> use db2;

mysql> CREATE TABLE engagements ( EngagementID int(11) NOT NULL AUTO_INCREMENT, EmployeeID int(11), PRIMARY KEY (EngagementID));

mysql> insert into engagements (EmployeeID) values(1);

mysql> select * from engagements;

+--------------+------------+

| EngagementID | EmployeeID |

+--------------+------------+

|            1 |          1 |

+--------------+------------+

1 row in set (0.00 sec)

Now lets test the following MySQL query and get results. Here I have used a LEFT OUTER JOIN.

Result will be obtained from the two databases. We will save this query in WSO2 DSS. 

mysql> SELECT A.EmployeeID, A.FirstName, A.LastName, A.Team, B.EngagementID from db1.employees AS A LEFT OUTER JOIN db2.engagements AS B ON A.EmployeeID=B.EmployeeID;

+------------+-----------+--------------+------------+--------------+

| EmployeeID | FirstName | LastName     | Team       | EngagementID |

+------------+-----------+--------------+------------+--------------+

|          1 | Suhan     | Dharmasuriya | InternalIT |            1 |

|          2 | Thilina   | Perera       | Finance    |         NULL |

+------------+-----------+--------------+------------+--------------+

2 rows in set (0.00 sec)

Part 2 - Configure WSO2 DSS

1. Download WSO2 DSS 3.5.0 from here (http://wso2.com/products/data-services-server/). If you already have the product zip file (wso2dss-3.5.0.zip) continue with the next step.
2. Unzip the product to a path containing no spaces in the path name. This is your <DSS_HOME>
3. Download the MySQL connector jar here (http://dev.mysql.com/downloads/connector/j/) and copy it to <DSS_HOME>/repository/components/lib/
4. Start the WSO2 DSS server.

To start the server, your have to run the script wso2server.bat (on Windows) or

wso2server.sh (on Linux/Solaris) from the <DSS_HOME>/bin folder.

6. Log in to DSS by using the default credentials (username: admin/ password: admin).

Creating the data service

1. Create a new data service 'sampleDS'
Home -> Manage -> Services -> Add -> Data Service -> Create
2. Create a datasource as follows by referring to the above local MySQL database.
3. Add new Query; query ID: 'getEngagementsPerEmployee', query: above tested query, and press generate response link
4. No operation is defined in this example, therefore press next
5. Add new Resource; Resource Path: '/engagements' by selecting Resource Method: 'GET' and selecting Query ID: 'getEngagementsPerEmployee'
6. Press Finish to create the data service

Refer following screenshots for more information.

Now you can test the datasource as follows.

Open a new tab in your browser and type the following URL: http://localhost:9763/services/sampleDS/engagements

You will get the following response:

<Entries xmlns="http://ws.wso2.org/dataservice">

<Entry>

<EmployeeID>1</EmployeeID>

<FirstName>Suhan</FirstName>

<LastName>Dharmasuriya</LastName>

<Team>InternalIT</Team>

<EngagementID>1</EngagementID>

</Entry>

<Entry>

<EmployeeID>2</EmployeeID>

<FirstName>Thilina</FirstName>

<LastName>Perera</LastName>

<Team>Finance</Team>

<EngagementID xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>

</Entry>

</Entries>

Refer following Data Service synapse configuration for more information.
<data name="sampleDS" transports="http https local">
   <config enableOData="false" id="datasource">
      <property name="driverClassName">com.mysql.jdbc.Driver</property>
      <property name="url">jdbc:mysql://localhost:3306</property>
      <property name="username">root</property>
      <property name="password">root</property>
   </config>
   <query id="getEngagementsPerEmployee" useConfig="datasource">
      <sql>SELECT A.EmployeeID, A.FirstName, A.LastName, A.Team, B.EngagementID from db1.employees AS A LEFT OUTER JOIN db2.engagements AS B ON A.EmployeeID=B.EmployeeID;</sql>
      <result element="Entries" rowName="Entry">
         <element column="EmployeeID" name="EmployeeID" xsdType="string"/>
         <element column="FirstName" name="FirstName" xsdType="string"/>
         <element column="LastName" name="LastName" xsdType="string"/>
         <element column="Team" name="Team" xsdType="string"/>
         <element column="EngagementID" name="EngagementID" xsdType="string"/>
      </result>
   </query>
   <resource method="GET" path="/engagements">
      <call-query href="getEngagementsPerEmployee"/>
   </resource>
</data>