A CachedRowSet is a disconnected ResultSet object. Data in a CachedRowSet is stored in memory. CachedRowSets from the WebLogic Server implementation have the following characteristics:

• Can be used to insert, update, or delete data.

• Are serializable, so they can be passed to various application components, including wireless devices.

• Include transaction handling to enable rowset reuse. See Reusing a WebLogic RowSet After Completing a Transaction.

• Use an optimistic concurrency control for synchronizing data changes in the rowset with the database.

• Use a SyncResolver object from a SyncProvider exception to resolve conflicts between data changes in the rowset and the database. See Handling SyncProviderExceptions with a SyncResolver.

When designing your application, consider the following information:

• Entire RowSet Query Results Stored in Memory

• Data Contention

• Entire RowSet Query Results Stored in Memory
  
• Data Contention
  

Example 8-1 shows the basic workflow of a CachedRowSet. It includes comments that describe each major operation and its corresponding rowset life cycle stage. Following the code example is a more detailed explanation of each of the major sections of the example.

import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory; 
public class CachedRowSetDemo {
public static void main (String[] args) {
//DESIGNING lifecycle stage - Create the rowset and set properties
  try {
     //Create a RowSetFactory instance and from the factory,
    //create a FilteredRowSet.
    RowSetFactory rsfact = RowSetProvider.newFactory("weblogic.jdbc.rowset.JdbcRowSetFactory",null);
    CachedRowSet rs = rsfact.createCachedRowSet();
    //Set database access through a DataSource. 
    rs.setDataSourceName(examples-dataSource-demoPool);
    //See Database Connection Options for more options.
    //Set query command
    rs.setCommand("SELECT ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME,
    PHONE, EMAIL FROM PHYSICIAN WHERE ID>?");
   //CONFIGURE QUERY lifecycle operation
    rs.setInt(1, 0); 
    //POPULATING lifecycle stage - Execute the command to populate the rowset
    rs.execute();
   }
   //CONFIGURING METADATA - Populate first, then set MetaData, 
   //including KeyColumns
    rs.setKeyColumns(new int[] { 1 });
    while (rs.next ()) //NAVIGATING lifecycle stage
      {
      System.out.println ("ID: " +rs.getInt (1));
      System.out.println ("FIRST_NAME: " +rs.getString (2));
      System.out.println ("MIDDLE_NAME: " +rs.getString (3));
      System.out.println ("LAST_NAME: " +rs.getString (4));
      System.out.println ("PHONE: " +rs.getString (5));
      System.out.println ("EMAIL: " +rs.getString (6));
      }
   }
//Working with data
//Delete rows in the rowset
  try {
  //MANIPULATING lifecycle stage - navigate to a row 
  //(manually moving the cursor)
  rs.last();
  rs.deleteRow();
  //Note that the database is not updated yet.
  }  
//Update a row in the rowset
  try {
  //MANIPULATING lifecycle stage - navigate to a row
  //(manually moving the cursor)
  rs.first();
  //UPDATING lifecycle stage - call an update() method
  rs.updateString(4, "Francis");
  //MANIPULATING lifecycle stage - finish update
  rs.updateRow();
  //Note that the database is not updated yet.
  }  
//INSERTING lifecycle stage - Insert rows in the rowset
  try {
    rs.moveToInsertRow();
    rs.updateInt(1, 104);
    rs.updateString("FIRST_NAME", "Yuri");
    rs.updateString("MIDDLE_NAME", "M");
    rs.updateString("LAST_NAME", "Zhivago");
    rs.updateString("PHONE", "1234567812");
    rs.updateString("EMAIL", "Yuri@poet.com");
    rs.insertRow(); //"Finish Update" action; 
    //MANIPULATING lifecycle stage - navigate to a row
    rs.moveToCurrentRow();
    //Note that the database is not updated yet.
  }  
//Send all changes (delete, update, and insert) to the database.
//DESIGNING or POPULATING lifecycle stage - after synchronizing changes
//with the database, lifecycle stage depends on other environment settings. 
//See Reusing a WebLogic RowSet After Completing a Transaction.
  try {
    rs.acceptChanges();
    rs.close();
   }
}

For standard RowSets, you must import the following classes:

javax.sql.rowset.CachedRowSet;
javax.sql.rowset.RowSetFactory;

1. Create a RowSetFactory instance, which serves as a factory to create rowset objects for use in your application.

