5 Updating Data

5.1.1 Creating a JavaBean in JDeveloper

Employee.java is the JavaBean that is used in the sample application to hold a single employee record and modify its contents. To create a JavaBean, do the following:

1. Right-click the View project, and from the shortcut menu, click New.

2. In the New Gallery dialog box, select the All Technologies tab.

3. Expand the General category and select JavaBeans in the General category. From the Items list, select Bean. Click OK.

4. In the Create Bean dialog box, enter Employee as the name, hr as the package, and ensure that the Extends: field is set to java.lang.Object. Click OK to create the bean.

5. Save the file. The Employee.java file should now contain the following code:

   package hr;
    
   public class Employee {
     public Employee(){
     }
   }
   

5.1.2 Defining the JavaBean Properties and Methods

In the JavaBean, you must create one field for each column in the Employees table, and accessor methods (get and set methods) for each field.

1. Add an import statement for java.sql.Date, which is the field type for one of the fields:

   import java.sql.Date;
   

2. Add a field to the Employee class for each of the columns in the Employees table. Each field is private, and the field types are as follows:

   private Integer employeeId;
     private String firstName;
     private String lastName;
     private String email;
     private String phoneNumber;
     private Date hireDate;
     private String jobId;
     private Double salary;
     private Double commissionPct;
     private Integer departmentId;
   

3. Right-click on the Source Editor page and select Generate Accessors from the shortcut menu. In the Generate Accessors dialog box, select the top-level Employee node. A check mark is displayed for that node and for all the fields. Click OK. Figure 5-1 shows the Generate Accessors dialog box with all the fields selected.

   Figure 5-1 Generate Accessors Dialog Box

   
   Description of "Figure 5-1 Generate Accessors Dialog Box"
   
   

4. Save the file. The Employee.java file should now contain the following code:

   Example 5-1 Skeleton Code for a Basic Java Bean with Accessor Methods

   package hr;
   import java.sql.Date;
    
   public class Employee {
       public Employee() {
       }
       private Integer employeeId;
       private String firstName;
       private String lastName;
       private String email;
       private String phoneNumber;
       private Date hireDate;
       private String jobId;
       private Double salary;
       private Double commissionPct;
       private Integer departmentId;
   
       public void setEmployeeId(Integer employeeId) {
           this.employeeId = employeeId;
       }
    
       public Integer getEmployeeId() {
           return employeeId;
       }
    
       public void setFirstName(String firstName) {
           this.firstName = firstName;
       }
    
       public String getFirstName() {
           return firstName;
       }
   ...
   ...
   ...
   ...
   // This list has been shortened and is not comprehensive. The actual code contains // accessor methods for all the fields declared in the bean.
   
       public void setDepartmentId(Integer departmentId) {
           this.departmentId = departmentId;
       }
    
       public Integer getDepartmentId() {
           return departmentId;
       }
   }
   

5.2.1 Creating a Method to Identify an Employee Record

The method you create in these steps is used to find the record for a particular employee. It is used when a user wants to edit or delete a particular employee record, and selects a link for that employee on the Employee.java page.

1. If the DataHandler class is not already open in the Java Source Editor, double-click it in the Application Navigator to open it.

2. In the DataHandler class, declare a new method that identifies the employee record to be updated:

   public Employee findEmployeeById(int id) throws SQLException {
    
   }
   

3. Within the body of this method, create a new instance of the Employee bean called selectedEmp.

   Employee selectedEmp = new Employee();
   

4. Connect to the database.

   getDBConnection();
   

5. Create a Statement object, define a ResultSet type, and formulate the query. Add a trace message to assist with debugging.

   stmt =
     conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                          ResultSet.CONCUR_READ_ONLY);
   query = "SELECT * FROM Employees WHERE employee_id = " + id;
   System.out.println("\nExecuting: " + query);
   

6. Run the query and use a ResultSet object to contain the result.

   rset = stmt.executeQuery(query);
   

