7.4.2 Build and Run the Project to Test the Data Deletion
Now click on the Clean and Build Main Project button from the toolbar to build our project. Then click on the Run Main Project button to run the project.
Enter a suiTable username and password, such as jhenry and test, to complete the login process and select the Faculty Information from the SelectFrame window to open the FacultyFrame window. Make sure that the Runtime Object Method has been selected from the Query Method combo box. Then click on the Select button to query the default faculty information. The default faculty information is displayed.
To test the data deletion function, we can try to delete one faculty member, James Carson, which was inserted into our database in Section 7.2.7. To do that, select this faculty member from the Faculty Name combo box, and click on the Delete button. Immediately, you can see that this faculty name has been removed from the Faculty Name combo box. Also, the run result is shown in the Output window, as shown in Figure 7.15.
To confirm this data deletion, one can open the Faculty Name combo box, and there is no faculty member named James Carson in there. Another way to do this validation is to click on the Back and the Exit button to stop our project. Then open our Faculty Table by going to the Services window and expand the Databases node; our connection URL; and finally our sample data-base, CSE _ DEPT. Right-click on the Faculty Table and select the View Data item from the popup menu to open it. In the opened Faculty Table, you can see that the faculty member James Carson has been removed from the Table.
However, the story is not finished. If an original faculty member, such as Ying Bai, is deleted, not only is that faculty record deleted from the Faculty Table, but other records related to that deleted faculty member located in the child Tables, such as LogIn, Course and StudentCourse, will also be deleted due to the cascading delete option we selected when we built this database. Therefore, we need to recover all of those deleted records to keep our sample database in good shape.
FIGURE 7.15 The successful data deletion message.
In order to recover this deleted faculty member and related records in our Faculty, LogIn, Course and StudentCourse Tables, refer to Tables 7.1~7.4.
An easy way to do this recovery is to use the Oracle SQL Developer. One can select and copy data from each Table (Tables 7.1~7.4) and paste it into the bottom line of each opened Table with Oracle SQL Developer.
Three points to be noted when recovering the data are:
1) The order to perform the row recovery. The faculty record in the parent Table must be recovered first. In our case, the record in the Faculty Table must be recovered first, since it is a parent Table. The records in the child Tables can be recovered after the record in the parent Table has been completed. Otherwise, an error may be encountered.
2) To recover the deleted faculty member Ying Bai, one can run this project again to insert it back into our sample database based on data shown in Table 7.1 by using the Insert button.
3) Then one can perform the recovery job by adding all deleted records into the other Tables by using the Oracle SQL Developer based on the data in Tables 7.2~7.4.
4) When you finish your recovery operations for all the rows, click File > Save All in Oracle SQL Developer to save all the additions.
As we discussed in Section 6.3.3.5 in Chapter 6, in addition to using the executeUpdate() method to perform data manipulations such as data insert, update and delete actions, one can use the execute() method to perform the similar data manipulations. It is preferred to leave this optional method as homework and allow students to handle this issue.
A complete sample project, OracleDeleteFaculty, can be found in the folder Class DB Projects\Chapter 7 that is located in the Students folder on the CRC Press ftp site (refer to Figure 1.2 in Chapter 1).
Next let’s take care of the data manipulations against the Oracle database using the Java UpdaTable ResultSet method.
