Filtering 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 JdbTextField that lets the user specify the column to filter. Then we create another JdbTextField 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 JButton 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/DataExpress/FilterRows subdirectory of your JBuilder installation.

To create this application:

  1. Select File|Close All, and File|Close Project from the menu.
  2. Select File|New from the menu. Double-click the Application icon.
  3. Enter a name and location for the project in the Project Wizard. Click Finish.
  4. Specify the package name and class name in the Application Wizard. Click Finish.

  5. Select the Design tab to activate the UI designer.

  6. Click the Database component on the Data Express tab of the component palette, then click in the Structure pane to add the component to the application.

    Open the connection property editor for the Database component by selecting, then double-clicking the connection property ellipsis in the Inspector. Set the connection properties to the JDataStore sample employee table as follows. The Connection URL points to a specific installation location. If you have installed JBuilder to a different directory, point to the correct location for your installation.
    Property name Value
    Driver com.borland.datastore.jdbc.DataStoreDriver
    URL jdbc:borland:dslocal:/usr/local/jbuilder/samples/JDataStore/datastores/employee.jds (The employee.jds database is located under the samples directory of your JBuilder installation, which may be different on your system. You can use the Browse button to browse to this fileto reduce the chance of making a typing error.)
    Username Enter your name
    Password none required

    The connection dialog includes a Test Connection button. Click this button to check that the connection properties have been correctly set. Results of the connection attempt are displayed in the status area. When the connection is successful, click OK.

  7. Add a QueryDataSet component to the designer by clicking on the QueryDataSet component on the Data Express tab and then clicking in the Structure pane.

    Select the query property of the QueryDataSet component in the Inspector, double-click its ellipsis to open the QueryDescriptor dialog, and set the following properties:
    Property name Value
    Database database1
    SQL Statement SELECT * FROM EMPLOYEE

    Click Test Query to ensure that the query is runnable. When the status area indicates Success, click OK to close the dialog.

  8. Select a DBDisposeMonitor component from the More dbSwing tab. Click in the Structure pane to add it to the application. The DBDisposeMonitor will close the JDataStore when the window is closed.

  9. Set the DBDisposeMonitor's dataAwareComponentContainer property to this.

To view the data in your application we need to add some UI components and bind them to the data set. To do this,

  1. Select contentPane(BorderLayout) in the Structure pane. In the Inspector, set its layout property to null.

  2. Select a JdbNavToolBar component from the dbSwing tab. Drop the component in the area slightly above the panel in the Design frame. JdbNavToolBar automatically attaches itself to whichever DataSet has focus, so you do not need to set its dataSet property.

    The JdbNavToolBar will enable you to move quickly through the data set when the application is running, as well as provide a default mechanism for saving changes back to your data source.

  3. Click on the JdbStatusLabel component on the dbSwing tab of the component palette. Drop the component in the area slightly below the panel in the Design pane. JdbStatusLabel automatically attaches itself to whichever DataSet has focus, so you do not need to set its dataSet property.

    Among other information, the status label displays information about the current record or current operation.

  4. Add a TableScrollPane component from the dbSwing tab to the designer. Click and drag the component to the appropriate size for this application.

    Scrolling behavior is not available by default in any Swing component or dbSwing extension, so, to get scrolling behavior, we add the scrollable Swing or dbSwing components to a JScrollPane or a TableScrollPane. TableScrollPane provides special capabilities to JdbTable over JScrollPane. See the dbSwing documentation for more information.

  5. Drop a JdbTable component from the dbSwing tab into the TableScrollPane component. Set its dataSet property to queryDataSet1.

    You'll notice that the designer displays live data at this point.

  6. Add two JdbTextField components from the dbSwing tab and a JButton component from the Swing tab. The JdbTextField components enable you to enter a field and a value to filter on. The JButton component executes the filtering mechanism.

  7. Define the name of the column to be filtered and its formatter. To do this, select the Source tab and add this import statement to the existing import statements:
    import com.borland.dx.text.VariantFormatter;

  8. 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;
    

  9. 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,

    1. Select the Design tab.
    2. Select the queryDataSet1 in the structure pane.
    3. Select the Events tab of the Inspector.
    4. Select the filterRow event.
    5. Double-click the filterRow 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.

    6. Add the filtering code to the queryDataSet1_filterRow event. You can copy the code from the online help by selecting the code and pressing Ctrl+C or selecting Edit|Copy from the Help Viewer menu.

      void queryDataSet1_filterRow(ReadRow row, RowFilterResponse     response) {
        try {
          if (formatter == null || columnName == null ||
              columnValue == null || columnName.length() == 0 ||
              columnValue.length() == 0)
            // user set field(s) are blank, so add all rows
            response.add();
          else {
            row.getVariant(columnName, v);
            // fetches row's value of column
            // formats this to a string
            String s = formatter.format(v);
            // true means show this row
            if (columnValue.equals(s))
              response.add();
            else
              response.ignore();
          }
        catch (Exception e) {
          System.err.println("Filter example failed");
        }
      }
      

  10. Override the actionPerformed event for the JButton to retrigger the actual filtering of data. To do this,
    1. Select the Design tab.
    2. Select the JButton in the structure pane.
    3. Click the Events tab on the Inspector.
    4. Select the actionPerformed event, and double-click the value box for its event.

      The Source tab displays the stub for the jButton1_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.

    5. Add this code to the generated stub.
      void jButton1_actionPerformed(ActionEvent e) {
        try {
          // Get new values for variables that the filter uses.
          // Then force the data set to be refiltered.
          columnName = jdbTextField1.getText();
          columnValue = jdbTextField2.getText();
          Column column = queryDataSet1.getColumn(columnName);
          formatter = column.getFormatter();
      
          // Trigger a recalc of the filters
          queryDataSet1.refilter();
      
          // The table should now repaint only those rows matching these criteria
        }
        catch (Exception ex) {
          System.err.println("Filter example failed");
        }
      }
      
  11. Compile and run the application.
To test this application,

  1. Enter the name of the column you wish to filter (for example, Last_Name) in the first JdbTextField.
  2. Enter the value you wish to filter for in the second JdbTextField (for example, Young).
  3. Click the JButton.

    Leaving either the column name or the value blank removes any filtering and allows all values to be viewed.