How to Expose WSO2 EI Data Services as REST

In general cases, data services created using WSO2 DSS or EI are exposed as a SOAP service to the user. But actually they can be exposed as REST services too with some slight changes. This blog post will guide you through the entire process starting from the creation of a database to exposure of it as a data service in the REST form.

So where do we start?

Before starting we have to fulfill the following prerequisites.

• Wso2 EI.
  Here I have used EI v6.5. But feel free to use a different EI version as you prefer. You can download and install the product from this link.
• MySQL Server.
  We will be using MySQL in this example. You can use any other RDBMS database but make sure to use the corresponding JDBC connector which is mentioned in the following steps.

Now we can start.

Step 1: Download and extract the MySQL JDBC connector from this link and copy it to your <EI_HOME>/lib folder.

Here you will be downloading a .deb folder which you have to extract both parent folder and then the data folder inside it. You will find the MySQL JDBC connector inside the <parent_directory>/data/usr/share/java folder.

Step2: Login to the MySQL Server, create a database, create a table to hold the data, insert some records

• Create the database ‘AccountDetails’

CREATE DATABASE AccountDetails;

• Create ‘Account’ table

USE AccountDetails;
CREATE TABLE Account (AccountID int NOT NULL,Branch varchar(255) NOT NULL,AccountNumber varchar(255),AccountType ENUM ('CURRENT', 'SAVINGS') NOT NULL,Balance FLOAT,ModifiedDate DATE,PRIMARY KEY (AccountID));

• Add some sample data

INSERT INTO Account VALUES (1,"AOB","A00012","CURRENT",231221,'2014-12-02');

Step 3: As the next step, let’s create a data service

First, start your EI server and log into the management console https://localhost:9443/cabon by using ‘admin’ as both username and password. Here I assume that you have some basic knowledge of Wso2 products at least of EI.

Then create a new data service from:
Main-> Services-> Add-> Data Service-> Create

Here I gave ‘AccountDetailsService’ as the data service name.

Step4: Add a new data source

Click on the ‘Add New Datasource’ from the next page. It will take you to a form where you have to give ‘Datasource Id’ and ‘Datasource Type’. Give a name for the Datasource Id and select ‘RDBMS’ for the type.

Here are some sample values that you can use at this stage.

Datasource Id: AccountDetails
Datasource Type: RDBMS
Database Engine: MySQL
Driver Class: com.mysql.jdbc.Driver
URL: jdbc:mysql://localhost:3306/AccountDetails
Username: root
Password: root

Save the values and go to the next step.

Step5: Add new Queries

At this step, it facilitates you to add database queries that you want to execute through this data service.

Give a value for the ‘QueryId’ and then select above-created Datasource ‘AccountDetails’ from the dropdown list. Then let’s add a query to retrieve account details.

Query Id: getAccountDetails
Datasource: AccountDetails
SQL: SELECT AccountID, Branch, AccountNumber, AccountType, Balance, ModifiedDate FROM Account WHERE AccountID=:AccountID

• Add input mappings

Then let’s add input mappings for the AccountID field in the database. You can automatically generate this by clicking on ‘Generate Input Mapping’.

• Add output mappings

Set the following values under the ‘Results’ section

Grouped by element: Entries
Row name: Entry

Click on ‘Generate Response’ to automatically generate mappings for the fields that should come in the output.

Save and go to the next step.

Step 6: Add new Operations

Actually this step is really not necessary for our final goal which is exposing the data service as a REST. Adding operations facilitates you to call the data service as a SOAP service.

Anyway, it won’t hurt if we add a one.

Here you have to give the Operation Name and select the previously created Query ID from the dropdown list.

Operation Name: Operation1
Query ID: getAccountDetails

Step 7: Add new Resources

Finally!!

This is the step that we are going to define resources that make it allow us to call the data service as a REST service.

Here you have to give a value for ‘Resource Path’, and then select ‘Resource Method’ (GET, POST, PUT or DELETE), and ‘Query ID’ from the dropdown lists.

For this example let’s expose the previously defined SELECT query as a GET call.

Resource Path: Account
Resource Method: GET
Query ID: getAccountDetails

