Sending a Simple Message to a Datasource

Let’s try a simple scenario where a patient makes an inquiry specifying the doctor's specialization (category) to retrieve a list of doctors that match the specialization. The required information is stored in an H2 database that is shipped with this product. We will create a data service in the ESB profile of WSO2 Enterprise Integrator (WSO2 EI), which will expose the information in the database, thereby decoupling the client and the database layer in the back end. The client will then communicate with the data service hosted in WSO2 EI to get the required information instead of communicating directly with the back end.

Let's get started!

This tutorial includes the following sections:

Download and set up WSO2 EI

Let's set up a back-end database for the healthcare service. We will create a database named DATA_SERV_QSG in the <EI_HOME>/samples/data-services/database directory for this purpose.

1. Download the dataServiceSample.zip file and extract it to a location on your computer. Let's call this location <Dataservice_Home>. This contains a DB script for updating the back-end database with the channelling information of the healthcare service.

2. Open a terminal, navigate to the <Dataservice_Home> directory and execute the following command:

   When executing the below command, replace the <PATH_TO_EI_HOME> with the folder path of your WSO2 EI distribution. For example, if your WSO2 EI distribution (i.e., <EI_HOME>) is located in the /Users/Documents/ directory, execute the following command: ant -Ddshome=/Users/Documents/wso2ei-6.x.x

   Also, you need to install Apache Ant to execute this command.

   ant -Ddshome=<PATH_TO_EI_HOME>

The database is now updated with information on all available doctors in the healthcare service.

Exposing a datasource through a data service

Now, start the ESB runtime of WSO2 EI and create the data service:

1. Open a terminal and navigate to the <EI_HOME>/bin directory.
2. Execute one of the following scripts to start the ESB profile of WSO2 EI.
   • On Windows: integrator.bat --run
   • On Linux/Mac OS: sh integrator.sh
3. In your Web browser, navigate to the ESB profile's management console using the following URL:  https://localhost:9443/carbon/.
4. Log into the management console using the following credentials:
   • Username: admin
   • Password: admin

Now, let's start creating the data service using the management console.

1. On the Main tab, click Create under Data Service. 

2. In the Create Data Service screen, enter DOCTORS_DataService as the data service name. Click Next.

3. When you get to the Add New Data Source screen, enter the following details:

   Field	Value
   DatasourceID	default
   Datasourcetype	RDBMS
   Database Engine	H2
   Driver Class	org.h2.Driver
   URL	jdbc:h2:file:./samples/data-services/database/DATA_SERV_QSG
   User Name	wso2ds
   Password	wso2ds

4. Click Save and then Next to start defining a query.

5. Now, let's start writing a query for getting data from the datasource. We will first define a query that can get information of all available doctors from the database.

   1. Click Add New Query to open the Add New Query screen.

   2. Enter the following values:
      

      Field	Value
      Query ID	select_all_DOCTORS_query
      Datasource	default
      SQL	SELECT NAME, HOSPITAL, SPECIALITY, AVAILABILITY, CHARGE FROM PUBLIC.DOCTORS

   3. Add output mappings: Click Generate Response to automatically create mappings for the fields. Change the Grouped by element and Row name to 'DOCTORSCollection' and 'DOCTOR'. The output mapping will be as shown below.
      

      Output mapping is used to specify how the data that is fetched from your query will be shown in the response. Note that, by default, the output type is XML. Find out more about defining Output Mappings. 

6. Now let's create a query that can get the doctor information based on specialization.
   1. Click Add New Query to open the Add New Query screen.

   2. Enter the following values:

      Field	Value
      Query ID	select_DOCTORS_from_SPECIALITY_query
      Datasource	default
      SQL	SELECT NAME, HOSPITAL, SPECIALITY, AVAILABILITY, CHARGE FROM PUBLIC.DOCTORS WHERE SPECIALITY=?

   3. Add Input mapping: Click Generate Input Mappings and a new input mapping record will be created. Edit the record and change the mapping name to SPECIALITY. You will now have the following input mapping:
      

      Input mappings allow you to add parameters to a query so that you can set the parameter value when executing the query. According to the above definition, you need to provide the account ID as an input in order to retrieve the data corresponding to the account ID.

      Find out more about defining Input Mappings.

   4. Add output mappings: Click Generate Response to automatically create mappings for the fields. Change the Grouped by element and Row name to 'DOCTORList' and 'DOCTOR'. The output mapping will be as shown below.
      
      

      Output mapping is used to specify how the data that is fetched from your query will be shown in the response. Note that, by default, the output type is XML. Find out more about defining Output Mappings. 

7. Click Save and then Next to open the Operations screen. Since we are exposing the data as a REST resource, we do not need an operation. Click Next to open the Resources screen.

   1. Click Add New Resources to open Add Resources screen. We will first create a resource to invoke the select_all_DOCTORS_query:
      

      Field	Value
      Resource Path	/getAllDoctors
      Resource Method	GET
      Query ID	select_all_DOCTORS_query

   2. Save the resource.
8. Now, let's create a Resource to invoke the select_DOCTORS_from_SPECIALITY_query query.

   1. Click Add New Resources to open Add Resources screen and enter the following details:

      Field	Value
      Resource Path	/getDoctors
      Resource Method	GET
      Query ID	select_DOCTORS_from_SPECIALITY_query

   2. Save the resource.
9. Once you have defined the resources, 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.

Sending requests to the ESB

Let's send a request to the data service, which is now deployed in the ESB profile of WSO2 EI. You will need a REST client like curl for this.

1. Open a command line terminal and enter the following request to get information of all surgeons:

   curl -v http://localhost:8280/services/DOCTORS_DataService/getDoctors?SPECIALITY=surgery

   Other categories you can try sending in the request are:

   • cardiology

   • gynaecology

   • ent

   • paediatric

2. You will see the response message from data service with a list of all available doctors and relevant details.

   <DOCTORSLIST xmlns="http://ws.wso2.org/dataservice">
   <DOCTOR>
       <NAME>thomas collins</NAME>
       <HOSPITAL>grand oak community hospital</HOSPITAL>
       <SPECIALITY>surgery</SPECIALITY>
       <AVAILABILITY>9.00 a.m - 11.00 a.m</AVAILABILITY>
       <CHARGE>7000</CHARGE>
   </DOCTOR>
   <DOCTOR>
       <NAME>anne clement</NAME>
       <HOSPITAL>clemency medical center</HOSPITAL>
       <SPECIALITY>surgery</SPECIALITY>
       <AVAILABILITY>8.00 a.m - 10.00 a.m</AVAILABILITY>
       <CHARGE>12000</CHARGE>
   </DOCTOR>
   <DOCTOR>
       <NAME>seth mears</NAME>
       <HOSPITAL>pine valley community hospital</HOSPITAL>
       <SPECIALITY>surgery</SPECIALITY>
       <AVAILABILITY>3.00 p.m - 5.00 p.m</AVAILABILITY>
       <CHARGE>8000</CHARGE>
   </DOCTOR>
   </DOCTORSLIST>