Skip to Content
Author's profile photo Former Member

CRUD Operations with XSJS – SAP HANA Native Application

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

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Florian Pfeffer
      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

      Author's profile photo Former Member
      Former Member
      Blog 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

      Author's profile photo Daniel K
      Daniel K

      Hi Kapil

      Good Blog. Very useful for beginners.

      thanks

      Daniel

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Daniel,

      Thanks.!!

      I wrote this blog for beginners like me. 😉

      Cheers

      Author's profile photo Daniel K
      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

      Author's profile photo Former Member
      Former Member

      Hello, Kapil

      Could you please share me how to insert multiple rows at 1 time?


      Thanks.

      Jerry

      Author's profile photo archit wahi
      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

      Author's profile photo Prakash Singh Gariya
      Prakash Singh Gariya

      Hello Kapil,

      It's 2017 now and still i found your post very useful .
      Continue to write brother.

      Cheers.....!!

      Author's profile photo Jaglika Perkova
      Jaglika Perkova

      Hello and thanks for your blog sharing. I would like to ask you if it is possible to do CRUD operations on calculation views exposed as xsodata service or it is only possible for the tables exposed as xsodata service? Thanks in advance

      Author's profile photo Sebastian Wolters
      Sebastian Wolters

      The recommended approach for implementing CRUD operations is very dangerous as it is prone to SQL injections. Constructing SQL queries by appending request parameters allows callers to execute arbitrary SQL code. This is a major security flaw.
      Instead parameters should be set on the prepared statement. See https://blogs.sap.com/2012/12/04/how-to-insert-data-in-hana-through-javascript-with-xs-engine/ for a much better example and Thomas Jungs comment for a better explanation.