Creating a Method to Update Employee Data

In the following steps, you will see how to create a method to update employee data in the database:

Open the DataHandler class.

Declare an updateEmployee method as follows:

public String updateEmployee(int employee_id, String first_name,
                             String last_name, String email,
                             String phone_number, String salary,
                             String job_id) throws SQLException {
 
}

Within the body of this method, create an instance of the Employee bean, containing details for the selected employee:
```
Employee oldEmployee = findEmployeeById(employee_id);
```
Connect to the database.
```
getDBConnection();
```

Create a Statement object and specify the ResultSet type as before.

stmt =
  conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
                       ResultSet.CONCUR_READ_ONLY);

Create a StringBuffer to accumulate details of the SQL UPDATE statement that needs to be built:
```
StringBuffer columns = new StringBuffer( 255 );
```
For each field in an employee record, check whether the user has changed the value and if so, add relevant code to the StringBuffer. For each item added after the first one, add a comma to separate the items. The following code checks if the first_name variable changed, and if so, adds details to the SQL in the StringBuffer that will be used to update the database:
```
  if ( first_name != null && 
     !first_name.equals(oldEmployee.getFirstName() ) )
  {
    columns.append( "first_name = '" + first_name + "'" );
  }
```
For the last_name, before appending the new last name, check to see whether there are already some changes in the StringBuffer and if so, append a comma to separate the new change from the previous one. Use the following code:
```
  if ( last_name != null && 
      !last_name.equals(oldEmployee.getLastName() ) ) {
    if ( columns.length() > 0 ) {
      columns.append( ", " );
    }
    columns.append( "last_name = '" + last_name + "'" );
  }
```
Use the same code logic to check for changes made to email, and phone_number.

Note:

Only significant parts of the code are included within this procedure. Example 5-1 contains the complete code for this method.

For the salary field, obtain a String value to add to the StringBuffer as follows:
```
  if ( salary != null && 
      !salary.equals( oldEmployee.getSalary().toString() ) ) {
    if ( columns.length() > 0 ) {
      columns.append( ", " );
    }
    columns.append( "salary = '" + salary + "'" );
```

When the whole set of changes has been assembled, check to see whether there are in fact any changes, that is, whether the StringBuffer contains anything. If so, construct a SQL UPDATE statement using the information in the StringBuffer and execute it. If the StringBuffer does not contain any changes, output a message saying so:

  if ( columns.length() > 0 )
  {
    sqlString = "update Employees SET " + columns.toString() + 
            " WHERE employee_id = " + employee_id;
    System.out.println("\nExecuting: " + sqlString);
    stmt.execute(sqlString);
  }
  else
  {
    System.out.println( "Nothing to do to update Employee Id: " + 
                        employee_id);
  }

Return the word "success".
```
return "success";
```
Save your work and make the file to check there are no syntax errors.

Example 5-1 Method for Updating a Database Record

public String updateEmployee(int employee_id, String first_name,
                             String last_name, String email,
                             String phone_number, String salary,
                             String job_id) throws SQLException {
  
  Employee oldEmployee = findEmployeeById(employee_id);
  getDBConnection();
  stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
                              ResultSet.CONCUR_READ_ONLY);
  
  StringBuffer columns = new StringBuffer( 255 );
  if ( first_name != null && 
     !first_name.equals( oldEmployee.getFirstName() ) )
  {
    columns.append( "first_name = '" + first_name + "'" );
  }
  if ( last_name != null && 
      !last_name.equals( oldEmployee.getLastName() ) ) {
    if ( columns.length() > 0 ) {
      columns.append( ", " );
    }
    columns.append( "last_name = '" + last_name + "'" );
  }
  if ( email != null && 
      !email.equals( oldEmployee.getEmail() ) ) {
    if ( columns.length() > 0 ) {
      columns.append( ", " );
    }
    columns.append( "email = '" + email + "'" );
  }
  if ( phone_number != null && 
      !phone_number.equals( oldEmployee.getPhoneNumber() ) ) {
    if ( columns.length() > 0 ) {
      columns.append( ", " );
    }
    columns.append( "phone_number = '" + phone_number + "'" );
  }
  if ( salary != null && 
      !salary.equals( oldEmployee.getSalary().toString() ) ) {
    if ( columns.length() > 0 ) {
      columns.append( ", " );
    }
    columns.append( "salary = '" + salary + "'" );
  }
  if ( job_id != null && 
      !job_id.equals( oldEmployee.getJobId() ) ) {
    if ( columns.length() > 0 ) {
      columns.append( ", " );
    }
    columns.append( "job_id = '" + job_id + "'" );
  }
  
  if ( columns.length() > 0 )
  {
  sqlString = 
    "UPDATE Employees SET " + columns.toString() + 
      " WHERE employee_id = " + employee_id;
    System.out.println("\nExecuting: " + sqlString);
    stmt.execute(sqlString);
  }
  else
  {
    System.out.println( "Nothing to do to update Employee Id: " + 
                        employee_id);
  }
  return "success";
}

Example 5-1 contains the complete code for this method.