   RowSetFactory rsfact = RowSetProvider.newFactory("weblogic.jdbc.rowset.JdbcRowSetFactory",null);
   

2. Create a javax.sql.rowset.CachedRowSet object. For example:

   CachedRowSet rs = rsfact.createCachedRowSet();

There are numerous rowset properties, such as concurrency type, data source name, transaction isolation level, and so forth, that you can set to determine the behavior of the rowset. You are required to set only those properties that are needed for your particular use of the rowset. For information about available properties, see the Javadoc for the javax.sql.rowset.BaseRowSet class at http://docs.oracle.com/javase/1.5.0/docs/api/javax/sql/rowset/BaseRowSet.html.

In most applications, you populate a rowset with data from a database. You can set rowset database connectivity in any of the following ways:

• Automatically with a data source—You can use the setDataSourceName() method to specify the JNDI name of a JDBC data source. When you call execute() and acceptChanges(), the rowset gets a database connection from the data source, uses it, and returns it to the pool of connections in the data source. This is a preferred method.

  rs.setDataSourceName(examples-dataSource-demoPool);
  

• Manually get a database connection—In your application, you can get a database connection before the rowset needs it, and then pass the connection object as a parameter in the execute() and acceptChanges() methods. You must also close the connection as necessary.

  //Lookup DataSource and get a connection
  ctx = new InitialContext(ht);
  javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup ("myDS");
  conn = ds.getConnection();
  
  //Pass the connection to the rowset
  rs.execute(conn); 
  

  For more information about JDBC data sources, see Getting a Database Connection from a DataSource Object.

• Load the JDBC driver for a direct connection—When you load the JDBC driver and set the appropriate properties, the rowset creates a database connection when you call execute() and acceptChanges(). The rowset closes the connection immediately after it uses it. The rowset does not keep the connection between the execute() and acceptChanges() method calls.

