Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use the storage process in MySQL, Xiaobian thinks it is quite practical, so share it for everyone to make a reference, I hope you can gain something after reading this article.

Executing stored procedures using CallableStatements

mysql Version:5.0

Version of Connector/J:3.1.1 +(java.sql.CallableStatement interface fully implemented, except getParameterMetaData() method)

MySQL's stored procedure syntax is in the MySQL Reference Manual chapter "Stored Procedures and Functions."

http://www.mysql.com/doc/en/Stored_Procedures.html

The following is a stored procedure that returns an inOutParam incremented by 1, passing a string parameter inputParam as ResultSet.

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 demoSp with connector/J, there are several steps:

1.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");

The Connection.prepareCall() method is very resource intensive because the jdbc driver supports output parameters through metadata retrieval. For execution efficiency, unnecessary prepareCall calls should be minimized and CallableStatement objects reused.

2. Register output parameters (if any)

To get the values of the output parameters (OUT and INOUT set when the stored procedure was created),JDBC requires that these parameters be set via the registerOutputPrparameter () method before the database operation is performed.

import java.sql.Types;

...

//

//Here are a few ways to set output parameters

//

//Register the second parameter as an output parameter

//

cStmt.registerOutParameter(2);

//

//Register the second parameter as the output parameter, set the type of the return value obtained by getObject to integer

//

cStmt.registerOutParameter(2, Types.INTEGER);

//

//Register parameter named "inOutParam" as output parameter

//

cStmt.registerOutParameter("inOutParam");

//

//Register the parameter named "inOutParam" as the output parameter, and set the type of the return value obtained by getObject to integer.

//

cStmt.registerOutParameter("inOutParam", Types.INTEGER);

3. Set input parameters (if any)

Similar to the parameter setting method of Prepared Statement.

//

//Set the first parameter value

//

cStmt.setString(1, "abcdefg");

//

//Set parameter value according to name

//

cStmt.setString("inputParameter", "abcdefg");

//

//Set input/output parameters according to index value

//

cStmt.setInt(2, 1);

//

//Set input/output parameters according to parameter name

//

//

cStmt.setInt("inOutParam", 1);

...

4. Execute CallableStatement to get result set or output parameters

Although CallableStatement supports all the execution methods of the Statement interface (executeUpdate(),executeQuery(), or execute()), the most scalable method is execute(), because you don't need to know if the stored procedure returns a result set.

...

boolean hadResults = cStmt.execute();

//

//process the returned result set

//

while (hadResults) {

ResultSet rs = cStmt.getResultSet();

//

...

hadResults = cStmt.getMoreResults();

}

//

//Get output parameters, either by index value or parameter name

//

//

int outputValue = cStmt.getInt(1);

outputValue = cStmt.getInt("inOutParam");

About "how to use stored procedures in MySQL" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.