[Crazy Java] Jdbc:callablestatement Execute Stored Procedure _ Crazy Java

1. The creation of simple stored procedures:

1 The creation of a typical stored procedure in MySQL:

Delimiter
CREATE PROCEDURE Add (a int, b int, out sum int)
begin
	Set sum = a + b;
End

。。 Stored procedures are similar to functions and methods in C/java, with parameter passing, but no return value;

。。 The return value is obtained by an output parameter, which is qualified with the Out keyword before the output parameter is finished, and the desired calculation result can be removed from the output parameter after the stored procedure has been executed.

。。 The knowledge of the specific stored procedure is unknown here;

2. JDBC Executes stored procedures:

1 First, the stored procedure must now be written and compiled in the database. For other applications to call it, after all, stored procedures and packets, indexes, views, etc. are database objects, status is the same, so in the application query a table and call a stored procedure is the same status;

2 to invoke a stored procedure must first obtain a "call the stored procedure handle", it is also a statement, can also be obtained through connection (conn), is callablestatement;

3) Get callablestatement:callablestatement connection.preparecall (String sql);

I can see that the resulting call handle is also precompiled, as long as the method is prepare as a prefix of the precompiled, only the create as a prefix of the expression is directly submitted execution (that is, each commit to compile);

Ii. after all, a stored procedure is a database object kept in a database, so the only SQL statement that can be committed here is the statement that invokes the stored procedure, so the precompiled SQL statement here must be a call statement;

III. The Call statement syntax format for invoking a stored procedure is: {Calling procedure name (parameter 1, Parameter 2 ...)}; Attention. Make sure you put braces on the outside.

IV. Since it is precompiled that allows the use of placeholders, it is to be taken for granted, generally, the formal parameter part of the use of placeholders to replace, later fill it with specific parameters;

V. Example (get a precompiled SQL statement that invokes the stored procedure): CallableStatement cstmt = Conn.preparecall ("{Call Add (?,?,?)}");

4 later can be completed by CallableStatement Setxxx method to fill in placeholders, and Preparestatement setxxx, except that the CallableStatement also provides a formal parameter name to fill in the overloaded version of placeholders:

I. void setxxx (int parameterindex, Xxx x); Normal version

ii. void Setxxx (String parametername, Xxx x); Fill in placeholders based on parameter names

。。 Because the parameter in the stored procedure definition has a name, it is the name to fill in the placeholder;

5 How do I get the value of the output parameter?

I. The value of the incoming input parameter should be taken for granted (and necessary) when the stored procedure is invoked, and the Setxxx method above is the value of the incoming input parameter;

Ii. and output parameters can also be used as input parameters, except when input as input parameters processing, when the stored procedures are calculated after the result will be covered to output parameters output, so output parameters can also be used setxxx transfer value;

Iii. However, the general compliance practice is not to pass output parameters to, that is, call the stored procedure using JDBC not to pass the value of the output parameter (the output parameter is preferably pure when the return value is used), and only after the stored procedure is computed, then take out the value of the output parameter;

Iv. JDBC requires that the CallableStatement object be registered with an output parameter (that is, the advance number of callablestatement who is the output parameter so that it is prepared first), Register by using registeroutparameter: void Callablestatement.registeroutparameter (String parametername | int Parameterindex, SqlType SqlType);

A. The function can locate the output parameter using the parameter name ParameterName or the parametric index Parameterindex (starting from 1);

B. SqlType is the data type of SQL (that is, int, varchar, blob, and so on), so sqltype even if the type of the parameter should be what is in the SQL data type;

C. Here sqltype is just an abstract interface, which should actually be implemented with its implementation class types (in the SQL package), which defines almost all types of standard SQL support, such as Interger, DOUBLE, blob, and so on;

D. Example: Cstmt.registeroutparameter (3, Types.integer); Tell cstmt the third parameter is an output parameter, and its type in SQL is int type

V. When the stored procedure is executed, the GetXXX method can be used to get the value of the output parameter, getxxx and setxxx are corresponding, there are two overloaded versions, one is to specify the parameters according to the index, and the other is to specify the parameter according to the parameter name: XXX callablestatement.getxxx (int Parameterindex | String parametername);

。。 Note: Be sure to get the value of the output parameter using this method, and do not get the value of the input parameter.

6 Execute the stored procedure: directly call the CallableStatement execute method, since it is precompiled inevitable parameterless, and there is no other version (Executeupdate, executequery), Because CallableStatement is specifically used to invoke stored procedures, only the Execute method is one: Boolean Callablestatement.execute ();

3. Example: A complete example of executing a stored procedure

CallableStatement cstmt = Conn.preparecall ("{Call Add_pro (?,?,?)}"); Create a precompiled stored procedure call SQL statement
Cstmt.setint (1, 5);//Parameter index specified, first parameter
cstmt.setint ("B", 6);
Cstmt.registeroutparameter (3, Types.integer); Registers the output parameter and its SQL type
cstmt.execute ();//execute stored procedure
int ret = Cstmt.getint (3);//Get output parameter value after calculation