7. Use the result set returned in rset to populate the fields of the employee bean using the set methods of the bean.

   while (rset.next()) {
     selectedEmp.setEmployeeId(new Integer(rset.getInt("employee_id")));
     selectedEmp.setFirstName(rset.getString("first_name"));
     selectedEmp.setLastName(rset.getString("last_name"));
     selectedEmp.setEmail(rset.getString("email"));
     selectedEmp.setPhoneNumber(rset.getString("phone_number"));
     selectedEmp.setHireDate(rset.getDate("hire_date"));
     selectedEmp.setSalary(new Double(rset.getDouble("salary")));
     selectedEmp.setJobId(rset.getString("job_id"));
   }
   

8. Return the populated object.

   return selectedEmp;
   

5.2.2 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:

1. Open the DataHandler class.

2. 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 {
    
   }
   

3. Within the body of this method, create an instance of the Employee bean, containing details for the selected employee:

   Employee oldEmployee = findEmployeeById(employee_id);
   

4. Connect to the database.

   getDBConnection();
   

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

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

6. Create a StringBuffer to accumulate details of the SQL UPDATE statement that needs to be built:

   StringBuffer columns = new StringBuffer( 255 );
   

7. 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 samecode 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-2 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 + "'" );
   

8. 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);
     }
   

9. Return the word "success".

   return "success";
   

10. Save your work and make the file to check there are no syntax errors.

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

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";
}

5.2.3 Adding a Link to Navigate to an Update Page

In the following steps, you add a link to each row of the employees table on the employees.jsp page, that users will click to edit that row.

1. Open employees.jsp in the Visual Editor.

2. Add an extra column to the table that displays employee details. To do this, position the cursor in the last column of the table, right-click and select Table from the shortcut menu, then select Insert Rows Or Columns. In the Insert Rows or Columns dialog box, select Columns and After Selection and click OK.

3. This extra column will contain the link that reads Edit for each row. Each of these links leads to a separate page where the selected employee record can be edited. To do this, double-click the scriptlet that is inside the Employees table, to display the Scriptlet Properties dialog box.

4. Modify the scriptlet to include a link to the edit.jsp page. The modified scriptlet should contain the following code:

   while (rset.next ())
       {
       out.println("<tr>");
         out.println("<td>" + 
         rset.getString("first_name") + "</td><td> " + 
         rset.getString("last_name") + "</td><td> " + 
         rset.getString("email") + "</td><td> " + 
         rset.getString("job_id") + "</td><td>" + 
         rset.getString("phone_number") + "</td><td>" + 
         rset.getDouble("salary") + 
         "</td><td> <a href=\"edit.jsp?empid=" + rset.getInt(1) +
         "\">Edit</a></td>");
       out.println("<tr>");
       }
   

   When the edit link is clicked for any employee, this code passes the employee ID to the edit.jsp page, which will handle the employee record updates. The edit.jsp page will use this to search for the record of that particular employee in the database.

5. Save employees.jsp. Figure 5-2 shows employees.jsp when it is run and displayed in a browser, illustrating the link users can click to edit employee data.

   Figure 5-2 Link to Edit Employees in employees.jsp

   
   Description of "Figure 5-2 Link to Edit Employees in employees.jsp"
   
   

5.2.4 Creating a JSP Page to Edit Employee Data

In this section, you will create the edit.jsp file that allows users to update an employee record.

1. Create a new JSP page and name it edit.jsp. Accept all other defaults.

2. Give the page the same heading as earlier, AnyCo Corporation: HR Application, apply the Heading 2 style to it, and align it to the center of the page.

3. On the next line, type Edit Employee Record, with the Heading 3 style applied. Align this heading to the left of the page.

4. Add the JDeveloper style sheet to the page.

5. Add a jsp:usebean tag. Enter empsbean as the ID, and hr.DataHandler as the Class. Set the Scope to session, and click OK.

6. Position the cursor after the useBean tag and add another jsp:usebean tag. This time enter employee as the ID, browse to select hr.Employee as the class, and leave the Scope as page. Click OK.

7. Add a Scriptlet to the page. The scriptlet code passes the employee ID to the findEmployeeById method and retrieves the data inside the Employee bean. Enter the following code in the Insert Scriptlet dialog box:

   Integer employee_id = new Integer(request.getParameter("empid"));
   employee = empsbean.findEmployeeById(employee_id.intValue());
   

