Hello Everyone,

This blog explains how to create SAP NAtive application having CRUD(Create, Read, Update & Delete) operations using SAPUI5 and XSJS services. We will also see how to consume XSJS.

As all we know ,Data can be transferred over HTTP using either the Atom (XML) or the JSON (JavaScript) format. Here I am using JSON to pass the data.


Creating the User Interface

1.       Create a project

2.       Setup SAPUI5 bootstrap and required libraries

3.       Create view components

       4.     Implement XSJS Services

               4.1.    Read

               4.2.    Create

               4.3.    Update

               4.4.    Delete


Prerequisites


     ·       You have to install the SAPUI5 Application Development Tool into your Eclipse

     ·       UI Development Toolkit for HTML5 Version SAPUI5 1.28


             1.    Create the XS project.

            Structure of your project will look like this:

     /wp-content/uploads/2015/12/1_858102.png

      2.   Create a simple HTML Page ,say index.html

    

           


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
  <meta http-equiv="X-UA-Compatible" content="IE=edge"/>
  <title>CRUD Operation with XSJS</title>
  <script src="js/jquery.min.js"></script>
    <script src="https://sapui5.hana.ondemand.com/resources/sap-ui-core.js"
  id="sap-ui-bootstrap"
  data-sap-ui-libs="sap.ui.ux3,sap.ui.commons,sap.ui.table,sap.viz,sap.suite.ui.commons,sap.ui.ux3,sap.m"
  data-sap-ui-theme="sap_goldreflection">
  </script>
  <script>
  sap.ui.localResources("views");
  var view = sap.ui.view({id:"id1", viewName:"views.sample", type:sap.ui.core.mvc.ViewType.JS});
  </script>
  </head>
            <body>
                <div id="content" style="height: 400px;width: 400px; border: 1px solid #AAA"></div>
            </body>
</html>

        

1.           3.       Create one view.js file , I named it as sample.view.js


