使用預存程序讀取大型資料範例

下載 JDBC 驅動程式

此 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 範例資料庫中建立必要的預存程序：

範例

此程式碼範例：

1. 連線到 AdventureWorks2022 資料庫。
2. 藉由使用參數化查詢來建立範例資料並且更新 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);
    }
}

另請參閱

使用大型資料