This post shows how to use a FilteredRowSet object. It lets you filter the number of rows that are visible in a RowSet
object so that you can work with only the relevant data that you need.
You may decide 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.
You may know a JdbcRowSet
object, which always has a connection to its data source, so you can do filtering with a query to the data source (using WHERE
clause which defines the filtering criteria).
A FilteredRowSet
object provides a way for a disconnected RowSet
object for filtering without having to execute a query on the data source, consequently avoiding having to get a connection to the data source and sending queries to it.

RowSet Classes in JDBC Java (source: javarevisited.blogspot.cz)
There are two main steps to use the FilteredRowSet:
- Create a new filter (an implementation of Predicate interface).
- Use your filter (setting a filter to your FilteredRowSet).
Create a new filter
You need to implement the evaluate
method, which accepts a Rowset object
. The documentation about this method says:
This method is typically called a
FilteredRowSet
object internal methods (not public) that control theRowSet
object's cursor moving from row to the next. In addition, if this internal method moves the cursor onto a row that has been deleted, the internal method will continue to ove the cursor until a valid row is found.
Other methods, like evaluate(Object value, int column)
and evaluate(Object value, String columnName),
are called when you are inserting new rows to a FilteredRowSet
instance.
My example tries to match any regular expression pattern, which was set during a filter initialization. If a pattern found, method returned true
(= yes, we want this row).
/** * Search Filter for {@link FilteredRowSet} * * @author pavel.sklenar * */ class SearchFilter implements Predicate { private Pattern pattern; public SearchFilter(String searchRegex) { if (searchRegex != null && !searchRegex.isEmpty()) { pattern = Pattern.compile(searchRegex); } } public boolean evaluate(RowSet rs) { System.out.println("SearchFilter.evaluate called "); try { if (!rs.isAfterLast()) { String name = rs.getString("name"); System.out.println(String.format( "Searching for pattern '%s' in %s", pattern.toString(), name)); Matcher matcher = pattern.matcher(name); return matcher.matches(); } else return false; } catch (Exception e) { e.printStackTrace(); return false; } } public boolean evaluate(Object value, int column) throws SQLException { throw new UnsupportedOperationException("Not supported yet."); } public boolean evaluate(Object value, String columnName) throws SQLException { throw new UnsupportedOperationException("Not supported yet."); } }
Use your filter
You need to set your new instance of Predicate
interface to your FilteredRowSet
using method setFilter:
usersRS.setFilter(new SearchFilter("^[A-L].*"));
The complete example of my code is bellow.
To run it, you need to add an H2 library (a memory database driver) to your classpath, e.g. available here.
/** * Example usage of {@link FilteredRowSet} * * @author pavel.sklenar * */ public class FilteredRowSetTest { /* * Sample names for test */ private static final String[] NAMES = { "Bill Gates", "Steve Jobs", "Mark Zuckerberg", "Alan Turing", "Linus Torlvalds" }; /** * The main class to run test */ public static void main(String[] args) throws Exception { Connection c = DriverManager.getConnection("jdbc:h2:mem:db1", "test", "test"); // Just only prepare data for test prepareData(c); RowSetFactory rsf = RowSetProvider.newFactory(); FilteredRowSet usersRS = rsf.createFilteredRowSet(); usersRS.setCommand("select * from USER"); usersRS.execute(c); usersRS.setFilter(new SearchFilter("^[A-L].*")); dumpRS(usersRS); } /** * Dump {@link ResultSet} * * @param rs * input{@link ResultSet} to dump * @throws SQLException * @throws Exception */ public static void dumpRS(ResultSet rs) throws SQLException { ResultSetMetaData rsmd = rs.getMetaData(); int cc = rsmd.getColumnCount(); while (rs.next()) { for (int i = 1; i <= cc; i++) { System.out.println(rsmd.getColumnLabel(i) + " = " + rs.getObject(i) + " "); } System.out.println(""); } } /** * Prepare data for test * * @param c * {@link Connection} will be used to prepare data * @throws SQLException */ private static void prepareData(Connection c) throws SQLException { c.createStatement().execute("create table USER (name varchar(256))"); PreparedStatement prepareStatement = c .prepareStatement("insert into USER (name) values (?)"); for (String name : NAMES) { prepareStatement.setString(1, name); prepareStatement.execute(); } } }
Output of the previous code:
ContainFilter.evaluate called Searching for pattern '^[A-L].*' in Bill Gates NAME = Bill Gates ContainFilter.evaluate called Searching for pattern '^[A-L].*' in Steve Jobs ContainFilter.evaluate called Searching for pattern '^[A-L].*' in Mark Zuckerberg ContainFilter.evaluate called Searching for pattern '^[A-L].*' in Alan Turing NAME = Alan Turing ContainFilter.evaluate called Searching for pattern '^[A-L].*' in Linus Torlvalds NAME = Linus Torlvalds
Resources
https://docs.oracle.com/javase/7/docs/api/javax/sql/rowset/FilteredRowSet.html
https://docs.oracle.com/javase/7/docs/api/javax/sql/rowset/Predicate.html
https://docs.oracle.com/javase/tutorial/jdbc/basics/filteredrowset.html