Using Stored Procedures

For a secure, consistent, and manageable multi- tier client/server system, the data access should allow the use of stored procedures. Stored procedures centralize the business logic in terms of manageability and running the query. Java applets or applications running on clients with limited resources cannot be expected to run huge queries. JDBC allows the use of stored procedures by way of the CallableStatement class.

A CallableStatement object is created by the prepareCall method in the Connection object. The prepareCall method takes a string as the parameter. This string, called an escape clause, is of the form

 { [? =] call <stored procedure name> [<parameter>,<parameter> ...]} 

The CallableStatement class supports parameters. These parameters are of the OUT kind to receive data from a stored procedure or the IN kind to pass values into a stored procedure. The parameter marker (question mark) must be used for the return value (if any) and any output arguments because the parameter marker is bound to a program variable in the stored procedure. In other words, a database stored procedure can receive values through the parameters of a CallableStatement, and may also return results through those same parameters or by a separate return value. Like a PreparedStatement, a question mark identifies the place where parameters are inserted, and in addition can also mark the place where values are returned.

Input arguments can be either literals or parameters. For a dynamic parameterized statement, the escape clause string takes the form

 { [? =] call <stored procedure name> [<?>,<?> ...]} 

If there are any OUT parameters, they must be registered using the registerOutparameter method (see Table 26.5) before the call to the executeQuery, executeUpdate, or execute methods .

Table 26.5. CallableStatement ”OUT Parameter Register Methods

Return Type	Method Name	Parameter
void	registerOutParameter	(int parameterIndex, int sqlType)
void	registerOutParameter	(int parameterIndex, int sqlType, int scale)

When the executeQuery method is called, the escape clause is translated into a form that the database can use to execute the named stored procedure. After the stored procedure is executed, the DBMS returns the result value to the JDBC driver. The return value is accessed by using the methods in Table 26.6.

Table 26.6. CallableStatement Parameter Access Methods

Return Type	Method Name	Parameter
boolean	getBoolean	(int parameterIndex)
byte	getByte	(int parameterIndex)
byte[]	getBytes	(int parameterIndex)
java.sql.Date	getDate	(int parameterIndex)
double	getDouble	(int parameterIndex)
float	getFloat	(int parameterIndex)
int	getInt	(int parameterIndex)
long	getLong	(int parameterIndex)
java.lang.Bignum	getBignum	(int parameterIndex, int scale)
Object	getObject	(int parameterIndex)
short	getShort	(int parameterIndex)
String	getString	(int parameterIndex)
java.sql.Time	getTime	(int parameterIndex)
java.sql.Timestamp	getTimestamp	(int parameterIndex)
Miscellaneous Functions
boolean	wasNull	()

In the JDBC call, you create a CallableStatement object with the ? symbol as a placeholder for parameters, and then connect Java variables to the parameters as shown in Listing 26.17. (This example is not complete or meant to execute.)

Listing 26.17 Example Method Executing a Stored Procedure

 public double getEmployeeStartSalary( Connection connection,  String employeeId ) throws SQLException {   //Create a Callable Statement object.   CallableStatement cStmt = connection.prepareCall( "{ call getEmployeeStartSalary(?,?)} " );   // Set the IN parameter for the stored procedure cstmt.setString(1, employeeId );   // Register the OUT parameter for the stored procedure cStmt.registerOutParameter( 2,java.sql.Types.DOUBLE );   // Now you are ready to call the stored procedure   cStmt.executeQuery();   // Get the OUT parameter from the registered parameter   // Note that you get the result from the CallableStatement object   double salary = cStmt.getDouble( 2 ); return salary; }