Programming WebLogic JDBC

Using RowSets with WebLogic Server

This section includes the following information about using JDBC rowSets with WebLogic Server:

About RowSets

RowSets are a JDBC 2.0 extension to the java.sql.ResultSet interface. The WebLogic Server implementation of RowSets provides a disconnected RowSet. In this model, a RowSet object is populated from the database and then the database cursor and connection are immediately released. The RowSet is disconnected from the database and provides a ResultSet interface for the cached data. The user may read, modify, delete, or even insert new rows into the RowSet in memory. When acceptChanges is called, the RowSet takes all of the in-memory updates and writes them back to the database.

In most cases, populating a RowSet data and updating the database occur in separate transactions. The RowSet implementation uses optimistic concurrency control to ensure data consistency.

WebLogic Server RowSets implementation implements and extends java.io.Serializable, so the RowSet can be sent as an RMI parameter or return value. For example, an EJB method could populate a RowSet from a database query and then return the RowSet to a client.

RowSets can also read and write their state and metadata to an XML format. The RowSet metadata is written as an XML schema document and the RowSet data is written as an XML document that conforms to the schema. You can also populate the metadata and cached data for a RowSet from XML documents.

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.

Creating RowSets

RowSets are created from a factory interface.

import weblogic.jdbc.rowset.RowSetFactory;
import weblogic.jdbc.rowset.WLCachedRowSet;

RowSetFactory factory = RowSetFactory.newInstance();
WLCachedRowSet rowSet = factory.newCachedRowSet();

Working with Data in a RowSet

The following sections describe how to populate a RowSet, manipulate the data in the RowSet, and then flush the changes to the database.

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.

Populating a RowSet

After the RowSet object is created, its cache can be filled with data. Once a RowSet has been populated, it is disconnected from the database and acts as a memory cache. There are three methods sources for populating the RowSet's cache with data:

An existing result set. See Populating a RowSet from an Existing ResultSet.
A Database Query. See Populating a RowSet from a DataSource and Query.
An XML Document. See Populating a RowSet from an XML Document.

Populating a RowSet from an Existing ResultSet

A RowSet can be populated from an existing JDBC ResultSet. This is a common case when data is read from a stored procedure or JDBC code already exists to load the data. The RowSet can be loaded by calling its populate method.

rowSet.populate(myResultSet);

Populating a RowSet from a DataSource and Query

A RowSet can be populated by providing database connection information and a SQL query. First, you provide the CachedRowSet with information needed to get a JDBC connection. This can be done by providing a javax.sql.DataSource object, a DataSource JNDI name, or a JDBC Driver URL. The DataSource API is recommended since it is the standard JDBC 2.0 method for retrieving JDBC connections. Also, only connections retrieved via the DataSource API can participate in XA/2PC transactions.

rowSet.setDataSourceName("myDataSource");

If necessary, the setUsername and setPassword methods can be used to set the credentials necessary to access your DataSource in WebLogic Server.

rowSet.setUsername("weblogic");
rowSet.setPassword("weblogic");

Next, specify a SQL query to use to load the database. For instance, the following query populates the RowSet with all employees with a salary greater than 50000:

rowSet.setCommand("select e_name, e_id from employees WHERE e_salary > ?");
rowSet.setInt(1, 50000);

Finally, run the execute method that runs the specified query and loads the RowSet with data. The execute method closes the JDBC connection. The RowSet does not maintain open cursors or connections to the database.

rowSet.execute();

Retrieving Data from a RowSet

Because the RowSet is an extension to the ResultSet interfaces, it inherits all of the ResultSet methods for retrieving data. As with a ResultSet, you can iterate through a RowSet using the next() method. The getXXX methods can be used to read data from the RowSet.

while(rowSet.next()) {
String name = rowSet.getString("e_name");
int id      = rowSet.getInt("e_id");

System.out.println("Read name: "+name+ " id: "+id);
}

