JDBC for MySQL - Herong's Tutorial Examples - v3.13, by Herong Yang
Overview of CallableStatement Objects
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 another 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);
Table of Contents
JDBC (Java Database Connectivity) Introduction
MySQL JDBC Driver (MySQL Connector/J)
MySQL - Reference Implementation of JdbcRowSet
►MySQL - JBDC CallableStatement
►Overview of CallableStatement Objects
"CREATE PROCEDURE" - Creating a Simple Procedure
Creating Procedures with IN and OUT Parameters
Creating Procedures with INOUT Parameters
Creating Procedures with Multiple Queries
Creating CallableStatement Objects with prepareCall()
Capturing ResultSet with executeQuery()
Creating CallableStatement Objects with Parameters
Common Errors with CallableStatement Parameters
Creating CallableStatement Objects with INOUT Parameters
Retrieving Multiple ResultSet Objects
Executing Stored Procedures without Permission
getProcedures() - Listing Stored Procedures
MySQL CLOB (Character Large Object) - TEXT
MySQL BLOB (Binary Large Object) - BLOB