Thursday, February 06, 2014

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

WSO2 Data Services Server - (current latest 3.1.1)

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.


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>
   <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"/>
   <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"/>

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();
        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, "", "test", 2));
        try {
            System.out.println("Executing Add Flights..");
        } catch (Exception e) {
            System.out.println("Error in Add Flights!");

Complete client code can be found here.




  1. Pusphalanks'
    thanks for nice blog.
    can you help me on this issue. i'm trying to insert data into multiple table using wso2 DSS. Not inserting data into table using the JSON.

    T1(COL1, COL2, COL3)
    T2(COL1, COL4, COL5)

    Common column is COL1. A single JSON is will have data for both tables.
    when I call the service requests, able to insert data independently. But with combine queries of insert statements for both T1 and T2 , data is not inserted into the tables.


  2. This comment has been removed by a blog administrator.

  3. hello nice presentation
    but is ther a way to Bypass WSO2 DSS GUI and call directly the DBS file Uploader ?

    My goal : calling FileUploadService by program but not with WSO2 GUI
    i found that in osgiConsole HiddenServices can be exposed
    but how to call them?
    best regards

    1. Hi Philg,

      Of course you can call these services via java client without using the UI. You can get the relevant service stub located in /repository/components/plugins folder and use it as dependency in the client you are to write.

      This [1] link will help you.
      [1] -

      Best Regards,

  4. Thanks a lot Pushpalanka very helpful doc
    but firstly i try to use soapUI client
    i have set Username+Passwrd ( admin/admin) + SSL carbon jks store ( wso2carbon.jks) and the call works but i got back :

    Unauthorized call!. AuthorizationAction has not been specified for service:FileUploadService, operation:uploadFiles

    + some server side one logged record ....
    [2015-09-08 17:38:10,888] WARN - Unauthorized call by tenant carbon.super,user admin to service:FileUploadService,operation:uploadFiles

    Seems to be just some auth in a config file for this operation ?
    thx a lot
    have a good day

  5. and this service FileLoadService a HiddenService ( just exposed after -DosgiConsole option at start ..)
    so to connect and use it perharps special credentials are mandatory ?

    Best Regards