Filtering data

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.

Tutorial: Adding and removing filters

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:

  1. Create a new application, add a Database component, a QueryDataSet component (select * from employee), and a GridControl control (connected to the queryDataSet1) by following "Providing data". This step enables you to connect to a database, read data from a table, and view and edit that data in a data-aware control.

  2. Add two TextFieldControl components and a ButtonControl component from the JBCL tab. The TextFieldControl components enable you to enter a field and a value to filter on. The ButtonControl component executes the filtering mechanism.

  3. Define the name of the column to be filtered and its formatter. To do this, select the Frame file in the Structure pane, then select the Source tab. Add this import statement to the existing import statements:
    import borland.jbcl.model.*;
    import borland.jbcl.util.Variant;

  4. Add these variable definitions to the existing variable definitions in the class definition:
    Variant v = new Variant();
    String columnName = "Last_Name";
    String columnValue = "Young";
    VariantFormatter formatter;
    

  5. Specify the filter mechanism. You restrict the rows included in a view by adding a RowFilterListener and using it to define which rows should be shown. The default action in a RowFilterListener is to exclude the row. Your code should call the RowFilterResponse's add() method for every row that should be included in the view. Note that in this example we are checking to see if the columnName or columnValue fields are blank. If either is blank, all rows are added to the current view.

    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");
      }
    }
    

  6. Override the actionPerformed event for the ButtonControl to retrigger the actual filtering of data. To do this, select the Frame file in the Navigation pane, select the Design tab, select the ButtonControl, and click the Events tab on the Inspector. Select the actionPerformed event and double-click the value box for its event.

    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");
        }
      }
    }
    
  7. Compile and run the application.
To filter data, define the column you wish to filter (Last_Name as initially defined) in the first TextFieldControl, define the value you wish to filter for in the second TextFieldControl (Young as initially defined), and then press the ButtonControl. Leaving either the column name or the value blank removes any filtering and allows all values to be viewed.

Example: Filtering with a restrictive clause in a query

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,

  1. Select Frame in the Structure pane and then select the Design tab of the Content pane.

  2. Drop a Database component, a QueryDataSet component, and a GridControl component on the Component tree.

  3. Set the connection property for the Database component as follows (assuming your system is set up as in Installing and setting up):

  4. Select the query property for the data set. In the query custom property editor, set Database to database1. Enter the following text in the SQL Statement text box: SELECT * FROM employee WHERE JOB_COUNTRY = "Japan"

  5. Run the application by selecting Run|Run.

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.