8. Add a Form to the page. In the Insert Form dialog, enter update_action.jsp for the Action field. You cannot select this page from the drop down list as you have not yet created it.

9. Add a Table to the page. Position it inside the Form. Specify a 6-row and 2-column layout, and accept other layout defaults.

10. In the first column, enter the following headings, each on a separate row: First Name, Last Name, Email, Phone, Job, Monthly Salary.

11. Drag a Hidden Field component from the HTML Forms page of the Component Palette. Drop it in the second column, adjacent to the First Name heading. In the Insert Hidden Field dialog, enter employee_id as the Name property and enter <%= employee.getEmployeeId() %> as the Value property.

12. Drag a Text Field component to this column, adjacent to the First Name heading. In the Insert Text Field dialog, enter first_name in the Name field, and <%= employee.getFirstName() %> in the Value field. Click OK.

13. Drag a second Text Field component to this column, adjacent to the Last Name heading. In the Insert Text Field dialog, enter last_name in the Name field, and <%= employee.getLastName() %> in the Value field. Click OK.

14. In a similar way, add text fields adjacent to each of the remaining column headings, using email, phone_number, job_id, and salary as the field names and the corresponding getter method for each field. These are specified in the following table.

15. Add a Submit button in the form, below the table. Enter Update as its Value.

16. Save the application.

    The resultant edit.jsp page should look similar to the page shown in Figure 5-3.

    Figure 5-3 Creating a JSP Page to Edit Employee Details

    
    Description of "Figure 5-3 Creating a JSP Page to Edit Employee Details"
    
    

5.2.5 Creating a JSP Page to Handle an Update Action

In this section, you will see how to create the update_action.jsp file. This page processes the form on the edit.jsp page that allows users to update an employee record. There are no visual elements on this page, this page is used only to process the edit.jsp form and returns control to the employees.jsp file.

1. Create a new JSP page and call it update_action.jsp. Accept all other defaults for the page in the JSP Creation Wizard.

2. Drag a Page Directive component from the JSP page of the Component Palette onto the page. In the Insert Page Directive dialog box, browse to import java.sql.ResultSet. Click OK.

3. Add a jsp:usebean tag. Enter empsbean as the ID, and hr.DataHandler as the Class. Set the Scope to session, and click OK.

4. Add a Scriptlet to the page. Enter the following code into the Insert Scriptlet dialog box:

   Integer employee_id = new Integer(request.getParameter("employee_id"));
   String first_name = request.getParameter("first_name");
   String last_name = request.getParameter("last_name");
   String email = request.getParameter("email");
   String phone_number = request.getParameter("phone_number");
   String salary = request.getParameter("salary");
   String job_id = request.getParameter("job_id");
   empsbean.updateEmployee(employee_id.intValue(), first_name, last_name, email, phone_number, salary, job_id );
   

5. Drag a jsp:forward tag onto the page. In the Insert Forward dialog box, enter employees.jsp for the Page property.

6. Save your work.

7. Run the project and test whether you can edit an employee record. Click Edit for any employee on the employees.jsp page, and you should be directed to the page shown in Figure 5-4. Modify any of the employee details and check whether the change reflects in the employees.jsp page.

Figure 5-4 Editing Employee Data

Description of "Figure 5-4 Editing Employee Data"

5.3.1 Creating a Method to Insert Data

In the following steps, you will create a method for inserting a new employee record.

1. Open DataHandler.java in the Java Source Editor.

2. Declare a method to add a new employee record.

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

3. Add a line to connect to the database.

   getDBConnection();
   

4. Create a Statement object, define a ResultSet type as before, and formulate the SQL statement.

   stmt =
     conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
                          ResultSet.CONCUR_READ_ONLY);
   sqlString =
     "INSERT INTO Employees VALUES (EMPLOYEES_SEQ.nextval, '" + 
     first_name + "','" + 
     last_name + "','" + 
     email + "','" + 
     phone_number + "'," +
     "SYSDATE, '" + 
     job_id + "', " + 
     salary + ",.30,100,80)";
   

   Note:

   The last three columns (Commission, ManagerId, and DepartmentId) contain hard-coded values for the sample application.

5. Add a trace message, and then run the SQL statement.

6. Return a message that says "success" if the insertion was successful.