  Class.forName("org.apache.derby.jdbc.ClientDriver");
  rs.setUrl("jdbc:derby://localhost:1527/demo");
  rs.setUsername("examples");
  rs.setPassword("examples");
  rs.execute();
  

In some cases, you may need to set metadata for the rowset in order to synchronize data changes in the rowset with data in the database. See RowSet MetaData Settings for Database Updates.

After you populate the cached rowset with rows of data, you can work with the cached data in much the same way as you would work with data in a result set, except that before your changes are made in the database, you must explicitly call acceptChanges().

• Getting Data from a Row in a RowSet
  
• Updating a Row in a RowSet
  
• Inserting a Row in a RowSet
  
• Deleting a Row in a RowSet
  

After you make changes to individual rows in a rowset, you call acceptChanges() to propagate those changes to the database. For example:

rs.acceptChanges();

When you call acceptChanges(), the rowset connects to the database using the database connection information already used by the rowset (see Database Connection Options) or using a connection object passed with the acceptChanges(connection) method. You can call acceptChanges() after making changes to one row or several rows. Calling acceptChanges() after making all changes to the rowset is more efficient because the rowset connects to the database only once.

When using rowsets with WebLogic Server, WebLogic Server internally uses a weblogic.jdbc.rowset.WLSyncProvider object to read from and write to the database. The WLSyncProvider uses an optimistic concurrency algorithm for making changes to the database, which means that the design assumes data in the database will not be changed by another process during the time between when a rowset is populated to when rowset data changes are propagated to the database. Before writing changes to the database, the WLSyncProvider compares the data in the database against the original values in the rowset (values read into the rowset when the rowset was created or at the last synchronization). If any values in the database have changed, WebLogic Server throws a javax.sql.rowset.spi.SyncProviderException and does not write any changes to the database. You can catch the exception in your application and determine how to proceed. See Handling SyncProviderExceptions with a SyncResolver.

The WLCachedRowSet interface, an extension to the standard CachedRowSet interface, provides options for selecting an optimistic concurrency policy. See Optimistic Concurrency Policies.

After propagating changes to the database, WebLogic Server changes the life cycle stage of the rowset to Designing or Populating, depending on your application environment. In the Designing stage, you must repopulate the rowset before you can use it again; in the Populating stage, you can use the rowset with its current data. See Reusing a WebLogic RowSet After Completing a Transaction for more details.

If you do not plan to use the rowset again, you should close it with the close() method. For example:

rs.close();

When populating a rowset with an SQL query, you typically use the execute() method to run the query and read the data. The WLCachedRowSet implementation provides the executeAndGuessTableName and executeAndGuessTableNameAndPrimaryKeys methods that extend the execute method to also determine the associated table metadata.

The executeAndGuessTableName method parses the associated SQL and sets the table name for all columns as the first word following the SQL keyword FROM.

The executeAndGuessTableNameAndPrimaryKeys method parses the SQL command to read the table name. It then uses the java.sql.DatabaseMetaData to determine the table's primary keys.

You can also choose to manually set the table and primary key information using the WLRowSetMetaData interface.

WLRowSetMetaData metaData = (WLRowSetMetaData) rowSet.getMetaData();
// Sets one table name for all columns
metaData.setTableName("employees");

or

metaData.setTableName("e_id", "employees");
metaData.setTableName("e_name", "employees");

You can also use the WLRowSetMetaData interface to identify primary key columns.

metaData.setPrimaryKeyColumn("e_id", true);

See weblogic.jdbc.rowset.WLRowSetMetaData in Java API Reference for Oracle WebLogic Server.

The WLRowSetMetaData interface includes the setWriteTableName method to indicate the only table that should be updated or deleted. This is typically used when a rowset is populated with a join from multiple tables, but the rowset should only update one table. Any column that is not from the write table is marked as read-only.

For instance, a rowset might include a join of orders and customers. The write table could be set to orders. If deleteRow were called, it would delete the order row, but not delete the customer row.

The EJB container and the UserTransaction interface start transactions with the JTA transaction manager. The RowSet operations can participate in this transaction. To participate in the JTA transaction, the RowSet must use a transaction-aware DataSource (TxDataSource). The DataSource can be configured in the WebLogic Remote console.

If an Optimistic conflict or other exception occurs during acceptChanges, the RowSet aborts the global JTA transaction. The application will typically re-read the data and process the update again in a new transaction.

• Behavior of Rowsets Using Global Transactions
  

If a JTA global transaction is not being used, the RowSet uses a local transaction. It first calls setAutoCommit(false) on the connection, then it issues all of the SQL statements, and finally it calls connection.commit(). This attempts to commit the local transaction. This method should not be used when trying to integrate with a JTA transaction that was started by the EJB or JMS containers.

If an Optimistic conflict or other exception occurs during acceptChanges, the RowSet rolls back the local transaction. In this case, none of the SQL issued in acceptChanges will commit to the database.

• Behavior of Rowsets Using Local Transactions
  

In many cases, after you synchronize changes in the rowset with the database, you may want to continue to use the rowset with its current data, which can improve application performance by reducing the number of database round trips. However, to reuse the rowset and its data, WebLogic Server needs to make sure that any transaction in which the rowset participates has completed before allowing you to make further changes to the data.

If you use a rowset in a local transaction and if autocommit=true is set on the connection object before rowset data changes are synchronized with the database, you can reuse the rowset with its current data after synchronizing the data because the autocommit setting forces the local transaction to complete immediately. WebLogic Server can be sure that the local transaction is complete before any further changes are made to the rowset.

WebLogic Server cannot automatically be sure that all transactions are complete if you use a rowset in either of the following scenarios:

• In a global transaction

• In a local transaction using a connection object with autocommit=false to synchronize data changes with the database

With either of these conditions, before you can reuse a rowset with its current data, after calling acceptChanges() to synchronize your changes with the database, you must call javax.sql.rowset.CachedRowSet.commit() instead of tx.commit() or java.sql.Connection.commit() to commit the transaction. The CachedRowSet.commit() method wraps the Connection.commit() method and enables WebLogic Server to ensure that the transaction is complete before allowing changes to the rowset.

A FilteredRowSet enables you to work with a subset of cached rows and change the subset of rows while disconnected from the database. A filtered rowset is simply a cached rowset in which only certain rows are available for viewing, navigating, and manipulating. FilteredRowSets have the following characteristics:

• The rows available are determined by a javax.sql.rowset.Predicate object supplied by the application and set with the setFilter() method.

• The Predicate object must implement the javax.sql.rowset.Predicate interface. The Predicate interface includes the public boolean evaluate(RowSet rs) method, which evaluates each row in the rowset

