6.3.8.3 Develop the Code to Perform the CallableStatement Query
Open the Select button click event handler in the CourseFrame Form window, and add the code shown in Figure 6.47 to this event handler.
Let’s have a closer look at this piece of modified code to see how it works.
A. An if selection structure is used to check whether the query method is the Java Callable Method. If it is, a local CallableStatement object, cstmt, is generated.
FIGURE 6.45 The opened Run PL/SQL wizard (Copyrighted by Oracle and used with permission).
FIGURE 6.46 The run result for our FacultyCourse package (Copyrighted by Oracle and used with permission).
B. A try-catch block with SQL92 syntax is used to call the Oracle package, Faculty Course, we built in the last section. The point is that the stored procedure Select FacultyCourse() must be prefixed with the package name when this procedure is called. Two positional parameters are involved in this call. The first one is an input, faculty _ name, and the second is an output, a cursor in which all course _ id values are stored and returned.
C. The first dynamic positional parameter, faculty _ name, which is selected by the user from the Faculty Name combo box, is initialized with a system method, setString().
D. The second parameter, which is an output cursor, is registered by calling the registerOut-Parameter() method. The data type of this parameter is a cursor, and it must be clearly indicated with an Oracle extension Java type, oracle.jdbc.OracleTypes.CURSOR.
E. The CallableStatement method is executed by calling the execute() method to access our Oracle package and stored procedure to perform this course data query.
F. The getObject() method is used to pick up the returned result that is stored in a returned cursor when this CallableStatement is done and assign it to a ResultSet object.
FIGURE 6.47 The added code for the Java Callable Method.
G. A local integer variable, index, and a blank String array, cResult[], are created. The first one is used as a loop number for a while() loop to be executed later to pick up each returned column, and the String array is used to hold each column from the returned cursor. Due to the upperbound of the number of courses taught by each faculty, the dimension of this array is defined as 9.
H. A while() loop is used with the next() method as the loop condition. First, the next() method moves the cursor one step down to point to the current row or a valid row. The getString(1) method is used to pick up the returned column and assign it to one ele-ment in the blank String array cResult[]. Only one column is returned (this is indicated by the index of 1 in the getString() method), and each next() method is used to move and point to the next row until all rows have been selected and picked up.
I. All collected course _ id values are assigned to the Course ID List box by calling the method setListData(), and all of them are displayed there.
J. The catch block is used to monitor and display any possible exception during the project’s run.