he latest version of Java, J2SE 5.0 (version 1.5 of the JDK) is the first to deliver the concept of disconnected RowSet objects to the Java language. The FilteredRowset interface extends from the WebRowSet interface, which in turn extends from the javax.sql.Rowset interface. As you will see, the FilteredRowSet lets one narrow down the number of rows in a disconnected object based on filtering logic you provide without requiring an ongoing connection to your database. In this article, I’ll introduce you to FilteredRowSet objects.

Where Oh WHERE Clause?
If you were using simple JDBC, you could achieve the same thing using a WHERE clause in a query using a JdbcRowset object. But JdbcRowset objects require a connection to the database, whereas RowSet objects (a superset of FilteredRowSet objects) do not (i.e., they are disconnected). A WHERE clause requires a connection to the database to filter your database data. With the FilteredRowSet, you can retrieve your data from the database and then disconnect. As you will see, the FilteredRowSet uses Predicate objects to retrieve data from RowSet objects without a database connection using WHERE-like logic.

A Real World Example
I’ve decided to build a fun example to help facilitate understanding. This application will retrieve NBA (National Basketball Association) player statistics for the 2003-04 season. Don’t worry, you don’t need to know much about basketball for this study. You’ll build a database table that stores player data including games played (G), field goals made (FG), free throws made (FT), total points scored (P), and average points per game (PG).

To do this, you’ll need a database and, of course, a database table. You can choose any database you prefer but it must support FilteredRowSet objects, which are an offering of Java 1.5. I am going to use IBM’s DB2 8.1, which you can download as a trial from http://www-306.ibm.com/software/data/db2/udb/v8/.

I’ll start by creating a database using the DB2 command line processor:

db2 => create database balldb

Next, I’ll connect to the database with my user name (db2admin) and password (db2admin).

db2=> connect to balldb user db2admin using db2admin

Next, I’ll create a table (named STATS) to house all the statistical data mentioned above. The following statement creates the table:

db2=> create table stats (firstname varchar(40) not null, lastname varchar(40)    not null, team varchar(40) not null, G int not null, FG int not null, FT int not null, P int not null,
 PG decimal(3,1) not null)

I need some data in my table. In the real world, this table would likely have data for all the players in the NBA. For the purposes of this example, I’ve included only 10 players in the table?namely the players with the highest average points per game during the 2003-2004 regular season:

Table 1. The NBA Table Data.

The following statement inserts the first row into the database.

insert into stats values ('Tracy','McGrady','Orlando Magic',67,653,398,1878,28.0)

You can add each of the remaining nine rows with appropriate data using the same process, until the table is complete.

The Predicate Interface
In order to filter data in a disconnected RowSet, one must provide a filtering criterion. This criterion is dependent on the javax.sql.rowset.Predicate interface. At the time this article was published, Java did not ship with a default implementation of the Predicate interface. Accordingly, the onus of implementation is placed on developers.

In order to implement the Predicate interface, you need to implement the following methods (pulled directly from the javax.sql.rowset.Predicate API).

Table 2. Methods for Implementing the Predicate Interface

For demonstration purposes, I’ll create a simple range filter. This filter checks if a given column value falls between two other values. I’ve initialized the Predicate implementation object with the values for the filter. My filtering class is RangeFilter.java, which you can find in the downloadable zip file associated with this article. It is a good idea to study its structure alongside my scrutiny of the class.

You can build any filter you like. I chose a range evaluation, but you can create a Predicate implementation that grabs all names beginning with a certain letter or anything else you can dream up.

The filtering logic for my Predicate implementation lies in the evaluate methods. It is in these methods that I have to check if a given value for a specified column falls between the high and low values I established when I call the RangeFilter constructor. You can see this logic in the evaluate method below, which takes in the column name as an argument.

public boolean evaluate(Object value, String columnName) {     int colVal;     boolean bool = true;     if(columnName.equals(this.columnName) )     {          colVal = ( (Integer)value ).intValue();          if ((colVal>=low) && (colVal<=high)) bool = true;          else bool = false;     }      return bool;}

In order to decide whether a row meets the filter requirements, I first have to check to make sure I'm looking at the proper column:

if(columnName.equals(this.columnName))

The Predicate object forces me to pass in an Object as an argument. Because I am scrutinizing integers, I have to cast to an Integer wrapper object and get the housed integer to evaluate. I'll then do a check to see if the value falls between the specified range:

if ((colVal>=low) && (colVal<=high))

If it does, I'll set my boolean variable to true so that the evaluate method will return a true, signifying that the given row is allowed to be part of the FilteredRowSet. If I am on the correct column and the value does not fall between the range, my boolean variable is set to false. False causes an SQLException to be thrown when a row that does not pass the filter is inserted.

You will notice similar code to the evaluate method above in the evaluate(RowSet) method. This method is used internally when you apply your filter. When a row does not pass the filter scrutiny, an SQLException is thrown and the row is discarded from the RowSet.

