Ajax Call in JSP to fetch and display data from Database

We knew that with the help of JQuery we can dynamically display data on a webpage. So here in this post we are using  JQuery to make an Ajax call to Servlet, [Servlet connects to DB, fetches data and returns that data to Ajax call] and display the returned data as a table on JSP web page.

Create Servlet first

Create dataServlet.java

package model;

import java.io.IOException;
import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.util.HashMap;
import java.util.Map;

import javax.servlet.*;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;

import org.eclipse.persistence.oxm.json.JsonArrayBuilderResult;
import org.eclipse.persistence.oxm.json.JsonGeneratorResult;
import org.eclipse.persistence.oxm.json.JsonObjectBuilderResult;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

@WebServlet(name = "dataServlet", urlPatterns = { "/dataservlet" })
public class dataServlet extends HttpServlet {
private static final String CONTENT_TYPE = "text/html; charset=windows-1252";

public void init(ServletConfig config) throws ServletException {
super.init(config);
}

public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
System.out.println("enterred servlet...");
String empID = "";
try {
empID = request.getParameter("empID");
System.out.println("empID is:" + empID);
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr");
PreparedStatement ps = con.prepareStatement("select * from emp where empid != ? order by empid");
ps.setString(1, empID);
ResultSet rs = ps.executeQuery();
System.out.println("got response....");

JSONArray json = new JSONArray();
ResultSetMetaData metadata = rs.getMetaData();
int numColumns = metadata.getColumnCount();

while (rs.next()) //iterate rows
{
JSONObject obj = new JSONObject(); //extends HashMap
for (int i = 1; i <= numColumns; ++i) //iterate columns
{
String column_name = metadata.getColumnName(i);
obj.put(column_name, rs.getObject(column_name));
System.out.println("rs.getObject('" + column_name + "')........." + rs.getObject(column_name));
}
json.add(obj);
System.out.println("Added JSON object to JSON Array..");

}

response.setContentType("application/json;charset=UTF-8");
String jsonString = json.toJSONString();
System.out.println("jsonString:" + jsonString);
response.getWriter().write(jsonString);

Map<String, Object> jsonMap = new HashMap<String, Object>();
jsonMap.put("Status", "Success");
jsonMap.put("Rows", 100);

con.close();
} catch (Exception e) {
e.printStackTrace();
}

}
}

 

Create empData.jsp

  <!DOCTYPE html>
<%@ page contentType="text/html;charset=windows-1252"%>
<html>
<head>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js"></script>
         <script type="text/javascript">
          $.ajax( {
              type : "Get", url : "dataservlet", contentType : "application/json", dataType : 'json', data :  {
                  "empID" : "31"
              },
              success : function (data) {
                  $("#mytbl").append('

<tr id="headerRow"></tr>

');
                 /* In below function you can take data[any row number] data[0].
                    (a, b) are (key, value) for 0th json object. as we are shoiwng only Keys so we used only 'a' below.
                 */
                  $.each(data[0], function (a, b) {
                      $("#headerRow").append('

<td style="padding:10px;background:orange;color:white;">' + a + '</td>

');
                  });
                 /* now we have to display data [multiple row with multiple column data].
                 * Here you have to observe one thing is we have multiple rows of data [multiple JSON objects in a JSON Array &
                 * Each JSON object contains multiple key value pairs]. So we must loop through each row and and then loop through each column[key value].
                 * function $.each(data, function(a, b) will get all JSON objects inside JSON Array, where 'a' indicates JSON object number.
                 * 'b' indicates the 'JSON object'. similarly
                 * function $.each(data[a], function(c, d) will get 'a'th JSON object, And here 'c' indicates Key where as 'd' indicates value.
                 * */
                  $.each(data, function (a, b) {
                      $("#mytbl").append('

<tr id="' + a + '"></tr>

');
                      var dataRowId = '#' + a;
                      $.each(data[a], function (c, d) {
                          $(dataRowId).append('

<td style="padding:10px;background:green;color:white;">' + d + '</td>

');
                      });
                  });
              },
              failure : function () {
                  alert("error...");
              }
          });
        </script>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>first</title>
</head>
<body>
<?audit suppress oracle.ide.xml.mismatched-end-tag?>
<table id='mytbl'></table>
<div style="background:Green;width:100%;padding:5px 0px;margin:10px 0px; ">
<p style="color:#fff;margin-left:10px;"> copyrights @teja.com</p>

</div>
</body>
</html>

 

Data in the DB is like below:

1

JSON Array that the servlet returns to Ajax call will be like below.

JSON_Array.png

The JSP page will look like below.

2

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s