Understanding the checkbox component to display non boolean value

Some database such as Apache Derby does not support boolean type. According to JDBC documentation (see Table 8.7-JDBC Types Mapped to Database-specific SQL Types), the JDBC type BIT is mapped to the Java type boolean. If you look at the supported data types for derby, BIT (boolean) is not listed. In such case SMALLINT (1 or 0) is used to represent boolean type. However, SMALLINT does not map directly to boolean but to the Java type short. If the user wants to bind this column (SMALLINT) of the database table to a checkbox component, the actual status of the columns will not be reflected in the displayed table component. i.e column row with 1's will not be displayed as selected checkbox. This tip explains how to work around this and also explain about how checkbox component can be used to display any type of column. You can download this tip project from here.

First we need to understand the basic checkbox component. The bundled basic checkbox component is a versatile component. It can handle more than just boolean value. It can behave as

A boolean control
An object value holder, which can be other than a boolean type (behaves as a non boolean control)

Checkbox as a boolean control

If the database column is of type boolean, then this is a non brainer. Bind the checkbox to the database column directly and the checkbox would display the status as checked for the value true and unchecked for the value false. However, this is problematic if you are using database that does not support boolean type and you are using a SMALLINT to represent the true (1) and false (0) state. In this case you need to use the following work around.

Assume in the bundled database, the table "DataSources -> Travel -> Person" has a column "FrequestFlyer" which is of type SMALLINT (derby database connection). If the person is a frequent flyer, then the value of the column is 1, else 0. We want to display this column as a checkbox. In order to achieve this follow the below steps.

Create a project (call it NonBooleanCheckbox).
Add a Table component from the Palette.
Assume you have already connected to the derby database, drag and drop the "person" table on to the table component.
Right clcik on the table component and bring up the table layout dialog.
Selected the FrequentFlyer column and change its component to checkbox
(Note: if this column is of type boolean, checkbox will be automatically selected for you)
Select the source Page1.java from the project node and add a property of type boolean (call it frequentFlyer). Following code will be generated.

    public boolean isFrequentFlyer() {
        Object value = getValue("#{currentRow.value['PERSON.FREQUENTFLYER']}");
        if(value != null){
            Integer freqFlyer = (Integer) value;
            return freqFlyer.intValue()==1? true : false ;
        }
        return false;
    }
    public void setFrequentFlyer(boolean frequentFlyer) {
         if(frequentFlyer){
           setValue("#{currentRow.value['PERSON.FREQUENTFLYER']}", new Integer(1));
        }else{
           setValue("#{currentRow.value['PERSON.FREQUENTFLYER']}", new Integer(0));
        };
    }

Select the checkbox (table->tableColumn4->checkbox1) and click on the [...] of the selected property to bring up the custom property editor. In the dialog select Page1 -> frequentFlyer and click ok. Now the checkbox is bound to the frequemtFlyer property.
Deploy the application. You should see in the frequent flyer column, the rows with value "1" is selected and with "0" are unselected. (See Picture below)

Checkbox as a non boolean control

The checkbox component has two attributes selected and selectedValue. When used as a boolean control the selectedValue attribute is not used. The value of the selected attribute is set either as true or false. When used as a non boolean control both selected and selectedValue are used. When the value of the selected property is equal to the value of the selectedValue property, the checkbox is in a selected state and a checkmark is displayed.

Let us see an example. Assume you want to display a selected checkbox if the job title of the person is VP. Follow the below steps.

Add another Table component from the Palette
Drag and drop the "person" table on to the table component. Select to use the same CachedRowset when asked.
Right clcik on the table component and bring up the table layout dialog.
Selected the JobTitle column and change its component to checkbox
Select the selectedValue property of the checkbox in the propertysheet and type the value as "VP"
Note: If the column type is not a string but an integer then you need to set the value in the Java source as

    public void prerender() {
        checkbox2.setSelectedValue(new Integer(20));
    }

Deploy the application. You should see in the job title column, the rows with value "VP" is selected and rest are unselected. (See Picture below)

What happens when the user click on the checkbox to select it. In this case the value of the selected property is assigned the value of the selectedValue property. However, if you deselect the checkbox the selected property is set to null and if you update, you might loose your original values in the database. So in this scenario, my recommendation is to use a read only (disabled) checkbox, just to display the state. Use the method I discussed in the section "Checkbox as a boolean control", if you need to update the non boolean columns.

Dr. Winston Prakash Ph.D.

Personal Website

Understanding the checkbox component to display non boolean value