Displaying Database Table Contents Dynamically using SAPUI5(SAP PORTAL APPLICATION)
In this blog, I want to explain you how you can display dynamic table content and table layout from database table with SAPUI5. But I will not share java code, i will only share json data which i prepare in java code. First, we’ll read the table names and fill into the combobox; then, we’ll show the table content of the selected table name from combobox.
We have Servlet that returns table names and table content as json format.
- Table Names json :
{"tables":[{"id":"-1","name":"Select Table"},{"id":"ZDTY_CUSTOMER","name":"ZDTY_CUSTOMER"},{"id":"ZDTY_PERSON","name":"ZDTY_PERSON"},{"id":"ZHVL_REQUIREMENT","name":"ZHVL_REQUIREMENT"},{"id":"Z_IZINTALEPLERI","name":"Z_IZINTALEPLERI"},{"id":"Z_IZIN_FORM","name":"Z_IZIN_FORM"},{"id":"Z_IZIN_FORM_TABLE","name":"Z_IZIN_FORM_TABLE"},{"id":"Z_USERINFO","name":"Z_USERINFO"},{"id":"Z_USER_DETAIL","name":"Z_USER_DETAIL"},{"id":"Z_USER_INFO","name":"Z_USER_INFO"},{"id":"Z_USER_INFO_2","name":"Z_USER_INFO_2"},{"id":"Z_USER_INFO_3","name":"Z_USER_INFO_3"}]}- Table Content json :
{"message":"Success","content":[{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"1"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"2"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"3"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"4"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"5"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"6"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"7"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"8"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"9"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"10"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"11"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"12"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"13"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"14"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"15"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"16"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"17"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"18"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"19"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"20"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"21"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"22"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"23"},{"CUSTNAME":"deneme1","CUSTTEL":"deneme2","CUSTID":"24"},{"CUSTNAME":"Deneme","CUSTTEL":"Deneme","CUSTID":"25"},{"CUSTNAME":"Deneme","CUSTTEL":"Deneme","CUSTID":"26"},{"CUSTNAME":"Deneme","CUSTTEL":"Deneme","CUSTID":"27"},{"CUSTNAME":"Deneme","CUSTTEL":"Deneme","CUSTID":"28"},{"CUSTNAME":"Deneme","CUSTTEL":"Deneme","CUSTID":"29"},{"CUSTNAME":"Deneme","CUSTTEL":"Deneme","CUSTID":"30"},{"CUSTNAME":"Deneme","CUSTTEL":"Deneme","CUSTID":"31"},{"CUSTNAME":"Deneme","CUSTTEL":"Deneme","CUSTID":"32"},{"CUSTNAME":"Deneme","CUSTTEL":"Deneme","CUSTID":"33"},{"CUSTNAME":"Deneme","CUSTTEL":"Deneme","CUSTID":"34"},{"CUSTNAME":"Deneme","CUSTTEL":"Deneme","CUSTID":"35"},{"CUSTNAME":"Deneme","CUSTTEL":"Deneme","CUSTID":"36"}],"columns":[{"name":"CUSTID"},{"name":"CUSTNAME"},{"name":"CUSTTEL"}],"tname":"ZDTY_CUSTOMER"}
We have index.jsp and our code is here :
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>SAPUI 5</title>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<script src='/sapui5latest/resources/sap-ui-core.js'
type="text/javascript"
id="sap-ui-bootstrap"
data-sap-ui-theme="sap_bluecrystal"
data-sap-ui-libs="sap.ui.commons,sap.ui.table,sap.m" >
</script>
<script>
var tablesModel = new sap.ui.model.json.JSONModel("/irj/servlet/prt/portal/prtroot/demo.sap.com~ui5~omer~tables.ReadTableServlet");
var tableContent = new sap.ui.model.json.JSONModel();
jQuery.sap.registerModulePath("ui5code", "/demo.sap.com~ui5~omer~tables/ui5code");
var app = new sap.m.App("myApp");
app.setInitialPage("idEklePage");
var view = sap.ui.view(
{ id:"idEklePage",
viewName:"ui5code.appview",
type:sap.ui.core.mvc.ViewType.JS
});
view.placeAt("area");
</script>
</head>
<body>
<div id="area"></div>
</body>
</html>
We have two javascript file (view and controller files) appview.view.js and appview.controller.js. They are in ui5code folder.
appview.view.js file code :
sap.ui.jsview("ui5code.appview", {
/** 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 ui5desk.appview
*/
getControllerName : function() {
return "ui5code.appview";
},
/** 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 ui5desk.appview
*/
createContent : function(oController) {
var mainFormLayout = new sap.ui.commons.layout.MatrixLayout();
mainFormLayout.setLayoutFixed(false);
var selectionLayout = new sap.ui.commons.layout.MatrixLayout();
selectionLayout.setLayoutFixed(false);
var sel_tables = new sap.m.Select("tablesId", {
change : oController.getContent
});
sel_tables.setModel(tablesModel);
var item_sel_table = new sap.ui.core.Item({ key: "{id}", text: "{name}" });
sel_tables.bindItems("/tables", item_sel_table);
sel_tables.setSelectedKey(-1);
selectionLayout.createRow(
new sap.m.Label({ text: "Table Name", width: "220px", required: true }),
sel_tables);
mainFormLayout.createRow(selectionLayout);
var table = new sap.m.Table("idTable",{
growing: true,
growingThreshold: 5,
headerToolbar : new sap.m.Toolbar({
content : [ new sap.m.Label({
text : "{/tname}"
})
]
}),
columns : [ new sap.m.Column({
width : "2em",
header : new sap.m.Label({
text : "Column Name"
})
})
]
});
table.setModel(tableContent);
mainFormLayout.createRow(table);
return mainFormLayout;
}
});
appview.controller.js File code:
sap.ui.controller("ui5code.appview", {
/**
* Called when a controller is instantiated and its View controls (if available) are already created.
* Can be used to modify the View before it is displayed, to bind event handlers and do other one-time initialization.
* @memberOf ui5desk.appview
*/
onInit: function() {
},
getContent : function(oEvent) {
var table = sap.ui.getCore().byId("idTable");
table.removeAllColumns();
var colitems = new sap.m.ColumnListItem();
var tablename = oEvent.getParameters().selectedItem.getText();
$.ajax({
type: 'get',
url: '/irj/servlet/prt/portal/prtroot/demo.sap.com~ui5~omer~tables.ReadTableServlet',
dataType: 'json',
data: {
tablename: tablename
},
success: function(data) {
tableContent.setData(data);
for(var i = 0 ; i < data.columns.length ; i++)
{
var column = new sap.m.Column({
header : new sap.m.Label({
text : data.columns[i].name
})
});
table.addColumn(column);
colitems.addCell(new sap.m.Text({
text : '{'+data.columns[i].name+'}'
}));
}
table.bindItems("/content", colitems);
},
error: function(data) {
alert('fail');
}
});
}
/**
* Similar to onAfterRendering, but this hook is invoked before the controller's View is re-rendered
* (NOT before the first rendering! onInit() is used for that one!).
* @memberOf ui5desk.appview
*/
// onBeforeRendering: function() {
//
// },
/**
* Called when the View has been rendered (so its HTML is part of the document). Post-rendering manipulations of the HTML could be done here.
* This hook is the same one that SAPUI5 controls get after being rendered.
* @memberOf ui5desk.appview
*/
// onAfterRendering: function() {
//
// },
/**
* Called when the Controller is destroyed. Use this one to free resources and finalize activities.
* @memberOf ui5desk.appview
*/
// onExit: function() {
//
// }
});
Then we deploy the project to portal. And we can see result as below :
Hi Ömer, you did here awesome job, very well defined guide you have prepared and released to the community.
Many thanks,
Hi Omer,
I am trying to create a similar application. I am able to get the contents from the table. But deploying it in the ui5 application is where i am facing issues. Did you create two different projects ? As in Dynamic web project and UI5 application project ?
If yes then, can you explain the project structure and also the structure of the path you gave for the model ?
Thanks in advance
Hi,
I create SAP Portal Application. You can create Dynamic web project also. Why do you create two different projects?
As the standard libraries loaded are different for different projects. I have two projects.
1. Dynamic Web project. (Here I am fetching the data from MySQL)
2. SAP UI5 Application project. (here I am creating all the views)
I am new to UI5 and SAP, so trying to figure out everything from scratch.
I think you don't use SAP Portal. Did you use javascript in web technologies before?
yes. I have used.
In the project I am currently working on, I need to develop a UI5 application with MySQL as backend. So, I am trying to bind the json model of the view to the servlet. Can I do that ?
did you prepare json data as in this blog? you have to prepare json data in servlet and call from web page or controller.js
Yes, I have prepared the json data. Since the servlet is existing in a different project, I am not able to figure out how to include/call the servlet in the controller.js file which resides in ui5 application project.
in my controller you can see "getContent " function. there is an ajax call. you can use it with your servlet url
Hi Omar,
I have a small question. For binding the model, what is path I have to give ?
Hi Ömer, what a pitty I have missed this great posting. Many thanks.
[Private Information removed by Moderator]