How to use CachedRowSetObjects in java

A CachedRowSet object is special in that it can operate without being connected to its data source, that is, it is a disconnected RowSet object. It gets its name from the fact that it stores (caches) its data in memory so that it can operate on its own data rather than on the data stored in a database.
The CachedRowSet interface is the superinterface for all disconnected RowSet objects, so everything demonstrated here also applies to WebRowSet, JoinRowSet, and FilteredRowSet objects.
Note that although the data source for a CachedRowSet object (and the RowSet objects derived from it) is almost always a relational database, a CachedRowSet object is capable of getting data from any data source that stores its data in a tabular format. For example, a flat file or spreadsheet could be the source of data. This is true when the RowSetReader object for a disconnected RowSet object is implemented to read data from such a data source. The reference implementation of the CachedRowSet interface has a RowSetReader object that reads data from a relational database, so in this tutorial, the data source is always a database.
The following topics are covered:

• Setting Up CachedRowSet Objects
• Populating CachedRowSet Objects
• What Reader Does
• Updating CachedRowSet Objects
• Updating Data Sources
• What Writer Does
• Notifying Listeners
• Sending Large Amounts of Data

Setting Up CachedRowSet Objects

Setting up a CachedRowSet object involves the following:

• Creating CachedRowSet Objects
• Setting CachedRowSet Properties
• Setting Key Columns

Creating CachedRowSet Objects

You can create a new CachedRowSet object in the different ways:

• Using the Default Constructor
• Using an instance of RowSetFactory, which is created from the class RowSetProvider: See Using the RowSetFactory Interface in Using JdbcRowSet Objects for more information.

Note: Alternatively, you can use the constructor from the CachedRowSet implementation of your JDBC driver. However, implementations of the RowSet interface will differ from the reference implementation. These implementations will have different names and constructors. For example, the Oracle JDBC driver's implementation of the CachedRowSet interface is named oracle.jdbc.rowset.OracleCachedRowSet.

Using the Default Constructor

One of the ways you can create a CachedRowSet object is by calling the default constructor defined in the reference implementation, as is done in the following line of code:

CachedRowSet crs = new CachedRowSetImpl();

The object crs has the same default values for its properties that a JdbcRowSet object has when it is first created. In addition, it has been assigned an instance of the default SyncProvider implementation, RIOptimisticProvider.
A SyncProvider object supplies a RowSetReader object (a reader) and a RowSetWriter object (a writer), which a disconnected RowSet object needs in order to read data from its data source or to write data back to its data source. What a reader and writer do is explained later in the sections What Reader Does and What Writer Does. One thing to keep in mind is that readers and writers work entirely in the background, so the explanation of how they work is for your information only. Having some background on readers and writers should help you understand what some of the methods defined in the CachedRowSet interface do in the background.

Setting CachedRowSet Properties

Generally, the default values for properties are fine as they are, but you may change the value of a property by calling the appropriate setter method. There are some properties without default values that you must set yourself.
In order to get data, a disconnected RowSet object must be able to connect to a data source and have some means of selecting the data it is to hold. The following properties hold information necessary to obtain a connection to a database.

• username: The name a user supplies to a database as part of gaining access
• password: The user's database password
• url: The JDBC URL for the database to which the user wants to connect
• datasourceName: The name used to retrieve a DataSource object that has been registered with a JNDI naming service

Which of these properties you must set depends on how you are going to make a connection. The preferred way is to use a DataSource object, but it may not be practical for you to register a DataSource object with a JNDI naming service, which is generally done by a system administrator. Therefore, the code examples all use the DriverManager mechanism to obtain a connection, for which you use the url property and not the datasourceName property.
The following lines of code set the username, password, and url properties so that a connection can be obtained using the DriverManager class. (You will find the JDBC URL to set as the value for the url property in the documentation for your JDBC driver.)

