In a few posts a few months back I have tried to retrieve data from Oracle database 12c through JSP and using $http.get retrieve that data to view in table format. Now what about inserting, updating or deleting data from oracle database. It's a wonder how much powerful angularjs is, data retrieving and page rendering is so fast. In the section below both the jsp code and angularjs code is provided. You can run the jsp file alone with parameters and see the result which is actually retrieved by $http.get.
All you need to do is create a table called emp with two fields emp_code and emp_name. And on the connection string change the hostname / ipaddress,
1. Table emp
create table emp
(
emp_code number not null,
emp_name varchar2(100) not null
);
2. test.html
<html>
<script src= ""http://ajax.googleapis.com/ajax/libs/angularjs/1.2.26/angular.min.js"></script>
<script src="src/app.js">
</script>
<body ng-app="app"> <!-- ng-app on BODY scope -->
<div id="divCtrl" ng-controller="MyCtrl"> <!-- ng-controller on div scope -->
<!-- ng-model empCode and empName is used for http get and post -->
<input type="text" name="empCode" ng-model="empCode" value="" />
<input type="text" name="empName" ng-model="empName" value="" /><br/>
<!-- Two buttons having ng-click each calling addData and updateData. updateData updates the record with emp_code value in where condition-->
<input type="button" value="Add" ng-click="addData()" /><br/>
<input type="button" value="Update" ng-click="updateData()" />
<table border=1>
<tr><th>Emp Code</th><th>Emp Name</th>
<tr ng-repeat="emp in empArr">
<!-- Remove eclosed in <a> tag having ng-click calling dalateData to delete the current row from the view as well from the database -->
<td>{{emp.EMP_CODE}}</td><td>{{emp.EMP_NAME}}</td><td>
<a href="" ng-click="deleteData(emp)">Remove</a></td>
</tr>
</table>
</div>
</body>
</html>
3. app.js
app = angular.module('app',[]);
app.controller('MyCtrl',function($scope,$http) {
refreshData();
/* This function is called from ng-click in button "Add" which adds data in table EMP and updates the view */
$scope.addData = function() {
$http.post("insertUpdateDelete.jsp?empCode=" + $scope.empCode + "&empName=" +
$scope.empName + "&dmlType=Ins")
.success(function(response) {
refreshData();
});
/* dmlType checks the type of DML transaction to perform, this prevents from creating separate JSP files for insert, update and delete */
}
/* This function is called from ng-click in button "Update" which updates data in table EMP and updates the view */ $scope.updateData = function() {
$http.post("insertUpdateDelete.jsp?empCode=" + $scope.empCode + "&empName=" +
$scope.empName + "&dmlType=Upd")
.success(function(response) {
refreshData();
}); }
/* This function is called from the link Remove in the third column of HTML table which adds data in table EMP and updates the view */
$scope.deleteData = function(curData) {
$http.post("insertUpdateDelete.jsp?empCode=" + curData.EMP_CODE +
"&empName=" + curData.EMP_NAME + "&dmlType=Del")
.success(function(response) {
$scope.empArr.splice($scope.empArr.indexOf(curData),1);
refreshData();
}); }
/* This function gets fresh data from table and updates the view */
/* get_oracle_data.jsp is explained in Oracle 12c data generator in JSON by dynamic sql using JSP */
function refreshData() {
$http.get("get_oracle_data.jsp?sqlStr=select * from emp")
.success(function(response) {
$scope.empArr = response;
})
.error(function(response) {
alert("");
$scope.empArr={};
}); }
});
4. insertUpdateDelete.jsp
/* This section is not explained here check this url http://techgigsonline.blogspot.in/2015/01/oracle-12c-data-generator-in-json-by.html for jsp oracle tutorial*/
<%@ page import = "java.sql.*" %>
<% Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.100.1:1521:orcl",
"username","password");
String dml_type = request.getParameter("dmlType");
String emp_code = request.getParameter("empCode");
String emp_name = request.getParameter("empName");
String sql="";
if("Ins".equals(dml_type))
sql = "insert into emp values(" + emp_code + ",'" + emp_name + "')";
else if("Del".equals(dml_type))
sql = "delete from emp where emp_code = " + emp_code + " and emp_name = '" + emp_name + "'";
else if("Upd".equals(dml_type))
sql = "update emp set emp_name = '" + emp_name + "' where emp_code = " + emp_code;
out.println(dml_type);
try
{
Statement stmt = con.createStatement();
stmt.executeQuery(sql);
}
catch(SQLException e)
{
out.print("SQL Error encountered " + dml_type + "," + e.getMessage());
}
con.close();
con=null;
%>
