About RowSets

WebLogic Server includes an implementation of Java RowSets according to the specifications indicated in JSR-114. See (http://www.oracle.com/technetwork/java/javase/jdbc/index.html) for details about the specification. The WebLogic rowset implementation also includes extensions to the RowSets specification. These extensions make RowSets more useful in your applications.

A rowset is an extension of a Java ResultSet. Like a ResultSet, a rowset is a Java object that holds tabular data. However, a rowset adds significant flexibility to ResultSet features and reduces or eliminates some ResultSet limitations.

Types of RowSets

The WebLogic Server implementation of rowsets includes the following rowset types and utilities:

The WebLogic rowset implementation includes a life cycle framework that prevents a rowset object from getting into an unhealthy condition. Internally, WebLogic Server sets a life cycle stage for the rowset as the rowset moves through the process described above. To reduce the risk of data loss, WebLogic Server limits the operations you can do on the rowset depending on the life cycle stage of the rowset. For example, when the rowset is in the Updating stage, you can only call updateXXX() methods, such as updateString() and updateInt(), on the rowset until you call updateRow() to complete the update phase.

Some important notes:

If you have pending changes, you cannot re-populate, filter, or sort the rowset. WebLogic Server prevents these operations on the rowset when the rowset data has changed but the changes have not been synchronized with the database to prevent the accidental loss of data changes.

There is no implicit movement of the cursor! You must explicitly move the cursor from row to row.

Rowset life cycle stage is an internal process. There are no public APIs to access it. You cannot set the life cycle stage. When you call acceptChanges() or restoreOriginal(), WebLogic Server rests the life cycle stage of the rowset so you can begin again.

Note:

When using a rowset in a client-side application, the exact same JDBC driver classes must be in the CLASSPATH on both the server and the client. If the driver classes do not match, you may see java.rmi.UnmarshalException exceptions.

See the comments in Example 8-1 for an illustration of the life cycle stages for a rowset from when it is created to when data changes are synchronized with the database.

CachedRowSets

The following sections describe using standard CachedRowSets with WebLogic Server:

Special Programming Considerations and Limitations for CachedRowSets

When designing your application, consider the following information:

Entire RowSet Query Results Stored in Memory

Data Contention

Entire RowSet Query Results Stored in Memory

Because a CachedRowSet does not hold a connection to the database, it must hold the entire query results in memory. If the query result is very large, you may see performance degradation or out-of-memory errors. For large data sets, a ResultSet may be more appropriate because it keeps a connection to the database, so it can hold partial query results in memory and return to the database for additional rows as needed.

Data Contention

CachedRowSets are most suitable for use with data that is not likely to be updated by another process between when the rowset is populated and when data changes in the rowset are synchronized with the database. Database changes during that period will cause data contention. See Handling SyncProviderExceptions with a SyncResolver for more information about detecting and handling data contention.

Code Example

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.

Importing Classes and Interfaces for a CachedRowSet

Creating a CachedRowSet

Rowsets are created from a factory interface. To create a rowset with WebLogic Server, follow these main steps:

Create a RowSetFactory instance, which serves as a factory to create rowset objects for use in your application. You can specify database connection properties in the RowSetFactory so that you can create RowSets with the same database connectivity using fewer lines of code.

RowSetFactory rsfact = RowSetFactory.newInstance();

Create a WLCachedRowSet and cast it as a javax.sql.rowset.CachedRowSet object. By default, the WebLogic newCachedRowSet() RowSetFactory method creates a WLCachedRowSet object. You can use it as-is, but if you prefer to use the standard CachedRowSet object, you can cast the object as such.

CachedRowSet rs = rsfact.newCachedRowSet();

Setting CachedRowSet Properties

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 https://download.oracle.com/javase/6/docs/api/javax/sql/rowset/BaseRowSet.html.

Database Connection Options

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.

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.

Set connectivity properties in the RowSetFactory—When you set database connection properties in the RowSetFactory, all rowsets created from the RowSetFactory inherit the connectivity properties. The preferred method is to lookup a data source and then set the DataSource property in the RowSetFactory with the setDataSource() method.

Populating a CachedRowSet

Populating a rowset is the act of filling the rowset with rows of data. The source of the data is most commonly a relational database. To populate a rowset with data from a database, you can use either of the following methods:

Set an SQL command with the setCommand() method, then execute the command with the execute() method:

Working with Data in a CachedRowSet

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().

Note:

Delimiter identifiers may not be used for column or table names in rowsets. Delimiter identifiers are identifiers that need to be enclosed in double quotation marks when appearing in a SQL statement. They include identifiers that are SQL reserved words (e.g., USER, DATE, etc.) and names that are not identifiers. A valid identifier must start with a letter and contain only letters, numbers, and underscores.

Getting Data from a Row in a RowSet

To get data from a rowset, you use the getXXX methods just as you would with a result set. For example:

Updating a Row in a RowSet

Data updates typically follow this course of events:

Navigate to the row or to an insert row.

Change the row with updateXXX methods.

Complete the operation with updateRow() or insertRow().

Note that completing the operation does not synchronize your changes with the database. Changes are made to the rowset only. You must explicitly synchronize your changes by calling acceptChanges(). For details, see Synchronizing RowSet Changes with the Database later in this section.

When working with a rowset, WebLogic Server internally sets the life cycle stage of the rowset after each operation on the rowset, and then limits further operations you can perform on the rowset based on its current life cycle stage. After you begin modifying a row with update methods, you must complete the operation with updateRow() or insertRow() before you can work with data in any other rows, including moving the cursor to another row. See Programming with RowSets for a complete discussion of rowset life cycle stages and operations allowed for each stage.

To update a row, you move the cursor to the row you want to update, call updateXXX methods on individual columns within the row, then call updateRow() to complete the operation. For example:

rs.first();
rs.updateString(4, "Francis");
rs.updateRow();

Note:

If you are updating same-named columns from more than one table, you must use the column index number to refer to the column in the update statement.

Inserting a Row in a RowSet

To insert a row, you move the cursor to a new insert row, update the column values within the row, then call insertRow() to add the row to the rowset. For example:

Note that you must explicitly move the cursor after inserting a row. There is no implicit movement of the cursor.

Deleting a Row in a RowSet

To delete a row in the rowset, you move the cursor to the row and call deleteRow(). For example:

rs.last();
rs.deleteRow();

Synchronizing RowSet Changes with the Database

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. For more information, 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 for more information.

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();

RowSet MetaData Settings for Database Updates

When populating a rowset with an SQL query, the WebLogic rowset implementation uses the ResultSetMetaData interface to automatically learn the table and column names of the data in the rowset. In many cases, this is enough information for the rowset to generate the required SQL to write changes back to the database. However, some JDBC drivers do not include table and column metadata for the rows returned by the query. When you attempt to synchronize data changes in the rowset with the database, you will see the following error:

java.sql.SQLException: Unable to determine the table name for column:
column_name. Please ensure that you've called WLRowSetMetaData.setTableName to
set a table name for this column.

Without the table name, you can use the rowset for read-only operations only. The rowset cannot issue updates unless the table name is specified programmatically. You may also need to set the primary key columns with the setKeyColumns() method. For example:

rs.setTableName(PHYSICIAN);
rs.setKeyColumns(new int[] { 1 });

See the documentation for the javax.sql.rowset.CachedRowSet interface for more details.

WebLogic RowSet Extensions for Working with MetaData

The following sections describe WebLogic rowset extensions that you can use to obtain or set the appropriate metadata for a rowset:

executeAndGuessTableName and executeAndGuessTableNameAndPrimaryKeys

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.

Note:

These methods rely on support in the DBMS or JDBC driver. They do not work with all DBMSs or all JDBC drivers.

Setting Table and Primary Key Information Using the MetaData Interface

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

Setting the Write Table

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.

RowSets and Transactions

Most database or JDBC applications use transactions, and RowSets support transactions, including JTA transactions. The common use case is to populate the RowSet in Transaction 1. Transaction 1 commits, and there are no database or application server locks on the underlying data. The RowSet holds the data in-memory, and it can be modified or shipped over the network to a client. When the application wishes to commit the changes to the database, it starts Transaction 2 and calls the RowSet's acceptChanges method. It then commits Transaction 2.

Integrating with JTA Global Transactions

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

In the case of a failure or rollback, the data is rolled back from the database, but is not rolled back from the rowset. Before proceeding you should do one of the following:

Call rowset.refresh to update the rowset with data from the database.

Create a new rowset with current data.

Using Local 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

This section provides information on the behavior of rowsets in failed local transactions. The behavior depends on the type of connection object:

Calling connection.commit

In this situation, the connection object is not created by the rowset and initiates a local transaction by calling connection.commit. If the transaction fails or if the connection calls connection.rollback, the data is rolled back from the database, but is not rolled back in the rowset. Before proceeding, you must do one of the following:

Call rowset.refresh to update the rowset with data from the database.

Create a new rowset with current data.

Calling acceptChanges

In this situation, the rowset creates its own connection object and uses it to update the data in rowset by calling acceptChanges. In the case of failure or if the rowset calls connection.rollback, the data is be rolled back from the rowset and also from the database.

Reusing a WebLogic RowSet After Completing a Transaction

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.

FilteredRowSets

The following sections describe using standard FilteredRowSets with WebLogic Server:

FilteredRowSet Characteristics

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

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.

Special Programming Considerations

RowSet Filters are Not Cumulative

Current behavior of WebLogic implementation of a FilteredRowSet is that when you set a filter for the second time on a FilteredRowSet, the new filter replaces the old filter. JSR-114 is not clear on this point. The reference implementation does not behave the same way, it further filters the filtered rows in the rowset. You can accomplish the same effect by changing the second filter to filter on all necessary criteria.

No Pending Changes Before Setting or Changing a Filter

If you have pending changes in a rowset before you set or change the rowset filter, you must either accept the changes (call acceptChanges()) or restore the rowset data to it pre-changed state (call restoreOriginal()). WebLogic Server considers navigating within a rowset to be indicative of a possible change and requires you to call either one of these methods before allowing you to change the rowset filter. Note that acceptChanges() includes a round-trip to the database, whereas restoreOriginal() does not.

FilteredRowSet Code Example

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

Importing Classes and Interfaces for FilteredRowSets

For standard FilteredRowSets, you must import the following classes:

javax.sql.rowset.FilteredRowSet;
weblogic.jdbc.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 for more information.

Creating a FilteredRowSet

Rowsets are created from a factory interface. To create a FilteredRowSet with WebLogic Server, follow these main steps:

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

RowSetFactory rsfact = RowSetFactory.newInstance();

Create a WLCachedRowSet and cast it as a javax.sql.rowset.FilteredRowSet object. By default, the WebLogic newCachedRowSet() RowSetFactory method creates a WLCachedRowSet object. You can use it as-is, but if you prefer to use the standard FilteredRowSet object, you can cast the object as such. For example:

Setting the Filter for a FilteredRowSet

To filter the rows in a FilteredRowSet, you must call the setFilter method and pass a predicate (filter) object as a parameter of the method. The predicate object is an instance of a class that implements the javax.sql.rowset.Predicate interface. With the WebLogic implementation of FilteredRowSets, you can define your own filter or use an instance of the weblogic.jdbc.rowset.SQLPredicate class.

User-Defined RowSet Filter

When defining the filter for a FilteredRowSet, you follow these main steps:

Define a class that implements the javax.sql.rowset.Predicate interface with the filtering behavior you plan to use, such as limiting displayed rows to rows with a value in a particular column. For example, you may want to limit displayed rows based on a range of values for the ID column. The class you define would include logic to filter values for the ID column

Create an instance of the class (a filter) to specify the filtering criteria that you want to use. For example, you may want to see only rows with values in the ID column between 100 and 199.

Call rowset.setFilter() and pass the class as a parameter of the method.

Example 8-3 shows an example of a class that implements the javax.sql.rowset.Predicate interface. This example shows a class that enables you to create a filter that evaluates a case-insensitive version of the value in a column. Example 8-4 shows code to create an instance of the class, which determines the filter criteria, and then set the filter object as the filter for a FilteredRowSet.

WebLogic SQL-Style Filter

WebLogic Server provides the weblogic.jdbc.rowset.SQLPredicate class, which implements the javax.sql.rowset.Predicate interface. You can use the SQLPredicate class to define a filter using SQL-like WHERE clause syntax to filter rows in a rowset. For example:

Working with Data in a FilteredRowSet

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.

WebLogic Server supports two schemas for rowsets: one for the standard WebRowSet and one for the WLCachedRowSet, which was implemented before JSR-114 was finalized.

Special Programming Considerations

The WebLogic WebRowSets implementation supports two XML schemas (and APIs): one for the standard WebRowSet specification (available at http://java.sun.com/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.

JoinRowSets

A JoinRowSet is a number of disconnected RowSet objects joined together in a single rowset by a SQL JOIN. JoinRowSets have the following characteristics:

Each rowset added to the JoinRowSet must have a "match" column specified in the addRowSet method used to add the rowset to the JoinRowSet. For example:

addRowSet(javax.sql.RowSet[] rowset,java.lang.String[] columnName);

You can set the join type using setJoinType method. The following join types are supported:

CROSS_JOIN
FULL_JOIN
INNER_JOIN
LEFT_OUTER_JOIN
RIGHT_OUTER_JOIN

Enables you to join data while disconnected from the database.

JoinRowSets are for read-only use. JoinRowSets cannot be used to update data in the database.

Match columns in a JoinRowSet are limited to four data types: Number, Boolean, Date, and String. Table 8-1 provides more details about data types allowed for a match column in a JoinRowSet.

JDBCRowSets

A JDBCRowSet is a wrapper around a ResultSet object that enables you to use the result set as a JavaBeans component. Note that a JDBCRowSet is a connected rowset. All other rowset types are disconnected rowsets.

Handling SyncProviderExceptions with a SyncResolver

When you call acceptChanges() to propagate changes in a rowset to the database, WebLogic Server compares the original data in the rowset (data since the last synchronization) based on an optimistic concurrency policy with the data in the database. If it detects data changes, it throws a javax.sql.rowset.spi.SyncProviderException. By default, your application does not have to do anything, but the changes in the rowset will not be synchronized in the database.You can design your application to handle these exceptions and process the data changes as is suitable for your system.

Note:

For javax.sql.rowset.CachedRowSets, WebLogic Server compares all original values in all rows in the rowset with the corresponding rows in the database. For weblogic.jdbc.rowset.WLCachedRowSet or other WebLogic extended rowset types, WebLogic Server makes the data comparison based on the optimistic concurrency setting. See Optimistic Concurrency Policies for more details.

Values in the same row in the rowset and database have changed. The syncresolver status is SyncResolver.UPDATE_ROW_CONFLICT.

Your application may need to supply logic to resolve the conflict or may need to present the new data to the user.

Update

Delete

Values in the row in the rowset have been updated, but the row has been deleted in the database. The syncresolver status is SyncResolver.UPDATE_ROW_CONFLICT.

Your application may need to supply logic to decide whether to leave the row as deleted (as it is in the database) or to restore the row and persist changes from the rowset.

To leave the row as deleted, revert the changes to the row in the rowset.

To restore the row with changes, insert a new row with the desired values.

Note that if the row is deleted in the database, there is no conflict value. When you call getConflictValue(), WebLogic Server throws a weblogic.jdbc.rowset.RowNotFoundException.

Delete

Update

The row has been deleted in the rowset, but the row has been updated in the database. The syncresolver status is SyncResolver.DELETE_ROW_CONFLICT.

Your application may need to supply logic to decide whether to delete the row (as it is in the rowset) or to keep the row and persist changes currently in the database.

Note that in this scenario, all values in the row will be conflicted values. To keep the row with the current values in the database, call setResolvedValue to set the resolved value for each column in the row to the current value in the database. To proceed with the delete, call syncprovider.deleteRow().

Delete

Delete

The row has been deleted in the rowset and has been deleted in the database by another process.The syncresolver status is SyncResolver.DELETE_ROW_CONFLICT.

To resolve the SyncProviderException, you must revert the delete operation on the row in the rowset.

Note that there will be no conflict value (not null, either) for any column in the row. When you call getConflictValue(), WebLogic Server throws a weblogic.jdbc.rowset.RowNotFoundException.

Insert

Insert

If a row is inserted in the rowset and a row is inserted in the database, a primary key conflict may occur, in which case an SQL exception will be thrown. You cannot directly handle this conflict type using a SyncResolver because a SyncProviderException is not thrown.

SyncResolver Code Example

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.

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.

Navigating in a SyncResolver Object

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.

Note:

In the WebLogic rowsets implementation, a value conflict occurs if any value in a row in the database differs from the values read into the rowset when the rowset was created or when it was last synchronized.

Setting the Resolved Value for a RowSet Data Synchronization Conflict

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.

Synchronizing Changes

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.

WLCachedRowSets

A WLCachedRowSet is an extension of CachedRowSets, FilteredRowSets, WebRowSets, and SortedRowSets. WLCachedRowSet have the following characteristics:

In the WebLogic Server RowSets implementation, all rowsets originate as a WLCachedRowset. WLCachedRowSet can be interchangeably used as any of the standard rowset types that it extends.

WLCachedRowSet include convenience methods that help make using rowsets easier and also include methods for setting optimistic concurrency options and data synchronization options.

It may not be possible to read or update an SQLXML datatype object. The JDBC 4.0 specification does not require vendors to make SQLXML objects readable after they have been set. Once WebLogic Server sets the value for an SQLXML datatype object, it cannot be read or updated.

SharedRowSets

Rowsets can be used by a single thread. They cannot be shared by multiple threads. A SharedRowSet extends CachedRowSets so that additional CachedRowSets can be created for use in other threads based on the data in an original CachedRowSet. SharedRowSets have the following characteristics:

Each SharedRowSet is a shallow copy of the original rowset (with references to data in the original rowset instead of a copy of the data) with its own context (cursor, filter, sorter, pending changes, and sync provider).

When data changes from any of the SharedRowSets are synchronized with the database, the base CachedRowSet is updated as well.

Using SharedRowSets can increase performance by reducing the number of database round-trips required by an application.

To create a SharedRowSet, you use the createShared() method in the WLCachedRowSet interface and cast the result as a WLCachedRowSet. For example:

WLCachedRowSet sharedrowset = (WLCachedRowSet)rowset.createShared();

SortedRowSets

A SortedRowSet extends CachedRowSets so that rows in a CachedRowSet can be sorted based on the Comparator object provided by the application. SortedRowSets have the following characteristics:

Sorting is set in a way similar to way filtering is set for a FilteredRowSet, except that sorting is based on a java.util.Comparator object instead of a javax.sql.rowset.Predicate object:

The application creates a Comparator object with the desired sorting behavior.

The application then sets the sorting criteria with the setSorter(java.util.Comparator) method.

Sorting is done in memory rather than depending on the database management system for sort processing. Using SortedRowSets can increase application performance by reducing the number of database round-trips.

WebLogic Server provides the SQLComparator object, which implements java.util.Comparator. You can use it to sort rows in a SortedRowSet by passing the list of columns that you want use as sorting criteria. For example:

SQLPredicate, a SQL-Style RowSet Filter

This section provides information about the SQLPredicate class.

What is SQLPredicate?

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.

SQLPredicate Grammar

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:

Optimistic Concurrency Policies

In most cases, populating a rowset with data and updating the database occur in separate transactions. The underlying data in the database can change in the time between the two transactions. The WebLogic Server rowset implementation (WLCachedRowSet) uses optimistic concurrency control to ensure data consistency.

With optimistic concurrency, RowSets work on the assumption that multiple users are unlikely to change the same data at the same time. Therefore, as part of the disconnected rowset model, the rowset does not lock database resources. However, before writing changes to the database, the rowset must check to make sure that the data to be changed in the database has not already changed since the data was read into the rowset.

The UPDATE and DELETE statements issued by the rowset include WHERE clauses that are used to verify the data in the database against what was read when the rowset was populated. If the rowset detects that the underlying data in the database has changed, it issues an OptimisticConflictException. The application can catch this exception and determine how to proceed. Typically, applications will refresh the updated data and present it to the user again.

The WLCachedRowSet implementation offers several optimistic concurrency policies that determine what SQL the rowset issues to verify the underlying database data:

VERIFY_READ_COLUMNS

VERIFY_MODIFIED_COLUMNS

VERIFY_SELECTED_COLUMNS

VERIFY_NONE

VERIFY_AUTO_VERSION_COLUMNS

VERIFY_VERSION_COLUMNS

To illustrate the differences between these policies, we will use an example that uses the following:

In the example for each of the optimistic concurrency policies listed below, the rowset will read this row from the employees table and set John Smith's salary to 20000. The example will then show how the optimistic concurrency policy affects the SQL code issued by the rowset.

VERIFY_READ_COLUMNS

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.

VERIFY_MODIFIED_COLUMNS

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.

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.

VERIFY_NONE

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

VERIFY_AUTO_VERSION_COLUMNS

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.

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

VERIFY_VERSION_COLUMNS

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.

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.

Optimistic Concurrency Control Limitations

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.

Choosing an Optimistic Policy

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.

Performance Options

Consider the following performance options when using RowSets.

JDBC Batching

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.

Note:

The setBatchInserts, setBatchDeletes, or setBatchUpdates methods must be called before the acceptChanges method is called.

Batching Limitations with and Oracle Database

Since the WLCachedRowSet relies on optimistic concurrency control, it needs to determine whether an update or delete command has succeeded or an optimistic conflict occurred. The WLCachedRowSet implementation relies on the JDBC driver to report the number of rows updated by a statement to determine whether a conflict occurred or not. In the case where 0 rows were updated, the WLCachedRowSet knows that a conflict did occur.

Oracle JDBC drivers return java.sql.Statement.SUCCESS_NO_INFO when batch updates are executed, so the rowset implementation cannot use the return value to determine whether a conflict occurred.

When the rowset detects that batching is used with an Oracle database, it automatically changes its batching behavior:

Batched inserts perform as usual since they are not verified.

Batched updates run as normal, but the rowset issues an extra SELECT query to check whether the batched update encountered an optimistic conflict.

Batched deletes use group deletes since this is more efficient than executing a batched delete followed by a SELECT verification query.

Group Deletes

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: