Using JSON with Data Services

The possibility of sending and receiving messages in JSON is enabled in the ESB of WSO2 Enterprise Integrator (WSO2 EI) by default. See the topics given below to understand how data can be exposed in JSON format, and how the data can be changed by sending JSON payloads.

Prerequisites

In this tutorial we will use a data service that exposes RDBMS data.

Upload the (already created) data service

You can download the RDBMSDataService from here. If required, you can create the same data service from scratch following the tutorial on exposing an RDBMS as a data service.

Create the RDBMS data store

If you haven't already completed the tutorial on exposing an RDBMS as a data service, follow the steps given below to set up the MySQL database for this tutorial.

1. Install the MySQL server.

2. Download the JDBC driver for MySQL from here and copy it to your <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'.
3. Create the following database: Employees

4. Create the Employee table inside the Employees database:
   

   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));

Expose data in JSON format

A data service can expose data in one of the following formats: XML, RDF, or JSON. You can select the required format by specifying the output type for the data service query. To expose data in JSON, you need to select JSON as the output type, and map the output to a JSON template. 

Map the output type to JSON

1. Log in to the product's management console and upload the data service.
   1. Click Add → Data Service → Upload.
   2. Browse and add the RDBMSDataService.dbs file.
2. Click List under Main → Services. The RDBMS data service should be listed.
3. Click the data service to open the Service Dashboard.
4. Click Edit Data Service (Wizard) to open the data service using the Create Data Service wizard.
5. Click Next until you get to the Queries screen.
6. Edit the GetEmployeeDetails query.
7. Change the Output Type to JSON. 

8. You can now start defining the JSON template for the output. Listed below are a few sample templates that you can use for this query.

   • Shown below is a basic mapping.

     Sample JSON Mapping

     { "Employees":
           {"Employee":[
             {"EmployeeNumber":"$EmployeeNumber",                        
              "Details": {
               "FirstName":"$FirstName",
               "LastName":"$LastName",
               "Email":"$Email",
               "Salary":"$Salary"
              }
             }                  
           ]
         }            
     } 

     Note the following:

     As shown in the sample given above, the column name values that are expected in the query result should be referred to by the column name with the "$" prefix. E.g. "$EmployeeNumber".

     Also, the structure of the JSON template should follow some guidelines in order to be compatible with the result. These guidelines are:

     • The top most item should be a JSON object. It cannot be a JSON array.
     • For handling multiple records from the result set, the immediate child of the top most object can be a JSON array, and the array should contain only a single object.
     • If only a single result is returned, the immediate child of the top most object can be a single JSON object.
     • After the immediate child of the top most object, there cannot be other JSON arrays in the mapping.

     All JSON responses will be returned as an array.

   • In a basic JSON output mapping, we specify the field values that we expect in the query result. You can give additional properties to this field mapping such as data type of the field, the possible content filtering user roles etc. These extended properties for the fields are given in parentheses, with a list of string tokens providing the additional properties, separated by a semicolon (";"). See the sample below.

     Sample JSON Mapping

     { "Employees":
           {"Employee":[
             {"EmployeeNumber":"$EmployeeNumber(type:integer)",                        
              "Details": {
               "FirstName":"$FirstName",
               "LastName":"$LastName",
               "Email":"$Email",
               "Salary":"$Salary(requiredRoles:hr,admin)"
              }
             }                  
           ]
         }            
     }

     Setting data types

     • The 'data type' property is added to the 'EmployeeNumber' field using parentheses "()".
     • The extended property 'type' is given along with the value 'integer'. 
     • The property and the value are separated by a colon. 
     • Note that the possible values for data type are "integer", "long", "double", and "boolean".

     Content filtering (Required Roles)

     • The 'data type' extended property, as well as the 'required roles' extended property, is added to the 'Salary' field using parentheses "()".
     • The 'requiredRoles' property is added along with the values 'hr' and 'admin'. 
     • Note that the two values for the 'requiredRoles' property are separated by a comma. 
     • Also, the two extended properties are separated by a semicolon. 

9. Save the query.

Get data in JSON

The RDBMSDataService that you are using will already contain the following resource defined:

You can now RESTfully invoke the above resource. To send a JSON message to a RESTful resource, you can simply add the “Accept:Application/json” to the request header when you send the request. 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 -H "Accept: application/json" http://localhost:8280/services/RDBMSDataService.HTTPEndpoint/Employee/1

You will receive the response in JSON format.

Update the datasource using JSON

When a client sends a request to change data (POST/PUT/DELETE) in the datasource, the HTTP header "Accept" should be set to "application/json".  Also, if the data is sent as a JSON payload, the HTTP header "Content-Type" should be set to "application/json".

The RDBMSDataService that you are using will already contain the following resources for adding and updating data.

• Resource for adding employee information:

  Resource Path	Employee
  Resource Method	POST
  Query ID	AddEmployeeDetails

• Resource for updating employee information:

  Resource Path	Employee
  Resource Method	PUT
  Query ID	UpdateEmployeeDetails

You can now RESTfully invoke the above resource by sending HTTP requests as explained below.

Post data

To post new employee information, you need to invoke the resource with the POST method.

1. First, create a file called employee-payload.json file, and define the JSON payload for posting new data as shown below.

   The following payload will work for this use case. When you create payloads for different use case, be mindful of the tips given here.

   {
     "_postemployee": {
       "EmployeeNumber" : "14001",
       "LastName": "Smith",
       "FirstName": "Will",
       "Email": "will@google.com",
       "Salary": "15500.0"
     }
   }

2. Send the following HTTP request from the location where the employee-payload.json file is stored:

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

Post data in batches

Batch requesting feature is enabled for the RDBMSDataService that you are using. This allows you to post JSON data in batches.

1. First, create a file called employee-batch-payload.json file, and define the JSON payload for posting multiple employee records (batch) as shown below.

   The following payload will work for this use case. When you create payloads for different use case, be mindful of the tips given here.

   {
       "_postemployee_batch_req": {
           "_postemployee": [
               {
                   "EmployeeNumber": "5012",
                   "FirstName": "Will",
   				"LastName": "Smith",
                   "Email": "will@smith.com",
                   "Salary": "13500.0"
               },
               {
                   "EmployeeNumber": "5013",
   				"FirstName": "Parker",
                   "LastName": "Peter",
                   "Email": "peter@parker.com",
                   "Salary": "15500.0"
               }
           ]
       }
   }

2. Send the following HTTP request from the location where the employee-batch-payload.json file is stored:

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

Update data

To update the existing employee records, you need to invoke the resource with the PUT method.

1. First, create a file called employee-payload-update.json file, and define the JSON payload for updating an existing employee record as shown below. For example, change the salary amount. Be sure that the employee number already exists in the database.

   The following payload will work for this use case. When you create payloads for different use case, be mindful of the tips given here.

   {
     "_putemployee": {
       "EmployeeNumber" : "1",
   	"FirstName": "Will",
       "LastName": "Smith",
       "Email": "will@smith.com",
       "Salary": "78500.0"
     }
   }

2. Send the following HTTP request from the location where the employee-upload-update.json file is stored:

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