sap.ui.jsview("views.sample", {
    /** Specifies the Controller belonging to this View.
     * In the case that it is not implemented, or that "null" is returned, this View does not have a Controller.
     * @memberOf views.Companies
     */
    getControllerName: function() {
        return null;
    },
    /** Is initially called once after the Controller has been instantiated. It is the place where the UI is constructed.
     * Since the Controller is given to this method, its event handlers can be attached right away.
     * @memberOf views.Companies
     */
    createContent: function(oController)
    {
        // create a simple matrix layout
        var oLayout = new sap.ui.commons.layout.MatrixLayout({
    id : "matrix1",
        columns : 5,
        width : "100%",
        widths : [ "15%", "30%","10%","30%","15%"],
     layoutFixed : false
     });
        var fname="",lname="",id,dob,ph_no;
 
        var oLabel1 = new sap.ui.commons.Label("l1");
  oLabel1.setText("Employee ID");
  var oLabel2 = new sap.ui.commons.Label("l2");
  oLabel2.setText("First Name");
  var oLabel3 = new sap.ui.commons.Label("l3");
  oLabel3.setText("Last Name");
  var oLabel4 = new sap.ui.commons.Label("l4");
  oLabel4.setText("DOB");
  var oLabel5 = new sap.ui.commons.Label("l5");
  oLabel5.setText("Phone Number");
  var oInput1= new sap.ui.commons.TextField('input1');
        oInput1.setValue("1");
       // oInput1.setEditable(false);
        oInput1.setTooltip("ID");
         oInput1.attachChange(function(){
            id= oInput1.getValue();
            });
  var oInput2 = new sap.ui.commons.TextField('input2');
        oInput2.setValue("Amit");
        oInput2.setTooltip("please enter first name");
        oInput2.attachChange(function(){
            fname= oInput2.getValue();
            });
     
    var oInput3 = new sap.ui.commons.TextField('input3');
        oInput3.setValue("Gupta");
        oInput3.setTooltip("please enter last name");
        oInput3.attachChange(function(){
            lname= oInput3.getValue();
            });
     
        var oInput4 = new sap.ui.commons.TextField('input4');
        oInput4.setValue("1975-03-27");
        oInput4.setTooltip("please enter Dob");
        oInput4.attachChange(function(){
            dob= oInput4.getValue();
            });
    var oInput5 = new sap.ui.commons.TextField('input5');
        oInput5.setValue("98196636666");
        oInput5.setTooltip("please enter phone number");
        oInput5.attachChange(function(){
            ph_no= oInput5.getValue();
            });
     
        var oButton1 = new sap.ui.commons.Button({
    text : "Read",
    tooltip : "This is a Read operation button",
         press : function() {
              var jurl="/services/read.xsjs";
              jQuery.ajax({
    url: jurl,
              async :false,
              TYPE: 'POST' ,
              method: 'GET',
              dataType: 'JSON',
              success: function(data) {
      
              oInput1.setValue(data[0].ID);
              oInput2.setValue(data[0].FirstName);
              oInput3.setValue(data[0].LastName);
              oInput4.setValue(data[0].DoB);
                 oInput5.setValue(data[0].Phone_No);
        
              }
       
       
          });
             }
         });
 
        var oButton2 = new sap.ui.commons.Button({
    text : "Insert",
    tooltip : "This is a Insert operation button",
         press : function() {
          var data1=
                 {
                 ID:id ,
                 FirstName:fname,
                 LastName:lname,
                 DoB:dob,
                     Phone:ph_no,
                  };
             var datavalue=JSON.stringify(data1);
             alert(datavalue);
             var jurl="/services/insert.xsjs";
      
           jQuery.ajax({
      
    url: jurl,
              async :false,
              TYPE: 'POST' ,
              data:{dataobject:datavalue},
              method: 'GET',
              dataType: 'text',
              success: function(data) {
              alert(data);
              console.log(data);
        
              }
       
       
          }); }
        });
        var oButton3 = new sap.ui.commons.Button({
    text : "Update",
     tooltip : "This is a Update operation button",
        press : function() {
    
           var data1=
                 {
                 ID:id ,
                 FirstName:fname,
                 LastName:lname,
                 DoB:dob,
                     Phone:ph_no,
                  };
           
             var datavalue=JSON.stringify(data1);
             var jurl="/services/update.xsjs";
      
           jQuery.ajax({
      
      url: jurl,
              async :false,
              TYPE: 'POST' ,
              data:{dataobject:datavalue},
              method: 'GET',
              dataType: 'text',
              success: function(data) {
              alert(data);
              console.log(data);
        
              }
       
       
          });
    
           }
        });
 
        var oButton4 = new sap.ui.commons.Button({
    text : "Delete",
     tooltip : "This is a delete operation button",
        press : function() {
           var jurl="/services/delete.xsjs";
           jQuery.ajax({
      
    url: jurl,
              async :false,
              TYPE: 'POST' ,
              data:{
                  "ID":id
                  },
              method: 'GET',
              dataType: 'text',
              success: function(data) {
              alert(data);
              console.log(data);
        
              }
       
       
          });
    
           }
    
        });
        oLayout.createRow("", oLabel1,"",oInput1,"" );
        oLayout.createRow("", oLabel2,"", oInput2,"" );
        oLayout.createRow("", oLabel3,"", oInput3,"" );
        oLayout.createRow("", oLabel4,"", oInput4,"" );
        oLayout.createRow("", oLabel5,"", oInput5,"" );
        oLayout.createRow("", oButton1,oButton2, oButton3,oButton4 );
// attach it to some element in the page
        oLayout.placeAt("content");
 
    }
});






4 . Create XSJS :


  • Insert.xsjs

              


