dcsimg
May 20, 2019
Hot Topics:

Filtering Data with the JDBC RowSet

  • February 19, 2019
  • By Manoj Debnath
  • Send Email »
  • More Articles »

Sometimes, application queries to a database return a large number of rows. Although fetched data are cached within the ResultSet object, it is often too large to work with them. As a result, we must be able to filter them in different sets of data to limit the visible rows. This article delves into describing the filtering aspect of JDBC RowSet with appropriate examples.

An Overview of RowSet

RowSet is an interface that complements the JDBC API for JavaBeans component model. It provides a set of properties that allows its instance to be configured to connect to a JDBC data source. A RowSet instance is primarily used to retrieve data from the data source. The setter methods of this interface are used to populate the parameters of the command property of a SQL query, which then is used to fetch records from the relational database. Because RowSet adheres to the JavaBean component model, it supports JavaBean events. These events are used to notify other components about events, such as a change of value on a rowset. Because the RowSet interface is designed as a layer above the JDBC driver, it is open to custom implementation. This freedom empowers the vendor to fabricate their own fine-tuned effectuation and ship it with the JDBC product.

The FilteredRowSet

The FilteredRowSet is an interface extension of the RowSet family. There is a reference implementation of this interface, called the FilteredRowSetImpl class. To provide a custom implementation of the FilteredRowSet interface, one may either extend the FilteredRowSetImpl class or use the FilteredRowSet interface as per your requirement. On some occasions, we need to apply some form of filtering on the content that RowSet fetches. A simple possible solution is to provide a query language for all the RowSet implementations. But then, this is not a viable approach because RowSet is built with the idea of a disconnected lightweight component. This would make the object heavy and go against its design principle. We need an approach that addresses the need yet doesn't inject heavyweight query language along with the processing logic of filtering. The JDBC FilteredRowSet standard implementation extends RowSet through the subinterfaces such as CachedRowSet and WebRowSet respectively. The FilteredRowSet can manipulate the cursor through the set of protected cursor manipulation methods supplied by the CachedRowSet interface. These methods can be overridden as per requirements and help while filtering RowSet content.

A Quick Example

Here is an example to illustrate how FilteredRowSet is used to store the contents returned by the query fired to the database. The result of the query is filtered according to the configuration applied to the FilteredRowset implementation. This defines the visible content or the rows that we are interested in from the result returned by the query. In the following example, we have created a filter class called SimpleFilter. This class, in our case, defines the custom implementation of the FilteredRowSet. We then applied this filter on the result returned from the database query. Filtering means limiting the number of rows that will be visible. Therefore, here we'll limit the number of book information records according to the selected author name supplied.

For hands on, following are the database tables used with the upcoming Java code.

Database table, book
Figure 1: Database table, book

Database table, author
Figure 2: Database table, author

Database table, book_author
Figure 3: Database table, book_author

The SimpleFilter class implements the Predicate's evaluate methods to implement our custom filter.

package org.mano.example;
import javax.sql.RowSet;
import javax.sql.rowset.Predicate;
import java.sql.SQLException;
public class SimpleFilter implements Predicate {
   private String[] authors;
   private String colname = null;
   private int colno = -1;
   public SimpleFilter(String[] authors, String colname) {
      this.authors = authors;
      this.colno = -1;
      this.colname = colname;
   }
   public SimpleFilter(String[] authors, int colno) {
      this.authors = authors;
      this.colno = colno;
      this.colname = null;
   }
   @Override
   public Boolean evaluate(Object value, String colName) {
      if (colName.equalsIgnoreCase(this.colname)) {
         for (String author : this.authors) {
            if (author.equalsIgnoreCase((String)value)) {
              return true;
            }
         }
      }
      return false;
   }
   @Override
   public Boolean evaluate(Object value, int colNumber) {
      if (colNumber == this.colno) {
         for (String author : this.authors)
            if (author.equalsIgnoreCase((String)value)) {
               return true;
            }
         }
      }
      return false
   }
   @Override
   public Boolean evaluate(RowSet rs) {
      if (rs == null) return false;
      try {
         for (int i=0;i<authors.length;i++) {
            String al = null;
            if (this.colno> 0) {
                al = (String)rs.getObject(this.colno);
            } else if (this.colname != null) {
               al = (String)rs.getObject(this.colname);
            } else {
               return false;
            }
            if (al.equalsIgnoreCase(authors[i])) {
               return true;
            }
         }
      } catch (SQLException e) {
         return false;
      }
      return false;
   }
}

