4.2.3.4.2 Creating the CallableStatement Object
To create a CallableStatement object, you need to use one of methods defined in the Connection class (refer to Table 4.3), prepareCall(). When SQL92 syntax is used to create this CallableStatement object, it will look like:
CallableStatement cstmt = null;
try{
String query = “{call FacultyCourse(?,?)}”; cstmt = con.prepareCall(query);
The operation sequence of this piece of code to create a new CallableStatement object is:
1) A new null CallableStatement object, cstmt, is first declared.
2) A try block is used to create the query string with SQL92 syntax. The name of the stored procedure to be called is FacultyCourse() with two arguments; the first one is an input parameter, faculty _ name, and the second one is an output parameter used to store all course _ id taught by the selected faculty. Both parameters are represented by placeholders, and they are positional parameters.
3) The CallableStatement object is created by calling the prepareCall() method, which belongs to the Connection class, with the query string as the argument.
Next let’s take a look at how to set up the input parameters for this object.
4.2.3.4.3 Setting the Input Parameters
We have provided a very detailed introduction in setting the input parameters for the PreparedStatement object in Section 4.2.3.3.2. Refer to that section to get a more detailed descrip-tion of setting the input parameters for a query string in the CallableStatement object. Figure 4.8
shows a piece of example code to set input parameters for two dynamic parameters, faculty_name and class_name; the data type for both input parameters is String. Therefore, a setString() method is used.
Now let’s take a look at how to register output parameters for a query string when using the CallableStatement object to perform a stored procedure call.
4.2.3.4.4 Registering the Output Parameters
After a CallableStatement interface is executed, you need to use the associated getXXX() method to pick up the run result from the CallableStatement object, since it cannot return any result itself. However, before you can do that, you must first register any output parameter in the Oracle statement to allow the CallableStatement to know that the output result is involved and stored in the related output parameters in the Oracle statement.
Once an output parameter is registered, the parameter is considered an OUT parameter, and it can contain run results that can be picked up by using the associated getXXX() method.
To register an output parameter, the registerOutParameter() method that belongs to the CallableStatement interface should be used to declare what Oracle type the OUT parameter will return. A point to be noted is that a parameter in an Oracle statement can be
defined as both an IN and an OUT at the same time, which means that you can set up the parameter as an IN by using the setXXX() method, and you can also register the parameter as an OUT using the registerOutParameter() method at the same time. In this way, this parameter can be considered an IN/OUT parameter with both input and the output functions.
The syntax to register an output parameter is:
registerOutParameter(int position, data _ type Oracle _ data _ type);where position is still the relative position of the OUT parameter in the Oracle statement, and Oracle _ data _ type is the Oracle data type of the OUT parameter, which can be found from the JDBC API classjava.sql.TYPE. An example of using this method is shown in Figure 4.9.
There are two parameters in the CallableStatement interface in this example. The first one is an IN parameter, which is set by using the setString() method. The second one is an IN/OUT parameter, which is first set up by using the setString() method and then registered by using the registerOutParameter() method with the data type of VARCHAR. The Oracle data type VARCHAR can be mapped to a data type of String in Java.
FIGURE 4.8 A code example for setting input parameters.
FIGURE 4.9 A code example for the registering of output parameters.
FIGURE 4.10 A code example for running the CallableStatement object.
An interesting point to the registerOutParameter() method is that all OUT parame-ters can be registered by using this syntax except those OUT parameters with the NUMERIC and DECIMAL data types. The syntax to register those OUT parameters looks like:
registerOutParameter(int position, data _ type Oracle _ data _ type, int scale);
The only difference is that a third parameter, scale, is added, and it is used to indicate the number of digits to the right of the decimal point for the OUT parameter.