此 Microsoft SQL Server JDBC Driver 示例应用程序演示如何从存储过程中检索大型 OUT 参数。
此示例的代码文件命名为 executeStoredProcedure.java,可以在以下位置找到:
<安装目录>\sqljdbc_<版本>\<语言>\help\samples\adaptive
要求
要运行此示例应用程序,需要访问 SQL Server 2005 AdventureWorks 示例数据库。还必须将 classpath 设置为包含 sqljdbc.jar 文件或 sqljdbc4.jar 文件。如果 classpath 缺少 sqljdbc.jar 项或 sqljdbc4.jar 项,示例应用程序将引发“找不到类”的常见异常。有关如何设置 classpath 的详细信息,请参阅使用 JDBC 驱动程序。
还必须在 SQL Server 2005 AdventureWorks 示例数据库中创建以下存储过程:
CREATE PROCEDURE GetLargeDataValue (@Document_ID int, @Document_ID_out int OUTPUT, @Document_Title varchar(50) OUTPUT, @Document_Summary nvarchar(max) OUTPUT) AS BEGIN SELECT @Document_ID_out = DocumentID, @Document_Title = Title, @Document_Summary = DocumentSummary FROM Production.Document WHERE DocumentID = @Document_ID END
示例
在下面的示例中,示例代码建立到 SQL Server 2005 AdventureWorks 数据库的连接。接下来,示例代码创建示例数据并使用参数化查询更新 Production.Document 表。然后,示例代码通过使用 SQLServerStatement 类的 getResponseBuffering 方法获取自适应缓冲模式,并执行 GetLargeDataValue 存储过程。请注意,从 JDBC Driver 2.0 发行版开始,responseBuffering 连接属性默认情况下设置为“adaptive”。
最后,示例代码显示使用 OUT 参数返回的数据,同时还演示如何在流中使用 mark
和 reset
方法以重新读取数据的任何部分。
import java.sql.*; import java.io.*; import com.microsoft.sqlserver.jdbc.SQLServerCallableStatement; public class executeStoredProcedure { public static void main(String[] args) { // Create a variable for the connection string. String connectionUrl = "jdbc:sqlserver://localhost:1433;" + "databaseName=AdventureWorks;integratedSecurity=true;"; // Declare the JDBC objects. Connection con = null; Statement stmt = null; ResultSet rs = null; try { // Establish the connection. Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); con = DriverManager.getConnection(connectionUrl); // Create test data as an example. StringBuffer buffer = new StringBuffer(4000); for (int i = 0; i < 4000; i++) buffer.append( (char) ('A')); PreparedStatement pstmt = con.prepareStatement( "UPDATE Production.Document " + "SET DocumentSummary = ? WHERE (DocumentID = 1)"); pstmt.setString(1, buffer.toString()); pstmt.executeUpdate(); pstmt.close(); // Query test data by using a stored procedure. CallableStatement cstmt = con.prepareCall("{call dbo.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. SQLServerCallableStatement SQLcstmt = (SQLServerCallableStatement) cstmt; System.out.println("Response buffering mode is: " + SQLcstmt.getResponseBuffering()); SQLcstmt.execute(); System.out.println("DocumentID: " + cstmt.getInt(2)); System.out.println("Document_Title: " + cstmt.getString(3)); Reader reader = SQLcstmt.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); // Close the stream. reader.close(); } // Handle any errors that may have occurred. catch (Exception e) { e.printStackTrace(); } finally { if (rs != null) try { rs.close(); } catch(Exception e) {} if (stmt != null) try { stmt.close(); } catch(Exception e) {} if (con != null) try { con.close(); } catch(Exception e) {} } } }