6.3.8.1 Import Java Packages and Code for the CourseFrame Constructor
Open the Code Window of the CourseFrame class by clicking on the Source tab from the top of the window and add the code shown in Figure 6.40 into this source file.
Let’s have a closer look at this piece of code to see how it works.
A. Two packages, java.sql.* and javax.swing.*, are added into this file, since we need to use some JDBC API classes and interfaces that are located in these packages.
B. A class-level object, msgDlg, which is an instance of the JDialog class, is created, since we need to use it to display debug and exception information to track and monitor the run status of our project as it runs.
C. A query method is added into the Query Method combo box to enable users to perform a query with it. In this project, we only use one method, Java Callable Method.
D. Eight faculty members are also added into the Faculty Name combo box to allow users to select one of them to query all courses taught by the selected faculty member.
Now let’s build our Java package, FacultyCourse, with the Oracle SQL Developer as we did in the last section for calling the Faculty Table.
6.3.8.2 Create an Oracle Package, FacultyCourse
When a faculty member has been selected from the Faculty Name combo box and the Select button is clicked by the user, all courses (course _ id) taught by the selected faculty member should be displayed in the Course ID List listbox. As we know, there is no faculty _ name column available in the Course Table; instead, the only connection between each course and the faculty member who teaches that course is the faculty _ id, which is a foreign key in the Course Table. Therefore, in order to get the course _ id that is taught by the selected faculty member, two queries are needed:
1) First, we need to perform a query to the Faculty Table to get the faculty _ id based on the selected faculty name.
2) Second, we need to perform another query to the Course Table to get all course _ id values based on the faculty _ id obtained from the first query.
FIGURE 6.41 The finished new package wizard.
To save time and space, a good solution for these two queries is to combine both of them into a stored procedure. As you know, stored procedures are developed and built inside a database. The execution speed and efficiency of stored procedures can be significantly better than those of a nor-mal query. In the JDBC API, a CallableStatement interface is used for this purpose.