$.response.contentType = "application/text";
var body='';
var aCmd = $.request.parameters.get('dataobject');
var obj=JSON.parse(aCmd);
var id= obj.ID ;
var firstname=obj.FirstName;
var lastname=obj.LastName;
var dob=obj.DoB;
var phone=obj.Phone;var tx_data_query="";
function getTxtData()
{
    var connection = $.db.getConnection();
    var statement = null;
    var resultSet = null;
    tx_data_query ='INSERT INTO EMP_DETAILS (ID, FIRST_NAME, LAST_NAME, DOB, PHONE_NUMBER) VALUES (' + id + ',\'' + firstname + '\',\'' + lastname +
    '\',\'' + dob + '\',\'' + phone + '\')';
   try
    {
     statement = connection.prepareStatement(tx_data_query);
    resultSet= statement.executeQuery();
    connection.commit();
    } finally {
    statement.close();
    connection.close();
    }
    return resultSet;
}
function doGet()
{
          try
          {
          $.response.contentType = "application/json";  $.response.contentType = "text/plain";  $.response.setBody(getTxtData());
          }  catch(err) {
             $.response.contentType = "text/plain";  $.response.setBody("Error while executing query: [" +err.message +"]");  $.response.returnCode = 200;
          }
}
doGet();




Before Insertion:

/wp-content/uploads/2015/12/11_858428.png

/wp-content/uploads/2015/12/12_858429.png

     After Insertion :

     /wp-content/uploads/2015/12/13_858430.png

  • Update.xsjs



$.response.contentType = "application/text";
var body='';
var aCmd = $.request.parameters.get('dataobject');
var obj=JSON.parse(aCmd);
var id= obj.ID ;
var firstname=obj.FirstName;
var lastname=obj.LastName;
var dob=obj.DoB;
var phone=obj.Phone;
var tx_data_query = "";
function getTxtData()
{
    var connection = $.db.getConnection();
    var statement = null;
    var resultSet = null;
   // tx_data_query = 'UPDATE NEO_4M2TWKN5K877VBPYTUG3UE323"."s0014075794trial.hanaxs.CRUD::emp_table set FIRST_NAME=\'' + firstname + '\', LAST_NAME=\'' + lastname + '\', DOB=\'' + dob + '\', PHONE_NUMBER= \'' + phone + '\' where ID=\''+id+ '\'';
tx_data_query='delete * from NEO_4M2TWKN5K877VBPYTUG3UE323"."s0014075794trial.hanaxs.CRUD::emp_table where id='+id;
    try
    {
    statement = connection.prepareStatement(tx_data_query);
    resultSet=statement.executeQuery();
    connection.commit();
    } finally {
    statement.close();
    connection.close();
    }
    return resultSet;
}
function doGet()
{
          try
          {
          $.response.contentType = "application/json";
                    $.response.contentType = "text/plain";
                    $.response.setBody(getTxtData());
          }
          catch(err)
          {
                    $.response.contentType = "text/plain";
                    $.response.setBody("Error while executing query: ["+tx_data_query +err.message +"]");
                    $.response.returnCode = 200;
          }
}
doGet();

After Update:

14.PNG

  • Delete.xsjs


         


$.response.contentType = "application/text";
var body='';
var id = $.request.parameters.get('ID');
var tx_data_query = "";
function getTxtData()
{
    var connection = $.db.getConnection();
    var statement = null;
    var resultSet = null;
    tx_data_query = 'DELETE from \"_150949\".\"EMP_DETAILS\"  where where ID='+id;
    try
    {
    statement = connection.prepareStatement(tx_data_query);
    resultSet=statement.executeQuery();
    connection.commit();
    } finally {
    statement.close();
    connection.close();
    }
    return resultSet;
}
function doGet()
{
          try
          {
          $.response.contentType = "application/json";
                    $.response.contentType = "text/plain";
                    $.response.setBody(getTxtData());
          }            catch(err)
          {
                    $.response.contentType = "text/plain";
                    $.response.setBody("Error while executing query: [" +err.message +"]");
                    $.response.returnCode = 200;
          }
}
doGet();


     

  After Delete:

15.PNG



  • Read.xsjs

   



$.response.contentType = "application/text";
var body='';
var aCmd = $.request.parameters.get('ID');
var tx_data_query ='select from \"_150949\".\"EMP_DETAILS\" where ID='+aCmd;
                 
