Update: InputNumberSpinbox without Spin inside af:query

A user asked how to get rid of the spin buttons if the InputNumberSpinbox is used in an af:query component?

Whenever you have a number attribute in a VO and use it in a view criteria which you then use to show an af:query using this view criteria, the af:query uses an af:inputNumberSpinBox in the query panel to allow the user to enter values. The problem is, that you can’t control how the components rendered inside an af:query is rendered. There are no properties you can change which are available if you use the same component directly.

af:query with af:inputNumberSpinBox and spin buttons

Using the af:inputNumberSpinBox in the af:query has the same advantage as I mentioned in the original post.

And the same disadvantage too. In most cases, you don’t want or need the up/down buttons to select a number. Well, this can be done by adding the same style class we added to the af:numberInputSpinBox to the af:query component. The result can be seen in the below image

And we get the same behavior inside the af:query component too.

The reason this works is, that the af:query uses an af:inputNumberSpinBox in the query panel. The skin selector we used in the skin file works for the af:numberInputSpinBox too.

As you see, if you select the af:query in the skin file and hover over the spin button, it tells you which selector is used. This is the same selector we use in our skin file

I added another sample which I extended from the original one build with JDeveloper 12.2.1.1.0. This sample uses the HR DB schema and can be downloaded from GitHub BlogInputSpinBoxWithoutSpinV2

Jdev 12c: Implementing SQL IN Clause in an ADF ViewObject Query or ViewCriteria (Part 2)

In part one, showed how to implement a SQL IN clause in ADF. Now I show how to use this technique in a ViewCriteria or directly in a query of a view object.

We have to solve a couple of problems before we can really use the technique from part one in a ViewCriteria. As you know, when using a ViewCriteria, you select an operator which in turn is translated into SQL code. So, we have to find a way to create a new operator which will then be used to create the needed SQL code.

The technique to do this comes from an older post. Please look at Extending ViewCriteria to use SQL CONTAINS where I showed the basics on how to do this. The older post was designed for JDeveloper 11.1.2.1.0. Using the current JDeveloper version 12.2.1.3 give some ways for improvement of the earlier code.

The first improvement is that JDeveloper 12.2.1.3 allows us to introduce custom operators to view criteria. In the older sample, I had to use the description field of the ViewCriteria to pass information which SQL to generate. Now we can define an operator named ‘IN’ and use it in the ViewCriteria like any other default operator.

The next problem is how to generate the SQL shown in part one when the new custom operator ‘IN’ should be used. One of the big advantages of ADF is reusability. We use a base class which extends from ViewObjectImpl and use this new base class in the project.

The base class is named BaseViewObjectForSqlInClause were we implement the needed method

public String getCriteriaItemClause(ViewCriteriaItem aVCI)

which gets called for each part or item of a ViewCriteria. See the code of hte base class below.

Base Class

public class BaseViewObjectForSqlInClause extends ViewObjectImpl {
    private static ADFLogger _logger = ADFLogger.createADFLogger(BaseViewObjectForSqlInClause.class);

    // comma-separated list of custom operators. Each custom operator muast have a ',' at the end as delimeter!
    private static final String CUSTOM_OPERATORS = "IN,";

    public BaseViewObjectForSqlInClause(String string, ViewDefImpl viewDefImpl) {
        super(string, viewDefImpl);
    }

    public BaseViewObjectForSqlInClause() {
        super();
    }

    /**
     * Check if a given criteria item tries to use an 'IN' operator using a bind parameter (comma seperated list of strings).
     * Create special SQL clause for 'IN' operator
     * @param aVCI Criteria item
     * @return where clause part for the criteria item
     */
    @Override
    public String getCriteriaItemClause(ViewCriteriaItem aVCI) {
        // we only handle the SQL 'IN' operator
        String sqloperator = aVCI.getOperator();
        // add comma to operator as delimiter
        boolean customOp = CUSTOM_OPERATORS.indexOf(sqloperator.concat(",")) >= 0;
        customOp |= sqloperator.indexOf("NVL") >= 0;
        if (customOp) {
            ArrayList<ViewCriteriaItemValue> lArrayList = aVCI.getValues();
            if (lArrayList != null && !lArrayList.isEmpty()) {
                // check if the criteria item has bind parameters (only the first if of interest here as the IN clause onlyallows one parameter)
                ViewCriteriaItemValue itemValue = (ViewCriteriaItemValue) lArrayList.get(0);
                if (itemValue.getIsBindVar()) {
                    // get variable and check if null values should be ignored for bind parameters
                    Variable lBindVariable = itemValue.getBindVariable();
                    Object obj = ensureVariableManager().getVariableValue(lBindVariable.getName());
                    boolean b = aVCI.isGenerateIsNullClauseForBindVariables();
                    if (b && obj == null) {
                        // if null values for bind variables should be ignored, use the default getCriteriaItemClause
                        return super.getCriteriaItemClause(aVCI);
                    }

                    try {
                        // we only handle strings data types for bind variables
                        String val = (String) obj;
                    } catch (Exception e) {
                        // the bind variabel has the wrong type! Only Strings are allowed
                        _logger.warning("Bind variabel for SQL " + sqloperator +
                                        " clause is not of type String! -> No custom SQL clause created! (Class: " +
                                        obj.getClass() + ", Content: " + obj + ", Variable: " +
                                        lBindVariable.getName() + ", View: " + this.getName() + ")");
                        String s = ":" + lBindVariable.getName() + " = :" + lBindVariable.getName();
                        return s;
                    }

                    // only handle queries send to the db
                    if (aVCI.getViewCriteria()
                            .getRootViewCriteria()
                            .isCriteriaForQuery()) {
                        String sql_clause = null;
                        switch (sqloperator) {
                        case "IN":
                            sql_clause = createINClause(aVCI, lBindVariable);
                            break;
                        default:
                            _logger.severe("Unknown custom operator '" + sqloperator + "' found! -> do nothing!");
                            break;
                        }

                        return sql_clause;
                    } else {
                        // bind variable not set or
                        // for in memory we don't need to anything so just return '1=1'
                        return "1=1";
                    }
                }
            }
        }

        return super.getCriteriaItemClause(aVCI);
    }

