存储过程是存储在 SQL 目录中的子例程、SQL 语句段。所有可以访问关系数据库的应用程序(Java、Python、PHP 等)都可以访问存储过程。
存储过程包含 IN 和/或 OUT 参数。如果您使用 SELECT 语句,它们可能会返回结果集。存储过程可以返回多个结果集。
从过程中检索结果:
可以使用可调用语句调用现有存储过程。连接接口的 prepareCall() 方法接受字符串格式的过程调用,并返回可调用语句对象。
CallableStatement cstmt = con.prepareCall("{call sampleProcedure()}");
使用执行Query()方法执行上面创建的可调用语句,该方法返回结果集对象。
//Executing the CallableStatement ResultSet rs1 = cstmt.executeQuery();
如果此过程返回更多结果集对象,请使用 cstmt.get 更多结果() 方法移动到下一个结果集。
然后,使用可调用语句接口的 getResultSet() 方法检索下一个结果集。
ResultSet rs2 = cstmt.getResultSet();
例
假设数据库中有一个名为 cricketers_data 的表,其说明如下:
+----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | First_Name | varchar(255) | YES | | NULL | | | Last_Name | varchar(255) | YES | | NULL | | | Year_Of_Birth | date | YES | | NULL | | | Place_Of_Birth | varchar(255) | YES | | NULL | | | Country | varchar(255) | YES | | NULL | | +----------------+--------------+------+-----+---------+-------+
以及一个名为 dispatch_data 的表,其说明如下:
+------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | Product_Name | varchar(255) | YES | | NULL | | | Name_Of_Customer | varchar(255) | YES | | NULL | | | Dispatch_Date | date | YES | | NULL | | | Location | varchar(255) | YES | | NULL | | +------------------+--------------+------+-----+---------+-------+
我们创建了一个名为 sampleProcedure 的过程,它检索这两个表的内容,如下所示:
mysql> DELIMITER // ; mysql> Create procedure sampleProcedure () BEGIN Select * from cricketers_data; Select * from dispatch_data; END// Query OK, 0 rows affected (0.04 sec) mysql> DELIMITER ;
下面的 JDBC 示例建立与数据库的连接,调用名为 sampleProcedure 的过程,检索它返回的结果集,然后打印内容。
import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; public class MultipleResultSetsStoredProcedure { 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/mydatabase"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established......"); //Preparing a CallableStatement to call the retrieveData procedure CallableStatement cstmt = con.prepareCall("{call sampleProcedure()}"); //Executing the CallableStatement ResultSet rs1 = cstmt.executeQuery(); //Displaying the result System.out.println("Contents of the first result-set"); while(rs1.next()) { System.out.print("First Name: "+rs1.getString("First_Name")+", "); System.out.print("Last Name: "+rs1.getString("Last_Name")+", "); System.out.print("Year of Birth: "+rs1.getDate("Year_Of_Birth")+", "); System.out.print("Place of Birth: "+rs1.getString("Place_Of_Birth")+", "); System.out.print("Country: "+rs1.getString("Country")); System.out.println(); } System.out.println(" "); cstmt.getMoreResults(); System.out.println("Contents of the second result-set"); ResultSet rs2 = cstmt.getResultSet(); while(rs2.next()) { System.out.print("Product Name: "+rs2.getString("Product_Name")+", "); System.out.print("Name of Customer: "+rs2.getString("Name_Of_Customer")+", "); System.out.print("Dispatch Date: "+rs2.getDate("Dispatch_Date")+", "); System.out.print("Location: "+rs2.getString("Location")); System.out.println(); } } }
输出
Connection established...... Contents of the first result-set First Name: Shikhar, Last Name: Dhawan, Year of Birth: 1981-12-05, Place of Birth: Delhi, Country: India First Name: Jonathan, Last Name: Trott, Year of Birth: 1981-04-22, Place of Birth: CapeTown, Country: SouthAfrica First Name: Lumara, Last Name: Sangakkara, Year of Birth: 1977-10-27, Place of Birth: Matale, Country: Srilanka First Name: Virat, Last Name: Kohli, Year of Birth: 1988-11-05, Place of Birth: Delhi, Country: India First Name: Rohit, Last Name: Sharma, Year of Birth: 1987-04-30, Place of Birth: Nagpur, Country: India Contents of the second result-set Product Name: KeyBoard, Name of Customer: Amith, Dispatch Date: 1981-12-05, Location: Hyderabad Product Name: Ear phones, Name of Customer: Sumith, Dispatch Date: 1981-04-22, Location: Vishakhapatnam Product Name: Mouse, Name of Customer: Sudha, Dispatch Date: 1988-11-05, Location: Vijayawada