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
-
Unzip the product to a path containing no spaces in the path name. This is your <DSS_HOME>
-
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.
- Log in to DSS by using the default credentials (username: admin/ password: admin).
Creating the data service
Create a new data service 'sampleDS'
Create a datasource as follows by referring to the above local MySQL database.
Add new Query; query ID: 'getEngagementsPerEmployee', query: above tested query, and press generate response link
- No operation is defined in this example, therefore press next
Add new Resource; Resource Path: '/engagements' by selecting Resource Method: 'GET' and selecting Query ID: 'getEngagementsPerEmployee'
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>