    private String createINClause(ViewCriteriaItem aVCI, Variable lBindVariable) {
        // start build the sql 'IN' where clause (COLUMN is the name of the column, bindParam the name of the bind variable):
        // COLUMN IN (SELECT regexp_substr(:bindParam,'[^,]+',1,level) FROM dual CONNECT BY regexp_substr(:bindParam,'[^,]+',1,level) IS NOT NULL
        // get flagg to create an sql where clause which ignores the case of the bind parameter
        boolean upper = aVCI.isUpperColumns();
        String sql_in_clause = null;
        StringBuilder sql = new StringBuilder();
        if (upper) {
            sql.append("UPPER(");
        }
        sql.append(aVCI.getColumnNameForQuery());
        if (upper) {
            sql.append(")");
        }
        sql.append(" ").append(aVCI.getOperator());
        sql.append(" (select regexp_substr(");
        if (upper) {
            sql.append("UPPER(");
        }
        sql.append(":");
        sql.append(lBindVariable.getName());
        if (upper) {
            sql.append(")");
        }
        sql.append(",'[^,]+', 1, level) from dual connect by regexp_substr(");
        if (upper) {
            sql.append("UPPER(");
        }
        sql.append(":").append(lBindVariable.getName());
        if (upper) {
            sql.append(")");
        }
        sql.append(", '[^,]+', 1, level) is not null)");
        sql_in_clause = sql.toString();

        _logger.finest("generated SQL-IN clause: " + sql_in_clause);

        return sql_in_clause;
    }
}

Using Base Class in Project

To use the base class in all new created ViewObjects of the project, we change the models project properties

Now, whenever you create a new ViewObject, the new base class is used and the SQL IN operator can be used in the VOs view criteria.

You can change any existing ViewObject to use the BaseViewObjectForSqlInClause by changing the extends clause in the class definition by hand.

Creating a ViewCriteria Using the Custom IN Operator

All pieces are in place and using the IN operator is pretty easy. We start by creating a new ViewObject named EmployeesOfDepartmentsViewCriteria

Now we have a ViewObject based on an EntityObject for the Employees. We need to make one change. The DepartmentId is an Integer type attribute, the comma-separated list is of type String (containing numbers). This doesn’t match. We add another attribute to the ViewObject of type String which we calculate from the DepartmentId Integer attribute. We change the SQL query for this by selecting the ‘Query’ node first unselecting the checkbox ‘Calculate Department Query at Runtime (recommended)’, second select the checkbox ‘Write Custom SQL’ and third add the line ‘to_char(Employees.DEPARTMENT_ID) DEPARTMENT_ID_STR,’ to the query.

Once this new ViewObject has been created, we add a ViewCriteria to it

If you like, you can turn off the checkbox ‘Ignore Case’ as it is not needed. The numbers are always lower case.

Running the ApplicationModule in the Tester

At this stage, we can test run the application module in the Application Module Tester (see JDeveloper & ADF: Use the Application Module Tester (BC4J Tester) to Test all your BusinessLogic).

Click the binocular button to select the ViewCriteria we created and click ‘Find’

Which will open a dialog asking for the value of the bind variable

Clicking ‘OK’ will show the result as

Running the ViewCriteria on a Page

Finally, we can add the ViewCriteria to a page as af:query and test it there. I’ll spare the exact howto here and just show hte running application.

Or with different parameters and spaces

You can download the sample application from GitHub BlogSqlInClause.

The Sample was built using JDeveloper 12.2.1.3 (but it should work in all 12c versions) and uses the HR DB schema.

JDev 12c: Change Label depending on Data in Field

A question on OTN forum JDev & ADF caught my attention. A user ask how to change the label of a field in an af:query depending on data entered in another field of the af:query.

This is an interesting problem as it can be used in other use cases, e.g. in forms, too.

Use case

Before going into detail on how this is implemented, let’s look at the use case in detail. Starting with a normal af:query component showing a search form for locations

We want to change the label of the ‘State’ field depending on the selected Value of the ‘CountryId’ field. The page is simply created by dragging the named criteria ‘All Queryable Attributes’ onto the page as ‘Query with Table’.

