The following sections describe characteristics and usage of WebLogic RowSets:
WebLogic Server includes an implementation of Java RowSets according to the specifications indicated in JSR-114. See the Sun Web site (http://java.sun.com/products/jdbc/download.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.
The WebLogic Server implementation of rowsets includes the following rowset types and utilities:
Standard RowSet Types:
WebLogic RowSet Extensions:
The WebLogic Server rowset implementation was designed with the expectation that you would work with a rowset in the following set of steps:
Create and configure the rowset — define the query, database connection, and other properties.
Populate the rowset with data — specify query parameters and execute the query.
Optionally, work with rowset metadata.
Optionally set the filter or sorter for the rowset.
Manipulate the data in the rowset — insert, update, and delete.
Synchronize data changes from the rowset to the database.
After synchronizing changes, the process can repeat starting with step 2 or 3, depending on the way your application is designed. See Reusing a WebLogic RowSet After Completing a Transaction.
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 theCLASSPATH 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 6-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.
The following sections describe using standard CachedRowSets with WebLogic Server:
Also see WLCachedRowSets for information about using WebLogic extensions to the standard CachedRowSet object.
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 thin clients and 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
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.
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.
Example 6-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.
Example 6-1 Cached RowSet Code Example
import javax.sql.rowset.CachedRowSet;
import weblogic.jdbc.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.
RowSetFactory rsfact = RowSetFactory.newInstance();
CachedRowSet rs = rsfact.newCachedRowSet();
//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; weblogic.jdbc.rowset.RowSetFactory;
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();
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://java.sun.com/javase/6/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("com.pointbase.jdbc.jdbcUniversalDriver");
rs.setUrl("jdbc:pointbase:server://localhost/demo");
rs.setUsername("examples");
rs.setPassword("examples");
rs.execute();
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.
//Lookup DataSource and get a connection
ctx = new InitialContext(ht);
javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup ("myDS");
//Set the datasource property on the RowSetFactory
rsfact.setDataSource(ds);
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:
rs.setCommand("SELECT ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME,
PHONE, EMAIL FROM PHYSICIAN");
rs.execute();
From an existing result set using the populate() method:
rs.populate(resultSet);
Note:
If using a result set that isResultSet.TYPE_FORWARD_ONLY, a SQLException will be thrown if you attempt to populate a row set with the following conditions:
If you call CachedRowset.populate(ResultSet rs) when the result set cursor is at a position beyond row 1.
If you call CachedRowset.populate(ResultSet rs, int newPosition) when newPosition is less than the current result set cursor position.
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 for more information.
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.To get data from a rowset, you use the getXXX methods just as you would with a result set. For example:
while (rs.next ())
{
int id = rs.getInt (1);
String fname = rs.getString ("FIRST_NAME");
String mname = rs.getString ("MIDDLE_NAME");
String lname = rs.getString ("LAST_NAME"));
}
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.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:
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();
rs.moveToCurrentRow();
Note that you must explicitly move the cursor after inserting a row. There is no implicit movement of the cursor.
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();
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.
The following sections describe WebLogic rowset extensions that you can use to obtain or set the appropriate metadata for a rowset:
executeAndGuessTableName and executeAndGuessTableNameAndPrimaryKeys
Setting Table and Primary Key Information Using the MetaData Interface
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.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 the Javadoc for weblogic.jdbc.rowset.WLRowSetMetaData for more details.
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.
Note:
JSR-114 provides the CachedRowSet.setTableName (seehttp://java.sun.com/javase/6/docs/api/javax/sql/rowset/CachedRowSet.html#setTableName(java.lang.String)) that provides the same functionality as the WebLogic CachedRowSetMetaData.setWriteTableName method. Calling either method marks those columns that do NOT belong to the write table as read-only. WebLogic also provides the CachedRowSetMetaData.setTableName method which is used to map which table a column belongs to. When setting the write table using setTableName, be careful to implement the method using the appropriate API for your application.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.
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.
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.
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.
This section provides information on the behavior of rowsets in failed local transactions. The behavior depends on the type of connection object:
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.
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.
The following sections describe using standard FilteredRowSets with WebLogic Server:
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 for more information.
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.
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. Oracle decided this was the proper way to implement the setFilter method. Sun's reference implementation does not behave the same way. Sun's implementation 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.
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.
The following example shows how to create a cached rowset and then apply and change a filter using the WebLogic Server SQLPredicate.
Example 6-2 FilteredRowSet Code Example
import javax.sql.rowset.FilteredRowSet;
import weblogic.jdbc.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 WLCachedRowSet and cast it to a FilteredRowSet.
RowSetFactory rsfact = RowSetFactory.newInstance();
FilteredRowSet rs = rsfact.newCachedRowSet();
//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; 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.
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:
FilteredRowSet rs = rsfact.newCachedRowSet();
Property options for a FilteredRowSet are the same as those for a CachedRowSet. See Setting CachedRowSet Properties.
Database connection options for a FilteredRowSet are the same as those for a CachedRowSet. See Database Connection Options.
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 for more information.
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.
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 6-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 6-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.
Example 6-3 Filter Class that Implements javax.sql.rowset.Predicate
package examples.jdbc.rowsets;
import javax.sql.rowset.Predicate;
import javax.sql.rowset.CachedRowSet;
import javax.sql.RowSet;
import java.sql.SQLException;
public class SearchPredicate implements Predicate, java.io.Serializable {
private boolean DEBUG = false;
private String col = null;
private String criteria = null;
//Constructor to create case-insensitive column - value comparison.
public SearchPredicate(String col, String criteria) {
this.col = col;
this.criteria = criteria;
}
public boolean evaluate(RowSet rs) {
CachedRowSet crs = (CachedRowSet)rs;
boolean bool = false;
try {
debug("evaluate(): "+crs.getString(col).toUpperCase()+" contains "+
criteria.toUpperCase()+" = "+
crs.getString(col).toUpperCase().contains(criteria.toUpperCase()));
if (crs.getString(col).toUpperCase().contains(criteria.toUpperCase()))
bool = true;
} catch(Throwable t) {
t.printStackTrace();
throw new RuntimeException(t.getMessage());
}
return bool;
}
public boolean evaluate(Object o, String s) throws SQLException {
throw new SQLException("String evaluation is not supported.");
}
public boolean evaluate(Object o, int i) throws SQLException {
throw new SQLException("Int evaluation is not supported.");
}
}
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:
SQLPredicate filter = new SQLPredicate("ID >= 103");
rs.setFilter(filter);
See SQLPredicate, a SQL-Style RowSet Filter for more information.
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.
A WebRowSet is a cached rowset that can read and write a rowset in XML format. WebRowSets have the following characteristics:
Uses the readXml(java.io.InputStream iStream) method to populate the rowset from an XML source.
Uses the writeXml(java.io.OutputStream oStream) method to write data and metadata in XML for use by other application components or to send to a remote client.
The XML code used to populate the rowset or written from the rowset conforms to the standard WebRowSet XML Schema definition available at http://java.sun.com/xml/ns/jdbc/webrowset.xsd.
For more information, see the Sun Web site at http://java.sun.com/products/jdbc/download.html and the Javadoc for the javax.sql.rowset.WebRowSet interface at http://java.sun.com/javase/6/docs/api/javax/sql/rowset/WebRowSet.html.
Note:
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.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 offers the following benefits:
Has more element types.
Is used by rowsets in Oracle Workshop for WebLogic Platform.
To interact with other rowset implementations, you must use the standard schema.
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 6-1 provides more details about data types allowed for a match column in a JoinRowSet.
Table 6-1 Data Types Allowed for Match Columns
| Left Data Type in the Join | Allowed Right Data Types in the Join |
|---|---|
|
Number |
Number String |
|
Boolean |
Boolean String |
|
Date |
Date String |
|
String |
String Number Boolean Date |
For more information about JoinRowSets, see the Javadoc for the javax.sql.rowset.Joinable (http://java.sun.com/javase/6/docs/api/javax/sql/rowset/Joinable.html) and JoinRowSet interfaces (http://java.sun.com/javase/6/docs/api/javax/sql/rowset/JoinRowSet.html).
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.
For more information, see the Javadoc for the javax.sql.rowset.JdbcRowSet interface at http://java.sun.com/javase/6/docs/api/javax/sql/rowset/JdbcRowSet.html.
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:
Forjavax.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.The main steps for handling a SyncProviderException are:
Catch the javax.sql.rowset.spi.SyncProviderException.
Get the SyncResolver object from the exception. See Getting a SyncResolver Object.
Page through conflicts using nextConflict() or any other navigation method. Navigating in a SyncResolver Object.
Determine the correct value, then set it with setResolvedValue(), which sets the value in the rowset. See Setting the Resolved Value for a RowSet Data Synchronization Conflict.
Repeat steps 3 and 4 for each conflicted value.
Call rowset.acceptChanges() on the rowset (not the SyncResolver) to synchronize changes with the database using the new resolved values. See Synchronizing Changes.
For more details about SyncResolvers and the SyncProviderException, see the RowSets specification or the Javadoc for the SyncResolver interface.
Note:
Before you begin to resolve the SyncProviderException, make sure that no other processes will update the data.Table 6-2 lists the types of conflict scenarios that can occur when synchronizing data changes from a rowset to the database.
Table 6-2 Conflict Types When Synchronizing RowSet Changes in the Database
| RowSet Data Change Type | Database Data Change Type | Notes |
|---|---|---|
|
Update |
Update |
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.
Note that if the row is deleted in the database, there is no conflict value. When you call |
|
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 |
|
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 |
|
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. |
Example 6-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.
Example 6-5 SyncResolver Abbreviated Code 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.
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.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.
A WLCachedRowSet is an extension of CachedRowSets, FilteredRowSets, WebRowSets, and SortedRowSets. JoinRowSets have the following characteristics:
In the WebLogic Server RowSets implementation, all rowsets originate as a WLCachedRowset. WLCachedRowSets can be interchangeably used as any of the standard rowset types that it extends.
WLCachedRowSets include convenience methods that help make using rowsets easier and also include methods for setting optimistic concurrency options and data synchronization options.
For more information, see the Javadoc for the weblogic.jdbc.rowset.WLCachedRowSet interface.
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();
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:
rs.setSorter(new weblogic.jdbc.rowset.SQLComparator("columnA,columnB,columnC"));
For more information, see the Javadocs for the following:
weblogic.jdbc.rowset.SortedRowSet interface
This section provides information about the SQLPredicate class.
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);
For more information, see the Javadoc for the weblogic.jdbc.rowset.SQLPredicate class.
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:
A very simple employees table with 3 columns:
CREATE TABLE employees ( e_id integer primary key, e_salary integer, e_name varchar(25) );
A single row in the table:
e_id = 1, e_salary = 10000, and e_name = 'John Smith'
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.
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.
Consider the following performance options when using RowSets.
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:
ThesetBatchInserts, setBatchDeletes, or setBatchUpdates methods must be called before the acceptChanges method is called.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.
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.