Reading large data sample

Download JDBC driver

This Microsoft JDBC Driver for SQL Server sample application demonstrates how to retrieve a large single-column value from a SQL Server database by using the getCharacterStream method.

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

To run this sample application, you'll need access to the AdventureWorks2022 sample database. You must also set the classpath to include the mssql-jdbc jar file. For more information about how to set the classpath, see Using the JDBC Driver.

Example

In the following example, the sample code makes a connection to the AdventureWorks2022 database. Next, the sample code creates sample data and updates the Production.Document table by using a parameterized query.

In addition, the sample code demonstrates how to get the adaptive buffering mode by using the getResponseBuffering method of the SQLServerStatement class. Note that starting with the JDBC driver version 2.0 release, the responseBuffering connection property is set to "adaptive" by default.

Then, using a SQL statement with the SQLServerStatement object, the sample code runs the SQL statement and places the data that it returns into a SQLServerResultSet object.

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 a 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