Browse DevX
Sign up for e-mail newsletters from DevX


Master the FilteredRowSet Interface for Disconnected Data Retrieval : Page 3

The FilteredRowSet interface, added to version 1.5 of Java, lets you retrieve a custom view of database data using a filter that takes a snapshot, but doesn't alter, your table. Best of all, it does this without a persistent database connection.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 - 1964 3 - Kevin Garnett : Games - 82 : Points - 1987 5 - Paul Pierce : Games - 80 : Points - 1836 10 - 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.

Shaquille O'Neal Los Angeles Lakers 67 554 331 1,439 21.5
Carmelo Anthony Denver Nuggets 82 624 408 1,725 21.0

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 - 1964 3 - Kevin Garnett : Games - 82 : Points - 1987 5 - 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:


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.

Kulvir Singh Bhogal works as an IBM consultant, devising and implementing J2EE-centric solutions at customer sites across the nation.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date