如何在 JDBC explain 中使用可调用语句调用存储过程?
jdbcjava 8mysqlmysqli database
您可以使用 CallableStatement 接口调用 SQL 存储过程。可调用语句可以具有输入参数、输出参数或两者。
您可以使用 Connection 接口的 prepareCall() 方法创建 CallableStatement(接口)的对象。此方法接受表示调用存储过程的查询的字符串变量并返回 CallableStatement 对象。
假设您在数据库中有一个名为 myProcedure 的过程，您可以准备一个可调用语句:
//Preparing a CallableStatement
CallableStatement cstmt = con.prepareCall("{call myProcedure(?, ?, ?)}");
然后，您可以使用 CallableStatement 接口的 setter 方法将值设置为占位符，并使用 execute() 方法执行可调用语句，如下所示。
cstmt.setString(1, "Raghav"); cstmt.setInt(2, 3000); cstmt.setString(3, "Hyderabad"); cstmt.execute();
如果该过程没有输入值，您可以简单地准备可调用语句并执行它，如下所示:
CallableStatement cstmt = con.prepareCall("{call myProcedure()}");
cstmt.execute();
示例
假设我们在 MySQL 数据库中有一个名为 Dispatches 的表，其中包含以下数据:
+--------------+------------------+------------------+----------------+ | Product_Name | Date_Of_Dispatch | Time_Of_Dispatch | Location | +--------------+------------------+------------------+----------------+ | KeyBoard | 1970-01-19 | 08:51:36 | Hyderabad | | Earphones | 1970-01-19 | 05:54:28 | Vishakhapatnam | | Mouse | 1970-01-19 | 04:26:38 | Vijayawada | +--------------+------------------+------------------+----------------+
如果我们创建了一个名为 myProcedure 的过程来从该表中检索值，如下所示:
Create procedure myProcedure () -> BEGIN -> SELECT * FROM Dispatches; -> END //
示例
以下是使用 JDBC 程序调用上述存储过程的 JDBC 示例。
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class CallingProcedure {
public static void main(String args[]) throws SQLException {
//Registering the Driver
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//Getting the connection
String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//Preparing a CallableStateement
CallableStatement cstmt = con.prepareCall("{call myProcedure()}");
//Retrieving the result
ResultSet rs = cstmt.executeQuery();
while(rs.next()) {
System.out.println("Product Name: "+rs.getString("Product_Name"));
System.out.println("Date of Dispatch: "+rs.getDate("Date_Of_Dispatch"));
System.out.println("Time of Dispatch: "+rs.getTime("Time_Of_Dispatch"));
System.out.println("Location: "+rs.getString("Location"));
System.out.println();
}
}
}
输出
Connection established...... Product Name: KeyBoard Date of Dispatch: 1970-01-19 Time of Dispatch: 08:51:36 Location: Hyderabad Product Name: Earphones Date of Dispatch: 1970-01-19 Time of Dispatch: 05:54:28 Location: Vishakhapatnam Product Name: Mouse Date of Dispatch: 1970-01-19 Time of Dispatch: 04:26:38 Location: Vijayawada
