In JBuilder, data is extracted from a server into a data set. Filtering temporarily hides rows in a data set, letting you select, view, and work with a subset of rows in a data set. For example, you may be interested in viewing account information by customer or in looking up a phone number by last name. Instead of running a new query each time your criteria change, you can use a filter to show a new view.
In JBuilder, you provide filter code that the data set calls via an event for each row of data to determine whether or not to include each row in the current view. Each time your method is called, it should examine the row passed in, and then indicate whether the row should be included in the view or not. It indicates this by calling add() or ignore() methods of a passed-in RowFilterResponse object. You hook this code up to the filterRow event of a data set using the Events page of the Inspector. When you open the data set, or let it be opened implicitly by running a frame with a control bound to the data set, the filter will be implemented. In this example, we use UI controls to let the user request a new filter on the fly.
A filter on a data set is a mechanism for restricting which rows in the data set are visible. The underlying data set is not changed, only the current view of the data is changed and this view is transient. An application can change which records are in the current view "on the fly", in response to a request from the user (such as is shown in the following example), or according to the application's logic (for example, displaying all rows to be deleted prior to saving changes to confirm or cancel the operation). When you work with a filtered view of the data and post an edit that is not within the filter specifications, the row disappears from the view, but is still in the data set.
You can work with multiple views of the same data set at the same time, using a DataSetView. For more information on working with multiple views of the same data set, see Presenting an alternate view of the data. You can change the order of a view by setting the DataSet's sort property. For more information on sorting data, see Sorting data.
This tutorial shows how to use a data set's RowFilterListener to view only rows that meet the filter criteria. In this example, we create a TextFieldControl that lets the user specify the column to filter. Then we create another TextFieldControl that lets the user specify the value that must be in that column in order for the record to be displayed in the view. We add a ButtonControl to let the user determine when to apply the filter criteria and show only those rows whose specified column contains exactly the specified value.
In this tutorial, we use a QueryDataSet component connected to a Database component to fetch data, but filtering can be done on any DataSet component.
The finished example is available as a completed project in the samples/borland/samples/tutorial/dataset/FilterRows directory of your JBuilder installation under the project name FilterRows.jpr.
To create this application:
import borland.jbcl.model.*; import borland.jbcl.util.Variant;
Variant v = new Variant(); String columnName = "Last_Name"; String columnValue = "Young"; VariantFormatter formatter;
To create the RowFilterListener as an event adapter using the visual design tools, select the Frame file in the Structure pane, then select the Design tab. Select the queryDataSet1, select the Events tab of the Inspector, select the filterRow event, and double-click the value box. A RowFilterListener is automatically generated at the end of your file. It calls a new method in your class, called queryDataSet1_filterRow method. Add the filtering code to this event. You can copy the code from the online help by selecting the code and clicking the Copy button (Ctrl+C will not copy the contents of the Help Viewer).
void queryDataSet1_filterRow(ReadRow readRow, RowFilterResponse rowFilterResponse) { try { if (formatter == null || columnName == null || columnValue == null || columnName.length() == 0 || columnValue.length() == 0) // user set field(s) are blank, so add all rows rowFilterResponse.add(); else { readRow.getVariant(columnName, v); // fetches row's value of column String s = formatter.format(v); // formats this to a string // true means show this row if (columnValue.equals(s)) rowFilterResponse.add(); else rowFilterResponse.ignore(); } } catch (Exception e) { System.err.println("Filter example failed"); } }
The Source tab displays the stub for the buttonControl1_actionPerformed method. The following code uses the adapter class to do the actual filtering of data by detaching and re-attaching the rowFilterListener event adapter that was generated in the previous step.
void buttonControl1_actionPerformed(ActionEvent e) { try { // Get new values for variables that the filter uses. // Then force the data set to be refiltered. columnName = textFieldControl1.getText(); columnValue = textFieldControl2.getText(); Column column = queryDataSet1.getColumn(columnName); formatter = column.getFormatter(); // Trigger a recalc of the filters queryDataSet1.refilter(); // The grid should now repaint only those rows matching these criteria } catch (Exception ex) { System.err.println("Filter example failed"); } } }
In Tutorial: Adding and removing filters, data was extracted from a server to a data set and additional filtering was applied to the data in the data set. You can also use a filter to restrict the data being fetched from the server into the data set. To do this, you can use a WHERE clause in your SQL statement. In this example, we view all employees that reside in Japan,
This example could be modified to code the query with a parameter variable instead of a constant. For more information on using parameterized queries, see Using parameterized queries to obtain data from your database.