Article Source: http://www.yesky.com/399/1855399_2.shtml
The CallableStatement object provides a way for all DBMS to invoke stored procedures in a standard form. Stored procedures are stored in the database. A call to a stored procedure is the content contained in the CallableStatement object. This invocation is written in a code-changing syntax, in two forms: one form with the result parameter and the other with no result argument. The resulting parameter is an output (out) parameter that is the return value of the stored procedure. Both forms can have variable input (in parameters), output (out parameters), or parameters for input and output (INOUT parameters). The question mark will be used as a placeholder for the argument
The syntax for invoking stored procedures in JDBC is shown below. Note that the square brackets indicate that the contents are optional, and that the square brackets themselves are not part of the syntax.
| {Call Procedure name [(?,?, ...)]} |
The syntax for the procedure that returns the result parameter is:
| {? = call Procedure name [(?,?, ...)]} |
Stored procedures with no parameters are similar in syntax:
Typically, the person who creates the CallableStatement object should know that the DBMS used is the one that supports the stored procedure and knows what the process is all about. However, multiple DatabaseMetaData methods can provide such information if a check is required. For example, if the DBMS supports calls to stored procedures, the Supportsstoredprocedures method returns True, and the Getprocedures method returns a description of the stored procedure. CallableStatement inherits Statement methods (which are used to handle generic SQL statements) and inherits PreparedStatement methods (which are used to process in parameters).
All methods defined in CallableStatement are used to process the output part of an out parameter or INOUT parameter: a JDBC type that registers out parameters (a generic SQL type), retrieves a result from these parameters, or checks whether the returned value is JDBC NULL.
1. Create CallableStatement Objects
The CallableStatement object was created using the Connection method Preparecall. The following example creates an instance of CallableStatement that contains a gettestdata call to a stored procedure. The procedure has two variables, but does not contain result parameters:
| CallableStatement cstmt = Con.preparecall ("{Call Gettestdata (?,?)}"); |
Where the placeholder is in, out, or inout parameters, depending on the stored procedure gettestdata.
2, in and out parameters
Passing the in parameter to the CallableStatement object is done through the Setxxx method. This method inherits from PreparedStatement. The type of the incoming parameter determines the Setxxx method used (for example, using SetFloat to pass in a float value, and so on).
If the stored procedure returns an out parameter, the JDBC type of each out parameter must be registered before the CallableStatement object is executed (this is necessary because some DBMS requires a JDBC type). Registering the JDBC type is done using the Registeroutparameter method. After the statement is executed, the CallableStatement GetXXX method retrieves the value of the parameter. The correct getxxx method is the Java type that corresponds to the JDBC type that is registered for each parameter. In other words, Registeroutparameter uses the JDBC type (so it matches the JDBC type returned by the database), and getxxx converts it to a Java type.
As an example, the following code registers the out parameter, executes the stored procedure called by cstmt, and then retrieves the value returned in the Out parameter. Method getbyte a Java byte from the first out parameter, and Getbigdecimal takes a BigDecimal object from the second out parameter (three digits after the decimal point):
CallableStatement cstmt = Con.preparecall ("{Call Gettestdata (?,?)}");
Cstmt.registeroutparameter (1, Java.sql.Types.TINYINT);
Cstmt.registeroutparameter (2, Java.sql.Types.DECIMAL, 3);
Cstmt.executequery ();
byte x = cstmt.getbyte (1);
Java.math.BigDecimal n = cstmt.getbigdecimal (2, 3); |
Unlike ResultSet, CallableStatement does not provide a special mechanism for retrieving large out values in an incremental way.
3, inout parameter
Parameters that support both input and output (the INOUT parameter), in addition to calling the Registeroutparameter method, require that the appropriate Setxxx method be invoked (the method is inherited from PreparedStatement). The Setxxx method sets the parameter value to an input parameter, and the Registeroutparameter method registers its JDBC type as an output parameter. The Setxxx method provides a Java value, and the driver first converts the value to a JDBC value and then sends it to the database. The JDBC type for this in value should be the same as the JDBC type provided to the Registeroutparameter method. Then, to retrieve the output value, it is necessary to use the corresponding GetXXX method. For example, a parameter with a Java type of byte should use method SetByte to assign an input value. Registeroutparameter should be provided with a type TINYINT JDBC type, and getbyte should be used to retrieve output values.
The following example assumes that there is a stored procedure revisetotal, and that its only parameter is the INOUT parameter. Method SetByte This parameter to 25, and the driver will send it to the database as a JDBC TINYINT type. Next, Registeroutparameter registers the parameter as a JDBC TINYINT. After the stored procedure is executed, a new JDBC TINYINT value is returned. Method GetByte will retrieve the new value as a Java byte type.
CallableStatement cstmt = Con.preparecall ("{Call Revisetotal (?)}");
Cstmt.setbyte (1, 25);
Cstmt.registeroutparameter (1, Java.sql.Types.TINYINT);
Cstmt.executeupdate ();
byte x = cstmt.getbyte (1); |
4, the first search results, and then retrieve out parameters
Because of the limitations of some DBMS, for maximum portability, it is recommended that the results generated by the execution of the CallableStatement object be retrieved before the out parameter is retrieved using the Callablestatement.getxxx method. If the CallableStatement object returns multiple ResultSet objects (by calling the Execute method), you should retrieve all the results before retrieving the out parameter. In this case, to ensure that all results are accessed, the Statement method Getresultset, Getupdatecount, and getmoreresults must be invoked until the result is no longer available.
After retrieving all the results, you can use the Callablestatement.getxxx method to retrieve the values in the out parameter.
5. Retrieving null values as Out parameters
The value returned to the out parameter might be JDBC NULL. When this happens, the JDBC NULL value is converted so that the value returned by the GetXXX method is NULL, 0, or false, depending on the GetXXX method type. For ResultSet objects, the only way to know whether 0 or false originates from Jdbcnull is by using method wasnull for detection. If the last value read by the GetXXX method is JDBC NULL, the method returns True, otherwise the flase is returned.
Article Source: http://www.yesky.com/399/1855399_2.shtml
