Updating Large Data Sample

This Microsoft SQL Server JDBC Driver sample application demonstrates how to update a large column in a database.

The code file for this sample is named updateLargeData.java, and can be found in the following location:

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

Requirements

To run this sample application, you will need access to the SQL Server 2005 AdventureWorks sample database. You must also set the classpath to include the sqljdbc4.jar file. If the classpath is missing an entry for sqljdbc4.jar, the sample application will throw the common "Class not found" exception. 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 SQL Server 2005 AdventureWorks database. Then, the sample code creates a Statement object and uses the isWrapperFor method to check whether the Statement object is a wrapper for the specified SQLServerStatement class. The unwrap method is used to access the driver-specific response buffering methods.

Next, the sample code sets the response buffering mode as "adaptive" by using the setResponseBuffering method of the SQLServerStatement class and also demonstrates how to get the adaptive buffering mode.

Then, it runs the SQL statement, and places the data that it returns into an updateable SQLServerResultSet object.

Finally, the sample code iterates through the rows of data that are contained in the result set. If it finds an empty document summary, it uses the combination of updateString and updateRow methods to update the row of data and again persist it to the database. If there is already data, it uses the getString method to display some of the data that it contains.

Note that starting with the JDBC Driver version 2.0 release, the default behavior of the driver is "adaptive." However, for the forward-only updatable result sets and when the data in the result set is larger than the application memory, the application has to set the adaptive buffering mode explicitly by using the setResponseBuffering method of the SQLServerStatement class.

import java.sql.*;
import java.io.*;
import com.microsoft.sqlserver.jdbc.SQLServerStatement;

public class updateLargeData {
    
   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;  
      Reader reader = null;
           
      try {
          // Establish the connection.
          Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
          con = DriverManager.getConnection(connectionUrl);
         
          stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

          // Since the summaries could be large, make sure that
          // the driver reads them incrementally from a database, 
          // even though a server cursor is used for the updatable result sets.
          
          // The recommended way to access the Microsoft SQL Server JDBC Driver 
          // specific methods is to use the JDBC 4.0 Wrapper functionality. 
          // The following code statement demonstrates how to use the 
          // Statement.isWrapperFor and Statement.unwrap methods
          // to access the driver specific response buffering methods.
          
          if (stmt.isWrapperFor(com.microsoft.sqlserver.jdbc.SQLServerStatement.class)) {
              SQLServerStatement SQLstmt = 
                 stmt.unwrap(com.microsoft.sqlserver.jdbc.SQLServerStatement.class);
              
              SQLstmt.setResponseBuffering("adaptive");
              System.out.println("Response buffering mode has been set to " +
                 SQLstmt.getResponseBuffering());
          }

          // Select all of the document summaries.
          rs = stmt.executeQuery("SELECT Title, DocumentSummary FROM Production.Document");

          // Update each document summary.
          while (rs.next()) {

               // Retrieve the original document summary.
               reader = rs.getCharacterStream("DocumentSummary");

               if (reader == null)
               {
                   // Update the document summary.
                   System.out.println("Updating " + rs.getString("Title"));
                   rs.updateString("DocumentSummary", "Work in progress");
                   rs.updateRow();
               }
               else
               {
                   // Do something with the chunk of the data that was                   
                   // read.
                   System.out.println("reading " + rs.getString("Title"));
                   reader.close();
                   reader = null;
               }
          }
      }
      // Handle any errors that may have occurred.
      catch (Exception e) {
         e.printStackTrace();
      }
      finally {
          if (reader != null) try { reader.close(); } catch(Exception e) {}
          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) {}
      }
   }
}

See Also

Other Resources

Working with Large Data