public void setConnectionProperties(
    String username, String password) {
    crs.setUsername(username);
    crs.setPassword(password);
    crs.setUrl("jdbc:mySubprotocol:mySubname");
    // ...

Another property that you must set is the command property. In the reference implementation, data is read into a RowSet object from a ResultSet object. The query that produces that ResultSet object is the value for the command property. For example, the following line of code sets the command property with a query that produces a ResultSet object containing all the data in the table MERCH_INVENTORY:

crs.setCommand("select * from MERCH_INVENTORY");

Setting Key Columns

If you are going make any updates to the crs object and want those updates saved in the database, you must set one more piece of information: the key columns. Key columns are essentially the same as a primary key because they indicate one or more columns that uniquely identify a row. The difference is that a primary key is set on a table in the database, whereas key columns are set on a particular RowSet object. The following lines of code set the key columns for crs to the first column:

int [] keys = {1};
crs.setKeyColumns(keys);

The first column in the table MERCH_INVENTORY is ITEM_ID. It can serve as the key column because every item identifier is different and therefore uniquely identifies one row and only one row in the table MERCH_INVENTORY. In addition, this column is specified as a primary key in the definition of the MERCH_INVENTORY table. The method setKeyColumns takes an array to allow for the fact that it may take two or more columns to identify a row uniquely.
As a point of interest, the method setKeyColumns does not set a value for a property. In this case, it sets the value for the field keyCols. Key columns are used internally, so after setting them, you do nothing more with them. You will see how and when key columns are used in the section Using SyncResolver Objects.

Populating CachedRowSet Objects

Populating a disconnected RowSet object involves more work than populating a connected RowSet object. Fortunately, the extra work is done in the background. After you have done the preliminary work to set up the CachedRowSet object crs, the following line of code populates crs:

crs.execute();

The data in crs is the data in the ResultSet object produced by executing the query in the command property.
What is different is that the CachedRowSet implementation for the execute method does a lot more than the JdbcRowSet implementation. Or more correctly, the CachedRowSet object's reader, to which the method execute delegates its tasks, does a lot more.
Every disconnected RowSet object has a SyncProvider object assigned to it, and this SyncProvider object is what provides the RowSet object's reader (a RowSetReader object). When the crs object was created, it was used as the default CachedRowSetImpl constructor, which, in addition to setting default values for properties, assigns an instance of the RIOptimisticProvider implementation as the default SyncProvider object.

What Reader Does

When an application calls the method execute, a disconnected RowSet object's reader works behind the scenes to populate the RowSet object with data. A newly created CachedRowSet object is not connected to a data source and therefore must obtain a connection to that data source in order to get data from it. The reference implementation of the default SyncProvider object (RIOptimisticProvider) provides a reader that obtains a connection by using the values set for the user name, password, and either the JDBC URL or the data source name, whichever was set more recently. Then the reader executes the query set for the command. It reads the data in the ResultSet object produced by the query, populating the CachedRowSet object with that data. Finally, the reader closes the connection.

Updating CachedRowSet Object

In the Coffee Break scenario, the owner wants to streamline operations. The owner decides to have employees at the warehouse enter inventory directly into a PDA (personal digital assistant), thereby avoiding the error-prone process of having a second person do the data entry. A CachedRowSet object is ideal in this situation because it is lightweight, serializable, and can be updated without a connection to the data source.
The owner will have the application development team create a GUI tool for the PDA that warehouse employees will use for entering inventory data. Headquarters will create a CachedRowSet object populated with the table showing the current inventory and send it using the Internet to the PDAs. When a warehouse employee enters data using the GUI tool, the tool adds each entry to an array, which the CachedRowSet object will use to perform the updates in the background. Upon completion of the inventory, the PDAs send their new data back to headquarters, where the data is uploaded to the main server.
This section covers the following topics:

• Updating Column Values
• Inserting and Deleting Rows

Updating Column Values

Updating data in a CachedRowSet object is just the same as updating data in a JdbcRowSet object. For example, the following code fragment from CachedRowSetSample.java increments the value in the column QUAN by 1 in the row whose ITEM_ID column has an item identifier of 12345:

while (crs.next()) {
    System.out.println(
        "Found item " + crs.getInt("ITEM_ID") +
        ": " + crs.getString("ITEM_NAME"));
    if (crs.getInt("ITEM_ID") == 1235) {
        int currentQuantity = crs.getInt("QUAN") + 1;
        System.out.println("Updating quantity to " +
          currentQuantity);
        crs.updateInt("QUAN", currentQuantity + 1);
        crs.updateRow();
        // Synchronizing the row
        // back to the DB
        crs.acceptChanges(con);
    }

Inserting and Deleting Rows

Just as with updating a column value, the code for inserting and deleting rows in a CachedRowSet object is the same as for a JdbcRowSet object.
The following excerpt from CachedRowSetSample.java inserts a new row into the CachedRowSet object crs:

crs.moveToInsertRow();
crs.updateInt("ITEM_ID", newItemId);
crs.updateString("ITEM_NAME", "TableCloth");
crs.updateInt("SUP_ID", 927);
crs.updateInt("QUAN", 14);
Calendar timeStamp;
timeStamp = new GregorianCalendar();
timeStamp.set(2006, 4, 1);
crs.updateTimestamp(
    "DATE_VAL",
    new Timestamp(timeStamp.getTimeInMillis()));
crs.insertRow();
crs.moveToCurrentRow();

If headquarters has decided to stop stocking a particular item, it would probably remove the row for that coffee itself. However, in the scenario, a warehouse employee using a PDA also has the capability of removing it. The following code fragment finds the row where the value in the ITEM_ID column is 12345 and deletes it from the CachedRowSet crs:

while (crs.next()) {
    if (crs.getInt("ITEM_ID") == 12345) {
        crs.deleteRow();
        break;
    }
}

Updating Data Sources

There is a major difference between making changes to a JdbcRowSet object and making changes to a CachedRowSet object. Because a JdbcRowSet object is connected to its data source, the methods updateRow, insertRow, and deleteRow can update both the JdbcRowSet object and the data source. In the case of a disconnected RowSet object, however, these methods update the data stored in the CachedRowSet object's memory but cannot affect the data source. A disconnected RowSet object must call the method acceptChanges in order to save its changes to the data source. In the inventory scenario, back at headquarters, an application will call the method acceptChanges to update the database with the new values for the column QUAN.

crs.acceptChanges();

What Writer Does

Like the method execute, the method acceptChanges does its work invisibly. Whereas the method execute delegates its work to the RowSet object's reader, the method acceptChanges delegates its tasks to the RowSet object's writer. In the background, the writer opens a connection to the database, updates the database with the changes made to the RowSet object, and then closes the connection.

Using Default Implementation

The difficulty is that a conflict can arise. A conflict is a situation in which another party has updated a value in the database that corresponds to a value that was updated in a RowSet object. Which value should persist in the database? What the writer does when there is a conflict depends on how it is implemented, and there are many possibilities. At one end of the spectrum, the writer does not even check for conflicts and just writes all changes to the database. This is the case with the RIXMLProvider implementation, which is used by a WebRowSet object. At the other end, the writer ensures that there are no conflicts by setting database locks that prevent others from making changes.
The writer for the crs object is the one provided by the default SyncProvider implementation, RIOptimisticProvider. The RIOPtimisticProvider implementation gets its name from the fact that it uses an optimistic concurrency model. This model assumes that there will be few, if any, conflicts and therefore sets no database locks. The writer checks to see if there are any conflicts, and if there is none, it writes the changes made to the crs object to the database, and those changes become persistent. If there are any conflicts, the default is not to write the new RowSet values to the database.
In the scenario, the default behavior works very well. Because no one at headquarters is likely to change the value in the QUAN column of COF_INVENTORY, there will be no conflicts. As a result, the values entered into the crs object at the warehouse will be written to the database and thus will be persistent, which is the desired outcome.

Using SyncResolver Objects

In other situations, however, it is possible for conflicts to exist. To accommodate these situations, the RIOPtimisticProvider implementation provides an option that lets you look at the values in conflict and decide which ones should be persistent. This option is the use of a SyncResolver object.
When the writer has finished looking for conflicts and has found one or more, it creates a SyncResolver object containing the database values that caused the conflicts. Next, the method acceptChanges throws a SyncProviderException object, which an application may catch and use to retrieve the SyncResolver object. The following lines of code retrieve the SyncResolver object resolver:

try {
    crs.acceptChanges();
} catch (SyncProviderException spe) {
    SyncResolver resolver = spe.getSyncResolver();
}

The object resolver is a RowSet object that replicates the crs object except that it contains only the values in the database that caused a conflict. All other column values are null.
With the resolver object, you can iterate through its rows to locate the values that are not null and are therefore values that caused a conflict. Then you can locate the value at the same position in the crs object and compare them. The following code fragment retrieves resolver and uses the SyncResolver method nextConflict to iterate through the rows that have conflicting values. The object resolver gets the status of each conflicting value, and if it is UPDATE_ROW_CONFLICT, meaning that the crs was attempting an update when the conflict occurred, the resolver object gets the row number of that value. Then the code moves the cursor for the crs object to the same row. Next, the code finds the column in that row of the resolver object that contains a conflicting value, which will be a value that is not null. After retrieving the value in that column from both the resolver and crs objects, you can compare the two and decide which one you want to become persistent. Finally, the code sets that value in both the crs object and the database using the method setResolvedValue, as shown in the following code:

try {
    crs.acceptChanges();
} catch (SyncProviderException spe) {
    SyncResolver resolver = spe.getSyncResolver();
  
    // value in crs
    Object crsValue;
  
    // value in the SyncResolver object
    Object resolverValue; 
  
    // value to be persistent
    Object resolvedValue; 

    while (resolver.nextConflict()) {
        if (resolver.getStatus() ==
            SyncResolver.UPDATE_ROW_CONFLICT) {
            int row = resolver.getRow();
            crs.absolute(row);
            int colCount =
                crs.getMetaData().getColumnCount();
            for (int j = 1; j <= colCount; j++) {
                if (resolver.getConflictValue(j)
                    != null) {
                    crsValue = crs.getObject(j);
                    resolverValue = 
                        resolver.getConflictValue(j);

                    // ...
                    // compare crsValue and
                    // resolverValue to
                    // determine the value to be
                    // persistent

                    resolvedValue = crsValue;
                    resolver.setResolvedValue(
                        j, resolvedValue);
                }
            }
        }
    }
}

Notifying Listeners

Being a JavaBeans component means that a RowSet object can notify other components when certain things happen to it. For example, if data in a RowSet object changes, the RowSet object can notify interested parties of that change. The nice thing about this notification mechanism is that, as an application programmer, all you have to do is add or remove the components that will be notified.
This section covers the following topics:

• Setting Up Listeners
• How Notification Works

Setting Up Listeners

A listener for a RowSet object is a component that implements the following methods from the RowSetListener interface:

• cursorMoved: Defines what the listener will do, if anything, when the cursor in the RowSet object moves.
• rowChanged: Defines what the listener will do, if anything, when one or more column values in a row have changed, a row has been inserted, or a row has been deleted.
• rowSetChanged: Defines what the listener will do, if anything, when the RowSet object has been populated with new data.

An example of a component that might want to be a listener is a BarGraph object that graphs the data in a RowSet object. As the data changes, the BarGraph object can update itself to reflect the new data.
As an application programmer, the only thing you must do to take advantage of the notification mechanism is to add or remove listeners. The following line of code means that every time the cursor for the crs objects moves, values in crs are changed, or crs as a whole gets new data, the BarGraph object bar will be notified:

crs.addRowSetListener(bar);

You can also stop notifications by removing a listener, as is done in the following line of code:

crs.removeRowSetListener(bar);

Using the Coffee Break scenario, assume that headquarters checks with the database periodically to get the latest price list for the coffees it sells online. In this case, the listener is the PriceList object priceList at the Coffee Break web site, which must implement the RowSetListener methods cursorMoved, rowChanged, and rowSetChanged. The implementation of the cursorMoved method could be to do nothing because the position of the cursor does not affect the priceList object. The implementations for the rowChanged and rowSetChanged methods, on the other hand, must ascertain what changes have been made and update priceList accordingly.

How Notification Works

In the reference implementation, methods that cause any of the RowSet events automatically notify all registered listeners. For example, any method that moves the cursor also calls the method cursorMoved on each of the listeners. Similarly, the method execute calls the method rowSetChanged on all listeners, and acceptChanges calls rowChanged on all listeners.

Sending Large Amounts of Data

The sample code CachedRowSetSample.testCachedRowSet demonstrates how data can be sent in smaller pieces.

A JoinRowSet implementation lets you create a SQL JOIN between RowSet objects when they are not connected to a data source. This is important because it saves the overhead of having to create one or more connections.
The following topics are covered:

• Creating JoinRowSet Objects
• Adding RowSet Objects
• Managing Match Columns

The JoinRowSet interface is a subinterface of the CachedRowSet interface and thereby inherits the capabilities of a CachedRowSet object. This means that a JoinRowSet object is a disconnected RowSet object and can operate without always being connected to a data source.

Creating JoinRowSet Objects

A JoinRowSet object serves as the holder of a SQL JOIN. The following line of code shows to create a JoinRowSet object:

JoinRowSet jrs = new JoinRowSetImpl();

The variable jrs holds nothing until RowSet objects are added to it.
Note: Alternatively, you can use the constructor from the JoinRowSet implementation of your JDBC driver. However, implementations of the RowSet interface will differ from the reference implementation. These implementations will have different names and constructors. For example, the Oracle JDBC driver's implementation of the JoinRowSet interface is named oracle.jdbc.rowset.OracleJoinRowSet.

Adding RowSet Objects

Any RowSet object can be added to a JoinRowSet object as long as it can be part of a SQL JOIN. A JdbcRowSet object, which is always connected to its data source, can be added, but typically it forms part of a JOIN by operating with the data source directly instead of becoming part of a JOIN by being added to a JoinRowSet object. The point of providing a JoinRowSet implementation is to make it possible for disconnected RowSet objects to become part of a JOIN relationship.
The owner of The Coffee Break chain of coffee houses wants to get a list of the coffees he buys from Acme, Inc. In order to do this, the owner will have to get information from two tables, COFFEES and SUPPLIERS. In the database world before RowSet technology, programmers would send the following query to the database:

String query =
    "SELECT COFFEES.COF_NAME " +
    "FROM COFFEES, SUPPLIERS " +
    "WHERE SUPPLIERS.SUP_NAME = Acme.Inc. " +
    "and " +
    "SUPPLIERS.SUP_ID = COFFEES.SUP_ID";

In the world of RowSet technology, you can accomplish the same result without having to send a query to the data source. You can add RowSet objects containing the data in the two tables to a JoinRowSet object. Then, because all the pertinent data is in the JoinRowSet object, you can perform a query on it to get the desired data.
The following code fragment from JoinSample.testJoinRowSet creates two CachedRowSet objects, coffees populated with the data from the table COFFEES, and suppliers populated with the data from the table SUPPLIERS. The coffees and suppliers objects have to make a connection to the database to execute their commands and get populated with data, but after that is done, they do not have to reconnect again in order to form a JOIN.

coffees = new CachedRowSetImpl();
coffees.setCommand("SELECT * FROM COFFEES");
coffees.setUsername(settings.userName);
coffees.setPassword(settings.password);
coffees.setUrl(settings.urlString);
coffees.execute();

suppliers = new CachedRowSetImpl();
suppliers.setCommand("SELECT * FROM SUPPLIERS");
suppliers.setUsername(settings.userName);
suppliers.setPassword(settings.password);
suppliers.setUrl(settings.urlString);
suppliers.execute(); 

Managing Match Columns

Looking at the SUPPLIERS table, you can see that Acme, Inc. has an identification number of 101. The coffees in the table COFFEES with the supplier identification number of 101 are Colombian and Colombian_Decaf. The joining of information from both tables is possible because the two tables have the column SUP_ID in common. In JDBC RowSet technology, SUP_ID, the column on which the JOIN is based, is called the match column.
Each RowSet object added to a JoinRowSet object must have a match column, the column on which the JOIN is based. There are two ways to set the match column for a RowSet object. The first way is to pass the match column to the JoinRowSet method addRowSet, as shown in the following line of code:

jrs.addRowSet(coffees, 2);

This line of code adds the coffees CachedRowSet to the jrs object and sets the second column of coffees (SUP_ID) as the match column. The line of code could also have used the column name rather that the column number.

jrs.addRowSet(coffees, "SUP_ID");

At this point, jrs has only coffees in it. The next RowSet object added to jrs will have to be able to form a JOIN with coffees, which is true of suppliers because both tables have the column SUP_ID. The following line of code adds suppliers to jrs and sets the column SUP_ID as the match column.

jrs.addRowSet(suppliers, 1);

Now jrs contains a JOIN between coffees and suppliers from which the owner can get the names of the coffees supplied by Acme, Inc. Because the code did not set the type of JOIN, jrs holds an inner JOIN, which is the default. In other words, a row in jrs combines a row in coffees and a row in suppliers. It holds the columns in coffees plus the columns in suppliers for rows in which the value in the COFFEES.SUP_ID column matches the value in SUPPLIERS.SUP_ID. The following code prints out the names of coffees supplied by Acme, Inc., where the String supplierName is equal to Acme, Inc. Note that this is possible because the column SUP_NAME, which is from suppliers, and COF_NAME, which is from coffees, are now both included in the JoinRowSet object jrs.

System.out.println("Coffees bought from " + supplierName + ": ");

while (jrs.next()) {
    if (jrs.getString("SUP_NAME").equals(supplierName)) {
        String coffeeName = jrs.getString(1);
        System.out.println("     " + coffeeName);
    }
}

This will produce output similar to the following:

Coffees bought from Acme, Inc.:
     Colombian
     Colombian_Decaf

The JoinRowSet interface provides constants for setting the type of JOIN that will be formed, but currently the only type that is implemented is JoinRowSet.INNER_JOIN.

Using FilteredRowSet Objects

A FilteredRowSet object lets you cut down the number of rows that are visible in a RowSet object so that you can work with only the data that is relevant to what you are doing. You decide what limits you want to set on your data (how you want to "filter" the data) and apply that filter to a FilteredRowSet object. In other words, the FilteredRowSet object makes visible only the rows of data that fit within the limits you set. A JdbcRowSet object, which always has a connection to its data source, can do this filtering with a query to the data source that selects only the columns and rows you want to see. The query's WHERE clause defines the filtering criteria. A FilteredRowSet object provides a way for a disconnected RowSet object to do this filtering without having to execute a query on the data source, thus avoiding having to get a connection to the data source and sending queries to it.
For example, assume that the Coffee Break chain of coffee houses has grown to hundreds of stores throughout the United States of America, and all of them are listed in a table called COFFEE_HOUSES. The owner wants to measure the success of only the stores in California with a coffee house comparison application that does not require a persistent connection to the database system. This comparison will look at the profitability of selling merchandise versus selling coffee drinks plus various other measures of success, and it will rank California stores by coffee drink sales, merchandise sales, and total sales. Because the table COFFEE_HOUSES has hundreds of rows, these comparisons will be faster and easier if the amount of data being searched is cut down to only those rows where the value in the column STORE_ID indicates California.
This is exactly the kind of problem that a FilteredRowSet object addresses by providing the following capabilities:

• Ability to limit the rows that are visible according to set criteria
• Ability to select which data is visible without being connected to a data source

The following topics are covered:

• Defining Filtering Criteria in Predicate Objects
• Creating FilteredRowSet Objects
• Creating and Setting Predicate Objects
• Setting FilteredRowSet Objects with New Predicate Objects to Filter Data Further
• Updating FilteredRowSet Objects
• Inserting or Updating Rows
• Removing All Filters so All Rows Are Visible
• Deleting Rows

Defining Filtering Criteria in Predicate Objects

To set the criteria for which rows in a FilteredRowSet object will be visible, you define a class that implements the Predicate interface. An object created with this class is initialized with the following:

• The high end of the range within which values must fall
• The low end of the range within which values must fall
• The column name or column number of the column with the value that must fall within the range of values set by the high and low boundaries

Note that the range of values is inclusive, meaning that a value at the boundary is included in the range. For example, if the range has a high of 100 and a low of 50, a value of 50 is considered to be within the range. A value of 49 is not. Likewise, 100 is within the range, but 101 is not.
In line with the scenario where the owner wants to compare California stores, an implementation of the Predicate interface that filters for Coffee Break coffee houses located in California must be written. There is no one right way to do this, which means there is a lot of latitude in how the implementation is written. For example, you could name the class and its members whatever you want and implement a constructor and the three evaluate methods in any way that accomplishes the desired results.
The table listing all of the coffee houses, named COFFEE_HOUSES, has hundreds of rows. To make things more manageable, this example uses a table with far fewer rows, which is enough to demonstrate how filtering is done.
A value in the column STORE_ID is an int value that indicates, among other things, the state in which the coffee house is located. A value beginning with 10, for example, means that the state is California. STORE_ID values beginning with 32 indicate Oregon, and those beginning with 33 indicate the state of Washington.
The following class StateFilter implements the Predicate interface:

public class StateFilter implements Predicate {

    private int lo;
    private int hi;
    private String colName = null;
    private int colNumber = -1;

    public StateFilter(int lo, int hi, int colNumber) {
        this.lo = lo;
        this.hi = hi;
        this.colNumber = colNumber;
    }

    public StateFilter(int lo, int hi, String colName) {
        this.lo = lo;
        this.hi = hi;
        this.colName = colName;
    }

    public boolean evaluate(Object value, String columnName) {
        boolean evaluation = true;
        if (columnName.equalsIgnoreCase(this.colName)) {
            int columnValue = ((Integer)value).intValue();
            if ((columnValue >= this.lo)
                &&
                (columnValue <= this.hi)) {
                evaluation = true;
            } else {
                evaluation = false;
            }
        }
        return evaluation;
    }

    public boolean evaluate(Object value, int columnNumber) {

        boolean evaluation = true;

        if (this.colNumber == columnNumber) {
            int columnValue = ((Integer)value).intValue();
            if ((columnValue >= this.lo)
                &&
                (columnValue <= this.hi)) {
                evaluation = true;
            } else {
                evaluation = false;
            }
        }
        return evaluation;
    }


    public boolean evaluate(RowSet rs) {
    
        CachedRowSet frs = (CachedRowSet)rs;
        boolean evaluation = false;

        try {
            int columnValue = -1;

            if (this.colNumber > 0) {
                columnValue = frs.getInt(this.colNumber);
            } else if (this.colName != null) {
                columnValue = frs.getInt(this.colName);
            } else {
                return false;
            }

            if ((columnValue >= this.lo)
                &&
                (columnValue <= this.hi)) {
                evaluation = true;
            }
        } catch (SQLException e) {
            JDBCTutorialUtilities.printSQLException(e);
            return false;
        } catch (NullPointerException npe) {
            System.err.println("NullPointerException caught");
            return false;
        }
        return evaluation;
    }
}

This is a very simple implementation that checks the value in the column specified by either colName or colNumber to see if it is in the range of lo to hi, inclusive. The following line of code, from FilteredRowSetSample, creates a filter that allows only the rows where the STORE_ID column value indicates a value between 10000 and 10999, which indicates a California location:

StateFilter myStateFilter = new StateFilter(10000, 10999, 1);

Note that the StateFilter class just defined applies to one column. It is possible to have it apply to two or more columns by making each of the parameters arrays instead of single values. For example, the constructor for a Filter object could look like the following:

public Filter2(Object [] lo, Object [] hi, Object [] colNumber) {
    this.lo = lo;
    this.hi = hi;
    this.colNumber = colNumber;
}

The first element in the colNumber object gives the first column in which the value will be checked against the first element in lo and the first element in hi. The value in the second column indicated by colNumber will be checked against the second elements in lo and hi, and so on. Therefore, the number of elements in the three arrays should be the same. The following code is what an implementation of the method evaluate(RowSet rs) might look like for a Filter2 object, in which the parameters are arrays:

public boolean evaluate(RowSet rs) {
    CachedRowSet crs = (CachedRowSet)rs;
    boolean bool1;
    boolean bool2;
    for (int i = 0; i < colNumber.length; i++) {

        if ((rs.getObject(colNumber[i] >= lo [i]) &&
            (rs.getObject(colNumber[i] <= hi[i]) {
            bool1 = true;
        } else {
            bool2 = true;
        }

        if (bool2) {
            return false;
        } else {
            return true;
        }
    }
}

The advantage of using a Filter2 implementation is that you can use parameters of any Object type and can check one column or multiple columns without having to write another implementation. However, you must pass an Object type, which means that you must convert a primitive type to its Object type. For example, if you use an int value for lo and hi, you must convert the int value to an Integer object before passing it to the constructor. String objects are already Object types, so you do not have to convert them.

Creating FilteredRowSet Objects

The reference implementation for the FilteredRowSet interface, FilteredRowSetImpl, includes a default constructor, which is used in the following line of code to create the empty FilteredRowSet object frs:.

FilteredRowSet frs = new FilteredRowSetImpl();

The implementation extends the BaseRowSet abstract class, so the frs object has the default properties defined in BaseRowSet. This means that frs is scrollable, updatable, does not show deleted rows, has escape processing turned on, and so on. Also, because the FilteredRowSet interface is a subinterface of CachedRowSet, Joinable, and WebRowSet, the frs object has the capabilities of each. It can operate as a disconnected RowSet object, can be part of a JoinRowSet object, and can read and write itself in XML format.
Note: Alternatively, you can use the constructor from the WebRowSet implementation of your JDBC driver. However, implementations of the RowSet interface will differ from the reference implementation. These implementations will have different names and constructors. For example, the Oracle JDBC driver's implementation of the WebRowSet interface is named oracle.jdbc.rowset.OracleWebRowSet.
You can use an instance of RowSetFactory, which is created from the class RowSetProvider, to create a FilteredRowSet object. See Using the RowSetFactory Interface in Using JdbcRowSet Objects for more information.
Like other disconnected RowSet objects, the frs object must populate itself with data from a tabular data source, which is a relational database in the reference implementation. The following code fragment from FilteredRowSetSample sets the properties necessary to connect to a database to execute its command. Note that this code uses the DriverManager class to make a connection, which is done for convenience. Usually, it is better to use a DataSource object that has been registered with a naming service that implements the Java Naming and Directory Interface (JNDI):

frs.setCommand("SELECT * FROM COFFEE_HOUSES");
frs.setUsername(settings.userName);
frs.setPassword(settings.password);
frs.setUrl(settings.urlString);

The following line of code populates the frs objectwith the data stored in the COFFEE_HOUSE table:

frs.execute();

The method execute does all kinds of things in the background by calling on the RowSetReader object for frs, which creates a connection, executes the command for frs, populates frs with the data from the ResultSet object that is produced, and closes the connection. Note that if the table COFFEE_HOUSES had more rows than the frs object could hold in memory at one time, the CachedRowSet paging methods would have been used.
In the scenario, the Coffee Break owner would have done the preceding tasks in the office and then imported or downloaded the information stored in the frs object to the coffee house comparison application. From now on, the frs object will operate independently without the benefit of a connection to the data source.

Creating and Setting Predicate Objects

Now that the FilteredRowSet object frs contains the list of Coffee Break establishments, you can set selection criteria for narrowing down the number of rows in the frs object that are visible.
The following line of code uses the StateFilter class defined previously to create the object myStateFilter, which checks the column STORE_ID to determine which stores are in California (a store is in California if its ID number is between 10000 and 10999, inclusive):

StateFilter myStateFilter = new StateFilter(10000, 10999, 1);

The following line sets myStateFilter as the filter for frs.

frs.setFilter(myStateFilter);

To do the actual filtering, you call the method next, which in the reference implementation calls the appropriate version of the Predicate.evaluate method that you have implemented previously.
If the return value is true, the row will be visible; if the return value is false, the row will not be visible.

Setting FilteredRowSet Objects with New Predicate Objects to Filter Data Further

You set multiple filters serially. The first time you call the method setFilter and pass it a Predicate object, you have applied the filtering criteria in that filter. After calling the method next on each row, which makes visible only those rows that satisfy the filter, you can call setFilter again, passing it a different Predicate object. Even though only one filter is set at a time, the effect is that both filters apply cumulatively.
For example, the owner has retrieved a list of the Coffee Break stores in California by setting stateFilter as the Predicate object for frs. Now the owner wants to compare the stores in two California cities, San Francisco (SF in the table COFFEE_HOUSES) and Los Angeles (LA in the table). The first thing to do is to write a Predicate implementation that filters for stores in either SF or LA:

public class CityFilter implements Predicate {

    private String[] cities;
    private String colName = null;
    private int colNumber = -1;

    public CityFilter(String[] citiesArg, String colNameArg) {
        this.cities = citiesArg;
        this.colNumber = -1;
        this.colName = colNameArg;
    }

    public CityFilter(String[] citiesArg, int colNumberArg) {
        this.cities = citiesArg;
        this.colNumber = colNumberArg;
        this.colName = null;
    }

    public boolean evaluate Object valueArg, String colNameArg) {

        if (colNameArg.equalsIgnoreCase(this.colName)) {
            for (int i = 0; i < this.cities.length; i++) {
                if (this.cities[i].equalsIgnoreCase((String)valueArg)) {
                    return true;
                }
            }
        }
        return false;
    }

    public boolean evaluate(Object valueArg, int colNumberArg) {

        if (colNumberArg == this.colNumber) {
            for (int i = 0; i < this.cities.length; i++) {
                if (this.cities[i].equalsIgnoreCase((String)valueArg)) {
                    return true;
                }
            }
        }
        return false;
    }


    public boolean evaluate(RowSet rs) {

        if (rs == null) return false;

        try {
            for (int i = 0; i < this.cities.length; i++) {

                String cityName = null;

                if (this.colNumber > 0) {
                    cityName = (String)rs.getObject(this.colNumber);
                } else if (this.colName != null) {
                    cityName = (String)rs.getObject(this.colName);
                } else {
                    return false;
                }

                if (cityName.equalsIgnoreCase(cities[i])) {
                    return true;
                }
            }
        } catch (SQLException e) {
            return false;
        }
        return false;
    }
}

The following code fragment from FilteredRowSetSample sets the new filter and iterates through the rows in frs, printing out the rows where the CITY column contains either SF or LA. Note that frs currently contains only rows where the store is in California, so the criteria of the Predicate object state are still in effect when the filter is changed to another Predicate object. The code that follows sets the filter to the CityFilter object city. The CityFilter implementation uses arrays as parameters to the constructors to illustrate how that can be done:

public void testFilteredRowSet() {

    FilteredRowSet frs = null;
    StateFilter myStateFilter = new StateFilter(10000, 10999, 1);
    String[] cityArray = { "SF", "LA" };

    CityFilter myCityFilter = new CityFilter(cityArray, 2);

    try {
        frs = new FilteredRowSetImpl();

        frs.setCommand("SELECT * FROM COFFEE_HOUSES");
        frs.setUsername(settings.userName);
        frs.setPassword(settings.password);
        frs.setUrl(settings.urlString);
        frs.execute();

        System.out.println("\nBefore filter:");
        FilteredRowSetSample.viewTable(this.con);

        System.out.println("\nSetting state filter:");
        frs.beforeFirst();
        frs.setFilter(myStateFilter);
        this.viewFilteredRowSet(frs);

        System.out.println("\nSetting city filter:");
        frs.beforeFirst();
        frs.setFilter(myCityFilter);
        this.viewFilteredRowSet(frs);
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    }
}

The output should contain a row for each store that is in San Francisco, California or Los Angeles, California. If there were a row in which the CITY column contained LA and the STORE_ID column contained 40003, it would not be included in the list because it had already been filtered out when the filter was set to state. (40003 is not in the range of 10000 to 10999.)

Updating FilteredRowSet Objects

You can make a change to a FilteredRowSet object but only if that change does not violate any of the filtering criteria currently in effect. For example, you can insert a new row or change one or more values in an existing row if the new value or values are within the filtering criteria.

Inserting or Updating Rows

Assume that two new Coffee Break coffee houses have just opened and the owner wants to add them to the list of all coffee houses. If a row to be inserted does not meet the cumulative filtering criteria in effect, it will be blocked from being added.
The current state of the frs object is that the StateFilter object was set and then the CityFilter object was set. As a result, frs currently makes visible only those rows that satisfy the criteria for both filters. And, equally important, you cannot add a row to the frs object unless it satisfies the criteria for both filters. The following code fragment attempts to insert two new rows into the frs object, one row in which the values in the STORE_ID and CITY columns both meet the criteria, and one row in which the value in STORE_ID does not pass the filter but the value in the CITY column does:

frs.moveToInsertRow();
frs.updateInt("STORE_ID", 10101);
frs.updateString("CITY", "SF");
frs.updateLong("COF_SALES", 0);
frs.updateLong("MERCH_SALES", 0);
frs.updateLong("TOTAL_SALES", 0);
frs.insertRow();

frs.updateInt("STORE_ID", 33101);
frs.updateString("CITY", "SF");
frs.updateLong("COF_SALES", 0);
frs.updateLong("MERCH_SALES", 0);
frs.updateLong("TOTAL_SALES", 0);
frs.insertRow();
frs.moveToCurrentRow();

If you were to iterate through the frs object using the method next, you would find a row for the new coffee house in San Francisco, California, but not for the store in San Francisco, Washington.

Removing All Filters so All Rows Are Visible

The owner can add the store in Washington by nullifying the filter. With no filter set, all rows in the frs object are once more visible, and a store in any location can be added to the list of stores. The following line of code unsets the current filter, effectively nullifying both of the Predicate implementations previously set on the frs object.

frs.setFilter(null);

Deleting Rows

If the owner decides to close down or sell one of the Coffee Break coffee houses, the owner will want to delete it from the COFFEE_HOUSES table. The owner can delete the row for the underperforming coffee house as long as the row is visible.
For example, given that the method setFilter has just been called with the argument null, there is no filter set on the frs object. This means that all rows are visible and can therefore be deleted. However, after the StateFilter object myStateFilter was set, which filtered out any state other than California, only stores located in California could be deleted. When the CityFilter object myCityFilter was set for the frs object, only coffee houses in San Francisco, California or Los Angeles, California could be deleted because they were in the only rows visible.

Using WebRowSet Objects

A WebRowSet object is very special because in addition to offering all of the capabilities of a CachedRowSet object, it can write itself as an XML document and can also read that XML document to convert itself back to a WebRowSet object. Because XML is the language through which disparate enterprises can communicate with each other, it has become the standard for Web Services communication. As a consequence, a WebRowSet object fills a real need by enabling Web Services to send and receive data from a database in the form of an XML document.
The following topics are covered:

• Creating and Populating WebRowSet Objects
• Writing and Reading WebRowSet Objects to XML
• What Is in XML Documents
• Making Changes to WebRowSet Objects

The Coffee Break company has expanded to selling coffee online. Users order coffee by the pound from the Coffee Break Web site. The price list is regularly updated by getting the latest information from the company's database. This section demonstrates how to send the price data as an XML document with a WebRowSet object and a single method call.

Creating and Populating WebRowSet Objects

You create a new WebRowSet object with the default constructor defined in the reference implementation, WebRowSetImpl, as shown in the following line of code:

WebRowSet priceList = new WebRowSetImpl();

Although the priceList object has no data yet, it has the default properties of a BaseRowSet object. Its SyncProvider object is at first set to the RIOptimisticProvider implementation, which is the default for all disconnected RowSet objects. However, the WebRowSet implementation resets the SyncProvider object to be the RIXMLProvider implementation.
You can use an instance of RowSetFactory, which is created from the RowSetProvider class, to create a WebRowSet object. See Using the RowSetFactory Interface in Using JdbcRowSet Objects for more information.
The Coffee Break headquarters regularly sends price list updates to its web site. This information on WebRowSet objects will show one way you can send the latest price list in an XML document.
The price list consists of the data in the columns COF_NAME and PRICE from the table COFFEES. The following code fragment sets the properties needed and populates the priceList object with the price list data:

public void getPriceList(String username, String password) {
    priceList.setCommand("SELECT COF_NAME, PRICE FROM COFFEES");
    priceList.setURL("jdbc:mySubprotocol:myDatabase");
    priceList.setUsername(username);
    priceList.setPassword(password);
    priceList.execute();
    // ...
}

At this point, in addition to the default properties, the priceList object contains the data in the COF_NAME and PRICE columns from the COFFEES table and also the metadata about these two columns.

Writing and Reading WebRowSet Object to XML

To write a WebRowSet object as an XML document, call the method writeXml. To read that XML document's contents into a WebRowSet object, call the method readXml. Both of these methods do their work in the background, meaning that everything, except the results, is invisible to you.

Using the writeXml Method

The method writeXml writes the WebRowSet object that invoked it as an XML document that represents its current state. It writes this XML document to the stream that you pass to it. The stream can be an OutputStream object, such as a FileOutputStream object, or a Writer object, such as a FileWriter object. If you pass the method writeXml an OutputStream object, you will write in bytes, which can handle all types of data; if you pass it a Writer object, you will write in characters. The following code demonstrates writing the WebRowSet object priceList as an XML document to the FileOutputStream object oStream:

java.io.FileOutputStream oStream =
    new java.io.FileOutputStream("priceList.xml");
priceList.writeXml(oStream);

The following code writes the XML document representing priceList to the FileWriter object writer instead of to an OutputStream object. The FileWriter class is a convenience class for writing characters to a file.

java.io.FileWriter writer =
    new java.io.FileWriter("priceList.xml");
priceList.writeXml(writer);

The other two versions of the method writeXml let you populate a WebRowSet object with the contents of a ResultSet object before writing it to a stream. In the following line of code, the method writeXml reads the contents of the ResultSet object rs into the priceList object and then writes priceList to the FileOutputStream object oStream as an XML document.

priceList.writeXml(rs, oStream);

In the next line of code, the writeXml methodpopulates priceList with the contents of rs, but it writes the XML document to a FileWriter object instead of to an OutputStream object:

priceList.writeXml(rs, writer);

Using the readXml Method

The method readXml parses an XML document in order to construct the WebRowSet object the XML document describes. Similar to the method writeXml, you can pass readXml an InputStream object or a Reader object from which to read the XML document.

java.io.FileInputStream iStream =
    new java.io.FileInputStream("priceList.xml");
priceList.readXml(iStream);

java.io.FileReader reader = new
    java.io.FileReader("priceList.xml");
priceList.readXml(reader);

Note that you can read the XML description into a new WebRowSet object or into the same WebRowSet object that called the writeXml method. In the scenario, where the price list information is being sent from headquarters to the Web site, you would use a new WebRowSet object, as shown in the following lines of code:

WebRowSet recipient = new WebRowSetImpl();
java.io.FileReader reader =
    new java.io.FileReader("priceList.xml");
recipient.readXml(reader);

What Is in XML Documents

RowSet objects are more than just the data they contain. They have properties and metadata about their columns as well. Therefore, an XML document representing a WebRowSet object includes this other information in addition to its data. Further, the data in an XML document includes both current values and original values. (Recall that original values are the values that existed immediately before the most recent changes to data were made. These values are necessary for checking if the corresponding value in the database has been changed, thus creating a conflict over which value should be persistent: the new value you put in the RowSet object or the new value someone else put in the database.)
The WebRowSet XML Schema, itself an XML document, defines what an XML document representing a WebRowSet object will contain and also the format in which it must be presented. Both the sender and the recipient use this schema because it tells the sender how to write the XML document (which represents the WebRowSet object) and the recipient how to parse the XML document. Because the actual writing and reading is done internally by the implementations of the methods writeXml and readXml, you, as a user, do not need to understand what is in the WebRowSet XML Schema document.
XML documents contain elements and subelements in a hierarchical structure. The following are the three main elements in an XML document describing a WebRowSet object:

• Properties
• Metadata
• Data

Element tags signal the beginning and end of an element. For example, the <properties> tag signals the beginning of the properties element, and the </properties> tag signals its end. The <map/> tag is a shorthand way of saying that the map subelement (one of the subelements in the properties element) has not been assigned a value. The following sample XML documents uses spacing and indentation to make it easier to read, but those are not used in an actual XML document, where spacing does not mean anything.
The next three sections show you what the three main elements contain for the WebRowSet priceList object, created in the sample WebRowSetSample.java.

Properties

Calling the method writeXml on the priceList object would produce an XML document describing priceList. The properties section of this XML document would look like the following:

<properties>
  <command>
    select COF_NAME, PRICE from COFFEES
  </command>
  <concurrency>1008</concurrency>
  <datasource><null/></datasource>
  <escape-processing>true</escape-processing>
  <fetch-direction>1000</fetch-direction>
  <fetch-size>0</fetch-size>
  <isolation-level>2</isolation-level>
  <key-columns>
    <column>1</column>
  </key-columns>
  <map>
  </map>
  <max-field-size>0</max-field-size>
  <max-rows>0</max-rows>
  <query-timeout>0</query-timeout>
  <read-only>true</read-only>
  <rowset-type>
    ResultSet.TYPE_SCROLL_INSENSITIVE
  </rowset-type>
  <show-deleted>false</show-deleted>
  <table-name>COFFEES</table-name>
  <url>jdbc:mysql://localhost:3306/testdb</url>
  <sync-provider>
    <sync-provider-name>
      com.sun.rowset.providers.RIOptimisticProvider
    </sync-provider-name>
    <sync-provider-vendor>
      Sun Microsystems Inc.
    </sync-provider-vendor>
    <sync-provider-version>
      1.0
    </sync-provider-version>
    <sync-provider-grade>
      2
    </sync-provider-grade>
    <data-source-lock>1</data-source-lock>
  </sync-provider>
</properties>

Notice that some properties have no value. For example, the datasource property is indicated with the <datasource/> tag, which is a shorthand way of saying <datasource></datasource>. No value is given because the url property is set. Any connections that are established will be done using this JDBC URL, so no DataSource object needs to be set. Also, the username and password properties are not listed because they must remain secret.

Metadata

The metadata section of the XML document describing a WebRowSet object contains information about the columns in that WebRowSet object. The following shows what this section looks like for the WebRowSet object priceList. Because the priceList object has two columns, the XML document describing it has two <column-definition> elements. Each <column-definition> element has subelements giving information about the column being described.

<metadata>
  <column-count>2</column-count>
  <column-definition>
    <column-index>1</column-index>
    <auto-increment>false</auto-increment>
    <case-sensitive>false</case-sensitive>
    <currency>false</currency>
    <nullable>0</nullable>
    <signed>false</signed>
    <searchable>true</searchable>
    <column-display-size>
      32
    </column-display-size>
    <column-label>COF_NAME</column-label>
    <column-name>COF_NAME</column-name>
    <schema-name></schema-name>
    <column-precision>32</column-precision>
    <column-scale>0</column-scale>
    <table-name>coffees</table-name>
    <catalog-name>testdb</catalog-name>
    <column-type>12</column-type>
    <column-type-name>
      VARCHAR
    </column-type-name>
  </column-definition>
  <column-definition>
    <column-index>2</column-index>
    <auto-increment>false</auto-increment>
    <case-sensitive>true</case-sensitive>
    <currency>false</currency>
    <nullable>0</nullable>
    <signed>true</signed>
    <searchable>true</searchable>
    <column-display-size>
      12
    </column-display-size>
    <column-label>PRICE</column-label>
    <column-name>PRICE</column-name>
    <schema-name></schema-name>
    <column-precision>10</column-precision>
    <column-scale>2</column-scale>
    <table-name>coffees</table-name>
    <catalog-name>testdb</catalog-name>
    <column-type>3</column-type>
    <column-type-name>
      DECIMAL
    </column-type-name>
  </column-definition>
</metadata>

From this metadata section, you can see that there are two columns in each row. The first column is COF_NAME, which holds values of type VARCHAR. The second column is PRICE, which holds values of type REAL, and so on. Note that the column types are the data types used in the data source, not types in the Java programming language. To get or update values in the COF_NAME column, you use the methods getString or updateString, and the driver makes the conversion to the VARCHAR type, as it usually does.

Data

The data section gives the values for each column in each row of a WebRowSet object. If you have populated the priceList object and not made any changes to it, the data element of the XML document will look like the following. In the next section you will see how the XML document changes when you modify the data in the priceList object.
For each row there is a <currentRow> element, and because priceList has two columns, each <currentRow> element contains two <columnValue> elements.

<data>
  <currentRow>
    <columnValue>Colombian</columnValue>
    <columnValue>7.99</columnValue>
  </currentRow>
  <currentRow>
    <columnValue>
      Colombian_Decaf
    </columnValue>
    <columnValue>8.99</columnValue>
  </currentRow>
  <currentRow>
    <columnValue>Espresso</columnValue>
    <columnValue>9.99</columnValue>
  </currentRow>
  <currentRow>
    <columnValue>French_Roast</columnValue>
    <columnValue>8.99</columnValue>
  </currentRow>
  <currentRow>
    <columnValue>French_Roast_Decaf</columnValue>
    <columnValue>9.99</columnValue>
  </currentRow>
</data>

Making Changes to WebRowSet Objects

You make changes to a WebRowSet object the same way you do to a CachedRowSet object. Unlike a CachedRowSet object, however, a WebRowSet object keeps track of updates, insertions, and deletions so that the writeXml method can write both the current values and the original values. The three sections that follow demonstrate making changes to the data and show what the XML document describing the WebRowSet object looks like after each change. You do not have to do anything at all regarding the XML document; any change to it is made automatically, just as with writing and reading the XML document.

Inserting Rows

If the owner of the Coffee Break chain wants to add a new coffee to the price list, the code might look like this:

priceList.absolute(3);
priceList.moveToInsertRow();
priceList.updateString(COF_NAME, "Kona");
priceList.updateFloat(PRICE, 8.99f);
priceList.insertRow();
priceList.moveToCurrentRow();

In the reference implementation, an insertion is made immediately following the current row. In the preceding code fragment, the current row is the third row, so the new row would be added after the third row and become the new fourth row. To reflect this insertion, the XML document would have the following <insertRow> element added to it after the third <currentRow> element in the <data> element.
The <insertRow> element will look similar to the following.

<insertRow>
  <columnValue>Kona</columnValue>
  <columnValue>8.99</columnValue>
</insertRow>

Deleting Rows

The owner decides that Espresso is not selling enough and should be removed from the coffees sold at The Coffee Break shops. The owner therefore wants to delete Espresso from the price list. Espresso is in the third row of the priceList object, so the following lines of code delete it:

priceList.absolute(3); priceList.deleteRow();

The following <deleteRow> element will appear after the second row in the data section of the XML document, indicating that the third row has been deleted.

<deleteRow>
  <columnValue>Espresso</columnValue>
  <columnValue>9.99</columnValue>
</deleteRow>

Modifying Rows

The owner further decides that the price of Colombian coffee is too expensive and wants to lower it to $6.99 a pound. The following code sets the new price for Colombian coffee, which is in the first row, to $6.99 a pound:

priceList.first();
priceList.updateFloat(PRICE, 6.99);

The XML document will reflect this change in an <updateRow> element that gives the new value. The value for the first column did not change, so there is an <updateValue> element only for the second column:

<currentRow>
  <columnValue>Colombian</columnValue>
  <columnValue>7.99</columnValue>
  <updateRow>6.99</updateRow>
</currentRow>

At this point, with the insertion of a row, the deletion of a row, and the modification of a row, the XML document for the priceList object would look like the following:

<data>
  <insertRow>
    <columnValue>Kona</columnValue>
    <columnValue>8.99</columnValue>
  </insertRow>
  <currentRow>
    <columnValue>Colombian</columnValue>
    <columnValue>7.99</columnValue>
    <updateRow>6.99</updateRow>
  </currentRow>
  <currentRow>
    <columnValue>
      Colombian_Decaf
    </columnValue>
    <columnValue>8.99</columnValue>
  </currentRow>
  <deleteRow>
    <columnValue>Espresso</columnValue>
    <columnValue>9.99</columnValue>
  </deleteRow>
  <currentRow>
    <columnValue>French_Roast</columnValue>
    <columnValue>8.99</columnValue>
  </currentRow>
  <currentRow>
    <columnValue>
      French_Roast_Decaf
    </columnValue>
    <columnValue>9.99</columnValue>
  </currentRow>
</data>

WebRowSet Code Example

The sample WebRowSetSample.java demonstrates all the features described on this page.

Using Advanced Data Types

The advanced data types introduced in this section give a relational database more flexibility in what can be used as a value for a table column. For example, a column can be used to store BLOB (binary large object) values, which can store very large amounts of data as raw bytes. A column can also be of type CLOB (character large object), which is capable of storing very large amounts of data in character format.
The latest version of the ANSI/ISO SQL standard is commonly referred to as SQL:2003. This standard specifies the following data types:

• SQL92 built-in types, which consist of the familiar SQL column types such as CHAR, FLOAT, and DATE
  
• SQL99 built-in types, which consist of types added by SQL99:
  
  • BOOLEAN: Boolean (true or false) value
    
  • BLOB: Binary large Bobject
    
  • CLOB: Character large object
    
• New built-in types added by SQL:2003:
  
  • XML: XML object
    
• User defined types:
  
  • Structured type: User-defined type; for example:
    

    CREATE TYPE PLANE_POINT
    AS (X FLOAT, Y FLOAT) NOT FINAL
    

  • DISTINCT type: User-defined type based on a built-in type; for example:
    

    CREATE TYPE MONEY
    AS NUMERIC(10,2) FINAL
    

• Constructed types: New types based on a given base type:
  
  • REF(structured-type): Pointer that persistently denotes an instance of a structured type that resides in the database
    
  • base-type ARRAY[n]: Array of n base-type elements
    
• Locators: Entities that are logical pointers to data that resides on the database server. A locator exists in the client computer and is a transient, logical pointer to data on the server. A locator typically refers to data that is too large to materialize on the client, such as images or audio. (Materialized views are query results that have been stored or "materialized" in advance as schema objects.) There are operators defined at the SQL level to retrieve randomly accessed pieces of the data denoted by the locator:
  
  • LOCATOR(structured-type): Locator to a structured instance in the server
    
  • LOCATOR(array): Locator to an array in the server
    
  • LOCATOR(blob): Locator to a binary large object in the server
    
  • LOCATOR(clob): Locator to a character large object in the server
    
• Datalink: Type for managing data external to the data source. Datalink values are part of SQL MED (Management of External Data), a part of the SQL ANSI/ISO standard specification.
  

Mapping Advanced Data Types

The JDBC API provides default mappings for advanced data types specified by the SQL:2003 standard. The following list gives the data types and the interfaces or classes to which they are mapped:

• BLOB: Blob interface
• CLOB: Clob interface
• NCLOB: NClob interface
• ARRAY: Array interface
• XML: SQLXML interface
• Structured types: Struct interface
• REF(structured type): Ref interface
• ROWID: RowId interface
• DISTINCT: Type to which the base type is mapped. For example, a DISTINCT value based on a SQL NUMERIC type maps to a java.math.BigDecimal type because NUMERIC maps to BigDecimal in the Java programming language.
• DATALINK: java.net.URL object

Using Advanced Data Types

You retrieve, store, and update advanced data types the same way you handle other data types. You use either ResultSet.getDataType or CallableStatement.getDataType methods to retrieve them, PreparedStatement.setDataType methods to store them, and ResultSet.updateDataType methods to update them. (The variable DataType is the name of a Java interface or class mapped to an advanced data type.) Probably 90 percent of the operations performed on advanced data types involve using the getDataType, setDataType, and updateDataType methods. The following table shows which methods to use:

Advanced Data Type	getDataType Method	setDataType method	updateDataType Method
BLOB	getBlob	setBlob	updateBlob
CLOB	getClob	setClob	updateClob
NCLOB	getNClob	setNClob	updateNClob
ARRAY	getArray	setArray	updateArray
XML	getSQLXML	setSQLXML	updateSQLXML
Structured type	getObject	setObject	updateObject
REF(structured type)	getRef	setRef	updateRef
ROWID	getRowId	setRowId	updateRowId
DISTINCT	getBigDecimal	setBigDecimal	updateBigDecimal
DATALINK	getURL	setURL	updateURL

Note: The DISTINCT data type behaves differently from other advanced SQL data types. Being a user-defined type that is based on an already existing built-in types, it has no interface as its mapping in the Java programming language. Consequently, you use the method that corresponds to the Java type on which the DISTINCT data type is based. See Using DISTINCT Data Type for more information.
For example, the following code fragment retrieves a SQL ARRAY value. For this example, suppose that the column SCORES in the table STUDENTS contains values of type ARRAY. The variable stmt is a Statement object.

ResultSet rs = stmt.executeQuery(
    "SELECT SCORES FROM STUDENTS " +
    "WHERE ID = 002238");
rs.next();
Array scores = rs.getArray("SCORES");

The variable scores is a logical pointer to the SQL ARRAY object stored in the table STUDENTS in the row for student 002238.
If you want to store a value in the database, you use the appropriate set method. For example, the following code fragment, in which rs is a ResultSet object, stores a Clob object:

Clob notes = rs.getClob("NOTES");
PreparedStatement pstmt =
    con.prepareStatement(
        "UPDATE MARKETS SET COMMENTS = ? " +
        "WHERE SALES < 1000000");
pstmt.setClob(1, notes);
pstmt.executeUpdate();

This code sets notes as the first parameter in the update statement being sent to the database. The Clob value designated by notes will be stored in the table MARKETS in column COMMENTS in every row where the value in the column SALES is less than one million.

Using Large Objects

An important feature of Blob, Clob, and NClob Java objects is that you can manipulate them without having to bring all of their data from the database server to your client computer. Some implementations represent an instance of these types with a locator (logical pointer) to the object in the database that the instance represents. Because a BLOB, CLOB, or NCLOB SQL object may be very large, the use of locators can make performance significantly faster. However, other implementations fully materialize large objects on the client computer.
If you want to bring the data of a BLOB, CLOB, or NCLOB SQL value to the client computer, use methods in the Blob, Clob, and NClob Java interfaces that are provided for this purpose. These large object type objects materialize the data of the objects they represent as a stream.
The following topics are covered:

• Adding Large Object Type Objects to Databases
• Retrieving CLOB Values
• Adding and Retrieving BLOB Objects
• Releasing Resources Held by Large Objects

Adding Large Object Type Object to Database

The following excerpt from ClobSample.addRowToCoffeeDescriptions adds a CLOB SQL value to the table COFFEE_DESCRIPTIONS. The Clob Java object myClob contains the contents of the file specified by fileName.

public void addRowToCoffeeDescriptions(
    String coffeeName, String fileName)
    throws SQLException {

    PreparedStatement pstmt = null;
    try {
        Clob myClob = this.con.createClob();
        Writer clobWriter = myClob.setCharacterStream(1);
        String str = this.readFile(fileName, clobWriter);
        System.out.println("Wrote the following: " +
            clobWriter.toString());

        if (this.settings.dbms.equals("mysql")) {
            System.out.println(
                "MySQL, setting String in Clob " +
                "object with setString method");
            myClob.setString(1, str);
        }
        System.out.println("Length of Clob: " + myClob.length());

        String sql = "INSERT INTO COFFEE_DESCRIPTIONS " +
                     "VALUES(?,?)";

        pstmt = this.con.prepareStatement(sql);
        pstmt.setString(1, coffeeName);
        pstmt.setClob(2, myClob);
        pstmt.executeUpdate();
    } catch (SQLException sqlex) {
        JDBCTutorialUtilities.printSQLException(sqlex);
    } catch (Exception ex) {
      System.out.println("Unexpected exception: " + ex.toString());
    } finally {
        if (pstmt != null)pstmt.close();
    }
}

The following line creates a Clob Java object:

Clob myClob = this.con.createClob();

The following line retrieves a stream (in this case a Writer object named clobWriter) that is used to write a stream of characters to the Clob Java object myClob. The method ClobSample.readFile writes this stream of characters; the stream is from the file specified by the String fileName. The method argument 1 indicates that the Writer object will start writing the stream of characters at the beginning of the Clob value:

Writer clobWriter = myClob.setCharacterStream(1);

The ClobSample.readFile method reads the file line-by-line specified by the file fileName and writes it to the Writer object specified by writerArg:

private String readFile(String fileName, Writer writerArg)
        throws FileNotFoundException, IOException {

    BufferedReader br = new BufferedReader(new FileReader(fileName));
    String nextLine = "";
    StringBuffer sb = new StringBuffer();
    while ((nextLine = br.readLine()) != null) {
        System.out.println("Writing: " + nextLine);
        writerArg.write(nextLine);
        sb.append(nextLine);
    }
    // Convert the content into to a string
    String clobData = sb.toString();

    // Return the data.
    return clobData;
}

The following excerpt creates a PreparedStatement object pstmt that inserts the Clob Java object myClob into COFFEE_DESCRIPTIONS:

PreparedStatement pstmt = null;
// ...
String sql = "INSERT INTO COFFEE_DESCRIPTIONS VALUES(?,?)";
pstmt = this.con.prepareStatement(sql);
pstmt.setString(1, coffeeName);
pstmt.setClob(2, myClob);
pstmt.executeUpdate();

Retrieving CLOB Values

The method ClobSample.retrieveExcerpt retrieves the CLOB SQL value stored in the COF_DESC column of COFFEE_DESCRIPTIONS from the row whose column value COF_NAME is equal to the String value specified by the coffeeName parameter:

public String retrieveExcerpt(String coffeeName, int numChar)
    throws SQLException {

    String description = null;
    Clob myClob = null;
    PreparedStatement pstmt = null;

    try {
        String sql =
            "select COF_DESC " +
            "from COFFEE_DESCRIPTIONS " +
            "where COF_NAME = ?";

        pstmt = this.con.prepareStatement(sql);
        pstmt.setString(1, coffeeName);
        ResultSet rs = pstmt.executeQuery();

        if (rs.next()) {
            myClob = rs.getClob(1);
            System.out.println("Length of retrieved Clob: " +
                myClob.length());
        }
        description = myClob.getSubString(1, numChar);
    } catch (SQLException sqlex) {
        JDBCTutorialUtilities.printSQLException(sqlex);
    } catch (Exception ex) {
        System.out.println("Unexpected exception: " + ex.toString());
    } finally {
        if (pstmt != null) pstmt.close();
    }
    return description;
}

The following line retrieves the Clob Java value from the ResultSet object rs:

myClob = rs.getClob(1);

The following line retrieves a substring from the myClob object. The substring begins at the first character of the value of myClob and has up to the number of consecutive characters specified in numChar, where numChar is an integer.

description = myClob.getSubString(1, numChar);

Adding and Retrieving BLOB Objects

Adding and retrieving BLOB SQL objects is similar to adding and retrieving CLOB SQL objects. Use the Blob.setBinaryStream method to retrieve an OutputStream object to write the BLOB SQL value that the Blob Java object (which called the method) represents.

Releasing Resources Held by Large Objects

Blob, Clob, and NClob Java objects remain valid for at least the duration of the transaction in which they are created. This could potentially result in an application running out of resources during a long running transaction. Applications may release Blob, Clob, and NClob resources by invoking their free method.
In the following excerpt, the method Clob.free is called to release the resources held for a previously created Clob object:

Clob aClob = con.createClob();
int numWritten = aClob.setString(1, val);
aClob.free();

Using SQLXML Objects

The Connection interface provides support for the creation of SQLXML objects using the method createSQLXML. The object that is created does not contain any data. Data may be added to the object by calling the setString, setBinaryStream, setCharacterStream or setResult method on the SQLXML interface.
The following topics are covered:

• Creating SQLXML Objects
• Retrieving SQLXML Values in ResultSet
• Accessing SQLXML Object Data
• Storing SQLXML Objects
• Initializing SQLXML Objects
• Releasing SQLXML Resources
• Sample Code

Creating SQLXML Objects

In the following excerpt, the method Connection.createSQLXML is used to create an empty SQLXML object. The SQLXML.setString method is used to write data to the SQLXML object that was created.

Connection con = DriverManager.getConnection(url, props);
SQLXML xmlVal = con.createSQLXML();
xmlVal.setString(val);

Retrieving SQLXML Values in ResultSet

The SQLXML data type is treated similarly to the more primitive built-in types. A SQLXML value can be retrieved by calling the getSQLXML method in the ResultSet or CallableStatement interface.
For example, the following excerpt retrieves a SQLXML value from the first column of the ResultSet rs:

SQLXML xmlVar = rs.getSQLXML(1);

SQLXML objects remain valid for at least the duration of the transaction in which they are created, unless their free method is invoked.

Accessing SQLXML Object Data

The SQLXML interface provides the getString, getBinaryStream, getCharacterStream, and getSource methods to access its internal content. The following excerpt retrieves the contents of an SQLXML object using the getString method:

SQLXML xmlVal= rs.getSQLXML(1);
String val = xmlVal.getString();

The getBinaryStream or getCharacterStream methods can be used to obtain an InputStream or a Reader object that can be passed directly to an XML parser. The following excerpt obtains an InputStream object from an SQLXML Object and then processes the stream using a DOM (Document Object Model) parser:

SQLXML sqlxml = rs.getSQLXML(column);
InputStream binaryStream = sqlxml.getBinaryStream();
DocumentBuilder parser = 
    DocumentBuilderFactory.newInstance().newDocumentBuilder();
Document result = parser.parse(binaryStream);

The getSource method returns a javax.xml.transform.Source object. Sources are used as input to XML parsers and XSLT transformers.
The following excerpt retrieves and parses the data from a SQLXML object using the SAXSource object returned by invoking the getSource method:

SQLXML xmlVal= rs.getSQLXML(1);
SAXSource saxSource = sqlxml.getSource(SAXSource.class);
XMLReader xmlReader = saxSource.getXMLReader();
xmlReader.setContentHandler(myHandler);
xmlReader.parse(saxSource.getInputSource());

Storing SQLXML Objects

A SQLXML object can be passed as an input parameter to a PreparedStatement object just like other data types. The method setSQLXML sets the designated PreparedStatement parameter with a SQLXML object.
In the following excerpt, authorData is an instance of the java.sql.SQLXML interface whose data was initialized previously.

PreparedStatement pstmt = conn.prepareStatement("INSERT INTO bio " +
                              "(xmlData, authId) VALUES (?, ?)");
pstmt.setSQLXML(1, authorData);
pstmt.setInt(2, authorId);

The updateSQLXML method can be used to update a column value in an updatable result set.
If the java.xml.transform.Result, Writer, or OutputStream object for the SQLXML object has not been closed prior to calling setSQLXML or updateSQLXML, a SQLException will be thrown.

Initializing SQLXML Objects

The SQLXML interface provides the methods setString, setBinaryStream, setCharacterStream, or setResult to initialize the content for a SQLXML object that has been created by calling the Connection.createSQLXML method.
The following excerpt uses the method setResult to return a SAXResult object to populate a newly created SQLXML object:

SQLXML sqlxml = con.createSQLXML();
SAXResult saxResult = sqlxml.setResult(SAXResult.class);
ContentHandler contentHandler = saxResult.getXMLReader().getContentHandler();
contentHandler.startDocument();
    
// set the XML elements and
// attributes into the result
contentHandler.endDocument();

The following excerpt uses the setCharacterStream method to obtain a java.io.Writer object in order to initialize a SQLXML object:

SQLXML sqlxml = con.createSQLXML();
Writer out= sqlxml.setCharacterStream();
BufferedReader in = new BufferedReader(new FileReader("xml/foo.xml"));
String line = null;
while((line = in.readLine() != null) {
    out.write(line);
}

Similarly, the SQLXML setString method can be used to initialize a SQLXML object.
If an attempt is made to call the setString, setBinaryStream, setCharacterStream, and setResult methods on a SQLXML object that has previously been initialized, a SQLException will be thrown. If more than one call to the methods setBinaryStream, setCharacterStream, and setResult occurs for the same SQLXML object, a SQLException is thrown and the previously returned javax.xml.transform.Result, Writer, or OutputStream object is not affected.

Releasing SQLXML Resources

SQLXML objects remain valid for at least the duration of the transaction in which they are created. This could potentially result in an application running out of resources during a long running transaction. Applications may release SQLXML resources by invoking their free method.
In the following excerpt, the method SQLXML.free is called to release the resources held for a previously created SQLXML object.

SQLXML xmlVar = con.createSQLXML();
xmlVar.setString(val);
xmlVar.free();

Sample Code

MySQL and Java DB and their respective JDBC drivers do not fully support the SQLXML JDBC data type as described on in this section. However, the sample RSSFeedsTable demonstrates how to handle XML data with MySQL and Java DB.
The owner of The Coffee Break follows several RSS feeds from various web sites that cover restaurant and beverage industry news. An RSS (Really Simple Syndication or Rich Site Summary) feed is an XML document that contains a series of articles and associated metadata, such as the date of publication and author for each article. The owner would like to store these RSS feeds into a database table, including the RSS feed from The Coffee Break's blog.
The file rss-the-coffee-break-blog.xml is an example RSS feed from The Coffee Break's blog.

Working with XML Data in MySQL

The sample RSSFeedsTable stores RSS feeds in the table RSS_FEEDS, which is created with the following command:

create table RSS_FEEDS
    (RSS_NAME varchar(32) NOT NULL,
    RSS_FEED_XML longtext NOT NULL,
    PRIMARY KEY (RSS_NAME));

MySQL does not support the XML data type. Instead, this sample stores XML data in a column of type LONGTEXT, which is a CLOB SQL data type. MySQL has four CLOB data types; the LONGTEXT data type holds the greatest amount of characters among the four.
The method RSSFeedsTable.addRSSFeed adds an RSS feed to the RSS_FEEDS table. The first statements of this method converts the RSS feed (which is represented by an XML file in this sample) into an object of type org.w3c.dom.Document, which represents a DOM (Document Object Model) document. This class, along with classes and interfaces contained in the package javax.xml, contain methods that enable you to manipulate XML data content. For example, the following statement uses an XPath expression to retrieve the title of the RSS feed from the Document object:

Node titleElement =
    (Node)xPath.evaluate("/rss/channel/title[1]",
        doc, XPathConstants.NODE);

The XPath expression /rss/channel/title[1] retrieves the contents of the first <title> element. For the file rss-the-coffee-break-blog.xml, this is the string The Coffee Break Blog.
The following statements add the RSS feed to the table RSS_FEEDS:

// For databases that support the SQLXML
// data type, this creates a
// SQLXML object from
// org.w3c.dom.Document.

System.out.println("Adding XML file " + fileName);
String insertRowQuery =
    "insert into RSS_FEEDS " +
    "(RSS_NAME, RSS_FEED_XML) values " +
    "(?, ?)";
insertRow = con.prepareStatement(insertRowQuery);
insertRow.setString(1, titleString);

System.out.println("Creating SQLXML object with MySQL");
rssData = con.createSQLXML();
System.out.println("Creating DOMResult object");
DOMResult dom = (DOMResult)rssData.setResult(DOMResult.class);
dom.setNode(doc);

insertRow.setSQLXML(2, rssData);
System.out.println("Running executeUpdate()");
insertRow.executeUpdate();

The RSSFeedsTable.viewTable method retrieves the contents of RSS_FEEDS. For each row, the method creates an object of type org.w3c.dom.Document named doc in which to store the XML content in the column RSS_FEED_XML. The method retrieves the XML content and stores it in an object of type SQLXML named rssFeedXML. The contents of rssFeedXML are parsed and stored in the doc object.

Working with XML Data in Java DB

Note: See the section "XML data types and operators" in Java DB Developer's Guide for more information about working with XML data in Java DB.
The sample RSSFeedsTable stores RSS feeds in the table RSS_FEEDS, which is created with the following command:

create table RSS_FEEDS
    (RSS_NAME varchar(32) NOT NULL,
    RSS_FEED_XML xml NOT NULL,
    PRIMARY KEY (RSS_NAME));

Java DB supports the XML data type, but it does not support the SQLXML JDBC data type. Consequently, you must convert any XML data to a character format, and then use the Java DB operator XMLPARSE to convert it to the XML data type.
The RSSFeedsTable.addRSSFeed method adds an RSS feed to the RSS_FEEDS table. The first statements of this method convert the RSS feed (which is represented by an XML file in this sample) into an object of type org.w3c.dom.Document. This is described in the section Working with XML Data in MySQL.
The RSSFeedsTable.addRSSFeed method converts the RSS feed to a String object with the method JDBCTutorialUtilities.convertDocumentToString.
Java DB has an operator named XMLPARSE that parses a character string representation into a Java DB XML value, which is demonstrated by the following excerpt:

String insertRowQuery =
    "insert into RSS_FEEDS " +
    "(RSS_NAME, RSS_FEED_XML) values " +
    "(?, xmlparse(document cast " +
    "(? as clob) preserve whitespace))";

The XMLPARSE operator requires that you convert the character representation of the XML document into a string data type that Java DB recognizes. In this example, it converts it into a CLOB data type. See Getting Started and the Java DB documentation for more information about Apache Xalan and Java DB requirements.
The method RSSFeedsTable.viewTable retrieves the contents of RSS_FEEDS. Because Java DB does not support the JDBC data type SQLXML you must retrieve the XML content as a string. Java DB has an operator named XMLSERIALIZE that converts an XML type to a character type:

String query =
    "select RSS_NAME, " +
    "xmlserialize " +
    "(RSS_FEED_XML as clob) " +
    "from RSS_FEEDS";

As with the XMLPARSE operator, the XMLSERIALIZE operator requires that Apache Xalan be listed in your Java class path.

Using Array Objects

Note: MySQL and Java DB currently do not support the ARRAY SQL data type. Consequently, no JDBC tutorial example is available to demonstrate the Array JDBC data type.
The following topics are covered:

• Creating Array Objects
• Retrieving and Accessing Array Values in ResultSet
• Storing and Updating Array Objects
• Releasing Array Resources

Creating Array Objects

Use the method Connection.createArrayOf to create Array objects.
For example, suppose your database contains a table named REGIONS, which has been created and populated with the following SQL statements; note that the syntax of these statements will vary depending on your database:

create table REGIONS
    (REGION_NAME varchar(32) NOT NULL,
    ZIPS varchar32 ARRAY[10] NOT NULL,
    PRIMARY KEY (REGION_NAME));

insert into REGIONS values(
    'Northwest',
    '{"93101", "97201", "99210"}');
insert into REGIONS values(
    'Southwest',
    '{"94105", "90049", "92027"}');

Connection con = DriverManager.getConnection(url, props);
String [] northEastRegion = { "10022", "02110", "07399" };
Array aArray = con.createArrayOf("VARCHAR", northEastRegionnewYork);

The Oracle Database JDBC driver implements the java.sql.Array interface with the oracle.sql.ARRAY class.

Retrieving and Accessing Array Values in ResultSet

As with the JDBC 4.0 large object interfaces (Blob, Clob, NClob), you can manipulate Array objects without having to bring all of their data from the database server to your client computer. An Array object materializes the SQL ARRAY it represents as either a result set or a Java array.
The following excerpt retrieves the SQL ARRAY value in the column ZIPS and assigns it to the java.sql.Array object z object. The excerpt retrieves the contents of z and stores it in zips, a Java array that contains objects of type String. The excerpt iterates through the zips array and checks that each postal (zip) code is valid. This code assumes that the class ZipCode has been defined previously with the method isValid returning true if the given zip code matches one of the zip codes in a master list of valid zip codes:

ResultSet rs = stmt.executeQuery(
    "SELECT region_name, zips FROM REGIONS");

while (rs.next()) {
    Array z = rs.getArray("ZIPS");
    String[] zips = (String[])z.getArray();
    for (int i = 0; i < zips.length; i++) {
        if (!ZipCode.isValid(zips[i])) {
            // ...
            // Code to display warning
        }
    }
}

In the following statement, the ResultSet method getArray returns the value stored in the column ZIPS of the current row as the java.sql.Array object z:

Array z = rs.getArray("ZIPS");

The variable z contains a locator, which is a logical pointer to the SQL ARRAY on the server; it does not contain the elements of the ARRAY itself. Being a logical pointer, z can be used to manipulate the array on the server.
In the following line, getArray is the Array.getArray method, not the ResultSet.getArray method used in the previous line. Because the Array.getArray method returns an Object in the Java programming language and because each zip code is a String object, the result is cast to an array of String objects before being assigned to the variable zips.

String[] zips = (String[])z.getArray();

The Array.getArray method materializes the SQL ARRAY elements on the client as an array of String objects. Because, in effect, the variable zips contains the elements of the array, it is possible to iterate through zips in a for loop, looking for zip codes that are not valid.

Storing and Updating Array Objects

Use the methods PreparedStatement.setArray and PreparedStatement.setObject to pass an Array value as an input parameter to a PreparedStatement object.
The following example sets the Array object northEastRegion (created in a previous example) as the second parameter to the PreparedStatement pstmt:

PreparedStatement pstmt = con.prepareStatement(
    "insert into REGIONS (region_name, zips) " + "VALUES (?, ?)");
pstmt.setString(1, "NorthEast");
pstmt.setArray(2, northEastRegion);
pstmt.executeUpdate();

Similarly, use the methods PreparedStatement.updateArray and PreparedStatement.updateObject to update a column in a table with an Array value.

Releasing Array Resources

Array objects remain valid for at least the duration of the transaction in which they are created. This could potentially result in an application running out of resources during a long running transaction. Applications may release Array resources by invoking their free method.
In the following excerpt, the method Array.free is called to release the resources held for a previously created Array object.

Array aArray = con.createArrayOf("VARCHAR", northEastRegionnewYork);
// ...
aArray.free();

Using DISTINCT Data Type

Note: MySQL and Java DB currently do not support the DISTINCT SQL data type. Consequently, no JDBC tutorial example is available to demonstrate the features described in this section.
The DISTINCT data type behaves differently from the other advanced SQL data types. Being a user-defined type that is based on one of the already existing built-in types, it has no interface as its mapping in the Java programming language. Instead, the standard mapping for a DISTINCT data type is the Java type to which its underlying SQL data type maps.
To illustrate, create a DISTINCT data type and then see how to retrieve, set, or update it. Suppose you always use a two-letter abbreviation for a state and want to create a DISTINCT data type to be used for these abbreviations. You could define your new DISTINCT data type with the following SQL statement:

CREATE TYPE STATE AS CHAR(2);

Some databases use an alternate syntax for creating a DISTINCT data type, which is shown in the following line of code:

CREATE DISTINCT TYPE STATE AS CHAR(2);

If one syntax does not work, you can try the other. Alternatively, you can check the documentation for your driver to see the exact syntax it expects.
These statements create a new data type, STATE, which can be used as a column value or as the value for an attribute of a SQL structured type. Because a value of type STATE is in reality a value that is two CHAR types, you use the same method to retrieve it that you would use to retrieve a CHAR value, that is, getString. For example, assuming that the fourth column of ResultSet rs stores values of type STATE, the following line of code retrieves its value:

String state = rs.getString(4);

Similarly, you would use the method setString to store a STATE value in the database and the method updateString to modify its value.

Using Structured Objects

Note: MySQL and Java DB currently do not support user-defined types. Consequently, no JDBC tutorial example is available to demonstrate the features described in this section.
The following topics are covered:

• Overview of Structured Types
• Using DISTINCT Type in Structured Types
• Using References to Structured Types
• Sample Code for Creating SQL REF Object
• Using User-Defined Types as Column Values
• Inserting User-Defined Types into Tables

Overview of Structured Types

SQL structured types and DISTINCT types are the two data types that a user can define in SQL. They are often referred to as UDTs (user-defined types), and you create them with a SQL CREATE TYPE statement.
Getting back to the example of The Coffee Break, suppose that the owner has been successful beyond all expectations and has been expanding with new branches. The owner has decided to add a STORES table to the database containing information about each establishment. STORES will have four columns:

• STORE_NO for each store's identification number
• LOCATION for its address
• COF_TYPES for the coffees it sells
• MGR for the name of the store manager

The owner makes the column LOCATION be a SQL structured type, the column COF_TYPES a SQL ARRAY, and the column MGR a REF(MANAGER), with MANAGER being a SQL structured type.
The first thing the owner must define the new structured types for the address and the manager. A SQL structured type is similar to structured types in the Java programming language in that it has members, called attributes, that may be any data type. The owner writes the following SQL statement to create the new data type ADDRESS:

CREATE TYPE ADDRESS
(
    NUM INTEGER,
    STREET VARCHAR(40),
    CITY VARCHAR(40),
    STATE CHAR(2),
    ZIP CHAR(5)
);

In this statement, the new type ADDRESS has five attributes, which are analogous to fields in a Java class. The attribute NUM is an INTEGER, the attribute STREET is a VARCHAR(40), the attribute CITY is a VARCHAR(40), the attribute STATE is a CHAR(2), and the attribute ZIP is a CHAR(5).
The following excerpt, in which con is a valid Connection object, sends the definition of ADDRESS to the database:

String createAddress =
    "CREATE TYPE ADDRESS " +
    "(NUM INTEGER, STREET VARCHAR(40), " +
    "CITY VARCHAR(40), STATE CHAR(2), ZIP CHAR(5))";
Statement stmt = con.createStatement();
stmt.executeUpdate(createAddress);

Now the ADDRESS structured type is registered with the database as a data type, and the owner can use it as the data type for a table column or an attribute of a structured type.

Using DISTINCT Type in Structured Type

One of the attributes the owner of The Coffee Break plans to include in the new structured type MANAGER is the manager's telephone number. Because the owner will always list the telephone number as a 10-digit number (to be sure it includes the area code) and will never manipulate it as a number, the owner decides to define a new type called PHONE_NO that consists of 10 characters. The SQL definition of this data type, which can be thought of as a structured type with only one attribute, looks like this:

CREATE TYPE PHONE_NO AS CHAR(10);

Or, as noted earlier, for some drivers the definition might look like this:

CREATE DISTINCT TYPE PHONE_NO AS CHAR(10);

A DISTINCT type is always based on another data type, which must be a predefined type. In other words, a DISTINCT type cannot be based on a user-defined type (UDT). To retrieve or set a value that is a DISTINCT type, use the appropriate method for the underlying type (the type on which it is based). For example, to retrieve an instance of PHONE_NO, which is based on a CHAR type, you would use the method getString because that is the method for retrieving a CHAR.
Assuming that a value of type PHONE_NO is in the fourth column of the current row of the ResultSet object rs, the following line of code retrieves it:

String phoneNumber = rs.getString(4);

Similarly, the following line of code sets an input parameter that has type PHONE_NO for a prepared statement being sent to the database:

pstmt.setString(1, phoneNumber);

Adding on to the previous code fragment, the definition of PHONE_NO will be sent to the database with the following line of code:

stmt.executeUpdate(
    "CREATE TYPE PHONE_NO AS CHAR(10)");

After registering the type PHONE_NO with the database, the owner can use it as a column type in a table or as the data type for an attribute in a structured type. The definition of MANAGER in the following SQL statement uses PHONE_NO as the data type for the attribute PHONE:

CREATE TYPE MANAGER
(
    MGR_ID INTEGER,
    LAST_NAME VARCHAR(40),
    FIRST_NAME VARCHAR(40),
    PHONE PHONE_NO
);

Reusing stmt, defined previously, the following code fragment sends the definition of the structured type MANAGER to the database:

  String createManager =
    "CREATE TYPE MANAGER " +
    "(MGR_ID INTEGER, LAST_NAME " +
    "VARCHAR(40), " +
    "FIRST_NAME VARCHAR(40), " +
    "PHONE PHONE_NO)";
  stmt.executeUpdate(createManager);

Using References to Structured Types

The owner of The Coffee Break has created three new data types used as column types or attribute types in the database: The structured types LOCATION and MANAGER, and the DISTINCT type PHONE_NO. The entrepreneur has used PHONE_NO as the type for the attribute PHONE in the new type MANAGER, and ADDRESS as the data type for the column LOCATION in the table STORES. The MANAGER type could be used as the type for the column MGR, but instead the entrepreneur prefers to use the type REF(MANAGER) because the entrepreneur often has one person manage two or three stores. Using REF(MANAGER) as a column type avoids repeating all the data for MANAGER when one person manages more than one store.
With the structured type MANAGER already created, the owner can now create a table containing instances of MANAGER that can be referenced. A reference to an instance of MANAGER will have the type REF(MANAGER). A SQL REF is nothing more than a logical pointer to a structured type, so an instance of REF(MANAGER) serves as a logical pointer to an instance of MANAGER.
Because a SQL REF value needs to be permanently associated with the instance of the structured type that it references, it is stored in a special table together with its associated instance. A programmer does not create REF types directly but rather creates the table that will store instances of a particular structured type that can be referenced. Every structured type that is to be referenced will have its own table. When you insert an instance of the structured type into the table, the database automatically creates a REF instance. For example, to contain instances of MANAGER that can be referenced, the owner created the following special table using SQL:

  CREATE TABLE MANAGERS OF MANAGER
  (OID REF(MANAGER)
  VALUES ARE SYSTEM GENERATED);

This statement creates a table with the special column OID, which stores values of type REF(MANAGER). Each time an instance of MANAGER is inserted into the table, the database will generate an instance of REF(MANAGER) and store it in the column OID. Implicitly, an additional column stores each attribute of MANAGER that has been inserted into the table, as well. For example, the following code fragment shows how the entrepreneur created three instances of the MANAGER structured type to represent three managers:

  INSERT INTO MANAGERS (
    MGR_ID, LAST_NAME,
    FIRST_NAME, PHONE) VALUES
  (
    000001,
    'MONTOYA',
    'ALFREDO',
    '8317225600'
  );

  INSERT INTO MANAGERS (
    MGR_ID, LAST_NAME,
    FIRST_NAME, PHONE) VALUES
  (
    000002,
    'HASKINS',
    'MARGARET',
    '4084355600'
  );

  INSERT INTO MANAGERS (
    MGR_ID, LAST_NAME,
    FIRST_NAME, PHONE) VALUES
  (
    000003,
    'CHEN',
    'HELEN',
    '4153785600'
   );

The table MANAGERS will now have three rows, one row for each manager inserted so far. The column OID will contain three unique object identifiers of type REF(MANAGER), one for each instance of MANAGER. These object identifiers were generated automatically by the database and will be permanently stored in the table MANAGERS. Implicitly, an additional column stores each attribute of MANAGER. For example, in the table MANAGERS, one row contains a REF(MANAGER) that references Alfredo Montoya, another row contains a REF(MANAGER) that references Margaret Haskins, and a third row contains a REF(MANAGER) that references Helen Chen.
To access a REF(MANAGER) instance, you select it from its table. For example, the owner retrieved the reference to Alfredo Montoya, whose ID number is 000001, with the following code fragment:

  String selectMgr =
    "SELECT OID FROM MANAGERS " +
    "WHERE MGR_ID = 000001";
  ResultSet rs = stmt.executeQuery(selectMgr);
  rs.next();
  Ref manager = rs.getRef("OID");

Now the variable manager can be used as a column value that references Alfredo Montoya.

Sample Code for Creating SQL REF Object

The following code example creates the table MANAGERS, a table of instances of the structured type MANAGER that can be referenced, and inserts three instances of MANAGER into the table. The column OID in this table will store instances of REF(MANAGER). After this code is executed, the MANAGERS table will have a row for each of the three MANAGER objects inserted, and the value in the OID column will be the REF(MANAGER) type that identifies the instance of MANAGER stored in that row.

package com.oracle.tutorial.jdbc;

import java.sql.*;

public class CreateRef {

    public static void main(String args[]) {

        JDBCTutorialUtilities myJDBCTutorialUtilities;
        Connection myConnection = null;

        if (args[0] == null) {
            System.err.println("Properties file not specified " +
                               "at command line");
            return;
        } else {
            try {
                myJDBCTutorialUtilities = new JDBCTutorialUtilities(args[0]);
            } catch (Exception e) {
                System.err.println("Problem reading properties " +
                                   "file " + args[0]);
                e.printStackTrace();
                return;
            }
        }

        Connection con = null;
        Statement stmt = null;

        try {
            String createManagers =
                "CREATE TABLE " +
                "MANAGERS OF MANAGER " +
                "(OID REF(MANAGER) " +
                "VALUES ARE SYSTEM " +
                "GENERATED)";

            String insertManager1 =
                "INSERT INTO MANAGERS " +
                "(MGR_ID, LAST_NAME, " +
                "FIRST_NAME, PHONE) " +
                "VALUES " +
                "(000001, 'MONTOYA', " +
                "'ALFREDO', " +
                "'8317225600')";

            String insertManager2 =
                "INSERT INTO MANAGERS " +
                "(MGR_ID, LAST_NAME, " +
                "FIRST_NAME, PHONE) " +
                "VALUES " +
                "(000002, 'HASKINS', " +
                "'MARGARET', " +
                "'4084355600')";

            String insertManager3 =
                "INSERT INTO MANAGERS " +
                "(MGR_ID, LAST_NAME, " +
                "FIRST_NAME, PHONE) " +
                "VALUES " +
                "(000003, 'CHEN', 'HELEN', " +
                "'4153785600')";
  
            con = myJDBCTutorialUtilities.getConnection();
            con.setAutoCommit(false);

            stmt = con.createStatement();
            stmt.executeUpdate(createManagers);

            stmt.addBatch(insertManager1);
            stmt.addBatch(insertManager2);
            stmt.addBatch(insertManager3);
            int [] updateCounts = stmt.executeBatch();

            con.commit();

            System.out.println("Update count for:  ");
            for (int i = 0; i < updateCounts.length; i++) {
                System.out.print("    command " + (i + 1) + " = ");
                System.out.println(updateCounts[i]);
            }
        } catch(BatchUpdateException b) {
            System.err.println("-----BatchUpdateException-----");
            System.err.println("Message:  " + b.getMessage());
            System.err.println("SQLState:  " + b.getSQLState());
            System.err.println("Vendor:  " + b.getErrorCode());
            System.err.print("Update counts for " + "successful commands:  ");
            int [] rowsUpdated = b.getUpdateCounts();
            for (int i = 0; i < rowsUpdated.length; i++) {
                System.err.print(rowsUpdated[i] + "   ");
            }
            System.err.println("");
        } catch(SQLException ex) {
            System.err.println("------SQLException------");
            System.err.println("Error message:  " + ex.getMessage());
            System.err.println("SQLState:  " + ex.getSQLState());
            System.err.println("Vendor:  " + ex.getErrorCode());
        } finally {
            if (stmt != null) { stmt.close(); }
              JDBCTutorialUtilities.closeConnection(con);
        }
    }
}

Using User-Defined Types as Column Values

Our entrepreneur now has the UDTs required to create the table STORES. The structured type ADDRESS is the type for the column LOCATION, and the type REF(MANAGER) is the type for the column MGR.
The UDT COF_TYPES is based on the SQL data type ARRAY and is the type for the column COF_TYPES. The following line of code creates the type COF_ARRAY as an ARRAY value with 10 elements. The base type of COF_ARRAY is VARCHAR(40).

  CREATE TYPE COF_ARRAY AS ARRAY(10) OF VARCHAR(40);

With the new data types defined, the following SQL statement creates the table STORES:

  CREATE TABLE STORES
  (
    STORE_NO INTEGER,
    LOCATION ADDRESS,
    COF_TYPES COF_ARRAY,
    MGR REF(MANAGER)
  );

Inserting User-Defined Types into Tables

The following code fragment inserts one row into the STORES table, supplying values for the columns STORE_NO, LOCATION, COF_TYPES, and MGR, in that order:

  INSERT INTO STORES VALUES
  (
    100001,
    ADDRESS(888, 'Main_Street',
      'Rancho_Alegre',
      'CA', '94049'),
    COF_ARRAY('Colombian', 'French_Roast',
      'Espresso', 'Colombian_Decaf',
      'French_Roast_Decaf'),
    SELECT OID FROM MANAGERS
      WHERE MGR_ID = 000001
  );

The following goes through each column and the value inserted into it.

  STORE_NO: 100001

This column is type INTEGER, and the number 100001 is an INTEGER type, similar to entries made before in the tables COFFEES and SUPPLIERS.

  LOCATION: ADDRESS(888, 'Main_Street',
    'Rancho_Alegre', 'CA', '94049')

The type for this column is the structured type ADDRESS, and this value is the constructor for an instance of ADDRESS. When we sent the definition of ADDRESS was sent to the database, one of the things it did was to create a constructor for the new type. The comma-separated values in parentheses are the initialization values for the attributes of the ADDRESS type, and they must appear in the same order in which the attributes were listed in the definition of the ADDRESS type. 888 is the value for the attribute NUM, which is an INTEGER value. "Main_Street" is the value for STREET, and "Rancho_Alegre" is the value for CITY, with both attributes being of type VARCHAR(40). The value for the attribute STATE is "CA", which is of type CHAR(2), and the value for the attribute ZIP is "94049", which is of type CHAR(5).

  COF_TYPES: COF_ARRAY(
    'Colombian',
    'French_Roast',
    'Espresso',
    'Colombian_Decaf',
    'French_Roast_Decaf'),

The column COF_TYPES is of type COF_ARRAY with a base type of VARCHAR(40), and the comma-separated values between parentheses are the String objects that are the array elements. The owner defined the type COF_ARRAY as having a maximum of 10 elements. This array has 5 elements because the entrepreneur supplied only 5 String objects for it.

  MGR: SELECT OID FROM MANAGERS
    WHERE MGR_ID = 000001

The column MGR is type REF(MANAGER), which means that a value in this column must be a reference to the structured type MANAGER. All of the instances of MANAGER are stored in the table MANAGERS. All of the instances of REF(MANAGER) are also stored in this table, in the column OID. The manager for the store described in this table row is Alfredo Montoya, and his information is stored in the instance of MANAGER that has 100001 for the attribute MGR_ID. To get the REF(MANAGER) instance associated with the MANAGER object for Alfredo Montoya, select the column OID that is in the row where MGR_ID is 100001 in the table MANAGERS. The value that will be stored in the MGR column of the STORES table (the REF(MANAGER) value) is the value the DBMS generated to uniquely identify this instance of the MANAGER structured type.
Send the preceding SQL statement to the database with the following code fragment:

  String insertMgr =
    "INSERT INTO STORES VALUES " +
    "(100001, " +
    "ADDRESS(888, 'Main_Street', " +
      "'Rancho_Alegre', 'CA', " +
      "'94049'), " +
    "COF_ARRAY('Colombian', " +
      "'French_Roast', 'Espresso', " +
      "'Colombian_Decaf', " +
      "'French_Roast_Decaf'}, " +
    "SELECT OID FROM MANAGERS " +
    "WHERE MGR_ID = 000001)";

  stmt.executeUpdate(insertMgr);

However, because you are going to send several INSERT INTO statements, it will be more efficient to send them all together as a batch update, as in the following code example:

package com.oracle.tutorial.jdbc;

import java.sql.*;

public class InsertStores {
    public static void main(String args[]) {

        JDBCTutorialUtilities myJDBCTutorialUtilities;
        Connection myConnection = null;

        if (args[0] == null) {
            System.err.println(
                "Properties file " +
                "not specified " +
                "at command line");
            return;
        } else {
            try {
                myJDBCTutorialUtilities = new
                    JDBCTutorialUtilities(args[0]);
            } catch (Exception e) {
                System.err.println(
                    "Problem reading " +
                    "properties file " +
                    args[0]);
                e.printStackTrace();
                return;
            }
        }

        Connection con = null;
        Statement stmt = null;

        try {
            con = myJDBCTutorialUtilities.getConnection();
            con.setAutoCommit(false);

            stmt = con.createStatement();

            String insertStore1 =
                "INSERT INTO STORES VALUES (" +
                "100001, " +
                "ADDRESS(888, 'Main_Street', " +
                    "'Rancho_Alegre', 'CA', " +
                    "'94049'), " +
                "COF_ARRAY('Colombian', " +
                    "'French_Roast', " +
                    "'Espresso', " +
                    "'Colombian_Decaf', " +
                    "'French_Roast_Decaf'), " +
                "(SELECT OID FROM MANAGERS " +
                "WHERE MGR_ID = 000001))";

            stmt.addBatch(insertStore1);

            String insertStore2 =
                "INSERT INTO STORES VALUES (" +
                "100002, " +
                "ADDRESS(1560, 'Alder', " +
                    "'Ochos_Pinos', " +
                    "'CA', '94049'), " +
                "COF_ARRAY('Colombian', " +
                    "'French_Roast', " +
                    "'Espresso', " +
                    "'Colombian_Decaf', " +
                    "'French_Roast_Decaf', " +
                    "'Kona', 'Kona_Decaf'), " +
                "(SELECT OID FROM MANAGERS " +
                "WHERE MGR_ID = 000001))";

            stmt.addBatch(insertStore2);

            String insertStore3 =
                "INSERT INTO STORES VALUES (" +
                "100003, " +
                "ADDRESS(4344, " +
                    "'First_Street', " +
                    "'Verona', " +
                    "'CA', '94545'), " +
                "COF_ARRAY('Colombian', " +
                    "'French_Roast', " +
                    "'Espresso', " +
                    "'Colombian_Decaf', " +
                    "'French_Roast_Decaf', " +
                    "'Kona', 'Kona_Decaf'), " +
                "(SELECT OID FROM MANAGERS " +
                "WHERE MGR_ID = 000002))";

            stmt.addBatch(insertStore3);

            String insertStore4 =
                "INSERT INTO STORES VALUES (" +
                "100004, " +
                "ADDRESS(321, 'Sandy_Way', " +
                    "'La_Playa', " +
                    "'CA', '94544'), " +
                "COF_ARRAY('Colombian', " +
                    "'French_Roast', " +
                    "'Espresso', " +
                    "'Colombian_Decaf', " +
                    "'French_Roast_Decaf', " +
                    "'Kona', 'Kona_Decaf'), " +
                "(SELECT OID FROM MANAGERS " +
                "WHERE MGR_ID = 000002))";

            stmt.addBatch(insertStore4);

            String insertStore5 =
                "INSERT INTO STORES VALUES (" +
                "100005, " +
                "ADDRESS(1000, 'Clover_Road', " +
                    "'Happyville', " +
                    "'CA', '90566'), " +
                "COF_ARRAY('Colombian', " +
                    "'French_Roast', " +
                    "'Espresso', " + 
                    "'Colombian_Decaf', " +
                    "'French_Roast_Decaf'), " +
                "(SELECT OID FROM MANAGERS " +
                "WHERE MGR_ID = 000003))";

            stmt.addBatch(insertStore5);

            int [] updateCounts = stmt.executeBatch();

            ResultSet rs = stmt.executeQuery(
                "SELECT * FROM STORES");
            System.out.println("Table STORES after insertion:");
            System.out.println("STORE_NO   " + "LOCATION   " +
                "COF_TYPE   " + "MGR");

            while (rs.next()) {
                int storeNo = rs.getInt("STORE_NO");
                Struct location = (Struct)rs.getObject("LOCATION");
                Object[] locAttrs = location.getAttributes();
                Array coffeeTypes = rs.getArray("COF_TYPE");
                String[] cofTypes = (String[])coffeeTypes.getArray();

                Ref managerRef = rs.getRef("MGR");
                PreparedStatement pstmt = con.prepareStatement(
                    "SELECT MANAGER " +
                    "FROM MANAGERS " +
                    "WHERE OID = ?");
  
                pstmt.setRef(1, managerRef);
                ResultSet rs2 = pstmt.executeQuery();
                rs2.next();
                Struct manager = (Struct)rs2.getObject("MANAGER");
                Object[] manAttrs = manager.getAttributes();
      
                System.out.print(storeNo + "   ");
                System.out.print(
                    locAttrs[0] + " " +
                    locAttrs[1] + " " +
                    locAttrs[2] + ", " +
                    locAttrs[3] + " " +
                    locAttrs[4] + " ");

                for (int i = 0; i < cofTypes.length; i++)
                    System.out.print( cofTypes[i] + " ");
          
                System.out.println(
                    manAttrs[1] + ", " +
                    manAttrs[2]);
        
                rs2.close();
                pstmt.close();
            }

            rs.close();

        } catch(BatchUpdateException b) {
            System.err.println("-----BatchUpdateException-----");
            System.err.println("SQLState:  " + b.getSQLState());
            System.err.println("Message:  " + b.getMessage());
            System.err.println("Vendor:  " + b.getErrorCode());
            System.err.print("Update counts:  ");
            int [] updateCounts = b.getUpdateCounts();

            for (int i = 0; i < updateCounts.length; i++) {
                System.err.print(updateCounts[i] + "   ");
            }
            System.err.println("");

        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            System.err.println("SQLState:  " + ex.getSQLState());
            System.err.println("Message:  " + ex.getMessage());
            System.err.println("Vendor:  " + ex.getErrorCode());
        } finally {
            if (stmt != null) { stmt.close(); }
                JDBCTutorialUtilities.closeConnection(con);
            }
        }
    }
}