Please be noted that this will set the allow us to give the ‘AccountID’ as a query parameter. If you want to set it as a path parameter, you have to give ‘Account/{AccountID}’ as the Resource Path value.

Resource Path value for:
- Query parameter: Account
- Path parameter: Account/{AccountID}

That’s it.

Now click on the Finish button to end the process. After a couple of seconds, you will see the created data service and under the Services-> List.

If you go inside the service you will see the following endpoint URLs for the created data service.

• http://localhost:8280/services/AccountDetailsService
• https://localhost:8243/services/AccountDetailsService
• local:///services/AccountDetailsService/

Step 8: Service invocation

Now let’s see how to invoke the service.

• Invoke as a SOAP service

First, let’s invoke it as a SOAP service as we do usually

You can use a Soap client such as SoapUI and call the endpoint HTTP endpoint http://localhost:8280/services/AccountDetailsService using the following Soap payload.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:dat="http://ws.wso2.org/dataservice">
   <soapenv:Header/>
   <soapenv:Body>
      <dat:Operation1>
         <dat:AccountID>1</dat:AccountID>
      </dat:Operation1>
   </soapenv:Body>
</soapenv:Envelope>

If successful, you will get the following response to the client.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <Entries xmlns="http://ws.wso2.org/dataservice">
         <Entry>
            <AccountID>1</AccountID>
            <Branch>AOB</Branch>
            <AccountNumber>A00012</AccountNumber>
            <AccountType>CURRENT</AccountType>
            <Balance>231221.0</Balance>
            <ModifiedDate>2014-12-02+05:30</ModifiedDate>
         </Entry>
      </Entries>
   </soapenv:Body>
</soapenv:Envelope>

• Invoke as a REST service

You can use the following curl command for the REST invocation of the created resource.

curl -X GET http://localhost:8280/services/AccountDetailsService/Account?AccountID=1

This will get the below result if all went successfully.

<Entries xmlns="http://ws.wso2.org/dataservice">
    <Entry>
        <AccountID>1</AccountID>
        <Branch>AOB</Branch>
        <AccountNumber>A00012</AccountNumber>  
        <AccountType>CURRENT</AccountType>
        <Balance>231221.0</Balance>
        <ModifiedDate>2014-12-02+05:30</ModifiedDate>
    </Entry>
</Entries>

If you have defined resources as path parameters, here is the curl command that you should use.

curl -X GET http://localhost:8280/services/AccountDetailsService/Account/1

You can define multiple resources with matching HTTP methods in order to expose various database operations. It’s up to you.

Here is the final synapse configuration for the created data service.

<data name="AccountDetailsService" transports="http https local">
   <config enableOData="false" id="AccountDetails">
      <property name="driverClassName">com.mysql.jdbc.Driver</property>
      <property name="url">jdbc:mysql://localhost:3306/AccountDetails</property>
      <property name="username">root</property>
      <property name="password">root</property>
   </config>
   <query id="getAccountDetails" useConfig="AccountDetails">
      <sql>SELECT AccountID,Branch,AccountNumber,AccountType,Balance,ModifiedDate FROM Account WHERE AccountID=:AccountID</sql>
      <result element="Entries" rowName="Entry">
         <element column="AccountID" name="AccountID" xsdType="string"/>
         <element column="Branch" name="Branch" xsdType="string"/>
         <element column="AccountNumber" name="AccountNumber" xsdType="string"/>
         <element column="AccountType" name="AccountType" xsdType="string"/>
         <element column="Balance" name="Balance" xsdType="string"/>
         <element column="ModifiedDate" name="ModifiedDate" xsdType="string"/>
      </result>
      <param name="AccountID" sqlType="STRING"/>
   </query>
   <operation name="Operation1">
      <call-query href="getAccountDetails">
         <with-param name="AccountID" query-param="AccountID"/>
      </call-query>
   </operation>
   <resource method="GET" path="Account">
      <call-query href="getAccountDetails">
         <with-param name="AccountID" query-param="AccountID"/>
      </call-query>
   </resource>
</data>

Yeah, I know. It looks messy. But it contains all you need.

OK. That’s the end of the post. I hope you had a great time reading this and had gathered the needed knowledge in this context. If you have any doubts regardings any of the steps in this process, please feel free to leave a comment.

Cheers.