1. HTML page in which a script is embedded using angular js script which sends sql string as post object to JSP page which parses the SQL string to generate data in json format along with table column and then http service in angular js is used to retrieve data generate by jsp engine.
2. JSP page parses the sql string received post method and open with recordset object and writes to the page with out.print in json format. This data written in the page is retrieved by http service in angular js.
I am writing the jsp program first
<%@ page import = "java.sql.*" %>
<% /* Class.forName is only required post 11g Oracle version, 11g and prior 11g you need to
download ojdbc.jar depending on oracle version, then you have to omit Class.forName function
call.*/
Class.forName("oracle.jdbc.driver.OracleDriver");
/* DriverManager creating connection object, didn't put it into try block but you can do so. Format
for connection string is {jdbc:oracle:thin:@[ipaddress or hostname]:oracle_sid" ,
"user_name",""password"}. */
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.109:1521:orcl",
"HR","HR");
/* request.getParameter reads from post object sqlStr which is actually an input element in the
html page consisting of the sql statement */
String sql=request.getParameter("sqlStr");
try
{
/* Statement object need to parse sql statement and return the cursor */
Statement stmt = conn.createStatement();
/* Resultset (recordset) object used to create cursor object */
ResultSet rs = stmt.executeQuery(sql);
/* ResultSetMetaData object is used to retrieve column name and table name and number of
columns from parse recordset object using getMetaData function. Since we are attempting to
make table columns dynamic and not be hard coded that's why we are using metadata. */
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
/* JSON data starts here */
out.print("[");
rs.next();
/* Outer loop for each row. */
while(true)
{ /* Inner row for each column */
for(int i=1;i<=numberOfColumns;i++)
out.print("{\"" + rsMetaData.getColumnName(i) + "\",\"" +
rs.getString(rsMetaData.getColumnName(i)) + "\"}");
/* This if statement serves two purpose, first one is it's takes cursor to the next record and it's
also check whether it is not at EOF, if not it prints a comma and a newline, as you know
each row in json data is in curly braces and separated by comma. i.e.
{"Name":"Simon","Age":"26"}, and if reaches EOF prints the closing bracket of the array
and exists loop */
if(rs.next())
out.print(",\n");
else
{
out.print("]");
break;
}
}
/* Object closed and released.*/ stmt.close();
rs.close();
stmt=null;
rs=null;
}
catch(SQLException e)
{
out.println("SQL Error encountered "+e.getMessage());
}
conn.close();
conn=null;
%>
The next one is the html code which send request to the jsp engine by invoking the url and get the response object anddisplays the data in respective views using controller.
<html ng-app="myApp">
<head>
<meta charset="utf-8">
<title>Angular.js example</title>
<script src= "http://ajax.googleapis.com/ajax/libs/angularjs/1.2.26/angular.min.js"></script>
<script>
myApp=angular.module('myApp',[]);
angular.module('myApp',[])
.controller('angController',function($scope,$http) {
$http.get("ora_export_json.php")
.success(function(response) {
$scope.allinfo = response;
});
});
</script>
</head>
<body ng-controller="angController">
Search : <input type=text ng-model="SearchField" />
<table border=1>
<tr>
<th><a href="" ng-click="sortField = 'emp_code'">Emp Code</a></th>
<th><a href="" ng-click="sortField = 'emp_name'">Emp Name</th>
<th><a href="" ng-click="sortField = 'emp_grade'">Emp Grade</a></th>
<th><a href="" ng-click="sortField = 'dob'">Date of Birth</th>
</tr>
<tr ng-repeat="perinfo in allinfo | filter:SearchField | orderBy:sortField">
<td>{{perinfo.emp_code}}</td>
<td>{{perinfo.emp_name}}</td>
<td>{{perinfo.emp_grade}}</td>
<td>{{perinfo.dob}}</td>
</tr>
</table>
</body>
</html>
No comments:
Post a Comment