function close(closables) {
          var closable;
          var i;
          for (i = 0; i < closables.length; i++) {
                    closable = closables[i];
                    if(closable) {
                              closable.close();
                    }
          }
}
function getTxData(){
          var txlist = [];
          var connection = $.db.getConnection();
          var statement = null;
          var resultSet = null;
          try{
                    statement = connection.prepareStatement(tx_data_query);
                    resultSet = statement.executeQuery();
                    var txitem;
         
                    while (resultSet.next()) {
                    txitem = {};
                    txitem.ID = resultSet.getString(1);
                    txitem.FirstName = resultSet.getString(2);
                    txitem.LastName = resultSet.getInteger(3);
                    txitem.DoB = resultSet.getString(4);
                    txitem.phone_no = resultSet.getString(5);
                    txlist.push(txitem);
                    }
          } finally {
                    close([resultSet, statement, connection]);
          }
          return txlist;
}
function doGet() {
    
          try{
                    $.response.contentType = "text/plain";
                    $.response.setBody(JSON.stringify(getTxData()));
          }
          catch(err){
                    $.response.contentType = "text/plain";
                    $.response.setBody("Error while executing query: [" + err.message + "]");
                    $.response.returnCode = 200;
          }
}
doGet();


After Reading:

16.PNG

I hope you find this blog useful and helpful.

Happy Coding! 🙂

Thanks,

Kapil Jain

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. Florian Pfeffer

    Hello Kapil,

    can you explain why are you using or not using following points in your implementation?

    • Why are you using XSJS service instead of an OData service or why you prefer XSJS for that case? For me for such simple CRUD operations produce to much overload in XSJS.
    • Why are you using the “old” sap.ui.commons UI5 controls?
    • Why are you using JS views instead of XML views?
    • Why are you loading a separate jquery lib w/o using the sap-ui-core-nojQuery.js?
    • Why are you loading so many UI5 libraries w/o using them (e.g. sap.viz)?
    • Why are you using the old “sap_goldreflection” theme?

    Regards,

    Florian

    (0) 
    1. Kapil Jain Post author

      Hi Florian,

      1) I found XSJS services more flexible rather than OData service, Also requirement was to use XSJS.

      2) I haven’t explored new sap UI5 controls.

      3) JS views are more easy for me to use rather then XML views.

      4- 5) I agree , No need to load separate jquery lib & to load sap.viz as I’m not using.(BY MISTAKE)

      6) Need to explore more about other themes.

      Regards,

      Kapil Jain

      (0) 
  2. Daniel K

    Hi Kapil

    Insert.xsjs

    tx_data_query =’INSERT INTO EMP_DETAILS (ID, FIRST_NAME, LAST_NAME, DOB, PHONE_NUMBER) VALUES (‘ + id + ‘,\” + firstname + ‘\’,\” + lastname +

        ‘\’,\” + dob + ‘\’,\” + phone + ‘\’)’;

    Update.xsjs

    tx_data_query=’delete * fromNEO_4M2TWKN5K877VBPYTUG3UE323″.”s0014075794trial.hanaxs.CRUD::emp_table where id=’+id;

    Delete.xsjs

    tx_data_query = ‘DELETE from \”_150949\”.\”EMP_DETAILS\”  where where ID=’+id;

    Read.xsjs

    var tx_data_query =’select from \”_150949\”.\”EMP_DETAILS\” where ID=’+aCmd;

    For all the above CRUD xsjs files, you mentioned the query with table name like this, \”_150949\”.\”EMP_DETAILS\”   whereas for UPDATE, the table name you mentioned was different. It might be the trial version of HANA WEB IDE table name.

    Hope you correct the same and update the blog.

    Also please mention the create statement for the table considering the time to create the columns and datatypes.

    Thanks

    Daniel

    (0) 
  3. archit wahi

    Hi All,

    Great post Kapil. Loads to learn for newbies like me.

    I am stuck at insert.xsjs. When im executing the service im getting “Error while executing query: [statement is null]” so there seems to be a problem with the query part thought the code is copiesd as is above. Plz suggest as to what can be wrong.

    Best regards
    Archit Wahi

    (0) 

Leave a Reply