This documentation is work in progress and will be released with the next WSO2 EI version.
Exposing a Datasource as a Data Service - WSO2 Enterprise Integrator 6.x.x - WSO2 Documentation

All docs This doc
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 30 Current »

In this tutorial, we will run through the process of service enabling an RDBMS as a data service.  For more information on the datasource types supported by WSO2 EI, see the What's Next section.

Before you begin!

Follow the steps given below to set up a MySQL database for this tutorial.

  1. Install the MySQL server.
  2. Download the product installer from here, and run the installer.
    Let's call the installation location of your product the <EI_HOME> directory. This is located in a place specific to your OS as shown below:

    OSHome directory
    Mac OS/Library/WSO2/EnterpriseIntegrator/6.5.0
    WindowsC:\Program Files\WSO2\EnterpriseIntegrator\6.5.0\
    Ubuntu/usr/lib/wso2/EnterpriseIntegrator/6.5.0
    CentOS/usr/lib64/EnterpriseIntegrator/6.5.0

  3. Download the JDBC driver for MySQL from here. Unzip it, get the <MySQL_HOME>/mysql-connector-java-8.0.16.jar JAR, and place it in the <EI_HOME>/lib directory.

    If the driver class does not exist in the relevant folders when you create the datasource, you will get an exception, such as 'Cannot load JDBC driver class com.mysql.jdbc.Driver'.

  4. Create a database named Employees.

    CREATE DATABASE Employees;
  5. Create the Employee table inside the Employees database:

    USE Employees;
    
    CREATE TABLE Employees (EmployeeNumber int(11) NOT NULL, FirstName varchar(255) NOT NULL, LastName varchar(255) DEFAULT NULL, Email varchar(255) DEFAULT NULL, Salary varchar(255));

Let's get started

Creating the data service

Follow the steps given below.

  1. Start the WSO2 ESB profile.

    Open a terminal and execute the following command:

    wso2ei-6.4.0-integrator
    Go to  Start Menu -> Programs -> WSO2 -> Enterprise Integrator 6.4.0 Integrator. This will open a terminal and start the ESB profile.
  2. Open the ESB profile's Management Console using https://localhost:9443/carbon, and log in using admin as the username and the password.
  3. Click Data Service → Create, to start creating a data service.
  4. Enter the following name for the data service.

    Data Service NameRDBMSDataService
  5. Click Next to enter the datasource connection details.

Connecting to the datasource

Follow the steps given below.

  1. Click Add New Datasource and enter the following details:

    Datasource IDDatasource
    Datasource TypeRDBMS
    Datasource Type (Default/External)Leave Default selected.
    Database EngineMySQL
    Driver Classcom.mysql.jdbc.Driver
    URL jdbc:mysql://localhost:3306/Employees
    User NameEnter your MySQL server's username.
    PasswordEnter your MySQL server's password.
    If you have not assigned a password, keep this field empty.

    If you enter External instead of the Default datasource type, your datasource should be supported by an external provider class, such as  com.mysql.jdbc.jdbc2.optional.MysqlXADataSource. You can select the External option and enter the name and value of connection properties by clicking  Add Property. For example,

    After an external datasource is created, it can be used as a usual datasource in queries. See the tutorial on handling distributed transactions for more information on using external datasources.

  2. Save the datasource.
  3. Click Next, to start creating queries.

Creating a query to Get data

Let's create a query that can retrieve employee data, based on the employee number. That is, when the employee number is provided as an input, the data service should get the relevant employee details and present the result.

  1. Click Add New Query to start creating a new query.

  2. Enter the following details:

    Query IDGetEmployeeDetails
    DatasourceDatasource
    SQL
    select EmployeeNumber, FirstName, LastName, Email, Salary from Employees where EmployeeNumber=:EmployeeNumber
  3. Click Generate Input Mapping to create the input mapping. The employee number is the input as shown below.

  4. Click Generate Response to create the output mapping. This defines how the employee details retrieved from the datasource will be presented in the result. Note that, by default, the output type is XML. 

    If required, you can choose RDF, or JSON as the output type. See Using JSON with Data Services for more information on exposing data in JSON format.

  5. Save the query.