This class is used to execute the SimpleRowSet filter class. Note how we have utilized FilteredRowSet to filter data in the application. The processing occurs at the application level rather than at the SQL database level. As a result, we can implement a series of filters and apply them on the same result set to obtain the desired result. This leverages performance because we do not have to fire multiple queries to the database to get a modified result. Instead, we can apply multiple filtering on the query result fired once to the database. The application has two important phases:

  • We create a filter which lays down the criteria for filtering the data. This is done by implementing the Predicate interface. There can be multiple constructors accepting different set of arguments. Also, the filter may contain an array of evaluate() methods also accepting different set of arguments with their own distinct set of implementation.
  • The FilteredRowSet class must be instantiated to obtain the desired effect, something which we have done here with the applyFilter() method. The FilteredRowSet uses the custom filter class we have supplied to determine the records to be viewed.
package org.mano.example;
import com.sun.rowset.FilteredRowSetImpl;
import javax.sql.RowSet;
import javax.sql.rowset.FilteredRowSet;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DemoApp {
   private static final String DB_URL =
      "jdbc:mysql://localhost:3306/my_lib";
   private static final String DB_DRIVER =
      "com.mysql.cj.jdbc.Driver";
   private static final String DB_USERNAME =
      "root";
   private static final String DB_PASSWORD =
      "secret";
   public static Connection conn = null;
   public static FilteredRowSet filteredRowSet = null;
   public static void main(String[] args) {
      try {
         Class.forName(DB_DRIVER);
         conn = DriverManager.getConnection(DB_URL,
            DB_USERNAME,DB_PASSWORD);
         System.out.println("Database connection
            successful.");
         applyFilter();
      } catch (SQLException | ClassNotFoundException ex) {
         System.out.println(ex);
      } finally {
         if (conn != null) {
            try {
               conn.close();
            catch (SQLException ex) {
               ex.printStackTrace();
            }
         }
         if (filteredRowSet != null) {
            try {
               filteredRowSet.close();
            } catch (SQLException ex) {
               ex.printStackTrace();
            }
         }
      }
   }
   public static void applyFilter() {
      String[] arr = {"Donne", "Milton"};
      SimpleFilter aFilter = new SimpleFilter(arr, 3);
      try {
         filteredRowSet = new FilteredRowSetImpl();
         filteredRowSet.setCommand("SELECT title, f_name, l_name "
            + "FROM book_author BA, "
            + "author A, "
            + "book B "
            + "WHERE A.auth_id = BA.fk_author "
            + "AND B.book_id = BA.fk_book");
            filteredRowSet.execute(conn);
            System.out.println
               ("--------------------------------------------");
            System.out.println("Before applying any
               filter:");
            System.out.println
               ("--------------------------------------------");
            show(filteredRowSet);
            System.out.println
               ("--------------------------------------------");
            System.out.println("After applying
               filter :");
            System.out.println
               ("--------------------------------------------");
            filteredRowSet.beforeFirst();
            filteredRowSet.setFilter(aFilter);
            show(filteredRowSet);
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
   public static void show(RowSet rs) {
      try {
         while (rs.next()) {
            System.out.println(rs.getString(1) + " / "
               + rs.getString(2)
               + " "+rs.getString(3));
         }
      } catch (SQLException ex) {
         ex.printStackTrace();
      }
   }
}

Output

Database connection successful.
--------------------------------------------
Before applying any filter:
--------------------------------------------
Gulliver's Travels / Jonathan Swift

...

Ill Pensoroso / John Milton
Areopagitica / John Milton
--------------------------------------------
After applying filter:
--------------------------------------------
The Flea / John Donne
Holy Sonnet / John Donne
Paradise Lost / John Milton
Paradise Regained / John Milton
Ill Pensoroso / John Milton
Areopagitica / John Milton

Conclusion

Working with a large number of rows returned from a query has many issues. For one, data retrieved occupies memory.

It always helps to limit them according to the need and relevance. With RowSet, we can filter them according to a criterion without making any additional database requests. This makes it more manageable to work with database rows and leverages efficiency of the code.






Comment and Contribute

 


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

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.


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