使用預存程序讀取大型資料範例
此 Microsoft JDBC Driver for SQL Server 範例應用程式將示範如何從預存程序中擷取大型 OUT 參數。
此範例的程式碼檔案名稱為 ExecuteStoredProcedure.java，可在下列位置找到：
\<installation directory>\sqljdbc_<version>\<language>\samples\adaptive
需求
若要執行此應用程式範例，您必須存取 AdventureWorks2022 範例資料庫。 請將 classpath 設定為包含 mssql-jdbc jar 檔案。 如需如何設定 classpath 的詳細資訊，請參閱使用 JDBC 驅動程式。
注意
適用於 SQL Server 的 Microsoft JDBC 驅動程式提供 mssql-jdbc 類別庫檔案，可根據您慣用的 Java Runtime Environment (JRE) 設定使用。 如需選擇哪個 JAR 檔案的詳細資訊，請參閱 JDBC Driver 的系統需求。
該範例會在 AdventureWorks2022 範例資料庫中建立必要的預存程序：
範例
此程式碼範例：
- 連線到 AdventureWorks2022 資料庫。
- 藉由使用參數化查詢來建立範例資料並且更新
Production.Document資料表。 最後，範例程式碼會使用 SQLServerStatement 類別的 getResponseBuffering 方法來取得適應性緩衝模式，並且執行GetLargeDataValue預存程序。 從 JDBC 驅動程式 2.0 版開始，responseBuffering連線屬性預設為 "adaptive"。
最後，範例程式碼會顯示利用 OUT 參數傳回的資料，同時示範如何在資料流上使用 mark 和 reset 方法來重新讀取任何部分的資料。
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import com.microsoft.sqlserver.jdbc.SQLServerCallableStatement;
public class ExecuteStoredProcedures {
public static void main(String[] args) {
String connectionUrl = "jdbc:sqlserver://<server>:<port>;databaseName=AdventureWorks;user=<user>;password=<password>";
try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement()) {
createTable(stmt);
createStoredProcedure(stmt);
// Create test data as an example.
StringBuffer buffer = new StringBuffer(4000);
for (int i = 0; i < 4000; i++)
buffer.append((char) ('A'));
try (PreparedStatement pstmt = con.prepareStatement(
"UPDATE Document_JDBC_Sample " + "SET DocumentSummary = ? WHERE (DocumentID = 1)")) {
pstmt.setString(1, buffer.toString());
pstmt.executeUpdate();
}
// Query test data by using a stored procedure.
try (SQLServerCallableStatement cstmt = (SQLServerCallableStatement) con
.prepareCall("{call GetLargeDataValue(?, ?, ?, ?)}")) {
cstmt.setInt(1, 1);
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
cstmt.registerOutParameter(3, java.sql.Types.CHAR);
cstmt.registerOutParameter(4, java.sql.Types.LONGVARCHAR);
// Display the response buffering mode.
System.out.println("Response buffering mode is: " + cstmt.getResponseBuffering());
cstmt.execute();
System.out.println("DocumentID: " + cstmt.getInt(2));
System.out.println("Document_Title: " + cstmt.getString(3));
try (Reader reader = cstmt.getCharacterStream(4)) {
// If your application needs to re-read any portion of the value,
// it must call the mark method on the InputStream or Reader to
// start buffering data that is to be re-read after a subsequent
// call to the reset method.
reader.mark(4000);
// Read the first half of data.
char output1[] = new char[2000];
reader.read(output1);
String stringOutput1 = new String(output1);
// Reset the stream.
reader.reset();
// Read all the data.
char output2[] = new char[4000];
reader.read(output2);
String stringOutput2 = new String(output2);
System.out.println("Document_Summary in half: " + stringOutput1);
System.out.println("Document_Summary: " + stringOutput2);
}
}
}
// Handle any errors that may have occurred.
catch (Exception e) {
e.printStackTrace();
}
}
private static void createStoredProcedure(Statement stmt) throws SQLException {
String outputProcedure = "GetLargeDataValue";
String sql = " IF EXISTS (select * from sysobjects where id = object_id(N'" + outputProcedure
+ "') and OBJECTPROPERTY(id, N'IsProcedure') = 1)" + " DROP PROCEDURE " + outputProcedure;
stmt.execute(sql);
sql = "CREATE PROCEDURE " + outputProcedure + " @p0 int, @p1 int OUTPUT, @p2 char(50) OUTPUT, "
+ "@p3 varchar(max) OUTPUT " + " AS" + " SELECT top 1 @p1=DocumentID, @p2=Title,"
+ " @p3=DocumentSummary FROM Document_JDBC_Sample where DocumentID = @p0";
stmt.execute(sql);
}
private static void createTable(Statement stmt) throws SQLException {
stmt.execute("if exists (select * from sys.objects where name = 'Document_JDBC_Sample')"
+ "drop table Document_JDBC_Sample");
String sql = "CREATE TABLE Document_JDBC_Sample(" + "[DocumentID] [int] NOT NULL identity,"
+ "[Title] [char](50) NOT NULL," + "[DocumentSummary] [varchar](max) NULL)";
stmt.execute(sql);
sql = "INSERT Document_JDBC_Sample VALUES ('title1','summary1') ";
stmt.execute(sql);
sql = "INSERT Document_JDBC_Sample VALUES ('title2','summary2') ";
stmt.execute(sql);
sql = "INSERT Document_JDBC_Sample VALUES ('title3','summary3') ";
stmt.execute(sql);
}
}