WSO2 DSS: Batch Insert Sample (End to End)

WSO2 DSS wraps Data Services Layer and provides us with a simple GUI to define a Data Service with zero Java code. With this, a change to the data source is just a simple click away and no other party needs to be aware of this.

With this sample demonstration, we will see how to do a batch insert to a table. Batch insert is useful when you want to insert data in sequential manner. This also means that if at least one of the insertion query fails all the other queries ran so far in the batch will be rolled back as well. If one insertion in the batch fails means whole batch is failed.

This can be used if you are running the same query to insert data many times. With batch insert all the data will be sent in one call. So this reduce the number calls you have to call, to get the data inserted. 

This comes with one condition that,

The query should not be producing results back. (We will only be notified whether the query was successful or not.)

Prerequisites: 

WSO2 Data Services Server - http://wso2.com/products/data-services-server/ (current latest 3.1.1)

Mysql connector (JDBC) - https://www.mysql.com/products/connector/

If we already have a data service running which is not sending back a result set , then it's just a matters of adding following property in service declaration.

enableBatchRequests="true"

Anyway I will be demonstrating the creation of the service from the scratch.

1. Create a service as follows going through the wizard,

2. Create the data source

3. Create the query - (This is an insert query. Also note the input mapping we have add as relevant to the query. To know more about input mapping and using validation refer the documentation.)

4. Create the operation - Select the query to be executed once the operation is called. By enabling return request status, we will be notified whether the operation was a success or not.

5. Try it! - When we list the services we will see this new service now. In the right we will have an option to try it.

Here we can see the option to try the service giving the input parameters. Here I have tried it two insertions in a batch.
Now if we go to XML view of the service it will be similar to following, which is saved in server as a .dbs file.

<data enableBatchRequests="true" name="BatchInsertSample">
   <config id="json">
      <property name="driverClassName">com.mysql.jdbc.Driver</property>
      <property name="url">jdbc:mysql://localhost:3306/json_array</property>
      <property name="username">root</property>
      <property name="password">root</property>
      <property name="minIdle">1</property>
      <property name="maxActive">10</property>
      <property name="validationQuery">SELECT 1</property>
   </config>
   <query id="addFlightQuery" useConfig="json">
      <sql>insert into flights (flight_no, number_of_cases, created_by, description, trips) values (:flight_no,:number_of_cases,:created_by,:description,:trips)</sql>
      <param name="flight_no" ordinal="1" sqlType="BIGINT"/>
      <param name="number_of_cases" ordinal="2" sqlType="BIGINT"/>
      <param name="created_by" ordinal="3" sqlType="STRING"/>
      <param name="description" ordinal="4" sqlType="STRING"/>
      <param name="trips" ordinal="5" sqlType="BIGINT"/>
   </query>
   <operation name="addFlight" returnRequestStatus="true">
      <call-query href="addFlightQuery">
         <with-param name="flight_no" query-param="flight_no"/>
         <with-param name="number_of_cases" query-param="number_of_cases"/>
         <with-param name="created_by" query-param="created_by"/>
         <with-param name="description" query-param="description"/>
         <with-param name="trips" query-param="trips"/>
      </call-query>
   </operation>
</data>

If we hit on the service name in the list of services, we will be directed to Service Dashboard where we can see several other options for the service. It provides the option to generate an Axis2 client for the service. Once we get the client then it's a matter of calling the methods in the stub as follows.

private static BatchRequestSampleOldStub.AddFlight_type0 createFlight(int cases, String creator, String description, int trips) {

        BatchRequestSampleOldStub.AddFlight_type0 val = new BatchRequestSampleOldStub.AddFlight_type0();
        val.setNumber_of_cases(cases);
        val.setCreated_by(creator);
        val.setDescription(description);
        val.setTrips(trips);
        printFlightInfo(cases, creator, description, trips);
        return val;
    }


    public static void main(String[] args) throws Exception {
        String epr = "http://localhost:9763" + "/services/BatchInsertSample";
        BatchRequestSampleOldStub stub = new BatchRequestSampleOldStub(epr);
        BatchRequestSampleOldStub.AddFlight_batch_req vals1 = new BatchRequestSampleOldStub.AddFlight_batch_req();


        vals1.addAddFlight(createFlight(1, "Pushpalanka", "test", 2));
        vals1.addAddFlight(createFlight(2, "Jayawardhana", "test", 2));
        vals1.addAddFlight(createFlight(3, "lanka@gmail.com", "test", 2));
        try {
            System.out.println("Executing Add Flights..");
            stub.addFlight_batch_req(vals1);
        } catch (Exception e) {
            System.out.println("Error in Add Flights!");
        }