Search by Employee ID

6 Search by Employee ID

Search by Employee Id searches for a particular employee based on the given employee Id.

The employee ID is the primary key in the EMPLOYEES table. You enter a valid employee ID in the HR Web application and submit to fetch the corresponding employee details from the database. Search an employee using the employee Id.

In this chapter, you learn to add code required to build the Search by Employee ID functionality. You will learn how to:

Declare a new method getEmployee(int) in JavaBean.java.
Implement a new method getEmployee(int) in JavaBeanImpl.java.
Add new code to WebController.java to process the request and response.
Create a HTML page listById.html to display the results.

6.1 Jdbc Java Bean

Declare and implement a new method getEmployee(int) in JBedbcan.java and JdbcBeanImpl.java respectively. This method takes the employee Id as an input parameter and returns an object of type Employee.

Class Name: src/main/java/com/oracle/jdbc/samples/bean/JdbcBean.java

Github Location: JdbcBean.java

Steps to declare the new method:

Open the JdbcBean.java file in IntelliJ. To create the JdbcBean.java class, refer to Creating a Java Bean Interface for a JDBC Connection. Use the same class and declare new methods for each one of the functionalities.
Add the following code snippet to declare the getEmployee(int) method which takes the employee Id as input:
```
public List<Employee> getEmployee(int empId);
```

Class Name: src/main/java/com/oracle/jdbc/samples/bean/JdbcBeanImpl.java

Github Location: JdbcBeanImpl.java

Steps to implement the new method:

Open the JdbcBeanImpl.java file in IntelliJ. To create the JdbcBeanImpl.java class, refer to Creating a Java Bean Implementation for a JDBC Connection. Use the same class and add new implementation methods for each one of the functionalities.

Add the following code snippet to implement the getEmployee(int) method:


public List<Employee> getEmployee(int empId) {
  List<Employee> returnValue = new ArrayList<>();

/*Get the database connection*/
  try (Connection connection = getConnection()) {
/* Insert the SQL statement to fetch an employee using the employee Id */
    try (PreparedStatement preparedStatement = connection.prepareStatement(
        "SELECT Employee_Id, First_Name, Last_Name, Email, Phone_Number, Job_Id, Salary FROM EMPLOYEES WHERE Employee_Id = ?")) {
        preparedStatement.setInt(1, empId);       /* Set the input parameter */
        try (ResultSet resultSet = preparedStatement.executeQuery()) {
        if(resultSet.next()) {    /* Check if the resultSet has any value */
          returnValue.add(new Employee(resultSet)); 
        }
      }
    }
  } catch (SQLException ex) {    /* Catch the SQLException and log the message in logger*/
logger.log(Level.SEVERE, null, ex);
    ex.printStackTrace();
  }

return returnValue;   /* Return the List of Employees */
}

6.2 Add the code to a Servlet to process the request

Add the relevant code to WebController.java to search by employee Id.

Class Name: src/main/java/com/oracle/jdbc/samples/web/WebController.java

Github Location: WebController.java

Steps to add the code:

Open the WebController.java class. To create the WebController.java, refer to Creating a Servlet to Process the Request. Use the same class and add the required code.
Declare a variable ID_KEY to capture the employee id. This is a global variable, hence, declare it outside the method processRequest() but within the WebController class.
```
private static final String ID_KEY = "id";
```
The method processRequest() is already created in the ListAll feature. Now, we add the code to implement Search by employee id functionality. Declare a variable value of the String type to capture the input from the user.
```
String value = null;
```

Add an IF condition to handle the new functionality. Get the employee id entered by the user and invoke the method getEmployee(int) to verify if the employee record exists.

if ((value = request.getParameter(ID_KEY)) != null) {
      int empId = Integer.valueOf(value).intValue();
      employeeList = employeeBean.getEmployee(empId);
   } else { 
      // Previously used getEmployees() method for Listall feature
      employeeList = employeeBean.getEmployees();
   }

6.3 Create a New HTML for Search by Employee Id

A HTML page that shows an input placeholder for the user to enter the employee id. If the employee record is found, then the details of the employee is displayed on the page, otherwise, an error message will be displayed.

Class Name:src/main/webapp/listById.html

Github Location: listById.html

Steps to create the HTML page:

Create the title, stylesheet, and body for the HTML page.

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>List Employee by Id</title>
<!-- Specify the stylesheet here -->
<link rel="stylesheet" type="text/css" href="css/app.css" >
<!-- Bootstrap JS for the UI -->
<link rel="stylesheet" 
href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
</head>

Start the <body> tag and a <input> tag for capturing the employee id.

<body>
<div><label>Employee Id: </label>
<input id="empId" type="textfield"
onkeypress="return waitForEnter(event)"\>
</div>
<br/>
<br/>
<script>
function waitForEnter(e) {
  if (e.keyCode == 13) {
    var tb = document.getElementById("empId");
    fetchElementById(tb.value)
    return false;
  }
}
<script>
var xmlhttp = new XMLHttpRequest();
var url = "WebController";

Define the action when a request is sent, that is, when a link for any one of the functionality is selected.

xmlhttp.onreadystatechange=function() {
     if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
       processResponse(xmlhttp.responseText);
     }
   }
xmlhttp.open("GET", url, true);
xmlhttp.send();

Create the processResponse() function to display the JSON results on HTML page.

function processResponse(response) {
//Process the JSON respnse into an array.
var arr = JSON.parse(response);
     var i;
var out = "<table>";
keys = Object.keys(arr[0]);

// Print Headers
out += "<tr>"
for(i = 0; i < keys.length; ++i) {
out += "<th>"+keys[i]+"</th>"
}
out += "</tr>";
// Print values
for(j = 0; j < arr.length; j++) {
out += "<tr>"
for(i = 0; i < keys.length; ++i) {
out += "<td>"+arr[j][keys[i]]+"</td>"

}
out += "</tr>"
}
out += "</table>";
document.getElementById("id-emp").innerHTML = out;
}