6.3.7.5 Use the Java CallableStatement Method to Query the Faculty Table
The JDBC CallableStatement method provides a way to allow us to call a stored procedure to per-form a complicated query. The speed and efficiency of a data query can be significantly improved by using the stored procedure, since it is built in the database side. An example of using the CallableStatement method to query detailed information for a selected faculty member is provided in the FacultyFrame class with an Oracle stored procedure named FacultyInfo. A more detailed discussion of developing and implementing the CallableStatement method will be given in the next section for the CourseFrame class.
Generally, the sequence to run a CallableStatement to call a stored procedure is:
1) Build and formulate the CallableStatement query string.
2) Create a CallableStatement object.
3) Set the input parameters.
4) Register the output parameters.
5) Execute CallableStatement.
6) Retrieve the run result by using a different getXXX() method.
Let’s discuss this issue in more detail in the following sections.
6.3.7.5.1 Build and Formulate the CallableStatement Query String
The CallableStatement interface is used to execute Oracle stored procedures. The JDBC API pro-vides a stored procedure escape syntax that allows stored procedures to be called in a standard way for all RDBMSs. This escape syntax has one form that includes an output parameter and one that does not. If used, the output parameter must be registered as an OUT parameter. The other param-eters can be used for input, output or both. Parameters are referred to sequentially by number, with the first parameter being 1.
{?= call [,, . . .]} {call [,, . . .]}
Two syntaxes are widely used to formulate a CallableStatement string: the SQL92 syntax and the Oracle syntax. The SQL92 syntax is more popular in most applications. We will concentrate on the SQL92 syntax in this section.
For a stand-alone stored procedure or packaged procedure, the SQL92 syntax can be represented as:
{call [schema.][package.]procedure _ name[(?,?, . . .)]}
For stand-alone or packaged functions, the SQL92 syntax looks like:
{? = call [schema.][package.]function _ name[(?,?, . . .)]}
The meanings of the elements used in these syntaxes are:
• All elements enclosed inside the square brackets [] are optional.
• The curly braces {} are necessary in building a CallableStatement string, and they must be used to enclose the whole string.
• The schema indicates the schema in which the stored procedure is created.
• The package indicates the name of the package if the stored procedure is from it.
• The procedure _ name or function _ name indicates the name of the stored pro-cedure or the function.
• The question mark (?) is a placeholder for either an IN, IN/OUT or OUT parameter used in the stored procedure or the returned value of a function. The order of these placeholders, which starts from 1, is very important, and it must be followed exactly when using either a setXXX() method to set up input parameters or register the output parameters for the built CallableStatement string later.
A CallableStatement can either return a ResultSet object and multiple ResultSet objects by using the executeQuery() method or return nothing by using the execute() method. Multiple ResultSet objects are handled using operations inherited from Statement. A suiTable getXXX() method is needed to pick up the run result from the execution of a CallableStatement.