Callable Statement
The Callable interface is a subinterface of the PreparedStatement interface. Callable statements are very similar to prepared statements because both use marker parameters. The main difference is that callable statements are used to invoke named stored procedures and stored functions that reside on the database side. The difference between a function and a procedure is that a function is intended to return a value. Both are precompiled code on the database side and can be invoked via a callable statement on the application side.
Syntax of Calling Stored Procedures and Functions
The syntax for calling stored procedures and functions is shown below at (1) and (2), respectively. The parameter list of a stored procedure or function can have any number of marker parameters, but the value returned by a function call is assigned to the first marker on the left-hand side of the = sign.
String procedureCall
= “{ call some_procedure(?,?) }”; // (1) Call a stored procedure.
String functionCall = “? = { call some_function(?) }”; // (2) Call a function.
CallableStatement cStatement1 = connection.prepareCall(procedureCall); // (3)
CallableStatement cStatement2 = connection.prepareCall(functionCall); // (4)
Callable statements are prepared analogous to prepared statements by calling the prepareCall() factory method of the Connection interface, as shown at (3) and (4). The marker parameters in the call must be set up in accordance with the parameters of the stored procedure or function before executing the callable statement.
Three kinds of marker parameters can be specified in a call to a stored procedure or function on the database side: IN, OUT, and INOUT parameters.
IN Parameters
These parameters pass values to the stored procedure or function. These are marker parameters whose values are initialized with the inherited setXXX() methods from the superinterface PreparedStatement. The IN parameters for callable statements are handled exactly as those for prepared statements—a prepared statement has only IN parameters. The parameter index starts at 1. It goes without saying that IN parameters must be initialized before the callable statement is executed.
cStatement1.setString(1, “Hi”); // Set value of IN parameter
// at marker 1
cStatement2.setObject(2, 42, Types.INTEGER); // Set value of IN parameter
// at marker 2
OUT Parameters
These parameters are marker parameters that hold values returned by a stored procedure or a function. Before executing callable statements with OUT parameters, the registerOutputParameter() method of the callable statement should be used to specify the expected types of the values returned in the OUT parameters.
cStatement1.registerOutParameter(2, Types.VARCHAR); // Register SQL VARCHAR as
// type for the
// 2nd marker parameter.
cStatement2.registerOutParameter(1, Types.INTEGER); // Register SQL INTEGER as
// type for the
// 1st marker parameter.
void registerOutParameter(int parameterIndex, int sqlType)
Registers the type of the OUT parameter at the marker parameter index to be of the specified JDBC type. The JDBC type is a constant specified by java.sql.Types—for example, Types.INTEGER and Types.VARCHAR.
Note there can be several OUT parameters, and the first marker parameter of a stored function call must also register the expected type of its return value.
The Callable interface inherits the execute methods of the PreparedStatement interface. The code below executes callable statements.
boolean result1 = cStatement1.execute();
boolean result2 = cStatement2.execute();
After execution, the getXXX() methods of the CallableStatement interface can be used to retrieve OUT parameter values of relevant types, based on the parameter index.
String result1 = cStatement1.getString(2); // Retrieve value of OUT parameter
// at marker 2.
The generic method getObject() can also be used to retrieve OUT parameter values, specifying both an index and the expected Java type of the value (see below).
String result2 = cStatement2.getObject(1, Integer.class);// Retrieve value of OUT
// parameter at marker 1.
Following are selected get methods in the CallableStatement interface to retrieve values of OUT parameters:
String getString(int paramIndex)
Retrieves the value of the designated marker parameter as a String.
int getBoolean(int paramIndex)
Retrieves the value of the designated marker parameter as a boolean.
int getInt(int paramIndex)
Retrieves the value of the designated marker parameter as an int.
double getLong(int paramIndex)
Retrieves the value of the designated marker parameter as a long.
double getDouble(int paramIndex)
Retrieves the value of the designated marker parameter as a double.
BigDecimal getBigDecimal(int paramIndex)
Retrieves the value of the designated marker parameter as a BigDecimal.
Date getDate(int paramIndex)
Retrieves the value of the designated marker parameter as a Date.
<T> T getObject(int paramIndex, Class<T> type)
Retrieves the value of the designated marker parameter as an object of the indicated Java type.
