Batch Insert and Update Processing with OData V2

ferrygun18 · ‎04-25-2019

In this blog, we'll learn how to perform batch insert and update operation with OData version 2 and we apply to the contact persons list where user can add, edit and delete the person first name and last name. I have no issue when performing the single batch insert/update alone. But when it comes updating and inserting at the same time with batch, I think this is one of the easiest way. Do let me know if you have any better solution.

Prepare the Components

Create the application public.aa.bb.

Create the following folder inside the package:

data

hdb

lib

sequence

ui5

Full list of populated files and database artifacts:

Clone the code from Git.

Let's take a look at the onWrite() method in /ui5/controller/ContactPersons.controller.js.

At line 365, we parse the oJsonData object. This object is populated from the model of table id persons in /ui5/view/ContactPersons.view.xml.

<Table id="persons" mode="SingleSelectMaster" selectionChange="onSelectionChange" items="{/results}" class="TableStyle">

    <columns>

        <Column>

            <Label text="First Name" required="true" />

        </Column>

        <Column>

            <Label text="Last Name" required="true" />

        </Column>

    </columns>

    <items>

        <ColumnListItem>

            <cells>

                <Input value="{FIRSTNAME}" editable="false" class="InputStyle" maxLength="50" />

                <Input value="{LASTNAME}" editable="false" class="InputStyle" maxLength="50" />

            </cells>

        </ColumnListItem>

    </items>

</Table>

At line 369, we check if there are any entries in the table. If no entries, we assume that user has deleted all records.

Perform Insert and Update

If there is an entry, we perform the batch operation using OData version 2 with destination "../lib/xsodata/goodmorning.xsodata/AddEditPersons"

var oParams = {};

oParams.json = true;

oParams.defaultUpdateMethod = "PUT";

oParams.useBatch = true;



var batchModel = new sap.ui.model.odata.v2.ODataModel("../lib/xsodata/goodmorning.xsodata", oParams);

var mParams = {};

mParams.groupId = "1001";

mParams.success = function() {

    this_.refreshEtag();

    var oModel_ = new sap.ui.model.odata.ODataModel("../lib/xsodata/goodmorning.xsodata", false);

    oModel_.read("/ContactPersons", {

        success: function(oData) {},

        error: function(e) {

            console.log(e);

        }

    });



    sap.m.MessageToast.show("Record has been updated");

};

mParams.error = this.onErrorCall;



for (var k = 0; k < oJsonData.length; k++) {

    if (oJsonData[k].ID === "")

        oJsonData[k].ID = 0;

    batchModel.create("/AddEditPersons", oJsonData[k], mParams);

}

Let's take a look the details in goodmorning.xsodata.

We call the modification exit for OData request. In this case is the xsodata library calls the exit "AddEditPersons.xsjslib" before creating the entity.

Now let's check create_before_exit() method in AddExitPersons.xsjslib.

Firstly, we need to insert the unique ID from table ContactPersons to array ArrD and unique ID from temp table after to arrB.We will perform the array comparison and merging later on.
```
@param {afterTableName} String -The name of a temporary table with the single entry after the operation (CREATE and UPDATE events only)

var after = param.afterTableName;
```

Check if unique ID (from temp table after) exists in ContactPersons table.

If exists, delete the existing ID in ContactPersons table to avoid the unique constraint violation error when inserting the record.

Find any difference between ArrD and ArrB and delete all the differences.
Example: In ArrD (database) we have record: Name A and Name B. in ArrB (temp table) we have record Name B. The difference is Name A and we will delete Name A from database.

If no unique ID exists in ContactPersons table, get the nextvalID from the sequence: public.aa.bb.sequence::persons.

Merge ArrB & ArrD into ArrB. ArrB will have the full set of IDs.

Again, find any difference between ArrD and ArrB and delete all the differences.

Update the ID in temp table after with nextvalID.

I hope the logic is not confuse you. Here is complete code.

/**

@param {connection} Connection - The SQL connection used in the OData request

@param {beforeTableName} String - The name of a temporary table with the single entry before the operation (UPDATE and DELETE events only)

@param {afterTableName} String -The name of a temporary table with the single entry after the operation (CREATE and UPDATE events only)

 */

$.import("public.aa.bb.lib.xsjs.session.xsjslib", "session");

var SESSIONINFO = $.public.aa.bb.lib.xsjs.session;



