Filtering data

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

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 all orders for a customer, all customers outside the U.S., or all orders that were not shipped within two days. 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 components 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".

Filtering is sometimes confused with sorting and locating.

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 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. Create a new application by following "Retrieving data for the tutorials". This step enables you to connect to a database, read data from a table, and view and edit that data in a data-aware component.

  2. Click the Design tab.

  3. 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.

  4. 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;
  5. 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;
    

  6. 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 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 and 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 component tree.
    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 as an inner class. 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");
        	}
      }
      

  7. 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 component tree.
    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");
              }
           }
      
  8. Compile and run the application.

The running application looks like this:

FilterRows application

To test this application,

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

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