while(rowSet.next()) {
  String name = rowSet.getString("e_name");
  int id      = rowSet.getInt("e_id");

  System.out.println("Read name: "+name+ " id: "+id);
}

Updating Data in a RowSet

RowSets use the ResultSet updateXXX methods for updating data.

It is important to understand that RowSet updates are kept in memory only. Updates are written back to the database only when you call the acceptChanges method.

// move back to the beginning of the rowSet
rowSet.beforeFirst();
while(rowSet.next()) {
  String name = rowSet.getString("e_name");
  // convert to upper case
  name = name.toUpper();
  rowSet.updateString("e_name", name);
  rowSet.updateRow();
}

// Call acceptChanges to write all of the in-memory updates to the database

rowSet.acceptChanges();

Note: You must call RowSet.updateRow or RowSet.cancelRowUpdates before moving the RowSet's cursor with the next method.

Deleting Data from a RowSet

Deleting rows is very similar to updating rows. The deleteRow() method marks a row for deletion. When you call the acceptChanges method, the RowSet issues the appropriate SQL to delete the selected rows.

// move back to the beginning of the rowSet
rowSet.beforeFirst();

while(rowSet.next()) {

  String name = rowSet.getString("e_name");

  if ("Rob".equals(name)) {
    rowSet.deleteRow();
  }
}

// When acceptChanges all of the in-memory deletions are written to
// the database

rowSet.acceptChanges();

Inserting Data into a RowSet

Like ResultSets, RowSets have the concept of a special insert row. To insert data, call moveToInsertRow and then update the values in the row. The insertRow method is called to indicate that the updates are done. You can either insert another row, or call moveToCurrentRow to return to the read data. When you call acceptChanges, all inserted rows are sent to the database.

rowSet.moveToInsertRow();

rowSet.updateString("e_name", "Seth");
rowSet.updateInt("e_id", 2);
rowSet.insertRow();

rowSet.updateString("e_name", "Matt");
rowSet.updateInt("e_id", 3);
rowSet.insertRow();

rowSet.moveToCurrentRow();

// issues SQL INSERTs to database
rowSet.acceptChanges();

Flushing Changes to the Database

A RowSet acts like a database cache, and all updates to it occur in memory. To flush these changes back to the database, call the acceptChanges method.

The RowSet's acceptChanges method uses the DataSource or connection information to acquire a database connection. It then issues all of the INSERT, UPDATE, or DELETE statements that have been made in memory to the database.

Since the RowSet was disconnected from the database and not holding any locks or database resources, it is possible that the underlying data in the database has been changed since the RowSet was populated. The RowSet implementation uses optimistic concurrency control on its UPDATE and DELETE statements to check for stale data. See Optimistic Concurrency Policies for details.

RowSet Meta Data

The RowSet API provides a getMetaData method for access to the associated javax.sql.RowSetMetaData object. The WLCachedRowSet implementation provides a WLRowSetMetaData interface that extends the standard RowSetMetaData with additional functionality.

The metadata can be accessed with:

WLRowSetMetaData metaData = (WLRowSetMetaData) rowSet.getMetaData();

Optimistic Concurrency Policies

In most cases, populating a RowSet with data and updating the database occur in separate transactions. The underlying data in the database can change in the time between the two transactions. The WebLogic Server RowSet implementation 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:

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.

VERIFY_READ_COLUMNS

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

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';

VERIFY_MODIFIED_COLUMNS

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

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.

VERIFY_SELECTED_COLUMNS

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.

VERIFY_NONE

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

In our example update, the RowSet issues:

UPDATE employees SET e_salary = 20000 WHERE e_id = 1

VERIFY_AUTO_VERSION_COLUMNS

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

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.

VERIFY_VERSION_COLUMNS

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

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.

Optimistic Concurrency Control Limitations

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

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

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

Choosing an Optimistic Policy

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

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

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

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

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

MetaData Settings for RowSet Updates