function merge_array(array1, array2) {

    var result_array = [];

    var arr = array1.concat(array2);

    var len = arr.length;

    var assoc = {};



    while(len--) {

        var item = arr[len];



        if(!assoc[item]) 

        { 

            result_array.unshift(item);

            assoc[item] = true;

        }

    }



    return result_array;

}



function differenceOf2Arrays (array1, array2) {

    var temp = [];

    array1 = array1.toString().split(',').map(Number);

    array2 = array2.toString().split(',').map(Number);

    

    for (var i in array1) {

    if(array2.indexOf(array1[i]) === -1) temp.push(array1[i]);

    }

    for(i in array2) {

    if(array1.indexOf(array2[i]) === -1) temp.push(array2[i]);

    }

    return temp.sort((a,b) => a-b);

}



function create_before_exit(param) {



    var after = param.afterTableName;

    var pStmt = null;



    pStmt = param.connection.prepareStatement("select * from \"" + after + "\"");	 

    var data = SESSIONINFO.recordSetToJSON(pStmt.executeQuery(), "Details");

    pStmt.close();  



    var ArrD = [];

    var ArrB = [];

    pStmt = param.connection.prepareStatement("select ID from \"goodmorning\".\"public.aa.bb.hdb::data.ContactPersons\"");

    var rs = pStmt.executeQuery();

    while (rs.next()) {

        ArrD.push(rs.getInteger(1));

    }

    

    pStmt = param.connection.prepareStatement("select ID from \"" + after + "\"");	

    rs = pStmt.executeQuery();

    while (rs.next()) {   

        ArrB.push(rs.getInteger(1));

    }



    pStmt = param.connection.prepareStatement("select ID, FIRSTNAME from \"goodmorning\".\"public.aa.bb.hdb::data.ContactPersons\" where \"ID\" = ?");

    pStmt.setString(1, data.Details[0].ID.toString());

    rs = pStmt.executeQuery();

    if (rs.next()) {

        //Existing record

		pStmt = param.connection.prepareStatement("delete from \"goodmorning\".\"public.aa.bb.hdb::data.ContactPersons\" where \"ID\" = ?");

        pStmt.setInteger(1, rs.getInteger(1));

        pStmt.execute();

        pStmt.close();



        var delArr = differenceOf2Arrays(ArrD, ArrB);

        for( var i = 0; i < delArr.length; i++) {

            pStmt = param.connection.prepareStatement("delete from \"goodmorning\".\"public.aa.bb.hdb::data.ContactPersons\" where \"ID\" = ?");

            pStmt.setInteger(1, parseInt(delArr[i]));

            pStmt.execute();

            pStmt.close();

        }

    } else {

        //New record

        pStmt = param.connection.prepareStatement("select \"goodmorning\".\"public.aa.bb.sequence::persons\".NEXTVAL from dummy");

        rs = pStmt.executeQuery();

        var NextValID = "";

        while (rs.next()) {

        	NextValID = rs.getString(1);

        }

        pStmt.close(); 

        

        ArrB = merge_array(ArrB, ArrD);

        var delArr = differenceOf2Arrays(ArrD, ArrB);

        for( var i = 0; i < delArr.length; i++) {

            pStmt = param.connection.prepareStatement("delete from \"goodmorning\".\"public.aa.bb.hdb::data.ContactPersons\" where \"ID\" = ?");

            pStmt.setInteger(1, parseInt(delArr[i]));

            pStmt.execute();

            pStmt.close();

        }

        

        pStmt = param.connection.prepareStatement("update\"" + after + "\"set \"ID\" = ?");

        pStmt.setString(1, NextValID);

        pStmt.execute();

        pStmt.close();

    }

}

Perform Deletion

To perform deletion is pretty straight forward, we just call the DeletePersons from UI5 with dummy entry oEntry.

var oEntry = {};

oEntry.ID = 0;

oEntry.FIRSTNAME = "";

oEntry.LASTNAME = "";



var oDataModel = new sap.ui.model.odata.ODataModel("../lib/xsodata/goodmorning.xsodata", true);

oDataModel.create("/DeletePersons", oEntry, {

    context: null,

    success: function(data) {

    },

    error: function(e) {

    }

});

And in DeletePersons.xsjslib, we just delete all records.

function delete_after_exit(param) {

    var pStmt = null;



    pStmt = param.connection.prepareStatement("delete from \"goodmorning\".\"public.aa.bb.hdb::data.ContactPersons\"");

    pStmt.execute();

    pStmt.close();

}