1. 2 Day + Java Developer's Guide
  2. Update an Employee Record

7 Update an Employee Record

The Update functionality modifies an employee record in the database according to the user edits on the web page.

First, you must search for an employee in the records. Once you retrieve the information related to the employee, you will find the Edit button to modify details related to the employee. Search an employee by first name and update the details of employee.

In this chapter, you learn to add code required to build the Update functionality. You will learn how to:

  1. Declare a new method getEmployeeByFn(String) in JavaBean.java.
  2. Declare a new method updateEmployee(int) in JavaBean.java.
  3. Implement a new method getEmployeeByFn(String) in JavaBeanImpl.java.
  4. Implement a new method updateEmployee(int) in JavaBeanImpl.java.
  5. Add new code to WebController.java to process the request and response.
  6. Create a HTML page listByName.html to display the results.

Note:

The hradmin user has the privilege to update an employee record. The hrstaff user does not have the privilege to update an employee record.

7.1 Declare a new method getEmployeeByFn(String) in EmployeeBean.java

To modify the details of an employee, the hradmin must first search for the employee based on his/her first name. The getEmployeeByFn(String) method searches employees based on their first name.

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

Github Location: EmployeeBean.java

Steps to declare the new method:

  1. 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.
  2. Declare a method getEmployeeByFn(String) that takes first name as an input parameter. 
    public List<Employee> getEmployeeByFn(String fn);

7.2 Declare a new method updateEmployee(Employee)

The updateEmployee(Employee) method updates the attributes of an employee such as first name, last name, salary, job_id and so on.

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

Github Location: EmployeeBean.java

Steps to declare a new method:

  1. 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.
  2. Declare a method updateEmployee(Employee) that takes Employee object as an input parameter.
    public Employee updateEmployee(int empId);

7.3 Implement a New Method getEmployeebyFn() for Search by Employee name

The getEmployeeByFn(String) method takes the employee id as the input parameter and returns an object of type Employee.

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

Github Location: EmployeeBeanImpl.java

Steps to implement the method:

  1. 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.
  2. Add the following code snippet to implement the getEmployeeByFn(String) method:
    
