|
 |
|
| |
26.3.1.3. Using CallableStatements to Execute Stored Procedures
Starting with MySQL server version 5.0 when used with
Connector/J 3.1.1 or newer, the
java.sql.CallableStatement interface is
fully implemented with the exception of the
getParameterMetaData() method.
MySQL's stored procedure syntax is documented in the
"Stored
Procedures and Functions" section of the MySQL Reference
Manual.
Connector/J exposes stored procedure functionality through
JDBC's CallableStatement interface.
The following example shows a stored procedure that returns the
value of inOutParam incremented by 1, and the
string passed in via inputParam as a
ResultSet:
Example 26.3. Stored Procedure Example
CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), INOUT inOutParam INT)
BEGIN
DECLARE z INT;
SET z = inOutParam + 1;
SET inOutParam = z;
SELECT inputParam;
SELECT CONCAT('zyxw', inputParam);
END
To use the demoSp procedure with
Connector/J, follow these steps:
-
Prepare the callable statement by using
Connection.prepareCall() .
Notice that you have to use JDBC escape syntax, and that the
parentheses surrounding the parameter placeholders are not
optional:
Example 26.4. Using Connection.prepareCall()
import java.sql.CallableStatement;
...
//
// Prepare a call to the stored procedure 'demoSp'
// with two parameters
//
// Notice the use of JDBC-escape syntax ({call ...})
//
CallableStatement cStmt = conn.prepareCall("{call demoSp(?, ?)}");
cStmt.setString(1, "abcdefg");
Note
Connection.prepareCall() is an
expensive method, due to the metadata retrieval that the
driver performs to support output parameters. For
performance reasons, you should try to minimize
unnecessary calls to
Connection.prepareCall() by reusing
CallableStatement instances in your
code.
-
Register the output parameters (if any exist)
To retrieve the values of output parameters (parameters
specified as OUT or
INOUT when you created the stored
procedure), JDBC requires that they be specified before
statement execution using the various
registerOutputParameter() methods in
the CallableStatement interface:
Example 26.5. Registering Output Parameters
import java.sql.Types;
...
//
// Connector/J supports both named and indexed
// output parameters. You can register output
// parameters using either method, as well
// as retrieve output parameters using either
// method, regardless of what method was
// used to register them.
//
// The following examples show how to use
// the various methods of registering
// output parameters (you should of course
// use only one registration per parameter).
//
//
// Registers the second parameter as output, and
// uses the type 'INTEGER' for values returned from
// getObject()
//
cStmt.registerOutParameter(2, Types.INTEGER);
//
// Registers the named parameter 'inOutParam', and
// uses the type 'INTEGER' for values returned from
// getObject()
//
cStmt.registerOutParameter("inOutParam", Types.INTEGER);
...
-
Set the input parameters (if any exist)
Input and in/out parameters are set as for
PreparedStatement objects. However,
CallableStatement also supports
setting parameters by name:
Example 26.6. Setting CallableStatement Input Parameters
...
//
// Set a parameter by index
//
cStmt.setString(1, "abcdefg");
//
// Alternatively, set a parameter using
// the parameter name
//
cStmt.setString("inputParameter", "abcdefg");
//
// Set the 'in/out' parameter using an index
//
cStmt.setInt(2, 1);
//
// Alternatively, set the 'in/out' parameter
// by name
//
cStmt.setInt("inOutParam", 1);
...
-
Execute the CallableStatement, and
retrieve any result sets or output parameters.
Although CallableStatement supports
calling any of the Statement execute
methods (executeUpdate(),
executeQuery() or
execute()), the most flexible method to
call is execute(), as you do not need
to know ahead of time if the stored procedure returns result
sets:
Example 26.7. Retrieving Results and Output Parameter Values
...
boolean hadResults = cStmt.execute();
//
// Process all returned result sets
//
while (hadResults) {
ResultSet rs = cStmt.getResultSet();
// process result set
...
hadResults = cStmt.getMoreResults();
}
//
// Retrieve output parameters
//
// Connector/J supports both index-based and
// name-based retrieval
//
int outputValue = cStmt.getInt(2); // index-based
outputValue = cStmt.getInt("inOutParam"); // name-based
...
|
|
|
