資料結構
網路
關係資料庫管理系統
作業系統
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 語言程式設計
C++
C#
MongoDB
MySQL
Javascript
PHP
物理學
化學
生物學
數學
英語
經濟學
心理學
社會學
時尚研究
法律研究如何使用 JDBC 程式從儲存過程中檢索多個 ResultSets？
儲存過程是子程式，儲存在 SQL 目錄中的 SQL 語句片段。所有可以訪問關係資料庫（Java、Python、PHP 等）的應用程式都可以訪問儲存過程。
儲存過程包含 IN 和 OUT 引數或兩者兼而有之。如果使用 SELECT 語句，它們可能會返回結果集。儲存過程可以返回多個結果集。
從過程中檢索結果
您可以使用 CallableStatement 呼叫現有的儲存過程。Connection 介面的 prepareCall() 方法接受字串格式的過程呼叫並返回一個可呼叫語句物件。
CallableStatement cstmt = con.prepareCall("{call sampleProcedure()}");使用 executeQuery() 方法執行上面建立的可呼叫語句，這將返回一個結果集物件。
//Executing the CallableStatement ResultSet rs1 = cstmt.executeQuery();
如果此過程返回多個結果集物件，請使用 cstmt.getMoreResults() 方法移動到下一個結果集。
然後，使用 CallableStatement 介面的 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:///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
更新於: 2019-07-30
6K+ 次檢視
廣告