7. Make the file to check for syntax errors.

Example 5-3 shows the code for the addEmployee() method.

public String addEmployee(String first_name, 
  String last_name, String email, 
  String phone_number, String job_id, int salary) throws SQLException {
  getDBConnection();
  stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
                              ResultSet.CONCUR_READ_ONLY);
  sqlString =
    "INSERT INTO Employees VALUES (EMPLOYEES_SEQ.nextval, '" + 
     first_name + "','" + 
    last_name + "','" + 
    email + "','" + 
    phone_number + "'," +
    "SYSDATE, '" + 
    job_id + "', " + 
    salary + ",.30,100,80)";
    
  System.out.println("\nInserting: " + sqlString);
  stmt.execute(sqlString);
  return "success";
}

5.3.2 Adding a Link to Navigate to an Insert Page

In these steps, you add a link to the header row of the employees table that users can click to add a new employee.

1. Open employees.jsp in the Visual Editor.

2. Drag a Hyper Link component from the HTML Common page of the Component Palette into the empty column header cell at the end of the header row. In the Insert HyperLink dialog box, enter insert.jsp in the HyperLink field, and Insert Employee in the Text field. You cannot browse to find insert.jsp as you have not yet created it. Click OK.

3. Save employees.jsp.

5.3.3 Creating a JSP Page to Enter New Data

In these steps, you create the insert.jsp page, which allows users to enter details of a new employee record.

1. Create a new JSP page and call it insert.jsp.

2. Give the page the same heading as before, AnyCo Corporation: HR Application, and format it as Heading 2, and center it.

3. On the next line enter Insert Employee Record, and apply the Heading 3 format. Align this heading to the left of the page.

4. Add the JDeveloper stylesheet to the page.

5. Add a Form. In the Insert Form dialog box, enter insert_action.jsp for the Action property, and click OK.

6. Add a Table inside the Form. Specify that you want 6 rows and 2 columns and accept all other layout defaults.

7. In the first column, enter the following headings, each on a separate row: First Name, Last Name, Email, Phone, Job, Monthly Salary.

8. Drag and drop a Text Field into the column to the right of the First Name header. In the Insert Field dialog box, type first_name in the Name property.

9. Drag a Text Field next to each of the Last Name, Email, Phone, and Monthly Salary headers. Specify the values for each of these text fields for the Name property in the Insert Field dialog box. The values are indicated in the following table:

   Text Field For	Set the Name Property To
   Last Name	last_name
   Email	email
   Phone	phone_number
   Monthly Salary	salary

   
   

   This procedure is different for the Job row.

10. Drag a Combo Box component from the HTML Forms page of the Component Palette to the column next to the Job heading.

11. In the Insert Select dialog box, enter job_id as the name, and 1 as the size. Click on the add (+) icon and enter SA_REP in the Value field, and in the Caption field, enter Sales Representative. Click on the add(+) sign to add each of the following job titles, then click OK.

    Value	Caption
    HR_REP	HR Representative
    PR_REP	PR Representative
    MK_MAN	Marketing Manager
    SA_MAN	Sales Manager
    FI_MAN	Finance Manager
    IT_PROG	Software Developer
    AD_VIP	Vice President

    
    

12. Drag a Submit button to the Form below the table. In the Insert Submit Button dialog box, enter Add Employee for the Value property.

13. Save your work.

Figure 5-5 shows the insert.jsp page in the Visual Editor.

Figure 5-5 Form to Insert Employee Data

Description of "Figure 5-5 Form to Insert Employee Data"

5.3.4 Creating a JSP Page to Handle an Insert Action

In these steps, you create the insert_action.jsp page. This is a page that processes the form input from insert.jsp, which is the page on which users enter a new employee record. There are no visual elements on this page, and it is only used to process the insert.jsp form and return control to the employees.jsp file.

1. Create a JSP page as before. Call it insert_action.jsp.

2. Add a jsp:usebean tag. As before, enter empsbean as the ID, and hr.DataHandler as the Class. Set the Scope to session, and click OK.

