使用存储过程读取大型数据的示例
此 Microsoft JDBC Driver for SQL Server 示例应用程序演示如何从存储过程中检索大型 OUT 参数。
此示例的代码文件命名为 ExecuteStoredProcedure.java，可以在以下位置找到：
\<installation directory>\sqljdbc_<version>\<language>\samples\adaptive
要求
若要运行此示例应用程序，需要访问 AdventureWorks2022 示例数据库。 将 classpath 设置为包含 mssql-jdbc jar 文件。 若要详细了解如何设置类路径，请参阅使用 JDBC 驱动程序。
注意
Microsoft JDBC Driver for SQL Server 提供要使用的 mssql-jdbc 类库文件，具体使用哪个文件取决于首选的 Java Runtime Environment (JRE) 设置。 有关选择哪个 JAR 文件的详细信息，请参阅 JDBC 驱动程序的系统要求。
该示例将在 AdventureWorks2022 示例数据库中创建所需的存储过程：
示例
此示例代码：
- 与 AdventureWorks2022 数据库建立连接。
- 创建示例数据并使用参数化查询更新
Production.Document表。 最后，示例代码通过使用 SQLServerStatement 类的 getResponseBuffering 方法获取自适应缓冲模式，并执行GetLargeDataValue存储过程。 从 JDBC Driver 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);
}
}