To make the UI more interesting we use an af:selectOneChoice to select the country. Depending on the selected country we like to show different labels for the ‘State’ field. If we select the ‘United States of America’ as country, the label should show ‘US States’, if we select ‘Germany’ we want to see ‘Bundesland’ and for Switzerland we want to show ‘Kanton’. For the remaining countries we show ‘State’.

Here we see that the label changed to ‘Kanton’ for the country Switzerland. Selecting the USA will change the label to ‘US State’

Implementation

To implement this we only need to add some groovy script to the model project. To be precise we add groovy to the attribute label of the view which is used in the UI for the af:query.

Adding the groovy to the view will guarantee that the UI always shows the effect. In the sample (download instructions below) you’ll find a second page where the view is dropped as a af:form. Running this page you’ll see the same effect.

OK, let’s have a look at the groovy script

if (CountryId == 'US') {
  return 'US State';
} else if (CountryId == 'DE') {
  return 'Bundesland';
} else if (CountryId == 'CH') {
  return 'Kanton';
} else if (CountryId != null) {
  return CountryId + ' State';
} else {
  return 'State';
}

The script checks for specific countries and depending on which country is currently selected it return a special label. For country ‘DE’ it return ‘Bundesland’, for country ‘CH’ it returns ‘Kanton’. For other countries we return the country code we get from the country attribute and add ‘State’ to it. A special case we have to handle is if country is null or empty. In this case we return ‘State’.

Below we see that we add the script to the attributes ‘UI Hint’ tab as ‘Label’. This is only possible in 12c, so if you are using an earlier version, you have to use java code to get the same result.

This is all we have to do. The sample which is build using JDev 12.2.1.2.0 can be downloaded from BlogChangeQueryLabel. The sample uses the HR DB schema.

JDev 12.1.3 Reorder Fields in Query Panel Clarification

Aside

Reading the ‘What’s new in this release’ document, I found

Reorder Fields in Query. Added ability to reorder fields in Query search panel.

I thought that we now can change the order of the fields in the af:query component at design time, like in an af:gridLayout, and started searching the documentation.
Sadly it turned out that this is not a design time option, as I thought, but a run time option!
This is documented at ‘33.2 Creating Query Search Forms’. Below you see the images from a query in advanced mode with the reorder button (down right) and the reorder dialog.


To reorder the fields at design time you can only use the technique blogged by Luc Bors ADF 11g: Change attribute order in query component

JDeveloper ADF af:query Component: How to Toggle Display Modes

ADF af:query component comes with many functions and layout possibilities. This post describes how to toggle between the ‘basic’ and the ‘advanced’ display mode from java code.
Fist lets see the difference between ‘basic’ and ‘advanced’ mode.

af:query in 'basic' mode

af:query in ‘basic’ mode

and the same component switched to ‘advanced’ mode:

af:query in 'advanced' mode

af:query in ‘advanced’ mode

You see the difference is that the user can change the conjunction used for each attribute. There is a button available on the af:query component which you can click to toggle the mode. However there are use cases where you need to do this from inside a java bean. When you check the properties available for hte component you’ll notice, that you can change the position and the visibility of the button

Properties to Change the Toggle Button

Properties to Change the Toggle Button

So the user is able to change the two modes by clicking the button. The problem is that you can’t start in advanced mode. Only when you create your own ViewCriteria in the ViewObject you can switch to the ‘UI Hints’ tab and select how you like to start the query region:

Select Display Mode for ViewCriteria

Select Display Mode for ViewCriteria

So you either create your own ViewCriteria and select to start in ‘advanced’ or you need to do it from a java bean as described below. The sample application is build using JDeveloper 11.1.1.5.0 and uses the HR schema. The link to download the sample can be found at the end of the post.

Use Case: lets assume we want to show an af:query panel in advanced mode to only a specific user named ‘King’. All other users should only see the ‘basic’ af:query panel. For this we use an inputText on the left side of a af:panelSplitter which is used to input the user name. When the value of this inputText changes we check if the name is ‘King’ and switch the af:query to ‘advanced’ mode, otherwise we set it to ‘basic’.

Implementation:
To implement the use case we have to do two things. First we need an inputText to enter the user name. In reality this name should be get from a security context. Here it’s enough to store the value entered by the user to a binding attribute, set the inputText component to autoSubmit and implement a valuechangeListener where we put the logic to switch the mode of the af:query to ‘advanced’ or to ‘basic’.
We start with setting up a variable in the executable section of the pageDef:

Create a Variable in Executable Section of pageDef

Create a Variable in Executable Section of pageDef

Name the Variable

Name the Variable

Create an Attribute Binding for the Variable

Create an Attribute Binding for the Variable

Create Attribute Binding 2

Create Attribute Binding 2

Create Attribute Binding 3

Create Attribute Binding 3

Create Attribute Binding 4

Create Attribute Binding 4

Create Attribute Binding 5

Create Attribute Binding 5

Now after the attribute binding is in place we can set up the inputText for the name and set the value property to the created binding attribute, set the autoSubmit property and the valueChangeListener to a bean method