Creating a query to Post data

Let's create another query to post new employee data to the datasource.

  1. Click Add New Query to start creating a new query.

  2. Enter the following details:

    Query IDAddEmployeeDetails
    DatasourceDatasource
    SQL
    insert into Employees (EmployeeNumber, FirstName, LastName, Email, Salary) values(:EmployeeNumber,:FirstName,:LastName,:Email,:Salary)
  3. Click Generate Input Mapping to create the input mapping. You need to specify values for the following elements when posting new employee data.

  4. Save the query.

Creating a query to Update data

Now, let's create a query that can update an existing employee record in the datasource.

  1. Click Add New Query to start creating a new query.

  2. Enter the following details:

    Query IDUpdateEmployeeDetails
    DatasourceDatasource
    SQL
    update Employees set LastName=:LastName, FirstName=:FirstName, Email=:Email, Salary=:Salary where EmployeeNumber=:EmployeeNumber
  3. Click Generate Input Mapping to create the input mapping. You need to specify values for the following elements when posting new employee data.

  4. Save the query.

You should now have the following three queries created:

Create SOAP operations to invoke queries

Now, let's create SOAP operations to invoke the queries created above. Alternatively, you can create REST resources to invoke the queries. See the next section for instructions.

  1. Click Add New Operation and enter the details as shown below.

    Operation NameGetEmployeeOp
    Query IDGetEmployeeDetails
  2. Save the operation.
  3. Click Add New Operation and enter the details as shown below.

    Operation NameAddEmployeeOp
    Query IDAddEmployeeDetails
  4. Save the operation.
  5. Click Add New Operation and enter the details as shown below.

    Operation NameUpdateEmployeeOp
    Query IDUpdateEmployeeDetails
  6. Save the operation.

You can now invoke the data service query using SOAP.

Create REST resources to invoke queries

Now, let's create REST resources to invoke the queries created above. Alternatively, you can create SOAP operations to invoke the queries. See the previous section, for instructions.

  1. Click Add New Resource and enter the details as shown below.

    Resource PathEmployee/{EmployeeNumber}
    Resource MethodGET
    Query IDGetEmployeeDetails
  2. Save the resource.
  3. Click Add New Resource and enter the details as shown below.

    Resource PathEmployee
    Resource MethodPOST
    Query IDAddEmployeeDetails
  4. Save the resource.
  5. Click Add New Resource and enter the details as shown below.

    Resource PathEmployee
    Resource MethodPUT
    Query IDUpdateEmployeeDetails

    Save the resource.

  6. Click Finish to complete creating the data service.

Once you have defined the operation, click Finish to complete the data service creation process. You will now be taken to the Deployed Services screen, which shows all the data services deployed on the server.
You can now invoke the data service query using REST.


Invoking your data service using SOAP

You can try the data service you created by using the TryIt tool that is in your product by default.  

  1. Go to the Deployed Services screen and refresh the page.
  2. Click the Try this service link for the RDBMS data service. The TryIt Tool will open with the data service.

Post new data

  1. Select the AddEmployeeOp operation you created earlier. 
  2. Provide the employee details by copying the code given below.

    <p:AddEmployeeOp xmlns:p="http://ws.wso2.org/dataservice">
          <!--Exactly 1 occurrence-->
          <xs:EmployeeNumber xmlns:xs="http://ws.wso2.org/dataservice">1</xs:EmployeeNumber>
          <!--Exactly 1 occurrence-->
          <xs:FirstName xmlns:xs="http://ws.wso2.org/dataservice">John</xs:FirstName>
          <!--Exactly 1 occurrence-->
          <xs:LastName xmlns:xs="http://ws.wso2.org/dataservice">Doe</xs:LastName>
          <!--Exactly 1 occurrence-->
          <xs:Email xmlns:xs="http://ws.wso2.org/dataservice">JohnDoe@gmail.com</xs:Email>
          <!--Exactly 1 occurrence-->
          <xs:Salary xmlns:xs="http://ws.wso2.org/dataservice">10000</xs:Salary>
       </p:AddEmployeeOp>
  3. Click Send.

