This section provides overview information on CallableStatement Objects for stored procedures.
JDBC CallableStatement objects are designed to represent calls to stored procedures in the database server.
Several things you need to remember about CallableStatement objects:
1. A CallableStatement object is created by calling the prepareCall() method on a Connection object like:
// Calling a procedure with no procedure parameters
CallableStatement cs1 = con.prepareCall("CALL Abc()");
// Calling a procedure with some literal parameters
CallableStatement cs2 = con.prepareCall("CALL Abc(v1, v2, ...)");
2. Place holders (question marks "?") can be used in call statements in the same way as in prepared statements. OUT (output) parameters, INOUT (input and output)
parameters, and return parameter must be represented by place holders:
// Calling a procedure with place-holder parameters
CallableStatement cs3 = con.prepareCall(
"CALL Abc(v1, v2, ?, ?, ...)");
// Calling a procedure with a return value
CallableStatement cs4 = con.prepareCall(
"? = CALL Abc(v1, v2, ?, ?, ...)");
The last format "? = CALL Abc(v1, v2, ?, ?, ...)" does not apply to MySQL, since
MySQL procedures are not allowed to return values.
3. If an IN parameter is represented by a place holder, its value should be provided by calling the setXXX() method before the execution of the
call statement:
// providing a string value to the first place holder
cs3.setString(1, "North Pole");
// providing a double value to the second place holder
cs3.setDouble(2, 3.14);
4. Place holders for OUT, INOUT and return parameters must registered as output parameters with JDBC data types before the execution of the call statement:
// Registering the first place holder as a INTEGER output
cs4.registerOutParameter(1, java.sql.Types.INTEGER);
// Registering the fourth place holder as a DATE output
cs4.registerOutParameter(4, java.sql.Types.DECIMAL, 3);
5. If no ResultSet objects is returned from the stored procedure, the call statement should be executed by calling the cs.executeUpdate() method.
// Executing the call statement
cs1.executeUpdate();
6. If one or more ResultSet objects are returned from the store procedure, the call statement should be executed by calling the cs.executeQuery() method.
It will return the first ResultSet object, representing the output of the first query statement executed in the stored procedure:
// Executing the call statement
ResultSet res = cs1.executeQuery();
7. A CallableStatement can also be executed in batch mode similar to a PreparedStatement:
// Close one copy of the call statement
cs3.setXXX(...);
cs3.addBatch();
// Close onather copy of the call statement
cs3.setXXX(...);
cs3.addBatch();
// Execute all copies of the call statement in batch mode
cs3.executeBatch();
8. After the execution of the call statement, all rows of all returning ResultSet objects should be processed before retrieving
any registered output parameters.
// Executing the call statement
ResultSet res = cs1.executeQuery();
while (res.next()) {
...
}
9. After finishing all ResultSet objects, registered output parameters can be retrieved by using the getXXX() methods:
// Retrieving registered output parameters
int first = cs4.getInt(1);
Date four = cs4.getDate(4);
