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:
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:
After Insertion :
- 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:
- 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:
- 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:
I hope you find this blog useful and helpful.
Happy Coding! 🙂
Thanks,
Kapil Jain
Hello Kapil,
can you explain why are you using or not using following points in your implementation?
Regards,
Florian
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
Hi Kapil
Good Blog. Very useful for beginners.
thanks
Daniel
Hi Daniel,
Thanks.!!
I wrote this blog for beginners like me. 😉
Cheers
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
Hello, Kapil
Could you please share me how to insert multiple rows at 1 time?
Thanks.
Jerry
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
Hello Kapil,
It's 2017 now and still i found your post very useful .
Continue to write brother.
Cheers.....!!
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
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.