Set value Property

Set value Property

Set autoSubmit and valueChangeListener Properties

Set autoSubmit and valueChangeListener Properties

The valueChangeListener we bind to a bean in request scope (BTAFQBean) and implement the method ‘nameChangeListener’ as

    public void nameChangeListener(ValueChangeEvent valueChangeEvent) {
        // get the queryPanel which is bound to the bean property
        RichQuery qp = getQueryPanel();
        Object newValue = valueChangeEvent.getNewValue();
        String nameNew = (String)newValue;
        Object oldValue = valueChangeEvent.getOldValue();
        String nameOld = (String)oldValue;
        // if old value != King and new value == king  set advanced
        if ("King".equalsIgnoreCase(nameNew) &&
            !("King".equalsIgnoreCase(nameOld))) {
            // switch queryPanel to advanced mode
            qp.getValue().changeMode(QueryDescriptor.QueryMode.ADVANCED);
            //ppr the query panel
            AdfFacesContext.getCurrentInstance().addPartialTarget(qp);
        } 
        // if New value != King and old value == kng switch to basic
        else if ("King".equalsIgnoreCase(nameOld) &&
                   !("King".equalsIgnoreCase(nameNew))) {
            // switch queryPanel to basic mode
            qp.getValue().changeMode(QueryDescriptor.QueryMode.BASIC);
            //ppr the query panel
            AdfFacesContext.getCurrentInstance().addPartialTarget(qp);
        }
    }

The af:queryPanel is bound to a bean property in the BTAFQBean for convenience. This makes it easy to get the component and sent it a PPR from java code. The essential part of the code is located in line 12 and 20. The method to switch the mode is hidden in the query descriptor which you get as the value of the af:query component. gp.getValue() get us the QueryDescriptor which exposes the changeMode() method. The parameter is a constant, either QueryDescriptor.QueryMode.BASIC or QueryDescriptor.QueryMode.ADVANCED.

When you run the application and enter e.g. ‘peter’ into the inputText field and leave the field (or hit return) the query panel looks like

Query Panel in BASIC Mode

Query Panel in BASIC Mode

Now type ‘king’ into the name field and leave the field. As a result you see

Query Panel in ADVANCED Mode

Query Panel in ADVANCED Mode

You can download the sample workspace, build with JDev 11.1.2.1.0 and depending on hte HR db schema, from here: http://java.net/projects/smuenchadf/sources/samples/content/BlogToggleAFQuery_V1.zip

JDEV: af:query hide ‘Add Fields’ from query panel via custom skin

In my last post ‘JDEV: af:query hide some attributes from query panel but show them in the result table’ I showed how to hide some of the available attributes from the af:query panel.
Juan, an other blogger informed me that he too had shown how to do this (Control visibility of a query in adf). The blog not only showed how to hide attributes from the panel, but also showed how to hide the ‘Add Fields’ button you see in the advanced mode.

Query Panel Advanced Mode

Query Panel Advanced Mode


The method (or better trick) is to put a component in the footer facet of the af:query, which is stated in the docs. If you use an af:spacer (e.g. 1×1) for this, nothing is visible in the panel. The automatically filled in button ‘Add Fields’ is gone.
Well, I’m not just copying the other blog, but like to show a different approach using a custom skin to do it. The advantage using the skin approach is that you can clearly see (via hte name of the style class) why you don’t see the ‘Add Field’ button. Using the spaces the button is simply gone and you have to remember how you get rid of it (in a year).

First we need to add an ADF Skin to the project. For this we add a new ADF Skin file to the project and set its properties:

Skin creation

Skin creation


Skin Properties

Skin Properties

As I’m using JDev 11.1.2.1.0 the skin editor is build in. If you are trying this on an older version, you can use the stand alone version to create the skin file.
In the skin editor we look for the af:query component, which we want to change. In the component properties for the af:query we look for the ‘footer-facet-content-style’ pseudo element. For this element we set the display property to none. This will hide the whole facet in the UI. As this facet holds the ‘Add Fields’ button, the button is not visible in the UI.

Change Element

Change Element

Preview of af:query with skin applied

Preview of af:query with skin applied

If you leaf the skin in this state, the change works for all af:query components of hte project. As I like it to be changeable on a per component basis, I define my own style class for this change. For this change to the source mode of the skin file and add a style class name in front of the selector:

/**ADFFaces_Skin_File / DO NOT REMOVE**/
@namespace af "http://xmlns.oracle.com/adf/faces/rich";
@namespace dvt "http://xmlns.oracle.com/dss/adf/faces";

.AFQueryHideAddFields af|query::footer-facet-content-style
{
  display: none; 
}

Now you can use this ‘AFQueryHideAddFields’ style class on each af:wuery component where you want to hide the ‘Add Fields’ button .

...
                    <af:query id="qryId1" headerText="Search" disclosed="true"
                              value="#{bindings.ImplicitViewCriteriaQuery.queryDescriptor}"
                              model="#{bindings.ImplicitViewCriteriaQuery.queryModel}"
                              queryListener="#{bindings.ImplicitViewCriteriaQuery.processQuery}"
                              queryOperationListener="#{bindings.ImplicitViewCriteriaQuery.processQueryOperation}"
                              resultComponentId="::pc1:resId1" styleClass="AFQueryHideAddFields">