When data is read into a RowSet, the RowSet implementation uses the ResultSetMetaData interface to automatically learn the table and column names of the read data. In many cases, this is enough information for the RowSet to generate the required SQL for writing changes back to the database. However, many JDBC drivers just return an empty string when asked for the table name of a given column. Without the table name, the RowSet can be used for read-only operations only. The RowSet cannot issue updates unless the table name is specified programmatically.

The RowSet implementation provides an extended MetaData interface that allows you to specify schema information that cannot be automatically determined via the JDBC Driver. The WLRowSetMetaData interface can be used to set the schema information.

executeAndGuessTableName and executeAndGuessTableNameAndPrimaryKeys

When a RowSet is populated via a SQL query, the execute() method is generally used 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.

Setting Table and Primary Key Information Using the MetaData Interface

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

WLRowSetMetaData metaData = (WLRowSetMetaData) rowSet.getMetaData();

// convenience method to set 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 to identify primary key columns.

metaData.setPrimaryKeyColumn("e_id", true);

Setting the Write Table

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

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

RowSets and Transactions

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

Integrating with JTA Global Transactions

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

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

Behavior of Rowsets Using Global Transactions

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

Call rowset.refresh to update the rowset with data from the database.
Create a new rowset with current data.

Using Local Transactions

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

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

Behavior of Rowsets Using Local Transactions

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

Calling connection.commit

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

Call rowset.refresh to update the rowset with data from the database.
Create a new rowset with current data.

Calling acceptChanges

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

Performance Options

Consider the following performance options when using RowSets.

JDBC Batching

The RowSet implementation includes support for JDBC 2.0 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.

Oracle Batching Limitations

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

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

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

Batched inserts perform as usual since they are not verified.

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

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

Group Deletes

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

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

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;

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

RowSets and XML

The WLCachedRowSet implementation provides support for writing its metadata as an XML schema document and its data as an XML document that conforms to the schema. The WLCachedRowSet can also populate itself and its metadata from an existing XML schema and XML document.

For instance, a RowSet can be converted to XML and sent as an XML message to another process. The other process could rebuild the RowSet instance in memory, read and update data, and send the response back as another XML message. Finally the original server could convert the XML message back to a RowSet and update the database.

Writing a RowSet Instance as XML

