JDBC - CallableStatement

The CallableStatement interface allows the use of SQL statements to call stored procedures. Stored procedures are programs that have a database interface. These programs possess the following:
1)    They can have input and output parameters, or parameters that    are   both input and output.
2)    They can have a return value.
3)    They have the ability to return multiple ResultSets.

Conceptually in JDBC, a stored procedure call is a single call to the database, but the program associated with the stored procedure may process hundreds of database requests. The stored procedure program may also perform a number of other programmatic tasks not typically done with SQL statements.

Creating CallableStatements

The prepareCall method is used to create new CallableStatement objects. As with the prepareStatement method, the SQL statement must be supplied at the time that the CallableStatement object is created. At that time, the SQL statement is precompiled. For example, assuming a Connection object named conn already exists, the following creates a CallableStatement object and completes the preparation phase of getting the SQL statement ready for processing within the database:

    

PreparedStatement ps = conn.prepareStatement("? = CALL ADDEMPLOYEE(?, ?, ?");

Handling parameters

As stated, CallableStatement objects may take three types of parameters:

    IN

    IN parameters are handled in the same manner as PreparedStatements. The various set methods of the inherited PreparedStatement class are used to set the parameters.

    OUT

    OUT parameters are handled with the registerOutParameter method. The most common form of registerOutParameter takes an index parameter as the first parameter and an SQL type as the second parameter. This tells the JDBC driver what to expect for data from the parameter when the statement is processed. There are two other variations on the registerOutParameter method that can be found in the java.sql package Javadoc.

    INOUT

    INOUT parameters require that the work for both IN parameters and OUT parameters be done. For each INOUT parameter, you must call a set method and the registerOutParameter method before the statement can be processed. Failing to set or register any parameter results in an SQLException being thrown when the statement is processed.

Using CallableStatement methods to call stored procedures

To call stored procedures, you invoke methods in the CallableStatement class. The basic steps are:

--->Invoke the Connection.prepareCall method to create a CallableStatementobject.
--->Invoke the CallableStatement.setXXX methods to pass values to the input (IN) parameters.
--->Invoke the CallableStatement.registerOutParameter method to indicate which parameters are output-only (OUT) parameters, or input and output (INOUT) parameters.
--->Invoke one of the following methods to call the stored procedure: 
--->CallableStatement.executeUpdate
--->Invoke this method if the stored procedure does not return result sets.
--->

1. 
   

   --->CallableStatement.executeQuery

   Invoke this method if the stored procedure returns one result set.

   CallableStatement.execute

   Invoke this method if the stored procedure returns multiple result sets.





2. --->If the stored procedure returns result sets, retrieve the result sets. SeeRetrieve multiple result sets from a stored procedure in a JDBC application.




3. --->Invoke the CallableStatement.getXXX methods to retrieve values from the OUT parameters or INOUT parameters.




4. --->Invoke the CallableStatement.close method to close theCallableStatement object when you have finished using that object.

The following code illustrates calling a stored procedure that has one input parameter, four output parameters, and no returned ResultSets. The numbers to the right of selected statements correspond to the previously-described steps.