...

Apply StyleClass

Apply StyleClass

You can download the sample workspace, build with JDev 11.1.2.1.0 and depending on hte HR db schema, from here: BlogHideAttributesInQueryPanel_v2.zip
Please rename the file to ‘.zip’ after downloading it!

JDEV: af:query hide some attributes from query panel but show them in the result table

An interesting question came up today in the OTN JDev forum. The use case is to use an af:query component to qurey a db table, but make some of the attributes available in the table invisible in the query panel.
You can archive this using viewCriteria, but in this case you still can reach the other attributes in advanced mode.
The way to go is to make the attribute which you want to hidenot queryable in the view definition. For this we open the VO and select the attribute node.

Queryable Attribute in a VO

Queryable Attribute in a VO

Here we remove the check mark from the ‘Queryable’ checkbox like I did for the JobId attribute in the picture below

Not queryable attribute in a VO

Not queryable attribute in a VO

Now when we use this VO in a af:query component we see all queryable attributes but not the ones where we removed the check box. In the sample I removed the checkmark for JobId, ManagerId and DepartmentId in the EmployeesView. The resulting query panel which I build using the ‘All Queriable Attributes’ from the ‘Names Criteria’ section of the EmployeesView

Build Query Panel

Build Query Panel

looks like the picture below in the running application. As you can see JobId, ManagerId and DepartmentId are not part of the query panel but can be seen the result table.

Query Panel

Query Panel

In advanced mode you can’t add the missing attributes

Query Panel Advanced Mode

Query Panel Advanced Mode

You can remove the checkbox from the EO too, but this would mean that no VO build on this EO can query the attributes. If you only remove the checkbox in the VO you can build an other VO based on the same EO and make all attributes queriable.

You can download the sample workspace, build with JDev 11.1.2.1.0 and depending on hte HR db schema, from here: BlogCascadingTable.zip
Please rename the file to ‘.zip’ after downloading it!

JDev: How to reset a filter on an af:table

In my last blog entry “How to reset or undo a af:table sort” I showed how to clear a sort on a column of an af:table.

Chris Muir asked me to do a follow up showing how to do the same for a filter an an af:table. A short research about this question did not turn up anything. If somebody already has bloged about this, please drop me note and I’ll mention you for reference.

************
OK, just 5 minutes after first publishing I found Steve Muenchs sample #146 at http://blogs.oracle.com/smuenchadf/resource/examples. More to come?
************

You can download a sample workspace, which was set up using JDeveloper 11.1.2.1.0 and the HR schema as DB connection, using the link at the end of the blog.

The use case for the blog is

  1. A use has a filterable af:table on a page. At some point he fills in one or more filter criteria and executes the query.
  2. Now he wants to clear the filter criteria to show all rows again.