public List<Employee> getEmployeeByFn(String fn) {
/* Declare an array to store the returned employee list */
  List<Employee> returnValue = new ArrayList<>();   

/* Get the database connection */
  try (Connection connection = getConnection()) {
/* Insert the SQL statement to fetch an employee using the employee first name */
try (PreparedStatement preparedStatement = connection.prepareStatement(
"SELECT Employee_Id, First_Name, Last_Name, Email, Phone_Number, Job_Id, Salary FROM EMPLOYEES WHERE First_Name LIKE ?")) {
      /* Set the input parameter as the first name */
      preparedStatement.setString(1, fn + '%');   
      try (ResultSet resultSet = preparedStatement.executeQuery()) {
while(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 the list of employees from the method */
return returnValue;   
}

7.4 Implement a new method updateEmployee(Employee)

The updateEmployee(Employee) method enables you to update the employee details such as first_name, last_name, and so on in the employee record.

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

Github Location: EmployeeBeanImpl.java

Steps to Implement a new method:

  1. 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.
  2. Add the following code snippet to implement the updateEmployee(Employee) method:
    
public String updateEmployee(Employee employee) throws SQLException {
  /*Declare and initialize a variable to capture the number of records updated*/
  int updateCount = 0;  

/* Get the database connection*/
  try (Connection connection = getConnection()) {
    try (PreparedStatement preparedStatement = connection.prepareStatement(
    /* Insert the SQL statement to select an employee based on the employee id */
      "UPDATE employees SET FIRST_NAME = ?, LAST_NAME = ?, EMAIL = ?, PHONE_NUMBER = ?,
         SALARY = ? WHERE EMPLOYEE_ID = ?")) {
        /*Set the new values entered by the user for each attribute 
           and execute the prepapredStatement */
              preparedStatement.setString(1, employee.getFirst_Name());
              preparedStatement.setString(2, employee.getLast_Name());
              preparedStatement.setString(3, employee.getEmail());
              preparedStatement.setString(4, employee.getPhone_Number());
              preparedStatement.setInt(5, employee.getSalary());
              preparedStatement.setInt(6, employee.getEmployee_Id());
              updateCount = preparedStatement.executeUpdate();
      }
   }catch (SQLException ex) {   /* Catch the SQLException and log the message in the logger*/
      logger.log(Level.SEVERE, "Unable to update record", ex);
      throw new SQLException("Alert! Record could not be updated, "+ex.getMessage(), ex);
    }

/* Log the message with the number of records updated to the logger */
   logger.fine("Update count: " +updateCount);
/* If none of the records were updated, enter an alert message */
   if (updateCount != 1) {     
     logger.severe("Unable to update record");
     throw new SQLException("Alert! Record could not be updated");
  }
/* Return the success message if the record was updated */
  return "Success: Record updated";  
}

7.5 Add the code to a Servlet to process the request

Add the relevant code to WebController.java to update an employee.

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

Github Location: WebController.java

Steps to add the code:

  1. 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.
  2. Declare a variable FN_KEY to capture first name of the employee. This is a global variable, hence, declare it outside the method processRequest() but within the WebController class.
    private static final String FN_KEY = "firstName";
  3. The method processRequest() is already created in the ListAll feature. Now, we add the code to implement Update an Employee functionality. Add an ELSEIF 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);
   } 
/* New code added below */
else if ((value = request.getParameter(FN_KEY)) != null) {
  employeeList = jdbcBean.getEmployeeByFn(value);
}
else { 
      /* Previously used getEmployees() method for Listall feature */
      employeeList = employeeBean.getEmployees();
   }

7.6 Create a New HTML for Search by Employee Id

A HTML page that shows an input placeholder for the user to enter the employee first name. 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: listByName.html

Steps to create the HTML page:

  1. 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">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.2/jquery.min.js"></script>
</head>
  2. Start the <body> tag and a <input> tag for capturing the employee id. 
    <body>
<div><label>Employee First Name: </label>
  <input id="firstName" type="textfield"
         onkeypress="return waitForEnter(event)"\> wildcard % is included at the end automatically.</div>
<br/>
<br/>
<div id="id-emp"></div>
<div id="UpdateButton">  <button type="button" class="btn btn-info btn-lg" 
  onclick='javascipt:confirmUpdate()'>Update Record</button> <button type="button" 
  class="btn btn-default btn-lg" onclick='javascipt:cancelUpdate()'>Cancel</button>
</div>
  3. Define the action when a request is sent, that is, when a link for any one of the functionality is selected. 
    
$('#UpdateButton').hide();
// keys;

function waitForEnter(e) {
  if (e.keyCode == 13) {
    fetchElement($("#firstName").val());
    return false;
  }
}

function fetchElement(firstName) {
  var xmlhttp = new XMLHttpRequest();
  var url = "WebController?firstName=" +firstName;

  xmlhttp.onreadystatechange=function() {
    if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
      processResponse(xmlhttp.responseText);
    }
  }

  xmlhttp.open("GET", url, true);
  xmlhttp.send();
}
  4. Create the processResponse() function to display the JSON results on HTML page.
    
function processResponse(response) {
  var arr = JSON.parse(response);
  if (arr == null || arr.length == 0) {
    out = '<div class="alert alert-warning"><strong>Alert!</strong>'
     +' No records found for the given Fist_Name</div>'
  }
  else {
    var i;
    var out = "<table>";
    // keys is global so that it can be used later as well
    keys = Object.keys(arr[0]);

    // Print headers
    out += "<tr><th>Trash</th><th>Edit</th>"

    for(i = 0; i < keys.length; ++i) {
      out += "<th>"+keys[i]+"</th>"
    }
    out += "</tr>";

    // Print values
    for(j = 0; j < arr.length; j++) {
      pk = arr[j][keys[0]];
          out += '<tr><td><a href="javascript:confirmDelete(\'' +pk +'\')">'
          +'<span  class="glyphicon glyphicon-trash"></span>'
          +'</a></td>'
          +'<td><a href="javascript:allowEditSalary(\'' +pk +'\')">'
          +'<span class="glyphicon glyphicon-edit"></span>'
          +'</a></td>';
      // 0 is the primary key
      for(i = 0; i < keys.length; ++i) {
        // creating an id to each column
        out += "<td id='" +pk +'_' +keys[i] +"'> "+arr[j][keys[i]]+"</td>";
      }
      out += "</tr>"
    }
    out += "</table>";
  }
  $('#id-emp').html(out);

}
  5. Add the allowEditSalary(pk) function to make the field names editable once the employee record is displayed.
    
function allowEditSalary(pk) {
  // If the edit button is pressed already
  if(typeof currentPK != 'undefined' && currentPK == pk) {
    console.log('Make column readonly');
    for(i = 1; i < keys.length; ++i) {
      var x = '#' +pk +"_" +keys[i];
      var value = $(x).text().trim();
      console.log(value);
      $(x).val(value);
    }
    $('#UpdateButton').hide();
    currentPK = '';
  }
  else{
    currentPK = pk;
    for(i = 1; i < keys.length; ++i) {
      var x = '#' +pk +"_" +keys[i];
      var value = $(x).text().trim();
      $(x).html("<input type='text' value='" +value +"' \>");
    }
    $('#UpdateButton').show();
  }
}
  6. Add the confirmUpdate() and cancelUpdate() functions to define the confirm and cancel actions respectively.
    
function confirmUpdate() {
  var res = confirm("Do you really want to Update");
  if(res == true) {
    console.log("Udating record");
      $('#UpdateButton').hide();
  }
  else {
    console.log("Record not updated");
  }
}

function cancelUpdate() {
   if(typeof currentPK != 'undefined') {
    console.log('Make column readonly');
    for(i = 1; i < keys.length; ++i) {
      var x = '#' +pk +"_" +keys[i];
      var value = $(x).text().trim();
      console.log("cancelUpdate: " +value);
      $(x).text(value);
    }
    $('#UpdateButton').hide();
    currentPK = '';
  }
}