3. Position the cursor after the useBean tag and add a Scriptlet to the page. Enter the following code into the Insert Scriptlet dialog box:

   String first_name = request.getParameter("first_name");
   String last_name = request.getParameter("last_name");
   String email = request.getParameter("email");
   String phone_number = request.getParameter("phone_number");
   String job_id = request.getParameter("job_id");
   Integer salary = new Integer(request.getParameter("salary"));
    
   empsbean.addEmployee(first_name, last_name, email, phone_number, job_id, salary.intValue());
   

4. Drag a jsp:forward tag onto the page. In the Insert Forward dialog box, enter employees.jsp.

5. Save your work.

6. Run the View project to test whether you can insert a new employee record.

To insert an employee, click Insert Employee on the employees.jsp page shown in Figure 5-6.

Figure 5-6 Inserting New Employee Data

Description of "Figure 5-6 Inserting New Employee Data"

Figure 5-7 shows the page where you can insert new employee data with some data filled in, and the list of jobs being used to select a job.

Figure 5-7 Inserting Employee Data

Description of "Figure 5-7 Inserting Employee Data"

5.4.1 Creating a Method for Deleting Data

The method created in the following steps is used to delete employee records by ID:

1. Open DataHandler.java in the Java Source Editor.

2. Declare a new method that identifies the employee record to be deleted:

   public String deleteEmployeeById(int id) throws SQLException {
    
   }
   

3. Connect to the database as before.

   getDBConnection();
   

4. Create a Statement object, define a ResultSet type as before, and formulate the SQL statement. Add a trace message to assist with debugging.

   stmt =
     conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                          ResultSet.CONCUR_READ_ONLY);
   sqlString = "DELETE FROM Employees WHERE employee_id = " + id;
   System.out.println("\nExecuting: " + sqlString);
   

5. Run the SQL statement.

   stmt.execute(sqlString);
   

6. If the SQL statement runs without any errors, return the word, Success.

   return "success";
   

   Example 5-4 shows the code for the deleteEmployeeById() method.

   Example 5-4 Method for Deleting an Employee Record

   public String deleteEmployeeById(int id) throws SQLException {
           getDBConnection();
           stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                   ResultSet.CONCUR_READ_ONLY);
       sqlString = "DELETE FROM Employees WHERE employee_id = " + id;
       System.out.println("\nExecuting: " + sqlString);
       stmt.execute(sqlString);
       return "success";
   }
   

5.4.2 Adding a Link to Delete an Employee

In the following instructions, you add a link to each row of the employees table on the employees.jsp page. Clicking on that link will delete all employee data for that row.

1. Open employees.jsp in the Visual Editor.

2. In the column you created to contain the Edit link, add another link for deleting the row. To do this, double-click the scriptlet that is inside the Employees table, to display the Scriptlet Properties dialog box.

3. Modify the scriptlet to include a link to a delete_action.jsp page. The modified scriptlet should contain the following code:

   while (rset.next ())
       {
       out.println("<tr>");
         out.println("<td>" + 
         rset.getString("first_name") + "</td><td> " + 
         rset.getString("last_name") + "</td><td> " + 
         rset.getString("email") + "</td><td> " + 
         rset.getString("job_id") + "</td><td>" + 
         rset.getString("phone_number") + "</td><td>" + 
         rset.getDouble("salary") + 
         "</td><td> <a href=\"edit.jsp?empid=" + rset.getInt(1) +
         "\">Edit</a>   <a href=\"delete_action.jsp?empid=" + 
         rset.getInt(1) + "\">Delete</a></td>");
       out.println("<tr>");
       }
   

4. Save employees.jsp.

5.4.3 Creating a JSP Page to Handle a Delete Action

In the following steps, you create the delete_action.jsp page, which is a page that only processes the delete operation. There are no visual elements on this page.

1. Create a JSP page and call it delete_action.jsp.

2. Add a jsp:usebean tag. As before, enter empsbean as the ID, and hr.DataHandler as the Class. Set the Scope to session, and click OK.

3. Add a Scriptlet to the page. Enter the following code into the Insert Scriptlet dialog box:

   Integer employee_id = 
    new Integer(request.getParameter("empid"));
   empsbean.deleteEmployeeById(employee_id.intValue());
   

4. Drag Forward from the Component Palette to add a jsp:forward tag to the page. In the Insert Forward dialog box, enter employees.jsp.

