Table of Contents Previous Next


7 Advanced JDBC Connector Functionality : 7.3 Executing Stored Procedures

The CallableStatement class provides a way for a Java program to call stored procedures. A CallableStatement object can have a variable number of parameters used for input (IN parameters), output (OUT parameters), or both (IN OUT parameters).
Each question mark serves as a placeholder for a parameter. The stored procedure determines if the placeholders represent IN, OUT, or IN OUT parameters and the Java code must match. We will show you how to supply values for IN (or IN OUT) parameters and how to retrieve values returned in OUT (or IN OUT) parameters in a moment.
Listing 1.7-a shows a stored procedure that increases the salary of each employee by 10%. increaseSalary expects no arguments from the caller and does not return any information:
To invoke a stored procedure from a Java application, use a CallableStatement object. The CallableStatement class is derived from the Statement class and, like the Statement class, you obtain a CallableStatement object by asking a Connection object to create one for you. To create a CallableStatement from a Connection, use the prepareCall() method:
As the name implies, the prepareCall() method prepares the statement, but does not execute it. As you will see in the next example, an application typically binds parameter values between the call to prepareCall() and the call to execute(). To invoke the stored procedure on the server, call the execute() method.
This stored procedure (increaseSalary) did not expect any IN parameters and did not return any information to the caller (using OUT parameters) so invoking the procedure is simply a matter of creating a CallableStatement object and then calling that object’s execute() method.
The code in the next example first creates and then invokes a stored procedure named empInsert; empInsert requires IN parameters that contain employee information: empno, ename, job, sal, comm, deptno, and mgr. empInsert then inserts that information into the emp table.
Each placeholder (?) in the command (commandText) represents a point in the command that is later replaced with data:
The setObject() method binds a value to an IN or IN OUT placeholder. Each call to setObject() specifies a parameter number and a value to bind to that parameter:
The next example creates and invokes an SPL stored procedure called deptSelect. This procedure requires one IN parameter (department number) and returns two OUT parameters (the department name and location) corresponding to the department number. The code in Listing 1.9-a creates the deptSelect procedure:
Each placeholder (?) in the command (commandText) represents a point in the command that is later replaced with data:
The setObject() method binds a value to an IN or IN OUT placeholder. When calling setObject() you must identify a placeholder (by its ordinal number) and provide a value to substitute in place of that placeholder:
The JDBC type of each OUT parameter must be registered before the CallableStatement object can be executed. Registering the JDBC type is done with the registerOutParameter() method.
After executing the statement, the CallableStatement’s getter method retrieves the OUT parameter values: to retrieve a VARCHAR value, use the getString() getter method.
In the current example GetDeptInfo() registers two OUT parameters and (after executing the stored procedure) retrieves the values returned in the OUT parameters. Since both OUT parameters are defined as VARCHAR values, GetDeptInfo() uses the getString() method to retrieve the OUT parameters.
The code in the next example creates and invokes a stored procedure named empQuery defined with one IN parameter (p_deptno), two IN OUT parameters (p_empno and p_ename) and three OUT parameters (p_job, p_hiredate and p_sal). empQuery then returns information about the employee in the two IN OUT parameters and three OUT parameters.
Listing 1.10-b demonstrates invoking the empQuery procedure, providing values for the IN parameters, and handling the OUT and IN OUT parameters:
Each placeholder (?) in the command (commandText) represents a point in the command that is later replaced with data:
The setInt() method is a type-specific setter method that binds an Integer value to an IN or IN OUT placeholder. The call to setInt() specifies a parameter number and provides a value to substitute in place of that placeholder:
The setString() method binds a String value to an IN or IN OUT placeholder:
Before executing the CallableStatement, you must register the JDBC type of each OUT parameter by calling the registerOutParameter() method.
Remember, before calling a procedure with an IN parameter, you must assign a value to that parameter with a setter method. Before calling a procedure with an OUT parameter, you register the type of that parameter; then you can retrieve the value returned by calling a getter method. When calling a procedure that defines an IN OUT parameter, you must perform all three actions:

7 Advanced JDBC Connector Functionality : 7.3 Executing Stored Procedures

Table of Contents Previous Next