Retrieve multiple rows from a database in WSO2 ESB using WSO2 DSS.

When you want to retrieve multiple rows from a database using WSO2 ESB, you will not be able to use the dblookup mediator as it only supports retrieving a single row. In a scenario like this we can use WSO2 DSS.

When you want to run two servers at the same time you have to set the offset in one server. Otherwise both servers will try to be up in the same port. You can do it by editing the carbon.xml file of the ESB or DSS. Go to [product-home]/repository/conf/carbon.xml. Then set the offset to ‘2’. (You can use any number you want). If you set the offset to 2,Server will be up in the port 9445.

Steps

Create a database in mysql server. And define a table including data you want. Here I have created a database named ‘company’ and a table named ‘employee’.

Configure DSS

Start the WSO2 DSS. Then go to the url of the serve and log into the server using username password. (username:admin password:admin).

Click on ‘Create’ under ‘Data Service’. You will be redirected to a page like the image shown below. You can simply give a Data Service Name and proceed. Here I have given the name as ‘Employee’.

When you click ‘Next’ you will see a screen like this. Here we have to add a new data source. Click on ‘Add New Datasource’. Give a datasource id and select the ‘Datasource Type’ as ‘RDBMS’ and ‘Database Engine’ as ‘MySQL’. Give the ‘Driver Class’ as ‘com.mysql.jdbc.Driver’. Give the ‘URL’ as ‘jdbc:mysql://localhost:3306/[database-name]’.You can give the name of the database you created. Then give the username and password of your database.

Save and click ‘Next’.

Click on ‘Add New Query’. Give a query id and select the Datasource you created. Write the sql query including the columns you want to retrieve from database. And click on ‘Generate Response’. It will automatically generate the Result(Output Mapping).

Then save the query and click ‘Next’.

Click on ‘Add New Operation’. Give an operation name and select the query Id of the query.

Save and click ‘Next’ and ‘Finish’. Now you have created the Data Service in DSS. When you refresh the page you will see the service you created.

Click on ‘Try this Service’ and it will redirect you to another page. You can click on ‘Send’ button to see the results you get after retrieving values from database.

If you know how to write the XML configuration you can directly edit the XML without doing the above mentioned steps using the UI. Here is the final XML you get after creating the data service.

<?xml version="1.0" encoding="UTF-8"?>
<data name="Employee" transports="http https local">
   <config enableOData="false" id="EmployeeDetails">
      <property name="driverClassName">com.mysql.jdbc.Driver</property>
      <property name="url">jdbc:mysql://localhost:3306/company</property>
      <property name="username">root</property>
   </config>
   <query id="getEmployees" useConfig="EmployeeDetails">
      <sql>SELECT name,emp_id,department from employee</sql>
      <result element="Entries" rowName="Entry">
         <element column="name" name="name" xsdType="string" />
         <element column="emp_id" name="emp_id" xsdType="string" />
         <element column="department" name="department" xsdType="string" />
      </result>
   </query>
   <operation name="retrieveEmployeeDetails">
      <call-query href="getEmployees" />
   </operation>
</data>

Now we are done with the configuration of DSS.

Configure ESB

Now we will move on to configure ESB. For you to fetch these data to ESB you have to  define an endpoint in ESB. Here I will define a HTTP endpoint. Go back again to DSS. Then click on the data service you created. Then copy the highlighted endpoint as shown in the image. (You have to copy the http endpoint. Not the https one).

Go to ESB to create the HTTP endpoint. Go to ‘Endpoints’ , ‘Add Endpoint’ and select ‘HTTP Endpoint’. Give a name. To define the URI template, paste the endpoint you copied from the DSS data service. And then add the operation name at the end of the URL like this.

http://192.168.1.4:9763/services/Employee/retrieveEmployeeDetails

Create a proxy service in ESB to retrieve data. You can create a proxy and write the XML configuration like this.

<?xml version="1.0" encoding="UTF-8"?>
<proxy xmlns="http://ws.apache.org/ns/synapse" name="retrieve" transports="https,http" statistics="disable" trace="disable" startOnLoad="true">
   <target>
      <inSequence>
         <payloadFactory media-type="xml">
            <format>
               <dat:retrieveEmployeeDetails xmlns:dat="http://ws.wso2.org/dataservice" />
            </format>
            <args />
         </payloadFactory>
         <property name="Content-Type" value="application/xml" scope="transport" />
         <call>
            <endpoint key="retrieveEmpDetails" />
         </call>
         <log level="full" />
      </inSequence>
   </target>
   <description />
</proxy>

In this proxy service we define a payload factory mediator before calling the endpoint to give the reference to the namespace of DSS. Inside that payload we can define the namespace of the operation(retrieveEmployeeDetails) of the data service we created like this.

Since we get a XML payload from the data service we invoke in DSS we can set the Content-Type property value to application/xml and the scope to transport.This is optional.

Then we can call the endpoint we created in ESB like this, using the call mediator.

<call>

          <endpoint key="retrieveEmpDetails"/>

</call>

When you run this proxy service you can see the XML payload with the retrieved values from database like this.