Introduction
The document will demonstrate how to create/update/delete entries in HANA DB using UI5 application.
This is in continuation to my previous document(Step-By-Step process to develop UI5 Application on HANA DB) where we saw how to display data from HANA DB in UI5 application .
For the example, I will use a table already existing in HANA DB, expose it in odata service and pass data from UI5 application to the odataservice.
Following is the structure of the table in HANA DB:
Table already has few entries as given in the screenshot below:
Before going into the example, let’s see below sample code for insert, update and delete operation on hana database using odatamodel.
INSERT
Create method on odatamodel instance triggers insert operation in database. The method takes following as its input: odata service entityset , payload and the event handler functions to be triggered on event of success or failure.
var mPayload = { ID: "101", NAME: "Test", AGE: "20" };
oEmpDataModel.create("/EMPLOYEE_TAB",mPayload, null,
function(){ alert("Create successful"); },
function(){ alert("Create failed");});
UPDATE
In addition to the entityset name update method also takes the ID of the record which needs to be modified.
var mPayload_upd={AGE: "30"};
oEmpDataModel.update("/EMPLOYEE_TAB(101)", mPayload_upd, null,
function(){ alert("UPDATE successful"); },
function(){ alert("UPDATE failed");});
DELETE
Similar to update method remove method on odatamodel instance takes the entityset and ID of the record to be deleted .
var empId="101";
oEmpDataModel.remove("/EMPLOYEE_TAB('"+empId+"')",null
function(){ alert("Delete successful"); },
function(){ alert("Delete failed");});
Creation of ODATA service
The steps are similar to the steps described in previous document.
- Create an XS Project with name EmployeeService.
- Create two files : .xsaccess & .xsapp files
- Create .xsodata file with name : EmployeeOData.xsodata.
- Insert following piece of code for exposing it into odata service.
service {
//<SCHEMA>.<TABLENAME> as <RESULT SET NAME>
"SCHEMANAME"."EMPLOYEE" as "EMPLOYEE_TAB";
}
- Save all files. Right click on the project’s root folder to share, commit and activate in HANA repository.
- You can now check your odata service at following location:
http://host:port/EmployeeService/EmployeeOData.xsodata/$metadata
Consuming ODATA service in UI5 Application
- Create an UI5 application project with name EmpApp and a default view with name EmpView.
- Add .xsaccess and .xsapp files in the root folder.
- On screen we will add input fields to take entry for ID, Name and age fields, radio button to select type of operation and a button to save data.
Also we will add a table element to show existing entries from database. So we will be able to verify database update in EMPLOYEE table.
Following is the screenshot for the application:
Index.html
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta http-equiv='Content-Type' content='text/html;charset=UTF-8'/>
<script src ="/sap/ui5/1/resources/sap-ui-core.js"
id="sap-ui-bootstrap"
data-sap-ui-libs="sap.ui.commons,sap.ui.table"
data-sap-ui-theme="sap_bluecrystal">
</script>
<!-- add sap.ui.table,sap.ui.ux3 and/or other libraries to 'data-sap-ui-libs' if required -->
<script>
sap.ui.localResources("empapp");
var oEmpDataUrl= "http://host:port/PlantService/EmployeeOData.xsodata/";
var oEmpDataModel= new sap.ui.model.odata.ODataModel(oEmpDataUrl);
var view = sap.ui.view({id:"idEmpView", viewName:"empapp.EmpView", type:sap.ui.core.mvc.ViewType.JS});
view.placeAt("content");
</script>
</head>
<body class="sapUiBody" role="application">
<div id="content"></div>
</body>
</html>
EmpView.view.js
EmpView.view.js file containes the code for UI elements and database insert, update or delete operations.
createContent : function(oController) {
//Create a matrix layout with 2 columns
var oMatrixMain = new sap.ui.commons.layout.MatrixLayout({layoutFixed: true, columns: 2});
oMatrixMain.setWidths('5px', '500px');
//Create a panel instance
var oPanel = new sap.ui.commons.Panel({width: "400px", showCollapseIcon: false});
oPanel.setAreaDesign(sap.ui.commons.enums.AreaDesign.Plain);
oPanel.setBorderDesign(sap.ui.commons.enums.BorderDesign.None);
//Set the title of the panel
oPanel.setText("Employee Data");
//Create a matrix layout with 2 columns
var oMatrix = new sap.ui.commons.layout.MatrixLayout({layoutFixed: true, width: '300px', columns: 2});
oMatrix.setWidths('100px', '200px');
//Create a simple form within the layout
var oLabel_Id = new sap.ui.commons.Label({text: 'Id'});
var oInput_Id = new sap.ui.commons.TextField({Id: "EMP_ID", width: '100%'});
oLabel_Id.setLabelFor(oInput_Id);
oMatrix.createRow(oLabel_Id, oInput_Id);
var oLabel_name = new sap.ui.commons.Label({text: 'Name'});
var oInput_name = new sap.ui.commons.TextField({Id:'EMP_NAME', width: '100%'});
oLabel_name.setLabelFor(oInput_name);
oMatrix.createRow(oLabel_name, oInput_name);
var oLabel_age = new sap.ui.commons.Label({text: 'Age'});
var oInput_age = new sap.ui.commons.TextField({Id:'EMP_AGE', width: '100%'});
oLabel_age.setLabelFor(oInput_age);
oMatrix.createRow(oLabel_age, oInput_age);
// create 2 simple RadioButtons
var oRBG2 = new sap.ui.commons.RadioButtonGroup({
tooltip : "Please select type of operation",
columns : 3,
selectedIndex : 1
});
var oRB1 = new sap.ui.core.Item({
text : 'Insert',
key : 'I',
tooltip : 'Insert new Entry',
});
oRBG2.addItem(oRB1);
var oRB2 = new sap.ui.core.Item({
text : 'Update',
key : 'U',
tooltip : 'Update Entry',
});
oRBG2.addItem(oRB2);
var oRB3 =new sap.ui.core.Item({
text : 'Delete',
key : 'D',
tooltip : 'Remove Entry',
});
oRBG2.addItem(oRB3);
//Add the form to the panels content area
oPanel.addContent(oMatrix);
oPanel.addContent(oRBG2);
var oPanel2 = new sap.ui.commons.Panel({width: "400px", showCollapseIcon: false});
oPanel2.setAreaDesign(sap.ui.commons.enums.AreaDesign.Plain);
oPanel2.setBorderDesign(sap.ui.commons.enums.BorderDesign.None);
//Set the title of the panel
oPanel2.setText("Employee List");
/* Create Table to display records from EMPLOYEE Table*/
var oEmpTable = new sap.ui.table.Table({
selectionMode: sap.ui.table.SelectionMode.None,
visibleRowCount: 10,
threshold:100,
width : "350px",
});
//Define the columns and the control templates to be used
oEmpTable.addColumn( new sap.ui.table.Column({
label: new sap.ui.commons.Label({text: "Employee ID "}),
template: new sap.ui.commons.TextView().bindProperty("text", "ID"),
sortProperty: "ID",
filterProperty: "ID",
width: "120px"
}));
oEmpTable.addColumn(new sap.ui.table.Column({
label: new sap.ui.commons.Label({text: "Name"}),
template: new sap.ui.commons.TextView().bindProperty("text", "NAME"),
sortProperty: "NAME",
filterProperty: "NAME",
width: "120px"
}));
oEmpTable.addColumn(new sap.ui.table.Column({
label: new sap.ui.commons.Label({text: "Age"}),
template: new sap.ui.commons.TextView().bindProperty("text", "AGE"),
sortProperty: "AGE",
filterProperty: "AGE",
width: "80px"
}));
oEmpTable.setModel(oEmpDataModel);
oEmpTable.bindRows("/EMPLOYEE_TAB");
oPanel2.addContent(oEmpTable);
oMatrixMain.createRow(null,oPanel);
oMatrixMain.createRow(null,oPanel2);
//Add some buttons to the panel header
oPanel.addButton(new sap.ui.commons.Button({text: 'Save',press :function()
{
var updateType=oRBG2.getSelectedItem().getKey();
var empId=oInput_Id.getValue();
var empName=oInput_name.getValue();
var empAge=oInput_age.getValue();
//Insert
if(updateType=='I')
{
var mPayload = { ID: empId, NAME: empName, AGE: empAge };
oEmpDataModel.create("/EMPLOYEE_TAB",mPayload,null,
function(){ alert("Create successful"); },
function(){ alert("Create failed");});
}
if(updateType=='U')
{
var mPayload={NAME: empName, AGE: empAge};
oEmpDataModel.update("/EMPLOYEE_TAB('"+empId+"')", mPayload,null,
function(){ alert("UPDATE successful"); },
function(){ alert("UPDATE failed");});
}
if(updateType=='D')
{
oEmpDataModel.remove("/EMPLOYEE_TAB('"+empId+"')", null,
function(){ alert("Delete successful"); },
function(){ alert("Delete failed");});
}
} }));
//Add Cancel button to the panel header
oPanel.addButton(new sap.ui.commons.Button({text: 'Cancel',press :function()
{
oInput_Id.setValue("");
oInput_name.setValue("");
oInput_age.setValue("");
}}));
return oMatrixMain;
}
- Save all files, share, commit and activate the root folder.
- Your application can now be tested at following location
http://host:port/trng/EmpApp/WebContent/index.html
Test Result:
After Saving :
- SAP Managed Tags:
2 Comments
