IDEA中使用JDBC访问SQL SERVER（八）使用存储过程读取大型数据

 https://docs.microsoft.com/zh-cn/sql/connect/jdbc/reading-large-data-with-stored-procedures-sample?view=sql-server-ver15

package com.example.wfydemo.controller;

import com.microsoft.sqlserver.jdbc.SQLServerCallableStatement;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.Reader;
import java.sql.*;

@RestController
public class jdbc_ExecuteStoredProcedures {
    @RequestMapping("/jdbc_ExecuteStoredProcedures")
    public String jdbc_ExecuteStoredProcedures() {
        String connectionUrl = "jdbc:sqlserver://127.0.0.1:1433;databaseName=AdventureWorks;user=sa;password=6617saSA";//sa身份连接
        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();
            }

            // Java Connection.prepareCall 方法通过创建一个 CallableStatement 对象来调用数据库存储过程。
            // CallableStatement 对象提供了设置其 IN 和 OUT 参数的方法，以及用来执行调用存储过程的方法。
            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();
        }

        return "jdbc_ExecuteStoredProcedures";
    }

    private static void createStoredProcedure(Statement stmt) throws SQLException {
        String outputProcedure = "GetLargeDataValue";
        //存储过程名称：GetLargeDataValue
        //删除存储过程
        String sql = " IF EXISTS (select * from " +
                "sysobjects where id = object_id(N'GetLargeDataValue') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE GetLargeDataValue";
        stmt.execute(sql);
        //创建存储过程
        sql = "CREATE PROCEDURE GetLargeDataValue  @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);
    }
}