  • If the method returns true, the row is available and visible.

  • If the method returns false, the row is not available or visible.

  See Setting the Filter for a FilteredRowSet.

• WebLogic Server provides the weblogic.jdbc.rowset.SQLPredicate class, which is an implementation of the javax.sql.rowset.Predicate interface that you can use to define a filter for a FilteredRowSet using SQL-like WHERE clause syntax. See SQLPredicate, a SQL-Style RowSet Filter.

• RowSet Filters are Not Cumulative
  
• No Pending Changes Before Setting or Changing a Filter
  

The following example shows how to create a cached rowset and then apply and change a filter using the WebLogic Server SQLPredicate.

import javax.sql.rowset.FilteredRowSet;
import javax.sql.rowset.RowSetFactory;
import weblogic.jdbc.rowset.SQLPredicate;
public class FilteredRowSetDemo {
public static void main (String[] args) {
//DESIGNING lifecycle stage - Create the rowset and set properties
  try {
    //Create a RowSetFactory instance and from the factory,
    //create a FilteredRowSet.
    RowSetFactory rsfact = RowSetProvider.newFactory("weblogic.jdbc.rowset.JdbcRowSetFactory",null);
    FilteredRowSet rs = rsfact.createFilteredRowSet();
    //Set database access through a DataSource. 
    //See Database Connection Options for more options.
    rs.setDataSourceName(examples-dataSource-demoPool);
    rs.setCommand("SELECT ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME,
    PHONE, EMAIL FROM PHYSICIAN WHERE ID>?");
   //CONFIGURE QUERY lifecycle operation - set values for query parameters.
    rs.setInt(1, 0); 
   //POPULATING lifecycle stage - Execute the command to populate the rowset
    rs.execute();
  }
//CONFIGURING METADATA - Populate first, then set MetaData, including KeyColumns
  rs.setKeyColumns(new int[] { 1 });
  while (rs.next ())
//NAVIGATE operations put the rowset in the MANIPULATING lifecycle stage
    {
    System.out.println ("ID: " +rs.getInt (1));
    System.out.println ("FIRST_NAME: " +rs.getString (2));
    System.out.println ("MIDDLE_NAME: " +rs.getString (3));
    System.out.println ("LAST_NAME: " +rs.getString (4));
    System.out.println ("PHONE: " +rs.getString (5));
    System.out.println ("EMAIL: " +rs.getString (6));
    }
  }
//Need to accept changes or call restoreOriginal to put the rowset 
//into the DESIGNING or POPULATING stage.
//After navigating, the rowset is in MANIPULATING stage,
//and you cannot change properties in that lifecycle stage.
  rs.restoreOriginal();
//S E T   F I L T E R
//use SQLPredicate class to create a SQLPredicate object,
//then pass the object in the setFilter method to filter the RowSet. 
  SQLPredicate filter = new SQLPredicate("ID >= 103");
  rs.setFilter(filter);
  System.out.println("Filtered data: ");
  while (rs.next ())
    {
    System.out.println ("ID: " +rs.getInt (1));
    System.out.println ("FIRST_NAME: " +rs.getString (2));
    System.out.println ("MIDDLE_NAME: " +rs.getString (3));
    System.out.println ("LAST_NAME: " +rs.getString (4));
    System.out.println ("PHONE: " +rs.getString (5));
    System.out.println ("EMAIL: " +rs.getString (6));
    System.out.println (" ");
    }
//Need to accept changes or call restoreOriginal to put the rowset 
//into the DESIGNING or POPULATING lifecycle stage.
//After navigating, the rowset is in MANIPULATING stage,
//and you cannot change properties in that lifecycle stage.
  rs.restoreOriginal();
//C H A N G I N G   F I L T E R
  SQLPredicate filter2 = new SQLPredicate("ID <= 103");
  rs.setFilter(filter2);
  System.out.println("Filtered data: ");
  while (rs.next ())
    {
    System.out.println ("ID: " +rs.getInt (1));
    System.out.println ("FIRST_NAME: " +rs.getString (2));
    System.out.println ("MIDDLE_NAME: " +rs.getString (3));
    System.out.println ("LAST_NAME: " +rs.getString (4));
    System.out.println ("PHONE: " +rs.getString (5));
    System.out.println ("EMAIL: " +rs.getString (6));
    System.out.println (" ");
    }
//Need to accept changes or call restoreOriginal to put the rowset 
//into the DESIGNING or POPULATING lifecycle stage.
//After navigating, the rowset is in MANIPULATING stage,
//and you cannot change properties in that lifecycle stage.
  rs.restoreOriginal();
//R E M O V I N G   F I L T E R
  rs.setFilter(null);
  while (rs.next ())
    {
    System.out.println ("ID: " +rs.getInt (1));
    System.out.println ("FIRST_NAME: " +rs.getString (2));
    System.out.println ("MIDDLE_NAME: " +rs.getString (3));
    System.out.println ("LAST_NAME: " +rs.getString (4));
    System.out.println ("PHONE: " +rs.getString (5));
    System.out.println ("EMAIL: " +rs.getString (6));
    System.out.println (" ");
    }
  rs.close();
  }
}

For standard FilteredRowSets, you must import the following classes:

javax.sql.rowset.FilteredRowSet;
javax.sql.rowset.RowSetFactory;

The preceding code example also uses the weblogic.jdbc.rowset.SQLPredicate class to create a filter. In your application, you can use the weblogic.jdbc.rowset.SQLPredicate class or you can create your own filter class. See Setting the Filter for a FilteredRowSet.

1. Create a RowSetFactory instance, which serves as a factory to create rowset objects for use in your application. For example:

