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:
The following topics are covered:
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:
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.
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.
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.
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.)
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.
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.
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);
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.