This post is the second part of the Entity Aggregation with WSO2 ESB and WSO2 Data Services Server series. Part I can be found
I am going to implement a prototype of the above scenario with the following assumptions:
The ERP system exposes the Customer Data through a MySQL database.
The requirement is to implement the Aggregation Service which will aggregate the customer information from the ERP and CRM systems and expose a unified view of the Customer through a RESTful API.
create database ERP_DB;
use ERP_DB;
CREATE TABLE `Customer` (
`ERPCustID` varchar(90) NOT NULL PRIMARY KEY,
`LName` varchar(200) DEFAULT NULL,
`FName` varchar(200) DEFAULT NULL,
`OrgID` varchar(200) DEFAULT NULL
);
insert into Customer values ("001", "Green", "Rachel","1010");
insert into Customer values ("002", "Buffet", "Phoebe","1020");
insert into Customer values ("003", "Geller", "Monica","1030");
insert into Customer values ("004", "Geller", "Ross","1040");
insert into Customer values ("005", "Bing", "Chandler","1050");
insert into Customer values ("006", "Tribbiani", "Joey","1060");
Once the Customer table is created a select * statement should show a populated table as given below:
Mocking the CRM System:
The CRM service as mentioned earlier will be a SOAP service named CRMCustomerService hosted in the WSO2 Application Service with two operations – getAllCRMCustomers and getCustomerByOrgId. A response from the getAllCRMCustomers would look like this:
<ns:getAllCRMCustomersResponse xmlns:ns="http://crm.wso2.org" xmlns:ax21="http://crm.wso2.org/xsd">
<ns:return xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ax21:Customer">
<ax21:CRMCustID>2001</ax21:CRMCustID>
<ax21:LName>Geller</ax21:LName>
<ax21:address>45, Broadway Avenue, NYC</ax21:address>
<ax21:fname>Ross</ax21:fname>
<ax21:orgID>1040</ax21:orgID>
<ax21:tel>43124562</ax21:tel>
</ns:return>
<ns:return xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ax21:Customer">
<ax21:CRMCustID>2002</ax21:CRMCustID>
<ax21:LName>Bing</ax21:LName>
<ax21:address>55, Fifth Avenue, NYC</ax21:address>
<ax21:fname>Chandler</ax21:fname>
<ax21:orgID>1050</ax21:orgID>
<ax21:tel>91234829</ax21:tel>
</ns:return>
<ns:return xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ax21:Customer">
<ax21:CRMCustID>2003</ax21:CRMCustID>
<ax21:LName>Tribbiani</ax21:LName>
<ax21:address>98, Park Avenue, NYC</ax21:address>
<ax21:fname>Joey</ax21:fname>
<ax21:orgID>1060</ax21:orgID>
<ax21:tel>12304883</ax21:tel>
</ns:return>
<ns:return xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ax21:Customer">
<ax21:CRMCustID>2004</ax21:CRMCustID>
<ax21:LName>Green</ax21:LName>
<ax21:address>999, Park Avenue, NYC</ax21:address>
<ax21:fname>Rachel</ax21:fname>
<ax21:orgID>1010</ax21:orgID>
<ax21:tel>12304883</ax21:tel>
</ns:return>
<ns:return xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ax21:Customer">
<ax21:CRMCustID>2005</ax21:CRMCustID>
<ax21:LName>Buffet</ax21:LName>
<ax21:address>123, Park Avenue, NYC</ax21:address>
<ax21:fname>Phoebe</ax21:fname>
<ax21:orgID>1020</ax21:orgID>
<ax21:tel>9786756</ax21:tel>
</ns:return>
<ns:return xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ax21:Customer">
<ax21:CRMCustID>2006</ax21:CRMCustID>
<ax21:LName>Geller</ax21:LName>
<ax21:address>98, Broadway Avenue, NYC</ax21:address>
<ax21:fname>Monica</ax21:fname>
<ax21:orgID>1030</ax21:orgID>
<ax21:tel>14652638</ax21:tel>
</ns:return>
</ns:getAllCRMCustomersResponse>
While a response from getCustomerByOrgID would look like this:
<ns:getCustomerByOrgIDResponse xmlns:ns="http://crm.wso2.org">
<ns:return xmlns:ax21="http://crm.wso2.org/xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ax21:Customer">
<ax21:CRMCustID>2004</ax21:CRMCustID>
<ax21:LName>Green</ax21:LName>
<ax21:address>999, Park Avenue, NYC</ax21:address>
<ax21:fname>Rachel</ax21:fname>
<ax21:orgID>1010</ax21:orgID>
<ax21:tel>12304883</ax21:tel>
</ns:return>
</ns:getCustomerByOrgIDResponse>
The CRMCustomerService was implemented using the following Java classes which were used to create an Axis2 Web Service using WSO2 Developer Studio. Here is how you can create an Axis2 Service from the WSO2 Developer Studio.
CRMCustomerService.java
package org.wso2.crm;
public class CRMCustomerService{
Customer[] customers;
public CRMCustomerService() {
super();
customers = new Customer[6];
customers[0] = new Customer("2001", "Geller", "Ross", "45, Broadway Avenue, NYC", "43124562", "1040");
customers[1] = new Customer("2002", "Bing", "Chandler", "55, Fifth Avenue, NYC", "91234829", "1050");
customers[2] = new Customer("2003", "Tribbiani", "Joey", "98, Park Avenue, NYC", "12304883", "1060");
customers[3] = new Customer("2004", "Green", "Rachel", "999, Park Avenue, NYC", "12304883", "1010");
customers[4] = new Customer("2005", "Buffet", "Phoebe", "123, Park Avenue, NYC", "9786756", "1020");
customers[5] = new Customer("2006", "Geller", "Monica", "98, Broadway Avenue, NYC", "14652638", "1030");
}
public Customer[] getAllCRMCustomers() {
return customers;
}
public Customer getCustomerByOrgID(String OrgID) {
for(int i=0; iif
(customers[i] != null && customers[i].getOrgID() != null && customers[i].getOrgID().equals(OrgID)){
return customers[i];
}
}
return null;
}
}
Customer.java
package org.wso2.crm;
public class Customer {
private String CRMCustID;
private String LName;
private String Fname;
private String Address;
private String Tel;
private String OrgID;
public Customer(String cRMCustID, String lName, String fname,
String address, String tel, String orgID) {
super();
CRMCustID = cRMCustID;
LName = lName;
Fname = fname;
Address = address;
Tel = tel;
OrgID = orgID;
}
public String getCRMCustID() {
return CRMCustID;
}
public void setCRMCustID(String cRMCustID) {
CRMCustID = cRMCustID;
}
public String getLName() {
return LName;
}
public void setLName(String lName) {
LName = lName;
}
public String getFname() {
return Fname;
}
public void setFname(String fname) {
Fname = fname;
}
public String getAddress() {
return Address;
}
public void setAddress(String address) {
Address = address;
}
public String getTel() {
return Tel;
}
public void setTel(String tel) {
Tel = tel;
}
public String getOrgID() {
return OrgID;
}
public void setOrgID(String orgID) {
OrgID = orgID;
}
To host the CRMCustomerService, download the service from here and host it in the WSO2 Application Server. When running the Application Server, run it with port offset 1. More information on setting port offset here.
Implementing the Entity Aggregation Solution
I have used the WSO2 ESB and the Data Services Server to implement the Entity Aggregation Solution.
Configuring WSO2 Data Services Server
First, the data from the ERP service needs to be exposed through a data service (SOAP service) hosted in the Data Services Server. Create a new data service named ERPCustomerDataService in the Data Services Server with the following configuration. The Data Services Server should run with port offset 2. Since we are using a MySQL database make sure to place the MySQL JDBC driver (
mysql-connector-java-5.XX-bin.jar)in HOME/repository/components/dropins folder of the Data Services Server.
<data name="ERPCustomerDataService">
<config id="mysql">
<property name="driverClassName">com.mysql.jdbc.Driver</property>
<property name="url">jdbc:mysql://localhost:3306/ERP_DB</property>
<property name="username">root</property>
<property name="password">root</property>
</config>
<query id="getAllCustomersQuery" useConfig="mysql">
<sql>SELECT * FROM Customer;</sql>
<result element="Customers" rowName="Customer">
<element column="ERPCustID" name="ERPCustID" xsdType="string"/>
<element column="LName" name="LName" xsdType="string"/>
<element column="FName" name="FName" xsdType="string"/>
<element column="OrgID" name="OrgID" xsdType="string"/>
</result>
</query>
<query id="getCustomerByOrgIDQuery" useConfig="mysql">
<sql>SELECT * FROM Customer WHERE OrgID LIKE ? ;</sql>
<param name="OrgID" ordinal="1" sqlType="STRING"/>
<result element="Customers" rowName="Customer">
<element column="ERPCustID" name="ERPCustID" xsdType="string"/>
<element column="LName" name="LName" xsdType="string"/>
<element column="FName" name="FName" xsdType="string"/>
<element column="OrgID" name="OrgID" xsdType="string"/>
</result>
</query>
<operation name="getAllCustomersOperation">
<call-query href="getAllCustomersQuery"/>
</operation>
<operation name="getCustomerByOrgIDOperation">
<call-query href="getCustomerByOrgIDQuery">
<with-param name="OrgID" query-param="OrgID"/>
</call-query>
</operation>
</data>
The above data service has two operations: getAllCustomers and getCustomerByOrgIDOperation. Sample responses from the above services are as shown below.
getAllCustomers:
<Customers xmlns="http://ws.wso2.org/dataservice">
<Customer>
<ERPCustID>001</ERPCustID>
<LName>Green</LName>
<FName>Rachel</FName>
<OrgID>1010</OrgID>
</Customer>
<Customer>
<ERPCustID>002</ERPCustID>
<LName>Buffet</LName>
<FName>Phoebe</FName>
<OrgID>1020</OrgID>
</Customer>
<Customer>
<ERPCustID>003</ERPCustID>
<LName>Geller</LName>
<FName>Monica</FName>
<OrgID>1030</OrgID>
</Customer>
<Customer>
<ERPCustID>004</ERPCustID>
<LName>Geller</LName>
<FName>Ross</FName>
<OrgID>1040</OrgID>
</Customer>
<Customer>
<ERPCustID>005</ERPCustID>
<LName>Bing</LName>
<FName>Chandler</FName>
<OrgID>1050</OrgID>
</Customer>
<Customer>
<ERPCustID>006</ERPCustID>
<LName>Tribbiani</LName>
<FName>Joey</FName>
<OrgID>1060</OrgID>
</Customer>
</Customers>
getCustomerByOrgIDOperation:
<Customers xmlns="http://ws.wso2.org/dataservice">
<Customer>
<ERPCustID>001</ERPCustID>
<LName>Green</LName>
<FName>Rachel</FName>
<OrgID>1010</OrgID>
</Customer>
</Customers>
Configuring WSO2 ESB
The ESB needs to be configured to expose a REST API which on invocation should call the respective data service and obtain the ERP customer data and then the web service hosted in the WSO2 Application server to retrieve the CRM customer data. Thereafter, it must aggregate the fields according to the new data model and send the aggregated response.
The ESB should be started with port offset 0. This is the default port offset so nothing needs to be changed in the configuration files.
Create a new API named CustomerAPI in the ESB with the following configuration:
<api xmlns="http://ws.apache.org/ns/synapse" name="CustomerAPI" context="/customer">
<resource methods="GET" uri-template="/{OrgID}">
<inSequence>
<payloadFactory media-type="xml">
<format>
<p:getCustomerByOrgIDOperation xmlns:p="http://ws.wso2.org/dataservice">
<a:OrgID xmlns:a="http://ws.wso2.org/dataservice">$1</a:OrgID>
</p:getCustomerByOrgIDOperation>
</format>
<args>
<arg evaluator="xml" expression="get-property('uri.var.OrgID')"></arg>
</args>
</payloadFactory>
<log level="full"></log>
<call>
<endpoint>
<address uri="http://localhost:9765/services/ERPCustomerDataService/getCustomerByOrgIDOperation" format="soap12"></address>
</endpoint>
</call>
<property xmlns:ns1="http://ws.wso2.org/dataservice" name="ERPCustID" expression="//ns1:ERPCustID"></property>
<payloadFactory media-type="xml">
<format>
<p:getCustomerByOrgID xmlns:p="http://crm.wso2.org">
<b:OrgID xmlns:b="http://crm.wso2.org">$1</b:OrgID>
</p:getCustomerByOrgID>
</format>
<args>
<arg evaluator="xml" expression="get-property('uri.var.OrgID')"></arg>
</args>
</payloadFactory>
<call>
<endpoint>
<address uri="http://localhost:9764/services/CRMCustomerService/getCustomerByOrgID" format="soap12"></address>
</endpoint>
</call>
<property xmlns:ns2="http://crm.wso2.org/xsd" xmlns:ns1="http://crm.wso2.org" name="LName" expression="//ns1:return/ns2:LName"></property>
<property xmlns:ns2="http://crm.wso2.org/xsd" xmlns:ns1="http://crm.wso2.org" name="address" expression="//ns1:return/ns2:address"></property>
<property xmlns:ns2="http://crm.wso2.org/xsd" xmlns:ns1="http://crm.wso2.org" name="fname" expression="//ns1:return/ns2:fname"></property>
<property xmlns:ns2="http://crm.wso2.org/xsd" xmlns:ns1="http://crm.wso2.org" name="orgID" expression="//ns1:return/ns2:orgID"></property>
<property xmlns:ns2="http://crm.wso2.org/xsd" xmlns:ns1="http://crm.wso2.org" name="tel" expression="//ns1:return/ns2:tel"></property>
<log>
<property name="TEL" expression="$ctx:tel"></property>
</log>
<payloadFactory media-type="xml">
<format>
<result>
<erp_cust_id>$1</erp_cust_id>
<last_name>$2</last_name>
<first_name>$3</first_name>
<address>$4</address>
<organization>$5</organization>
<tel>$6</tel>
</result>
</format>
<args>
<arg evaluator="xml" expression="$ctx:ERPCustID"></arg>
<arg evaluator="xml" expression="$ctx:LName"></arg>
<arg evaluator="xml" expression="$ctx:fname"></arg>
<arg evaluator="xml" expression="$ctx:address"></arg>
<arg evaluator="xml" expression="$ctx:orgID"></arg>
<arg evaluator="xml" expression="$ctx:tel"></arg>
</args>
</payloadFactory>
<property name="messageType" value="application/xml" scope="axis2"></property>
<respond></respond>
</inSequence>
</resource>
</api>
The API does the following:
1) The API is defined by a context - /customer and the URI template /{OrgID} is used to invoke the single GET resource that is defined in the API. So how it works is, you need to invoke the API with a URL like this: http://10.100.1.79:8280/customer/1010
2) An in sequence is defined. In the in sequence the transformation mediator Payloadfactory is used to construct a message (convert the payload to one that matches with the request format of the data service) to the data service with the given OrgID (1010 in this case). getCustomerByOrgIDOperation will be invoked from the data service with the given OrgID.
3) Next the message is logged.
4) Now the message needs to be sent to the data service. This is done with a Call mediator. The Call mediator invokes the service in a synchronous manner and mediation will be paused from that point. When the response is received, the mediation flow resumes from the next mediator in the sequence.
5) Once the response is received from the data service for the customer with OrgID = 1010, a Property mediator is used to store the ERPCustID from the response since this is the only field required from the ERP system and the rest should be taken from the CRM service.
6) Another Payloadfactory mediator is added to create the payload for the CRM service in the Application Server. The OrgID is passed to invoke the getCustomerByOrgID operation in the CRM service.
7) A Call mediator is included at this step to send the request to the getCustomerByOrgID operation in the CRM service.
8) Next there are five Property mediators to extract LName, FName,Address, Telephone and OrgID from the response.
9) A property is logged to check if a value has been assigned to the property. This was mainly used for debugging purposes.
10) Next the payload needs to be constructed that provides a holistic view of the customer and be sent back to the consumer. The payload is constructed with the Payloadfactory mediator and that payload is sent to the consumer using the Respond mediator, which is used to stop further processing of a message and send the message back to the client.
Testing the API
Do a search for a customer on your browser– e.g.
http://10.100.1.79:8280/customer/1010 and you will receive the following response from the API.
<result xmlns="http://ws.apache.org/ns/synapse">
<erp_cust_id>001</erp_cust_id>
<last_name>Green</last_name>
<first_name>Rachel</first_name>
<address>999, Park Avenue, NYC</address>
<organization>1010</organization>
<tel>12304883</tel>
</result>