   RowSetFactory rsfact = RowSetProvider.newFactory("weblogic.jdbc.rowset.JdbcRowSetFactory",null);
   

2. Create a javax.sql.rowset.FilteredRowSet object. For example:

   FilteredRowSet rs = rsfact.createCachedRowSet();

Property options for a FilteredRowSet are the same as those for a CachedRowSet. See Setting CachedRowSet Properties.

Data population options for a FilteredRowSet are the same as those for a CachedRowSet. See Populating a CachedRowSet.

In some cases, you may need to set metadata for the rowset in order to synchronize data changes in the rowset with data in the database. See RowSet MetaData Settings for Database Updates.

Working with data in a FilteredRowSet is much the same as working with data in a CachedRowSet, except that when you insert a row or update a row, the changes that you make must be within the filtering criteria so that the row will remain in the set of rows displayed. For example, if the filter on the rowset allowed only rows with an ID column value of less than 105 to be displayed, if you tried to insert a row with a value of 106 in the ID column or update an ID value to 106, that operation would fail and throw an SQLException.

For more details about working with data, see Working with Data in a CachedRowSet.

• The WebLogic WebRowSets implementation supports two XML schemas (and APIs): one for the standard WebRowSet specification (available at http://www.oracle.com/webfolder/technetwork/jsc/xml/ns/jdbc/webrowset.xsd.) and one for the WLCachedRowSet, which was implemented before JSR-114 was finalized.

• If you are using only WebLogic Server rowsets, you can use either schema. The proprietary schema has more element types.

• To interact with other rowset implementations, you must use the standard schema.

Table 8-2 lists the types of conflict scenarios that can occur when synchronizing data changes from a rowset to the database.

Example 8-5 shows an abbreviated example of how to use a SyncResolver to resolve conflicting values between the rowset and the database. This example checks the value for known column names in each row in the SyncResolver in which there is a conflict. Details about the example are explained in the sections that follow the example.

try {
    rs.acceptChanges();
} catch (SyncProviderException spex) {
    SyncResolver syncresolver = spex.getSyncResolver();
    while (syncresolver.nextConflict())  {
        int status = syncresolver.getStatus();
        int rownum = syncresolver.getRow();
        rs.absolute(rownum);
        //check for null in each column
        //write out the conflict
        //set resolved value to value in the db for this example
        //handle exception for deleted row in the database
        try {
           Object idConflictValue = syncresolver.getConflictValue("ID");
           if (idConflictValue != null)  {
              System.out.println("ID value in db: " + idConflictValue);
              System.out.println("ID value in rowset: " + rs.getInt("ID"));
              syncresolver.setResolvedValue("ID", idConflictValue);
              System.out.println("Set resolved value to " + idConflictValue);
            }
           else {
              System.out.println("ID: NULL - no conflict");
           }
        } catch (RowNotFoundException e)  { 
           System.out.println("An exception was thrown when requesting a ");
          System.out.println("value for ID. This row was ");
          System.out.println("deleted in the database.");
        }
. . .
    }
    try {
        rs.acceptChanges();
    } catch (Exception ignore2) {
        }
}

To handle a SyncProviderException, you can catch the exception and get a SyncResolver object from it. For example:

try {
   rowset.acceptChanges();
} catch (SyncProviderException spex) {
   SyncResolver syncresolver = spex.getSyncResolver();
. . .
}

A SyncResolver is a rowset that implements the SyncResolver interface. A SyncResolver object contains a row for every row in the original rowset. For values without a conflict, the value in the SyncResolver is null. For values with a conflict, the value is the current value in the database.

With a SyncResolver object, you can page through all conflicts and set the appropriate value for each conflict value. The SyncResolver interface includes the nextConflict() and previousConflict() methods that you can use to navigate directly to the next row in the SyncResolver that has a conflict value other than null. Because a SyncResolver object is a rowset, you can also use all of the rowset navigation methods to move the cursor to any row in the SyncResolver. However, the nextConflict() and previousConflict() methods enable you to easily skip rows that do not contain conflict values.

After you move the cursor to a conflict row, you must check the value in each column with the getConflictValue() method to find the values in the database that conflict with the values in the rowset, and then compare values to determine how to handle the conflict. For rows with values that do not conflict, the return value is null. If the row was deleted in the database, there is no value to return, so an exception is thrown.

An example of code to compare values in the rowset and database:

syncresolver.nextConflict()
for (int i = 1; i <= colCount; i++) {
   if (syncresolver.getConflictValue(i) != null)  {
      rsValue = rs.getObject(i);
      resolverValue = syncresolver.getConflictValue(i);
      . . . 
     // compare values in the rowset and SyncResolver to determine
     // which should be the resolved value (the value to persist)
   } 
} 

To set the appropriate value to persist in the database, you call setResolvedValue(). For example:

syncresolver.setResolvedValue(i, resolvedValue);

The setResolvedValue() method makes the following changes:

• Sets the value to persist in the database. That is, it sets the current value in the rowset. When changes are synchronized, the new value will be persisted to the database.

• Changes the original value for the rowset data to the current value in the database. The original value was the value since the last synchronization. After calling setResolvedValue(), the original value becomes the current value in the database.

• Changes the WHERE clause in the synchronization call so that updates are made to appropriate rows in the database.

After resolving conflicting values in the SyncResolver, you must synchronize your changes with the database. To do that, you call rowset.acceptChanges(). again. The acceptChanges() call closes the SyncResolver object and releases locks on the database after the synchronization completes.

WebLogic Server provides the weblogic.jdbc.rowset.SQLPredicate class, which is an implementation of the javax.sql.rowset.Predicate interface. You can use the SQLPredicate class to define a filter for a FilteredRowSet using SQL-like WHERE clause syntax.

The SQLPredicate class borrows its grammar from the JMS selector grammar, which is very similar to the grammar for an SQL select WHERE clause.

Some important notes:

• When referencing a column, you must use the column name; you cannot use column index number.

• The grammar supports the use of operators and mathematical operations, for example:

   (colA + ColB) >=100.
  

• In constructing the WHERE clause, you can use simple datatypes only, including:

  • String

  • Int

  • Boolean

  • Float

• Complex data types are not supported:

  • Array

  • BLOB

  • CLOB

  • Date

//S E T   F I L T E R
//use SQLPredicate class to create a SQLPredicate object,
//then pass the object in the setFilter method to filter the RowSet. 
SQLPredicate filter = new SQLPredicate("ID >= 103");
rs.setFilter(filter);

See weblogic.jdbc.rowset.SQLPredicate class in Java API Reference for Oracle WebLogic Server.

The default rowset optimistic concurrency control policy is VERIFY_READ_COLUMNS. When the rowset issues an UPDATE or DELETE, it includes all columns that were read from the database in the WHERE clause. This verifies that the value in all columns that were initially read into the rowset have not changed.

In our example update, the rowset issues:

UPDATE employees SET e_salary = 20000 
   WHERE e_id = 1 AND e_salary=10000 AND e_name = 'John Smith';

The VERIFY_MODIFIED_COLUMNS policy only includes the primary key columns and the updated columns in the WHERE clause. It is useful if your application only cares if its updated columns are consistent. It does allow your update to commit if columns that have not been updated have changed since the data has been read.

In our example update, the rowset issues:

UPDATE employees SET e_salary = 20000 
   WHERE e_id = 1 AND e_salary=10000 

The e_id column is included since it is a primary key column. The e_salary column is a modified column so it is included as well. The e_name column was only read so it is not verified.

The VERIFY_SELECTED_COLUMNS includes the primary key columns and columns you specify in the WHERE clause.

WLRowSetMetaData metaData = (WLRowSetMetaData) rowSet.getMetaData();
metaData.setOptimisticPolicy(WLRowSetMetaData.VERIFY_SELECTED_COLUMNS);
// Only verify the e_salary column
metaData.setVerifySelectedColumn("e_salary", true);

metaData.acceptChanges();

In our example update, the rowset issues:

UPDATE employees SET e_salary = 20000 
   WHERE e_id = 1 AND e_salary=10000 

The e_id column is included since it is a primary key column. The e_salary column is a selected column so it is included as well.

The VERIFY_NONE policy only includes the primary key columns in the WHERE clause. It does not provide any additional verification on the database data.

In our example update, the rowset issues:

UPDATE employees SET e_salary = 20000 WHERE e_id = 1

The VERIFY_AUTO_VERSION_COLUMNS includes the primary key columns as well as a separate version column that you specify in the WHERE clause. The rowset will also automatically increment the version column as part of the update. This version column must be an integer type. The database schema must be updated to include a separate version column (e_version). Assume for our example this column currently has a value of 1.

metaData.setOptimisticPolicy(WLRowSetMetaData.
   VERIFY_AUTO_VERSION_COLUMNS);

metaData.setAutoVersionColumn("e_version", true);

metaData.acceptChanges();

In our example update, the rowset issues:

UPDATE employees SET e_salary = 20000, e_version = 2
WHERE e_id = 1 AND e_version = 1

The e_version column is automatically incremented in the SET clause. The WHERE clause verified the primary key column and the version column.

The VERIFY_VERSION_COLUMNS has the rowset check the primary key columns as well as a separate version column. The rowset does not increment the version column as part of the update. The database schema must be updated to include a separate version column (e_version). Assume for our example this column currently has a value of 1.

metaData.setOptimisticPolicy(WLRowSetMetaData.VERIFY_VERSION_COLUMNS);
metaData.setVersionColumn("e_version", true);
metaData.acceptChanges();

In our example update, the rowset issues:

UPDATE employees SET e_salary = 20000
WHERE e_id = 1 AND e_version = 1

The WHERE clause verifies the primary key column and the version column. The rowset does not increment the version column so this must be handled by the database. Some databases provide automatic version columns that increment when the row is updated. It is also possible to use a database trigger to handle this type of update.

The Optimistic policies only verify UPDATE and DELETE statements against the row they are changing. Read-only rows are not verified against the database.

Most databases do not allow BLOB or CLOB columns in the WHERE clause so the rowset never verifies BLOB or CLOB columns.

When multiple tables are included in the rowset, the rowset only verifies tables that have been updated.

The default VERIFY_READ_COLUMNS provides a strong-level of consistency at the expense of some performance. Since all columns that were initially read must be sent to the database and compared in the database, there is some additional overhead to this policy. VERIFY_READ_COLUMNS is appropriate when strong levels of consistency are needed, and the database tables cannot be modified to include a version column.

The VERIFY_SELECTED_COLUMNS is useful when the developer needs complete control over the verification and wants to use application-specific knowledge to fine-tune the SQL.

The VERIFY_AUTO_VERSION_COLUMNS provides the same level of consistency as VERIFY_READ_COLUMNS but only has to compare a single integer column. This policy also handles incrementing the version column so it requires a minimal amount of database setup.

The VERIFY_VERSION_COLUMNS is recommended for production systems that want the highest level of performance and consistency. Like VERIFY_AUTO_VERSION_COLUMNS, it provides a high level of consistency while only incurring a single column comparison in the database. VERIFY_VERSION_COLUMNS requires that the database handle incrementing the version column. Some databases provide a column type that automatically increments itself on updates, but this behavior can also be implemented with a database trigger.

The VERIFY_MODIFIED_COLUMNS and VERIFY_NONE decrease the consistency guarantees, but they also decrease the likelihood of an optimistic conflict. You should consider these policies when performance and avoiding conflicts outweigh the need for higher level of data consistency.

The rowset implementation includes support for JDBC batch operations. Instead of sending each SQL statement individually to the JDBC driver, a batch sends a collection of statements in one bulk operation to the JDBC driver. Batching is disabled by default, but it generally improves performance when large numbers of updates occur in a single transaction. It is worthwhile to benchmark with this option enabled and disabled for your application and database.

The WLCachedRowSet interface contains the methods setBatchInserts(boolean), setBatchDeletes(boolean), and setBatchUpdates(boolean) to control batching of INSERT, DELETE, and UPDATE statements.

• Batching Limitations with and Oracle Database
  

When multiple rows are deleted, the rowset would normally issue a DELETE statement for each deleted row. When group deletes are enabled, the rowset issues a single DELETE statement with a WHERE clause that includes the deleted rows.

For instance, if we were deleting 3 employees from our table, the rowset would normally issue:

DELETE FROM employees WHERE e_id = 3 AND e_version = 1;
DELETE FROM employees WHERE e_id = 4 AND e_version = 3;
DELETE FROM employees WHERE e_id = 5 AND e_version = 10;

When group deletes are enabled, the rowset issues:

DELETE FROM employees 
WHERE e_id = 3 AND e_version = 1 OR
      e_id = 4 AND e_version = 3 OR
      e_id = 5 AND e_version = 10;

You can use the WLRowSetMetaData.setGroupDeleteSize to determine the number of rows included in a single DELETE statement. The default value is 50.