The WLRowSetMetaData interface contains the method writeXMLSchema to write the RowSetMetaData as an XML schema document. The WLRowSetMetaData interface has a writeXML method for converting the RowSet's data into an XML instance document.

  XMLOutputStreamFactory xoFactory = 
    XMLOutputStreamFactory.newInstance();

  WLRowSetMetaData metaData = (WLRowSetMetaData) rowSet.getMetaData();

  XMLOutputStream xos = null;

  // Write XSD Schema
  try {
    xos = xoFactory.newDebugOutputStream(new
      FileOutputStream("rowset.xsd");
    metaData.writeXMLSchema(xos);
  } finally {
    if (xos != null) xos.close();
  }

  // Write XML Instance data
  try {
    xos = xoFactory.newDebugOutputStream(new
      FileOutputStream("rowset.xml");
    rowSet.writeXML(xos);
  } finally {
    if (xos != null) xos.close();
  }

Populating a RowSet from an XML Document

The WLRowSetMetaData interface contains the method loadXMLSchema to load the RowSetMetaData from an XML schema document. The WLRowSetMetaData interface has a loadXML method for populating from an XML instance document.

  XMLInputStreamFactory xiFactory =
    XMLInputStreamFactory.newInstance();
  XMLInputStream xis = null;
  WLCachedRowSet rowSet = factory.newCachedRowSet();
  WLRowSetMetaData metaData = (WLRowSetMetaData) rowSet.getMetaData();
  // Read XSD
  try {
    xis = xiFactory.newInputStream(new FileInputStream("rowset.xsd"));
          metaData.loadXMLSchema(xis);

  } finally {
    if (xis != null) xis.close();
  }

  // Read XML

  try {
    xis = xiFactory.newInputStream(new FileInputStream("rowset.xml"));
    rs.loadXML(xis);

  } finally {
    if (xis != null) xis.close();
  }

JDBC Type to XML Schema Type Mapping

Table 6-1 JDBC Type to XML Schema Type Mapping

JDBC Type	XML Schema Type
`BIGINT`	`xsd:long`
`BINARY`	`xsd:base64Binary`
`BIT`	`xsd:boolean`
`BLOB`	`xsd:base64Binary`
`BOOLEAN`	`xsd:boolean`
`CHAR`	`xsd:string`
`DATE`	`xsd:dateTime`
`DECIMAL`	`xsd:decimal`
`DOUBLE`	`xsd:decimal`
`FLOAT`	`xsd:float`
`INTEGER`	`xsd:int`
`LONGVARBINARY`	`xsd:base64Binary`
`LONGVARCHAR`	`xsd:string`
`NUMERIC`	`xsd:integer`
`REAL`	`xsd:double`
`SMALLINT`	`xsd:short`
`TIME`	`xsd:dateTime`
`TIMESTAMP`	`xsd:dateTime`
`TINYINT`	`xsd:byte`
`VARBINARY`	`xsd:base64Binary`
`VARCHAR`	`xsd:string`

XML Schema Type to JDBC Type Mapping

Table 6-2 XML Schema Type to JDBC Type Mapping

XML Schema Type	JDBC Type
`base64Binary`	`BINARY`
`boolean`	`BOOLEAN`
`byte`	`SMALLINT`
`dateTime`	`DATE`
`decimal`	`DECIMAL`
`double`	`DOUBLE`
`float`	`FLOAT`
`hexBinary`	`BINARY`
`int`	`INTEGER`
`integer`	`NUMERIC`
`long`	`BIGINT`
`short`	`SMALLINT`
`string`	`VARCHAR`

Multi-table RowSet Mapping

RowSets can be populated from SQL queries that return columns from multiple tables. It is important to understand the RowSet semantics when dealing with multiple tables and the SQL issued by RowSets in multi-table scenarios.

The RowSet optimistic concurrency control policies only verify tables that have been updated. If a RowSet is populated with columns from tables t1 and t2, and column C from table t1 is updated, there will be no SQL UPDATE or SELECT that verifies the values read from table t2.

RowSets do not recognize foreign key or other constraints between tables, so when updating multiple tables, it is possible that RowSet updates will fail because of integrity constraints between tables.

Multi-table RowSets work well when a RowSet is a join from N tables with a single write table. For instance, a query might join in several tables but only update the employees table. In this case, the programmer should call setWriteTableName to ensure that updates and deletes only apply to the write table.

Another common multi-table scenario is multiple tables that share the same primary key space. This is one logical table that has been split over multiple physical tables in the database. In this scenario, the RowSet will be able to update multiple tables.

Since multi-table RowSets can have complicated update semantics, it is recommended that users set the write table name and only update a single table.

Multi-Table RowSet Example

Consider a simple order entry system that has customer and order tables.

CREATE TABLE customer (
  id integer primary key,
  name varchar(200),
  email varchar(200)
);

CREATE TABLE order (
  id integer primary key,
  sku integer,
  quantity integer,
  customer_id integer,
  foreign key customer_id references customer(id)
);

This example shows a 1 to many relationship where each customer may have many orders.

A customer portal application might issue a query that loads a customer's current orders and some information about the customer with SQL like this:

SELECT o.id, o.sku, o.quantity, c.name, c.email 
       FROM order o, customer c
       WHERE c.id = o.customer_id

This data will be read into the RowSet with one row containing the matching order and customer columns from the SQL Join.

In cases like this, it is recommended that the many side (order) be set as the write table. This ensures that the one side (customer) is read-only. This allows the user to update details in their order, but will prevent changes to their customer record. This is especially useful for deletes since calling deleteRow will delete the order record but will not delete the customer.