5. Save your work.

6. Run the project and try deleting an employee. Figure 5-8 shows the links for deleting employee records from the employees.jsp.

   Figure 5-8 Link for Deleting an Employee from employees.jsp

   
   Description of "Figure 5-8 Link for Deleting an Employee from employees.jsp"
   
   

If you click Delete for any of the employee records, then that employee record will be deleted.

5.5.1 Adding Exception Handling to Java Methods

To handle SQL exceptions in the methods in the sample application, do the following:

1. Ensure that the method throws SQLException. For example, the method:

   public ResultSet getAllEmployees() throws SQLException
   

2. Use try and catch blocks to catch any SQLExceptions. For example, in the getAllEmployees method, enclose your existing code in a try block, and add a catch block as follows:

   public ResultSet getAllEmployees() throws SQLException {
     try {
       getDBConnection();
       stmt =
         conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
                              ResultSet.CONCUR_READ_ONLY);
       sqlString = "SELECT * FROM Employees order by employee_id";
       System.out.println("\nExecuting: " + sqlString);
       rset = stmt.executeQuery(sqlString);
     } 
     catch (SQLException e) {
       e.printStackTrace();
     }
     return rset;
   }
   

3. As another example, the deleteEmployee method rewritten to use try and catch blocks would return "success" only if the method was successful, that is, the return statement is enclosed in the try block. The code could be as follows:

   public String deleteEmployeeById(int id) throws SQLException {
    
       try {
         getDBConnection();
         stmt =
           conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
                                ResultSet.CONCUR_READ_ONLY);
         sqlString = "delete FROM Employees where employee_id = " + id;
         System.out.println("\nExecuting: " + sqlString);
    
         stmt.execute(sqlString);
         return "success";
       }
       catch (SQLException e) {
         e.printStackTrace();
       }
     }
   

5.5.2 Creating a Method for Handling Any SQLException

As a refinement to the code for the sample application, you can create a method that can be used in any method that might throw a SQLException, to handle the exception. As an example, the following method could be called in the catch block of any of the methods in the sample application. This method cycles through all the exceptions that have accumulated, printing a stack trace for each.

public void logException( SQLException ex )
{
  while ( ex != null ) {
    ex.printStackTrace();
    ex = ex.getNextException();
  }
}

In addition, in the catch block, you can return text that explains why the method has failed. The catch block of a method could therefore be written as follows:

catch ( SQLException ex )  {
  logException( ex );
  return "failure";
}

To add this feature to your application:

1. In the DataHandler.java, add a logException method.

2. Edit each of the methods to include try and catch blocks.

3. In the catch block of each method, run the logException method.

4. For methods that have a return value of String, include a return statement to return a message indicating that the method has failed such as:

   return "failure";
   

5.6.1 Creating a Starting Page for an Application

In the following steps, you create the index.jsp page, which will be the default starting page for the application. The page does not include any display elements, and simply forwards the user to the application login page, login.jsp. To do this you use the jsp:forward tag. A jsp:forward tag runs on JSP pages that handle queries and forms, to forward to either the same JSP page again, or another JSP page.

1. Create a new JSP page and call it index.jsp.

2. For the sample application, we will not add any text to this page. From the JSP page of the Component Palette, drag Forward to include a jsp:forward tag in the page.

3. In the Insert Forward dialog box for the forward tag, enter login.jsp as the Page.

You can now specify this new page as the default target for the application as follows:

1. In the Application Navigator, right-click the View project and choose Project Properties.

2. In the displayed tree, select Run/Debug/Profile. In the Run/Debug/Profile area, ensure that Use Project Settings is selected, and in the Run Configurations area, ensure that Default Configurations is selected. Click Edit.

3. In the Edit Launch Settings dialog box, select Launch Settings. In the Launch Settings area on the right, click Browse next to the Default Run Target field and navigate to find the new index.jsp page you just created and click OK. Then click OK again to close the dialog box.

You can now run your application by right-clicking in the View project and select Run from the shortcut menu. The application runs and runs index.jsp, which has been set as the default launch target for the application. The index.jsp forwards you directly to the login page, login.jsp, which is displayed in your browser.