Invoking an Operation with Multiple Records

The batch requests feature allows you to send multiple (IN-Only) requests to a datasource using a single operation (batch operation). Follow the steps given below to define a data service that can invoke batch requests:

Setting up a datasource

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

1. Download the product installer from here, and run the installer.
   Let's call the installation location of your product the <EI_HOME> directory.

   If you installed the product using the installer, this is located in a place specific to your OS as shown below:

   OS	Home directory
   Mac OS	/Library/WSO2/EnterpriseIntegrator/6.5.0
   Windows	C:\Program Files\WSO2\EnterpriseIntegrator\6.5.0\
   Ubuntu	/usr/lib/wso2/EnterpriseIntegrator/6.5.0
   CentOS	/usr/lib64/EnterpriseIntegrator/6.5.0

2. Install the MySQL server.
3. Download the JDBC driver for MySQL from here and copy it to your <EI_HOME>/lib directory.

4. Create the following database: Company

   CREATE DATABASE Company;

5. Create the Employees table:

   USE Company;
   
   
   CREATE TABLE `Employees` (`EmployeeNumber` int(11) NOT NULL, `FirstName` varchar(255) NOT NULL, `LastName` varchar(255) DEFAULT NULL, `Email` varchar(255) DEFAULT NULL, `JobTitle` varchar(255) DEFAULT NULL, `OfficeCode` int(11) NOT NULL, PRIMARY KEY (`EmployeeNumber`,`OfficeCode`));

Define a data service to insert records in batches

Let's create a data service using the Create Data Service wizard:

1. Start the WSO2 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.
4. Add a name for the data service, such as BatchRequestDataService.
5. Select the Enable Batch Requests check box.
   
6. Click Next and add a new datasource.

7. Give an ID for the datasource, and create the connection to the Company database.

   Datasource ID	Datasource
   Datasource Type	RDBMS
   Datasource Type (Default/External)	Leave Default selected.
   Database Engine	MySQL
   Driver Class	com.mysql.jdbc.Driver
   URL	jdbc:mysql://localhost:3306/Company
   User Name	Enter your MySQL server's username.
   Password	Enter your MySQL server's password. If you have not assigned a password, keep this field empty.

   Example:
   

8. Save the datasource details, and click Next to open the Queries screen.

9. Click  Add New Query  to specify the query details:

1. Enter addEmployeeQuery as the query ID.

2. Enter the following SQL dialect:

   insert into Employees (EmployeeNumber, FirstName, LastName, Email, JobTitle, OfficeCode) values(:EmployeeNumber,:FirstName,:LastName,:Email,:JobTitle,:Officecode)

3. Click Generate Input Mapping and input mappings will be generated automatically for the EmployeeNumber, FirstName, LastName, Email, JobTitle, and OfficeCode fields.

10. Save the query, and click Next to open the Operations screen.

11. Click Add New Operation, and create an operation for the addEmployeeQuery query as shown below.
    
12. Save the operation.
13. Click Finish to complete the data service creation process.

Invoking the data service 

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.
2. Click Try this Service to open the data service from the TryIt tool.
3. Click Try this Service to open the data service from the TryIt tool. There will be an addemployeeOp_batch_request operation automatically generated.
4. Select the batch operation.

5. Enter multiple transactions as shown below. In this example, we are sending two transactions with details of two employees.

   <p:addEmployeeOp_batch_req xmlns:p="http://ws.wso2.org/dataservice">
         <!--1 or more occurrences-->
         <addEmployeeOp xmlns="http://ws.wso2.org/dataservice">
            <!--Exactly 1 occurrence-->
            <xs:EmployeeNumber xmlns:xs="http://ws.wso2.org/dataservice">1002</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">johnd@wso2.com</xs:Email>
            <!--Exactly 1 occurrence-->
            <xs:JobTitle xmlns:xs="http://ws.wso2.org/dataservice">Consultant</xs:JobTitle>
            <!--Exactly 1 occurrence-->
            <xs:Officecode xmlns:xs="http://ws.wso2.org/dataservice">01</xs:Officecode>
         </addEmployeeOp>
         <addEmployeeOp xmlns="http://ws.wso2.org/dataservice">
            <!--Exactly 1 occurrence-->
            <xs:EmployeeNumber xmlns:xs="http://ws.wso2.org/dataservice">1004</xs:EmployeeNumber>
            <!--Exactly 1 occurrence-->
            <xs:FirstName xmlns:xs="http://ws.wso2.org/dataservice">Peter</xs:FirstName>
            <!--Exactly 1 occurrence-->
            <xs:LastName xmlns:xs="http://ws.wso2.org/dataservice">Parker</xs:LastName>
            <!--Exactly 1 occurrence-->
            <xs:Email xmlns:xs="http://ws.wso2.org/dataservice">peterp@wso2.com</xs:Email>
            <!--Exactly 1 occurrence-->
            <xs:JobTitle xmlns:xs="http://ws.wso2.org/dataservice">Consultant</xs:JobTitle>
            <!--Exactly 1 occurrence-->
            <xs:Officecode xmlns:xs="http://ws.wso2.org/dataservice">01</xs:Officecode>
         </addEmployeeOp>
      </p:addEmployeeOp_batch_req>

6. Execute the batch operation. You will find that all the records have been inserted into the database simultaneously.

   Want to confirm that the records are added to the database? Run the following MySQL command.

   SELECT * FROM Employees

   You see all the records that are in the Employees table, including the two employee records that you added in the previous step.