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
- 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.
- Unzip the product to a path containing no spaces in the path name. This is your <DSS_HOME>
- Download the MySQL connector jar here (http://dev.mysql.com/downloads/connector/j/) and copy it to <DSS_HOME>/repository/components/lib/
- 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'
Home -> Manage -> Services -> Add -> Data Service -> Create
- 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">
</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>
<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>
No comments:
Post a Comment