Processing JSON with JQuery

In the previous blog, AJAX with JQuery, I have shown how to use JQuery (a JavaScript library) to implement AJAX. We have also seen how to use XML and JSON with JQuery.

In this blog, I continue using JQuery to develop a page where we first display all jobs that are present in JOBS table of HR schema of Oracle 10g in a ListBox. When user double clicks on a Job in the listbox, we make a request to server and get names of employees (from EMPLOYEES table) belonging to the selected Job.

I have created a Servlet (JobServlet.java) to send JSON from server to client. JQuery on the client is used to process JSON sent from server. The entire page is built using AJAX.

The following is the code for JobServlet.java. It is to be configured with URL pattern /job. It returns JSON containing jobs if action parameter is 1. It sends list of employees by taking jobid that is passed as request parameter.

Here is the screenshot when this page is run.

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.servlet.*;
import javax.servlet.http.*;
public class JobServlet extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/json");
        PrintWriter out = response.getWriter();
        
        try {
            String action = request.getParameter("action");
            if (action.equals("1")) // send jobs to client  {
                out.println(getJobs()); 
            } else {
                out.println(getEmployees(request.getParameter("jobid")));  // send employees of the given job
            }
        } catch (Exception ex) {
            System.out.println("Error message" + ex.getMessage());
        } finally {
            out.close();
        }
    }
    // returns JSON string
    private String getEmployees(String jobid) throws Exception {
        // connect to oracle using thin driver
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "hr", "hr");
        PreparedStatement ps = con.prepareStatement("select first_name || ' ' || last_name fullname from employees where job_id = ?");
        ps.setString(1,jobid);
        ResultSet rs = ps.executeQuery();
        StringBuffer emps = new StringBuffer("{employees:[");
        while (rs.next()) {
            emps.append("{fullname:\"" + rs.getString("fullname") + "\"},");
        }
        emps.setCharAt( emps.length()-1,']');  // replace last character with ]
        emps.append("}");
        rs.close();
        ps.close();
        con.close();
        return emps.toString();
    }
    // returns JSON string containing jobs 
    private String getJobs() throws Exception {
        // connect to oracle using thin driver
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "hr", "hr");
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("select job_id, job_title from jobs");
        StringBuffer jobs = new StringBuffer("{jobs:[");
        while (rs.next()) {
            jobs.append("{id:\"" + rs.getString("job_id") + "\",title:\"" + rs.getString("job_title") + "\"},");
        }
        jobs.setCharAt( jobs.length()-1,']');  // replace last character with ]
        jobs.append("}");
        rs.close();
        st.close();
        con.close();
        return jobs.toString();
    }
}

The format of JSON containing jobs will be as follows:

{jobs:[{id:"AD_PRES",title:"President"},{id:"AD_VP",title:"Administration Vice President"}]}

The format of JSON containing details of employees is as follows:

{employees:[{fullname:"Alexander Hunold"},{fullname:"Bruce Ernst"}]}

The following is the code for JOBS.HTML, which makes an AJAX request on page load to get list of Jobs. It places all job titles in a Listbox. When user double clicks on a job, we send job id to Servlet and get details of Employees who belong to the selected job. These names are displayed in another listbox.

<html>
<head>
<title>Jobs and Employees</title>
<script language="javascript" src="jquery-1.2.6.js"></script>      
<script language="javascript">
// this is done when page is loaded
$(function() {
   $.getJSON("job",{action : "1"},displayJobs);
  }
);
// callback function for obtaining jobs
function displayJobs(data) {
    $.each(data.jobs, function(index,job) {
        // add items to List box
        $("#jobs").append("<option value='" + job.id + "'>" + job.title + "</option");
       }
    );
}
function  getEmployees() {
    $.getJSON("job",{action : "2", jobid : $("#jobs").val()},displayEmployees);
}
// callback function for obtaining employees
function displayEmployees(data) {
  $("#employees").contents().remove();
  $.each(data.employees, function(index,emp) {
    // add items to List box
     $("#employees").append("<option>" + emp.fullname + "</option");
  } // end of function
  );  // each
}
    
</script>
</head>
<body>
<form id="form1">
    <h2>Jobs and Employees</h2>
    <table>
      <tr>
      <td valign="top" width="200px"> <h3>Jobs </h3> 
          <select id="jobs" size="10" ondblclick="getEmployees()" style="width:200px">
          </select>
          <p/>
          <b>Double click on Job to get Employees of that Job.</b>
      </td>
      <td valign ="top" width="200px">
          <h3>Employees </h3>
          <select id="employees" size="10" style="width:200px">
          </select>
      </td>
    </table>
    </form>
</body>
</html>