4.2.3.4 Using the CallableStatement Object
As we discussed in the early part of this chapter, CallableStatement is a subclass of both Statement and PreparedStatement, and this interface is mainly used to call stored pro-cedures to perform group data actions. The JDBC CallableStatement method provides a way to perform a complicated query. The speed and efficiency of a data query can be significantly improved by using the stored procedure, since it is built on the database side.
The difference between a PreparedStatement and a CallableStatement interface is: unlike the PreparedStatement interface, the CallableStatement interface has both input and output parameters, which are indicated with IN and OUT keywords, respec-tively. In order to set up values for input parameters or get values from output parameters, you have to use either a setXXX() method inherited from the PreparedStatement or a getXXX() method. However, the point is that before you can use any getXXX() method to pick up the values of output parameters, you must first register the output parameters to allow the CallableStatement interface to recognize them.
Generally, the sequence to run a CallableStatement to perform a stored procedure is:
1) Build a 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 these steps one by one in more detail in the following sections.
4.2.3.4.1 Building a CallableStatement Query String
The CallableStatement interface is used to execute Oracle stored procedures. The JDBC API provides 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 parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1.
{?= call <procedure-name>[<arg1>,<arg2>, . . .]} {call <procedure-name>[<arg1>,<arg2>, . . .]}
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 and take care of the Oracle syntax later when we build data queries for the Oracle database.
For a stand-alone stored procedure or packaged procedure, the SQL92 syntax can be represented
as:
{call [schema.][package.]procedure _ name[(?,?, . . .)]}
For stand-alone functions or packaged functions, the SQL92 syntax looks like:
{? = call [schema.][package.]function _ name[(?,?, . . .)]}
The definition and meaning 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 involved in a package.
- The procedure_name or the function_name indicates the name of the stored procedure or the function.
- The question mark,?, is the 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 the Statement inter-face. A suiTable getXXX() method is needed to pick up the run result of a CallableStatement.
Now that we have built a CallableStatement query string, next we need to create a CallableStatement object to execute the associated method to run stored procedures.