| Documentation Contents |
NOTE: The material in this chapter is based on JDBCtm API Tutorial and Reference, Second Edition: Universal Data Access for the Javatm 2 Platform, published by Addison Wesley as part of the Java series, ISBN 0-201-43328-1.
A CallableStatement object
provides a way to call stored procedures in a standard way for all
RDBMSs. A stored procedure is stored in a database; the
call to the stored procedure is what a
CallableStatement object contains. This call is
written in an escape syntax that may take one of two forms: one
form with a result parameter, and the other without one. A result
parameter, a kind of OUT parameter, is the return value for the
stored procedure. Both forms may have a variable number of
parameters used for input (IN parameters), output (OUT parameters),
or both (INOUT parameters). A question mark serves as a placeholder
for a parameter.
The syntax for invoking a stored procedure using the JDBC API is shown here. Note that the square brackets indicate that what is between them is optional; they are not themselves part of the syntax.
{call procedure_name[(?, ?, ...)]}
The syntax for a procedure that returns a result parameter is:
{? = call procedure_name[(?, ?, ...)]}
The syntax for a stored procedure with no parameters would look like this:
{call procedure_name}
Normally, anyone creating a
CallableStatement object would already know that the
DBMS being used supports stored procedures and what those
procedures are. If one needed to check, however, various
DatabaseMetaData methods will supply such information.
For instance, the method supportsStoredProcedures will
return true if the DBMS supports stored procedure
calls, and the method getProcedures will return a
description of the stored procedures available.
CallableStatement inherits
Statement methods, which deal with SQL statements in
general, and it also inherits PreparedStatement
methods, which deal with IN parameters. All of the methods defined
in CallableStatement deal with OUT parameters or the
output aspect of INOUT parameters: registering the JDBC types of
the OUT parameters, retrieving values from them, or checking
whether a returned value was JDBC NULL. Whereas the
getXXX methods defined in ResultSet
retrieve values from a result set, the getXXX methods
in CallableStatement retrieve values from the OUT
parameters and/or return value of a stored procedure.
CallableStatement objects are
created with the Connection method
prepareCall. The following example, in which con is an
active JDBC Connection object, creates an instance of
CallableStatement.
CallableStatement cstmt = con.prepareCall(
"{call getTestData(?, ?)}");
The variable cstmt contains a call to the
stored procedure getTestData, which has two input
parameters and no result parameter. Whether the ?
placeholders are IN, OUT, or INOUT parameters depends on the stored
procedure getTestData. This instance of a
CallableStatement object was created using JDBC 1.0
API; consequently, any query in the stored procedure called by
cstmt will produce a default ResultSet object (one
that is non-scrollable and non-updatable).
The JDBC 2.0 API provides the means to
create CallableStatement objects that can produce
ResultSet objects that are scrollable and updatable,
as the following code fragment demonstrates.
String sql = "{call getTestData(?, ?)}";
CallableStatement cstmt2 = con.prepareCall(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
The variable cstmt2 contains the same
call to the stored procedure TestData that cstmt does,
but with cstmt2, any resultSet objects that
TestData produces can be updated and are scrollable
(though they will not be sensitive to updates made while they are
open). Explanations for the constants used to indicate
scrollability and updatability are given in the chapter
"ResultSet."
Passing in any IN parameter values to a
CallableStatement object is done using the
setXXX methods inherited from
PreparedStatement. The type of the value being passed
in determines which setXXX method to use
(setFloat to pass in a float value,
setBoolean to pass in a boolean, and so
on). Of the programs that use parameters, the vast majority use
only IN parameters.
The ability to make batch updates is the
same for CallableStatement objects as it is for
PreparedStatement objects. In fact, a
CallableStatement object is restricted to the same
functionality that a PreparedStatement object has.
More precisely, when using the batch update facility, a
CallableStatement object can call only stored
procedures that take input parameters or no parameters at all.
Further, the stored procedure must return an update count. The
CallableStatement.executeBatch method (inherited from
PreparedStatement) will throw a
BatchUpdateException if the stored procedure returns
anything other than an update count or takes OUT or INOUT
parameters.
The following code fragment illustrates
using the batch update facility to associate two sets of parameters
with a CallableStatement object.
CallableStatement cstmt = con.prepareCall(
"{call updatePrices(?, ?)}");
cstmt.setString(1, "Colombian");
cstmt.setFloat(2, 8.49f);
cstmt.addBatch();
cstmt.setString(1, "Colombian_Decaf");
cstmt.setFloat(2, 9.49f);
cstmt.addBatch();
int [] updateCounts = cstmt.executeBatch();
The variable cstmt contains a call to the
stored procedure updatePrices with two sets of
parameters associated with it. When cstmt is executed, two update
statements will be executed together as a batch: one with the
parameters Colombian and 8.49f, and a
second one with the parameters Colombian_Decaf and
9.49f. An f after a number, as in
8.49f, tells the Java compiler that the value is a
float; otherwise, the compiler assumes that a number
with decimal digits is a double and will not allow it
to be used as a float.
If the stored procedure returns OUT
parameters, the JDBC type of each OUT parameter must be registered
before the CallableStatement object can be executed.
This is necessary because some DBMSs require the SQL type (which
the JDBC type represents), not because JDBC requires it. JDBC
types, a set of generic SQL type identifiers that represent the
most commonly used SQL types, are explained fully in the chapter
"Mapping SQL and Java Types" on page
85.
Registering the JDBC type is done with
the method registerOutParameter. Then after the
statement has been executed, CallableStatement's
getXXX methods can be used to retrieve OUT parameter
values. The correct
CallableStatement.getXXX method to use is
the type in the Java programming language that corresponds to the
JDBC type registered for that parameter. (The standard mapping from
JDBC types to Java types is shown in Table 8.1 on page 105.) In
other words, registerOutParameter uses a JDBC type (so
that it matches the data type that the database will return), and
getXXX casts this to a Java type.
To illustrate, the following code
registers the OUT parameters, executes the stored procedure called
by cstmt, and then retrieves the values returned in the OUT
parameters. The method getByte retrieves a Java
byte from the first OUT parameter, and
getBigDecimal retrieves a
java.math.BigDecimal object (with three digits after
the decimal point) from the second OUT parameter. The method
executeQuery is used to execute cstmt because the
stored procedure that it calls returns a result set.
CallableStatement cstmt = con.prepareCall(
"{call getTestData(?, ?)}");
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
cstmt.registerOutParameter(2, java.sql.Types.DECIMAL, 3);
ResultSet rs = cstmt.executeQuery();
// . . . retrieve result set values with rs.getXXX methods
byte x = cstmt.getByte(1);
java.math.BigDecimal n = cstmt.getBigDecimal(2);
Unlike ResultSet,
CallableStatement does not provide a special mechanism
for retrieving large OUT values incrementally. More specifically,
it does not have getXXX methods for streams of data,
such as getAsciiStream or
getBinary-Stream. However, the JDBC 2.0 API provides
CallableStatement methods for retrieving SQL3
datatypes as OUT or INOUT parameters, which includes the methods
getBlob and getClob for retrieving binary
large objects and character large objects.
When a method takes an int
specifying which parameter to act upon (setXXX,
getXXX, and registerOutParameter), that
int refers to ? placeholder parameters
only, with numbering starting at one. The parameter number does not
refer to literal parameters that might be supplied to a stored
procedure call. For example, the following code fragment
illustrates a stored procedure call with one literal parameter and
one ? parameter:
CallableStatement cstmt = con.prepareCall(
"{call getTestData(25, ?)}");
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
In this code, the first argument to
registerOutParameter, the int
1, refers to the first ? parameter (and
in this case, the only ? parameter). It does not refer
to the literal 25, which is the first parameter to the
stored procedure.
A parameter that supplies input as well as
accepts output (an INOUT parameter) requires a call to the
appropriate setXXX method (inherited from
PreparedStatement) in addition to a call to the method
registerOutParameter. The setXXX method
sets a parameter's value as an input parameter, and the method
registerOutParameter registers its JDBC type as an
output parameter. The setXXX method provides a Java
value that the driver converts to a JDBC value before sending it to
the database. The JDBC type of this IN value and the JDBC type
supplied to the method registerOutParameter should be
the same. Then, to retrieve the output value, a corresponding
getXXX method is used. For example, a parameter whose
Java type is byte should use the method
setByte to assign the input value, should supply a
TINYINT as the JDBC type to
registerOutParameter, and should use
getByte to retrieve the output value. ("Mapping SQL and Java Types" on page
85 contains tables of type mappings.)
The following example assumes that there
is a stored procedure reviseTotal whose only parameter
is an INOUT parameter. The method setByte sets the
parameter to 25, which the driver will send to the
database as a JDBC TINYINT. Next
registerOutParameter registers the parameter as a JDBC
TINYINT. After the stored procedure is executed, a new
JDBC TINYINT value is returned, and the method
getByte will retrieve this new value as a Java
byte. Since the stored procedure called in this
example returns an update count, the method
executeUpdate is used.
CallableStatement cstmt = con.prepareCall(
"{call reviseTotal(?)}");
cstmt.setByte(1, (byte)25);
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
cstmt.executeUpdate();
byte x = cstmt.getByte(1);
Because of limitations imposed by some
DBMSs, it is recommended that for maximum portability, all of the
results in a ResultSet object generated by the
execution of a CallableStatement object should be
retrieved before OUT parameters are retrieved. When all values have
been retrieved from a result set, the method
ResultSet.next will return false.
If a CallableStatement
object returns multiple ResultSet objects (which is
possible only if it is executed with a call to the method
execute), all of the results should be retrieved
before OUT parameters are retrieved. In this case, to be sure that
all results have been accessed, the Statement methods
getResultSet, getUpdateCount, and
getMoreResults need to be called until there are no
more results. When all results have been exhausted, the method
getMoreResults returns false, and the
method getUpdateCount returns -1.
After all values have been retrieved from
ResultSet objects (using ResultSet.getXXX
methods), and after it has been determined that there are no more
update counts, values from OUT parameters can be retrieved (using
CallableStatement.getXXX methods).
The value returned to an OUT parameter may
be JDBC NULL. When this happens, the JDBC
NULL value will be converted so that the value
returned by a getXXX method will be null,
0, or false, depending on the
getXXX method type. As with ResultSet
objects, the only way to know if a value of 0 or
false was originally JDBC NULL is to test
it with the method wasNull, which returns
true if the last value read by a getXXX
method was JDBC NULL, and false
otherwise.
Copyright © 1993, 2010, Oracle and/or its affiliates. All rights reserved. Please send comments using this Feedback page. |
 Java Technology |