The data is now added to the database.

Get data

  1. Select the GetEmployeeOp operation you created earlier. You need to provide the employee number as an input. Enter '1'.
  2. Click Send to see the details of the employee you added previously:

    <Entries xmlns="http://ws.wso2.org/dataservice">
       <Entry>
          <EmployeeNumber>1</EmployeeNumber>
          <FirstName>John</FirstName>
          <LastName>Doe</LastName>
    	  <Email>JohnDoe@gmail.com</Email>
       </Entry>
    </Entries>

Update data

  1. Select the UpdateEmployeeOp operation you created earlier. 
  2. Update the employee details by copying the code given below. Note that the salary value is changed to 20000.

    <p:UpdateEmployeeOp xmlns:p="http://ws.wso2.org/dataservice">
          <!--Exactly 1 occurrence-->
          <xs:LastName xmlns:xs="http://ws.wso2.org/dataservice">Doe</xs:LastName>
          <!--Exactly 1 occurrence-->
          <xs:FirstName xmlns:xs="http://ws.wso2.org/dataservice">John</xs:FirstName>
          <!--Exactly 1 occurrence-->
          <xs:Email xmlns:xs="http://ws.wso2.org/dataservice">JohnDoe@gmail.com</xs:Email>
          <!--Exactly 1 occurrence-->
          <xs:Salary xmlns:xs="http://ws.wso2.org/dataservice">20000</xs:Salary>
          <!--Exactly 1 occurrence-->
          <xs:EmployeeNumber xmlns:xs="http://ws.wso2.org/dataservice">1</xs:EmployeeNumber>
    </p:UpdateEmployeeOp>
  3. Click Send.

The data is now updated in the database.

Invoking your data service using REST

Let's take a look at the curl commands that are used to send the HTTP requests for each of the resources:

Post new data

  1. Create a file called employee-payload.xml file, and define the XML payload for posting new data as shown below.

    <_postemployee>
        <EmployeeNumber>3</EmployeeNumber>
        <FirstName>Will</FirstName>
        <LastName>Smith</LastName>
        <Email>will@google.com</Email>
        <Salary>15500.0</Salary>
    </_postemployee>
  2. Send the following HTTP request from the location where the employee-payload.xml file is stored:

    curl -X POST -H 'Accept: application/xml'  -H 'Content-Type: application/xml' --data "@employee-payload.xml" http://localhost:8280/services/RDBMSDataService/employee

Get data

The service can be invoked in REST-style via curl (http://curl.haxx.se). Shown below is the curl command to invoke the GET resource:

curl -X GET http://localhost:8280/services/RDBMSDataService.HTTPEndpoint/Employee/3

This generates a response as follows.

<Entries xmlns="http://ws.wso2.org/dataservice"><Entry><EmployeeNumber>3</EmployeeNumber><FirstName>Will</FirstName><LastName>Smith</LastName><Email>will@google.com</Email><Salary>15500.0</Salary></Entry><Entry><EmployeeNumber>3</EmployeeNumber><FirstName>Will</FirstName><LastName>Smith</LastName><Email>will@google.com</Email><Salary>15500.0</Salary></Entry><Entry><EmployeeNumber>3</EmployeeNumber><FirstName>Will</FirstName><LastName>Smith</LastName><Email>will@google.com</Email><Salary>15500.0</Salary></Entry></Entries>

Update data

  1. Create a file called employee-update-payload.xml file, and define the XML payload for updating an existing employee record as shown below.

    <_putemployee>
        <EmployeeNumber>3</EmployeeNumber>
        <LastName>Smith</LastName>
        <FirstName>Will</FirstName>
        <Email>will@google.com</Email>
        <Salary>30000.0</Salary>
    </_putemployee>
  2. Send the following HTTP request from the location where the employee-update-payload.xml file is stored:

    curl -X PUT -H 'Accept: application/xml'  -H 'Content-Type: application/xml' --data "@employee-update-payload.xml" http://localhost:8280/services/RDBMSDataService/employee

What's Next

  • No labels