Applying the Filter
Now that you've seen how to develop your filtering logic via the Predicate implementation, you are ready to see predicates in action. This is done in the class FilterDemo.java found in the project zip file. To execute the FilterDemo class you will need to have archives db2jcc.jar and db2jcc_license_cu.jar file in your Java classpath. Using the default installation, these will be located in the Java subdirectory of your DB2 installation.

My code starts off by establishing a connection to DB2:

Class.forName("com.ibm.db2.jcc.DB2Driver");FilteredRowSet frs = new FilteredRowSetImpl();frs.setUsername("db2admin"); frs.setPassword("db2admin"); frs.setUrl("jdbc:db2://localhost:50000/balldb");

In the code above, I use the DriverManager approach to grab the database connection. However, if I were in an application server setting, I would use a DataSource object (an alternative approach also supported by the RowSet). Going back to the DriverManager approach, populate your FilteredRowSet object with the code:

frs.setCommand("SELECT * from STATS");frs.execute();

At this point, your FilteredRowSet contains all of the rows in the STATS table. Next, apply your filter with the code:

Range gameFilter = new Range(80,100,4);frs.setFilter(gameFilter);

This applies your filter to the G column of the STATS table. More specifically, you've specified a range of values between 80 (low) and 100 (high).

To show the effect of this filter, cycle through your FilteredRowSet. As one could guess, what you are shown are the first and last names of the players in your table who have over 80 games and less than 100 games in the G (games) column:

Filtered RowSet:2 - Predrag Stojakovic : Games - 81 : Points - 19643 - Kevin Garnett : Games - 82 : Points - 19875 - Paul Pierce : Games - 80 : Points - 183610 - Michael Redd : Games - 82 : Points – 1776

Inserting, Updating, and Deleting
To perform an insert, the cursor must be moved to a special position called "insert row." From there, you populate the new row using update methods. The FilteredRowSet object is updated when you use the insertRow() method. The change is persisted only after the acceptChanges() method is executed. In the sample code, I've intentionally added two rows to see the effect of the filter.

Table 3. Creating an Exception Case.

After executing the code, you will see that the Shaquille O' Neal row is rejected as he has less than 80 games in the G column. As stated earlier, what is actually happening behind the scenes is that an SQLException is thrown due to the evaluate method returning a false (as the row insertion did not pass the filter).

After successfully inserting the Carmelo Anthony row, I demonstrate the effect of updating a row that does not pass the filter. I then update the Carmelo Anthony row's game column with a value of 50:

frs.updateInt("G",50);frs.acceptChanges();

Applying this update pulls the row from the FilteredRowSet, because it does not pass the filtering criterion after the G column update. It is important to note that the row is pulled from the FilteredRowSet. It is not pulled from the database table.

Compounding the Effect of Filters
What if you want to restrict your games (G) value between 80 and 100 and your points scored value (P) between 1800 and 2000 points? You can do this by applying multiple filters to your FilteredRowSet object. This is demonstrated in the sample code where I've applied a second filter to the FilteredRowSet object:

Range pointFilter = new Range(1800,2000,7);frs.setFilter(pointFilter);

What results is a compounded filtering effect, where FilteredRowSet contains rows with a G value between 80 and 100 further filtered with the rule of a point value (P) between 1800 and 2000:

2 - Predrag Stojakovic : Games - 81 : Points - 19643 - Kevin Garnett : Games - 82 : Points - 19875 - Paul Pierce : Games - 80 : Points - 1836

Nullifying the Effect of Your Filter(s)
All is not lost when you apply a filter to a FilteredRowSet. The rows not meeting the filtering criterion are simply hidden from you. To make them reappear in the FilteredRowSet, you can nullify the effect of the filter with a call like:

frs.setFilter(null)

Performance Implications and Buyer Beware
Database disk I/O is an expensive operation in terms of time. Furthermore, sending result data across the wire is time consuming. Disconnected RowSet objects let one get the most out of the investment of retrieving database data by allowing one to locally access and manipulate a disconnected object. FilteredRowSet objects let one further maximize the usage of local disconnected objects by allowing one to narrow down the data housed in them.

With FilteredRowSet objects in one's arsenal, one might consider combining queries to minimize multiple database accesses and network usage. In short, you should try and get more bang for your buck. This approach should be given major consideration for the pervasive computing space, where network connectivity is typically intermittent.

When using RowSet objects and FilteredRowSet objects, remember to apply the logic of caveat emptor. Closely consider whether this 'snapshot of data' approach meets your customers' service levels agreements. Further, users of FilteredRowSet objects and other types of RowSet objects (e.g., WebRowSet object, CachedRowSet objects, etc) need to be aware that their data can become stale while they are disconnected from their target data source. In other words, this is a great method but it's not applicable in every situation: Apply appropriate judiciousness when you consider adding the FilteredRowSet approach to your arsenal of data retrieval techniques.