![]() ![]() ![]() ![]() ![]() ![]() |
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:
The Weblogic Server rowset implementation was designed with the expectation that you would work with a rowset in the following set of steps:
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 lifecycle framework that prevents a rowset object from getting into an unhealthy condition. Internally, WebLogic Server sets a lifecycle 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 lifecycle 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.
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 Listing 6-1 for an illustration of the lifecycle 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:
When designing your application, consider the following information:
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.
Listing 6-1 shows the basic workflow of a CachedRowSet. It includes comments that describe each major operation and its corresponding rowset lifecycle stage. Following the code example is a more detailed explanation of each of the major sections of the example.
import javax.sql.rowset.CachedRowSet;
import 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:
RowSetFactory rsfact = RowSetFactory.newInstance(); |
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.
In most applications, you populate a rowset with data from a database. You can set rowset database connectivity in any of the following ways:
setDataSourceName()
method to specify the JNDI name of a JDBC datasource. When you call execute()
and acceptChanges()
, the rowset gets a database connection from the datasource, uses it, and returns it to the pool of connections in the datasource. This is a preferred method.
rs.setDataSourceName(examples-dataSource-demoPool); |
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.
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(); |
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:
Note: | If using a result set that is ResultSet.TYPE_FORWARD_ONLY , a SQLException will be thrown if you attempt to populate a row set with the following conditions: |
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:
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 lifecycle 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 lifecycle 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 lifecycle 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.
To delete a row in the rowset, you move the cursor to the row and call deleteRow()
. For example:
rs.last();
rs.deleteRow();
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 lifecycle 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:
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");
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 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:
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:
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.
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:
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:
javax.sql.rowset.Predicate
object supplied by the application and set with the setFilter()
method. javax.sql.rowset.Predicate
interface. The Predicate interface includes the public boolean evaluate(RowSet rs)
method, which evaluates each row in the rowsettrue
, the row is available and visible.false
, the row is not available or visible.See Setting the Filter for a FilteredRowSet for more information.
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. BEA 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.
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:
RowSetFactory rsfact = RowSetFactory.newInstance(); |
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:
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 columnrowset.setFilter()
and pass the class as a parameter of the method.
Listing 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. Listing 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.
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.");
}
}
SearchPredicate pred = new SearchPredicate(ROWSET_LASTNAME, lastName);
rs.setFilter(pred);
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:
readXml(java.io.InputStream iStream)
method to populate the rowset from an XML source.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.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.
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. |
A JoinRowSet is a number of disconnected RowSet objects joined together in a single rowset by a SQL JOIN. JoinRowSets have the following characteristics:
addRowSet(javax.sql.RowSet[] rowset,java.lang.String[] columnName); |
CROSS_JOIN FULL_JOIN INNER_JOIN LEFT_OUTER_JOIN RIGHT_OUTER_JOIN |
For more information about JoinRowSets, see the Javadoc for the javax.sql.rowset.Joinable and JoinRowSet interfaces.
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.
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. |
The main steps for handling a SyncProviderException
are:
javax.sql.rowset.spi.SyncProviderException
. nextConflict()
or any other navigation method. Navigating in a SyncResolver Object.setResolvedValue()
, which sets the value in the rowset. See Setting the Resolved Value for a RowSet Data Synchronization Conflict.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.
Listing 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.
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:
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:
setResolvedValue()
, the original value becomes the current value 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:
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:
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:
java.util.Comparator
object instead of a javax.sql.rowset.Predicate
object: 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 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.
(colA + ColB) >=100. |
//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:
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.
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: | The setBatchInserts , 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.
![]() ![]() ![]() |