读取大型数据的示例Reading large data sample

下载下载 JDBC 驱动程序DownloadDownload JDBC Driver

Microsoft JDBC Driver for SQL ServerMicrosoft JDBC Driver for SQL Server 示例应用程序还说明如何使用 getCharacterStream 方法从 SQL ServerSQL Server 数据库中检索大型单列值。This Microsoft JDBC Driver for SQL ServerMicrosoft JDBC Driver for SQL Server sample application demonstrates how to retrieve a large single-column value from a SQL ServerSQL Server database by using the getCharacterStream method.

此示例的代码文件名为 ReadLargeData.java,该文件可在以下位置找到:The code file for this sample is named ReadLargeData.java, and it can be found in the following location:

\<installation directory>\sqljdbc_<version>\<language>\samples\adaptive

要求Requirements

要运行此示例应用程序,将需要访问 AdventureWorksAdventureWorks 示例数据库。To run this sample application, you'll need access to the AdventureWorksAdventureWorks sample database. 还必须将 classpath 设置为包含 mssql-jdbc jar 文件。You must also set the classpath to include the mssql-jdbc jar file. 若要详细了解如何设置类路径,请参阅使用 JDBC 驱动程序For more information about how to set the classpath, see Using the JDBC Driver.

备注

Microsoft JDBC Driver for SQL ServerMicrosoft JDBC Driver for SQL Server 提供要使用的 mssql-jdbc 类库文件,具体使用哪个文件取决于首选的 Java Runtime Environment (JRE) 设置。The Microsoft JDBC Driver for SQL ServerMicrosoft JDBC Driver for SQL Server provides mssql-jdbc class library files to be used depending on your preferred Java Runtime Environment (JRE) settings. 有关选择哪个 JAR 文件的详细信息,请参阅 JDBC 驱动程序的系统要求For more information about which JAR file to choose, see System Requirements for the JDBC Driver.

示例Example

在下面的示例中,示例代码建立与 AdventureWorksAdventureWorks 数据库的连接。In the following example, the sample code makes a connection to the AdventureWorksAdventureWorks database. 接下来,示例代码创建示例数据并使用参数化查询更新 Production.Document 表。Next, the sample code creates sample data and updates the Production.Document table by using a parameterized query.

此外,示例代码还演示如何使用 SQLServerStatement 类的 getResponseBuffering 方法获取自适应缓冲模式。In addition, the sample code demonstrates how to get the adaptive buffering mode by using the getResponseBuffering method of the SQLServerStatement class. 请注意,从 JDBC Driver 2.0 发行版开始,responseBuffering 连接属性默认情况下设置为“adaptive”。Note that starting with the JDBC driver version 2.0 release, the responseBuffering connection property is set to "adaptive" by default.

然后,通过对 SQLServerStatement 对象使用 SQL 语句,示例代码将运行此 SQL 语句并将其返回的数据放入 SQLServerResultSet 对象中。Then, using an SQL statement with the SQLServerStatement object, the sample code runs the SQL statement and places the data that it returns into a SQLServerResultSet object.

最后,示例代码将循环访问结果集中的数据行,并使用 getCharacterStream 方法访问某些数据。Finally, the sample code iterates through the rows of data that are in the result set, and uses the getCharacterStream method to access some of the data.

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.microsoft.sqlserver.jdbc.SQLServerStatement;

public class ReadLargeData {

    public static void main(String[] args) {
        // Create a variable for the connection string.
        String connectionUrl = "jdbc:sqlserver://<server>:<port>;databaseName=AdventureWorks;user=<user>;password=<password>";
        
        // Create test data as an example.
        StringBuffer buffer = new StringBuffer(4000);
        for (int i = 0; i < 4000; i++)
            buffer.append((char) ('A'));
        
        try (Connection con = DriverManager.getConnection(connectionUrl);
                Statement stmt = con.createStatement();
                PreparedStatement pstmt = con.prepareStatement("UPDATE Production.Document SET DocumentSummary = ? WHERE (DocumentID = 1)");) {

            pstmt.setString(1, buffer.toString());
            pstmt.executeUpdate();

            // In adaptive mode, the application does not have to use a server cursor
            // to avoid OutOfMemoryError when the SELECT statement produces very large
            // results.

            // Create and execute an SQL statement that returns some data.
            String SQL = "SELECT Title, DocumentSummary FROM Production.Document";

            // Display the response buffering mode.
            SQLServerStatement SQLstmt = (SQLServerStatement) stmt;
            System.out.println("Response buffering mode is: " + SQLstmt.getResponseBuffering());
            SQLstmt.close();

            // Get the updated data from the database and display it.
            ResultSet rs = stmt.executeQuery(SQL);

            while (rs.next()) {
                Reader reader = rs.getCharacterStream(2);
                if (reader != null) {
                    char output[] = new char[40];
                    while (reader.read(output) != -1) {
                        // Do something with the chunk of the data that was
                        // read.
                    }

                    System.out.println(rs.getString(1) + " has been accessed for the summary column.");
                    // Close the stream.
                    reader.close();
                }
            }
        }
        // Handle any errors that may have occurred.
        catch (SQLException | IOException e) {
            e.printStackTrace();
        }
    }
}

另请参阅See also

处理大型数据Working with large data