Here is the code for a table with filtering enabled. As you see the filter is implemented as a filterModel (line 06: filterModel=”#{bindings.ImplicitViewCriteriaQuery.queryDescriptor}”).

                        <af:table value="#{bindings.EmployeesView1.collectionModel}" var="row"
                                  rows="#{bindings.EmployeesView1.rangeSize}"
                                  emptyText="#{bindings.EmployeesView1.viewable ? 'No data to display.' : 'Access Denied.'}"
                                  fetchSize="#{bindings.EmployeesView1.rangeSize}"
                                  rowBandingInterval="0"
                                  filterModel="#{bindings.ImplicitViewCriteriaQuery.queryDescriptor}"
                                  queryListener="#{bindings.ImplicitViewCriteriaQuery.processQuery}"
                                  filterVisible="true" varStatus="vs"
                                  selectedRowKeys="#{bindings.EmployeesView1.collectionModel.selectedRow}"
                                  selectionListener="#{bindings.EmployeesView1.collectionModel.makeCurrent}"
                                  rowSelection="single" id="resId1" styleClass="AFStretchWidth"
                                  binding="#{ResetTableFilterBean.empTable}"
                                  columnStretching="multiple">
                            <af:column sortProperty="#{bindings.EmployeesView1.hints.EmployeeId.name}"
                                       filterable="true" sortable="true"
                                       headerText="#{bindings.EmployeesView1.hints.EmployeeId.label}"
                                       id="resId1c1">

From the javadoc

getFilterModel

public final java.lang.Object getFilterModel()

    Gets the model used for filtering of data in the table. This attribute must be bound to an instance of FilterableQueryDescriptor class.

we see that the filterModel needs to be casted to a FilterableQueryDescriptor. A look into the javadoc shows that the class has a method to get a map of all criteria entered into the filter fields of a table. Here’s the javadoc for the FilterableQueryDescriptor.getFilterCriteria() method:

getFilterCriteria

public abstract java.util.Map<java.lang.String,java.lang.Object> getFilterCriteria()

    Gets the filter criteria associated with the query descriptor. Filter Criteria are generally useful for filtering data in the table.

    Returns:
        Map<String, Object> containg the filterCriteria

Clearing this map clears the filter fields of the table. Finally we queue an query event to the table to refresh it. Here is the bean code:

public class ResetTableFilterBean
{
    private RichTable empTable;

    public ResetTableFilterBean()
    {
    }

    public void resetTableFilter(ActionEvent actionEvent)
    {
        FilterableQueryDescriptor queryDescriptor =
            (FilterableQueryDescriptor) getEmpTable().getFilterModel();
        if (queryDescriptor != null && queryDescriptor.getFilterCriteria() != null)
        {
            queryDescriptor.getFilterCriteria().clear();
            getEmpTable().queueEvent(new QueryEvent(getEmpTable(), queryDescriptor));
        }
    }

    public void setEmpTable(RichTable empTable)
    {
        this.empTable = empTable;
    }

    public RichTable getEmpTable()
    {
        return empTable;
    }
}

In the following picture we see a page holding the query panel with a table with filter enabled. A search for employees with last name starting with ‘K’ has been executed and the result has been filtered for last name starts with ‘Ki’.

Query Panel with Filterable Table

Query Panel with Filterable Table

After a click an the ‘Reset Table Filter’ button clears the filter and shows the result for the query only.

Filter reset

Filter reset

The sample workspace uses JDeveloper 11.1.2.1.0 but the bean code should work in older JDeveloper version 11.1.1.x too. The sample used the HR schema as DB connection. You can download the sample workspace from BlogResetTableFilter.zip
After downloading the file rename it to ‘BlogResetTableFilter.zip’!

The sample also contains an other page showing the same for a simple table without the query panel.

JDev: How to reset or undo a af:table sort

This entry describes how to reset or undo a sort a user has initiated by clicking on a header in an af:table. The use case is based on a question on OTN Jdeveloper forum.
A more detailed description of the case is as follows:

  1. A use has a sortable af:table on a page. At some point he clicks the column header to sort the data.
  2. Now he wants to get back to the unsorted list which he gets is when the table is first shown.

The first part is easily accomplished by setting the sortable property of the columns of the table to true:

<af:table value="#{bindings.EmployeesView1.collectionModel}" var="row"
                          rows="#{bindings.EmployeesView1.rangeSize}"
                          emptyText="#{bindings.EmployeesView1.viewable ? 'No data to display.' : 'Access Denied.'}"
                          fetchSize="#{bindings.EmployeesView1.rangeSize}" rowBandingInterval="0"
                          selectedRowKeys="#{bindings.EmployeesView1.collectionModel.selectedRow}"
                          selectionListener="#{bindings.EmployeesView1.collectionModel.makeCurrent}"
                          rowSelection="single" id="resId1" styleClass="AFStretchWidth"
                          displayRow="selected" binding="#{RTSBean.qtable}">
                    <af:column sortProperty="#{bindings.EmployeesView1.hints.EmployeeId.name}"
                               sortable="true"
                               headerText="#{bindings.EmployeesView1.hints.EmployeeId.label}"
                               id="resId1c1">
...

Part 2 of the case isn’t obvious as there is no ‘unsort’ or ‘reset sort’ button available at design time. To undo the sort you have to do two things:

  1. remove the sort criteria from the table (this will remove the arrows in the column header)
  2. remove the sort criteria from the underlying iterator of the table and execute the query again (this gets the data unsorted from the DB)

Now that we know what to do let’s implement it. You’ll find a link to a working workspace at the end of the article.

public class RTSBean
{
    private RichTable qtable;

    public RTSBean()
    {
    }


    public void resetSort(ActionEvent actionEvent)
    {
        // get the binding container
        BindingContainer bindings = BindingContext.getCurrent().getCurrentBindingsEntry();
        DCBindingContainer dcBindings = (DCBindingContainer) bindings;
        RichTable table = getQtable();
        table.queueEvent(new SortEvent(table, new ArrayList<SortCriterion>()));
        DCIteratorBinding iterBind = (DCIteratorBinding) dcBindings.get("EmployeesView1Iterator");
        Key currentRow = null;
        Row row = iterBind.getCurrentRow();
        if (row != null)
            currentRow = row.getKey();
        SortCriteria[] sc = new SortCriteria[0];
        iterBind.applySortCriteria(sc); // iterBind is the iterator the table uses
        iterBind.executeQuery();
        if (currentRow != null)
            iterBind.setCurrentRowWithKey(currentRow.toStringFormat(true));
    }

    public void setQtable(RichTable qtable)
    {
        this.qtable = qtable;
    }

    public RichTable getQtable()
    {
        return qtable;
    }
}

I’m using the method resetSort(ActionEvent actionEvent) to do the work. I bound the table to a bean property to make it easily accessible in the method. You can also search for the table component in the component tree (using the id of the table) if you like.
The method first queues a new sort event to the table with an empty list of sort criterion. This removes the sort arrows from the column header. Next it gets the iterator which is used by the table and saves its current row. I do this to re-select the row after I executed the query again. This way the user still sees the selected row after the sort is reset. An empty array of sort criteria is created and applied to the iterator removing all sort criteria currently applied. The query of the iterator gets executed and finally the selected row is set again (if it was set before).

This successfully resets a table to unsorted mode. This picture show the table after an initial search (without criteria entered in the name field):

Unsortet table after first load

Unsortet table after first load

Next I sorted the ‘salary’ column and selected the Row with employee_id 162:

Sorted table with row 162 selected

Sorted table with row 162 selected

The final picture shows the table after a click on the ‘Reset Sort’ button:

Table after 'Reset Sort'

Table after 'Reset Sort'


As you see the row with ‘EmployeeId’ 162 is still selected and scrolled into first position as the table is set to displayRow=”selected”.

The sample workspace uses JDeveloper 11.1.2.1.0 but the bean code should work in older JDeveloper version 11.1.1.x too. The sample used the HR schema as DB connection.
You can download the sample workspace from BlogResetTableSort.zip
After downloading the file rename it to ‘BlogResetTableSort.zip’!

Disclaimer and Acknowledgment: I remember an old blog post by Frank Nimphius showing this too. However, I couldn’t find it on the web and decided to show how to do it again.

Extending ViewCriteria to use SQL CONTAINS

Some time ago a customer asked how to use more sophisticated search operands in a ViewCriteria like Oracles CONTAINS or CATSEARCH functions.
CONTAINS and CATSEARCH are part of the Oracle DB 10g and 11g (Enterprise) and offer full text search capabilities (Oracle Text) for columns (even columns containing pdf or word documents in a blob) and external data. As the customer extensively uses full text search the question was how to integrate this functionality in existing and new to develop ADF rich faces applications. As most of the queries are done using view criteria I looked for a way to integrate the CONTAINS search as operand in the ViewCriteria wizard. A thread on OTN I opened for this issue gave some inside hints on how to try to implement this.

Steve Muenchs hint to add the operator directly in the XML definition of the ViewObject (VO) did not work out. The solution I show in this article is a variation of Jobineshs blog. I overwrite the getCriteriaItemClause(…) method to generate the desired SQL clause and add it to the rest of the query. One problem needed to be solved: how to trigger the use of the CONTAINS in the query. As I mentioned before, putting the CONTAINS as operand into the definition of the VO is to cumbersome as it has to be done in every VO where you want to use the CONTAINS search. In the end I used the bind variable to trigger the special treatment.

Each bind variable provides custom properties which I use as trigger. I use the DESCRIPTION property (you can use your own name) and add CONTAINS as value. The overwritten getCriteriaItemClause(…) method checks for each ViewCriteriaItem if its uses a bind variable with the DESCRIPTION set to CONTAINS. If this is the case I generate the SQL clause for the CONTAINS search.

OK, lets walk through the implementation. First I need to create the CONTAINS index which I want to use in the ViewCriteria. I like the index to be case insensitive, so I need to define a preference which is used to alter the index in this way. As a CONTAINS index doesn’t automatically synchronize I change this behavior too, so that I don’t need to do this by hand. For more info about this kind of index check the Oracle Text Reference.

--create a preference to make the index case insensitive
begin
ctx_ddl.drop_preference('bloglex');
end;
/
begin
ctx_ddl.create_preference('bloglex', 'BASIC_LEXER');
ctx_ddl.set_attribute ('bloglex', 'mixed_case', 'NO');
end;
/
-- create the index with sync on commit 
drop index employee_ln_idx;
create index employee_ln_idx on employees(LAST_NAME) indextype is ctxsys.context PARAMETERS('LEXER bloglex SYNC(ON COMMIT)');

A typical SQL query using this index look like

SELECT Employees.EMPLOYEE_ID,
  Employees.FIRST_NAME,
  Employees.LAST_NAME,
  Employees.EMAIL,
  Employees.PHONE_NUMBER
FROM EMPLOYEES Employees
WHERE ( ( ( (contains(Employees.LAST_NAME, 'ha%' ) >0) ) ) );

The syntax I need to generate for a CONTAINS SQL where clause looks like: contains(Employees.LAST_NAME, :bindLN ) >0
I defined a ViewCriteria “ContainsLastNameCriteria” for this

ViewCriteria ContainsLastNameCriteria

ViewCriteria ContainsLastNameCriteria


The definition of the bind variable look like
Bind Variable "bindContainsLastName"

Bind Variable "bindContainsLastName"


As you see I choose ‘Equals’ as operator. It does not really matter what I choose, as the query where clause I build will overwrite it anyway. Instead of the default name “DESCRIPTON” you can choose any other name you like, just overwrite the name field in the dialog.
Now using the technique mentioned in Jobineshs blog I overwrite the getCriteriaItemClause(…) method of the ViewObjectImpl class like:

    ADFLogger mLogger = ADFLogger.createADFLogger(EmployeesViewImpl.class);

    /**
     * Check if a given criteria item holds a bind variable with a property DESCRIPTION set to CONTAINS
     * If yes we build a special where clause part to execute a contains search using the the bind variable as
     * parameter.
     * @param aVCI Criteria item
     * @return where clause part for the criteria item
     */
    @Override
    public String getCriteriaItemClause(ViewCriteriaItem aVCI)
    {
        ArrayList<ViewCriteriaItemValue> lArrayList = aVCI.getValues();
        ViewCriteriaItemValue itemValue = (ViewCriteriaItemValue) lArrayList.get(0);
        if (itemValue.getIsBindVar())
        {
            Variable lBindVariable = itemValue.getBindVariable();
            // check for the special DESCRIPTION in the used bind variable
            Object obj2 = lBindVariable.getProperty("DESCRIPTION");
            String desc = (obj2 != null? obj2.toString(): "null");
            if ("CONTAINS".equalsIgnoreCase(desc))
            {
                if (aVCI.getViewCriteria().getRootViewCriteria().isCriteriaForQuery())
                {
                    // normal query execution
                    return getCONTAINSClauseForDatabaseUse(aVCI);
                }
                else
                {
                    // for in memory we don't need to anything so just return '1=1'
                    return "1=1";
                }
            }
            else
            {
                // no special treetment for all other CriteriaItems
                return super.getCriteriaItemClause(aVCI);
            }
        }
        return super.getCriteriaItemClause(aVCI);
    }

    protected String getCONTAINSClauseForDatabaseUse(ViewCriteriaItem aVCI)
    {
        ArrayList<ViewCriteriaItemValue> lArrayList = aVCI.getValues();
        ViewCriteriaItemValue itemValue = (ViewCriteriaItemValue) lArrayList.get(0);
        String whereCluase = "1=1";
        if (itemValue.getIsBindVar())
        {
            Variable lBindVariable = itemValue.getBindVariable();
            Object objVarVal = ensureVariableManager().getVariableValue(lBindVariable.getName());
            String varVal = null;
            if (objVarVal != null)
            {
                varVal = objVarVal.toString();
            }
            else
            {
                // if no parameter given we search for all
                varVal = "%";
            }

            String bindVarName = lBindVariable.getName();
            // can use entiy name only if VO is based on an EO
            String entityName =
                (this.getEntityDefCount() > 0? this.getEntityDef(0).getAliasName() + ".": "");
            whereCluase =
                    "(contains(" + entityName + aVCI.getColumnName() + ", :" + bindVarName + " ) >0) ";
        }
        mLogger.fine("Build clause: " + whereCluase);
        return whereCluase;
    }

That’s about it for the code, lets look at the running sample which you can download from here BlogContainsSearchCriteria.zip. Remove the ‘.doc’ suffix after downloading the sample work space for JDeveloper 11.1.2 as it contains a normal Zip archive.
After the start of the sample app you see a normal QueryPanel with table component which I dragged fro mthe DataContron onto a page fragment. Select ‘ContainsLastNameCriteria’ from the search select list and enter e.g. ‘ha%’ into the LastName input field.

First run using ContainsLastNameCriteria

First run using ContainsLastNameCriteria

The query returns all last names starting with ‘ha’, but also the last name ‘De Haan’ where the ‘ha’ starts the second part of the last name. This is normal expected behavior. This is the output in the log window:

[368] SELECT Employees.EMPLOYEE_ID,
  Employees.FIRST_NAME,
  Employees.LAST_NAME,
  Employees.EMAIL,
  Employees.PHONE_NUMBER,
  Employees.HIRE_DATE,
  Employees.JOB_ID,
  Employees.SALARY,
  Employees.COMMISSION_PCT,
  Employees.MANAGER_ID,
  Employees.DEPARTMENT_ID
FROM EMPLOYEES Employees
WHERE ( ( ( (contains(Employees.LAST_NAME, :bindContainsLastName ) >0) ) ) )
[369] Bind params FOR ViewObject: [de.hahn.blog.containscriteria.model.dataaccess.EmployeesView]BCSCAppModule.EmployeesView1
[370] Binding param "bindContainsLastName": Ha%

Here we see the generated query using the CONTAINS index in the where clause. You may argument that this same result could archived using the normal ‘contains’ operator’ available in the ViewCriteria wizard. The generated where clause in this case looks like

... WHERE ( ( (UPPER(Employees.LAST_NAME) LIKE UPPER('%' || :bindLastName || '%') ) ) )

The big difference is the usage of ‘UPPER’ and the ‘%’ in front and after the bind variable. The usage of ‘UPPER’ is the result of selecting ‘Ignore case’ in the view criteria editor. One disadvantage of using UPPER is, that if you have defined an index on the column (LAST_NAME in this sample), it can only be used if it was created with the UPPER function too. For small data volumes it’s OK to use a query like this without an index, but if you query big volume data it’s an absolute (time) killer.
Next the usage of ‘%’ before the bind variable is problematic too fro big volume data, as the DB needs to read each value to match it against the expression. The result also differs from the desired as names like ‘Kochhar’ are found too.

Normal 'contains' criteria - Wrong result

Normal 'contains' criteria - Wrong result


The very popular web side Ask Tom has plenty of threads discussing this.

The sample workspace contains this criteria and a mixed criteria to show that you can mix normal criteria and the special criteria in one criteria group. Feel free to play with these cafeterias too.

The technique shown here can be used for almost any other SQL where clause which is not part of the ViewCritera editor (e.g. you want to call a pl/sql function as part of the ViewCriteria).