Problem
I have two databases containing related (logically) data. How can I write a Data Service to pull data from one database ,collect related data from other database & return the response to me in a single data service call?
Step 1 : Creating sample Databases
Let's create two databases containing two tables which need to be linked.
EMPLOYEE_DB has one table,Employee - containing employee records
SALARY_DB has one table, Salary - containing employee salary information.
These two tables can be linked using employeeNumber column.
Given bellow are the SQL (MySQL 5 compatible) for creating & populating our databases.
DROP DATABASE IF EXISTS EMPLOYEE_DB;
CREATE DATABASE EMPLOYEE_DB;
GRANT ALL ON EMPLOYEE_DB.* TO 'employee'@'localhost' IDENTIFIED BY 'user123';
USE EMPLOYEE_DB;
DROP TABLE IF EXISTS Employees;
CREATE TABLE Employees(
employeeNumber INTEGER,
lastName VARCHAR(50),
firstName VARCHAR(50),
extension VARCHAR(10),
email VARCHAR(100),
officeCode VARCHAR(10),
reportsTo INTEGER,
jobTitle VARCHAR(50)
);
insert into Employees values (1002,'Murphy','Diane','x5800','dmurphy@classicmodelcars.com','1',null,'President');
insert into Employees values (1056,'Patterson','Mary','x4611','mpatterso@classicmodelcars.com','1',1002,'VP Sales');
insert into Employees values (1076,'Firrelli','Jeff','x9273','jfirrelli@classicmodelcars.com','1',1002,'VP Marketing');
insert into Employees values (1088,'Patterson','William','x4871','wpatterson@classicmodelcars.com','6',1056,'Sales Manager (APAC)');
insert into Employees values (1102,'Bondur','Gerard','x5408','gbondur@classicmodelcars.com','4',1056,'Sale Manager (EMEA)');
insert into Employees values (1143,'Bow','Anthony','x5428','abow@classicmodelcars.com','1',1056,'Sales Manager (NA)');
insert into Employees values (1165,'Jennings','Leslie','x3291','ljennings@classicmodelcars.com','1',1143,'Sales Rep');
DROP DATABASE IF EXISTS SALARY_DB;
CREATE DATABASE SALARY_DB;
GRANT ALL ON SALARY_DB.* TO 'salary'@'localhost' IDENTIFIED BY 'user123';
USE SALARY_DB;
DROP TABLE IF EXISTS Salary;
CREATE TABLE Salary(
employeeNumber INTEGER,
salary DOUBLE,
lastRevisedDate DATE
);
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1002,13000,'2007/11/30');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1056,30000,'2007/01/20');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1076,17500,'2008/01/01');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1088,7000,'2007/05/20');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1102,25000,'2006/12/01');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1143,40500,'2006/03/20');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1165,12000,'2007/02/01');
Step 2 : Configuration File
Listed bellow is the configuration file demonstrating how to connect data in these two databases.
<data name="MultipleDatasourceSample">
<config id="EmployeeDB">
<property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>
<property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/EMPLOYEE_DB</property>
<property name="org.wso2.ws.dataservice.user">employee</property>
<property name="org.wso2.ws.dataservice.password">user123</property>
<property name="org.wso2.ws.dataservice.minpoolsize">1</property>
<property name="org.wso2.ws.dataservice.maxpoolsize">25</property>
</config>
<config id="SalaryDB">
<property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>
<property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/SALARY_DB</property>
<property name="org.wso2.ws.dataservice.user">salary</property>
<property name="org.wso2.ws.dataservice.password">user123</property>
<property name="org.wso2.ws.dataservice.minpoolsize">1</property>
<property name="org.wso2.ws.dataservice.maxpoolsize">25</property>
</config>
<query id="employeeQuery" useConfig="EmployeeDB">
<sql>select employeeNumber,firstName,lastName,email from Employees</sql>
<result element="Employees" rowName="Employee">
<element name="EmpNo" column="employeeNumber" />
<element name="FirstName" column="firstName" />
<element name="LastName" column="lastName" />
<element name="Email" column="email" />
<call-query href="salaryQuery">
<with-param name="employeeNumber" query-param="employeeNumber" />
</call-query>
</result>
</query>
<operation name="getEmployees">
<call-query href="employeeQuery" />
</operation>
<query id="salaryQuery" useConfig="SalaryDB">
<sql>select salary,lastRevisedDate from Salary where employeeNumber = ?</sql>
<result element="Salaries" rowName="Salary" defaultNamespace="http://salary.abc.com">
<element name="Amount" column="salary" />
<element name="LastRevisedDate" column="lastRevisedDate" />
</result>
<param name="employeeNumber" sqlType="INTEGER" type="" ordinal="" />
</query>
<operation name="getSalary">
<call-query href="salaryQuery">
<with-param name="employeeNumber" query-param="employeeNumber" />
</call-query>
</operation>
</data>
Step 3 : Understanding Multiple Datasource support
There are two configurations(config) for the two databases involved. They are given unique ids (EmployeeDB & SalaryDB).
<config id="EmployeeDB"> <config id="SalaryDB">
When defining the query, we need to tell using which data source configuration the query has to execute.
<query id="employeeQuery" useConfig="EmployeeDB"> <query id="salaryQuery" useConfig="SalaryDB">
Inside employeeQuery, salaryQuery is being called using the employeeNumber retrieved from former. (i.e. employeeQuery).
<result element="Employees" rowName="Employee"> <element name="EmpNo" column="employeeNumber" /> <element name="FirstName" column="firstName" /> <element name="LastName" column="lastName" /> <element name="Email" column="email" />
<call-query href="salaryQuery"> <with-param name="employeeNumber" query-param="employeeNumber" /> </call-query> </result>