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.

Advertisements

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

Using diferent VOs for Master Detail Navigation (the Declatative Way)

A user on the OTN forum asked a question how to do a master detail like navigation where the master VO is not equal to the detail VO and no accessors or link is available between the tow VOs.

A use case for this scenario is e.g. you have a read only table as master which holds an attribute which is the foreign key to an other table (the master table has a FK to the detail you like to change). In the sample I’m talking about in this blog I used the HR schema, the employees table as master and the the department as detail. I show how to use the employees as read only table, select an employee to edit the department the employee is assigned to.

Here is the data model of the sample:

Data Model

Data Model


As you can see there are no view links defined which could be used to navigate from the employee to the related department.

I’ll do all this the declarative way, so I don’t use a bean or other Java code. I use a bounded task flow and start with a query panel with the read only employees table. Each row shows the id of the employee, the name and the department id. I add a button to the department id of each row and use this to navigate to the departments edit page. Here you see the running app, the query panel which I used to select employees records and the button which I added to the department is column.

Start Screen

Start Screen

I used a button here because of an error in this version (11.1.2) of jdev which prevent the table from selection the current row when you just hit a link in a row. Frank Nimphius provided a workaround for this here:JDeveloper 11.1.2 : Command Link in Table Column Work Around. A click on the ‘Department’ button for ‘Jannette King’ will navigate to Department ’80’ which is editable

Select a Department from the Table and Edit Department

Select a Department from the Table and Edit Department

The work flow is implemented as shown below:

Work Flow

Work Flow

As you see the whole work is done in bounded task flow which first presents the query panel together with the resulting employees table (read only). The column ‘Department ID’ shows the button I use to navigate to the editable departments page. As there is no view link, it’s not enough to select the employees row to mark it as current row. I have to extract the department id from the selected row and use this to search for the department before showing the departments edit page.

I store the department id in a page flow scope variable named ‘#{pageFlowScope.depKey}’. If you like you can store the value of the department id elsewhere e.g. in the variables iterator of the page binding. To extract and store the value I use a af:setPropertyListener which allows to react on the action of the button and transfer the value to page flow scope variable. Here is the code of the department id column:

                            <af:column sortProperty="#{bindings.EmployeesView1.hints.DepartmentId.name}"
                                       sortable="true"
                                       headerText="#{bindings.EmployeesView1.hints.DepartmentId.label}"
                                       id="resId1c4" width="114">
                                <af:outputText value="#{row.DepartmentId}" id="ot5">
                                    <af:convertNumber groupingUsed="false"
                                                      pattern="#{bindings.EmployeesView1.hints.DepartmentId.format}"/>
                                </af:outputText>
                                <af:commandButton text="ShowDepartment" id="cb2" action="showDep">
                                    <af:setPropertyListener from="#{row.DepartmentId}"
                                                            to="#{pageFlowScope.depKey}"
                                                            type="action"/>
                                </af:commandButton>
                            </af:column>

The button action navigates to he method call ‘SetCurrentRowWithKeyValue’ in the bounded task flow. This method I dragged from the data control palette from the DepatermetnsView1 operation onto the bounded task flow definition page

SetCurrentRowWithKeyValue from DepartmentsView1

SetCurrentRowWithKeyValue from DepartmentsView1

The method searches the department using the the value stored in the page flow scope variable. The dialog below opens automatically when you drop the method on the task flow and lets me enter the key value to search for:

setCurrentRowWithKeyValue  Edit Action Binding

setCurrentRowWithKeyValue Edit Action Binding

Here is the pagedef file for the method call:

PageDef of setCurrentRowWithKeyValue Method

PageDef of setCurrentRowWithKeyValue Method


After the search the current row is set in the DepaertmensView1 and I can navigate to to the edit page. That’s about it.

You can download the sample work space from here Sample Workspace blogmasterdetaildeclarative_v2-zip. You have to rename the file to ‘.zip’ after download!

Pimp up an af:query to show the result table in an af:panelCollection

A user on the JDev Forum asked an interesting question on how to show the result of an af:query (e.g. dropped as ‘Query Panel with Table’).
A quick check showed that dropping a named criteria as ‘Query Panel with Table’ produces af:panelGroupLayout containing a af:panelHeader for the af:query component and an af:table for the result of the query.
Here is a sample of a drop as ‘Query Panel with Table’ (some details are omitted to save space):

                    <af:panelGroupLayout layout="vertical" id="pgl1">
                        <af:panelHeader text="Employees" id="ph1">
                            <af:query id="qryId1" headerText="Search" disclosed="true"...
                                      resultComponentId="::resId1"/>
                        </af:panelHeader>
                        <af:table id="resId1" value="#{bindings.EmployeesView1.collectionModel}" var="row"
                                  rows="#{bindings.EmployeesView1.rangeSize}" ...>
                            <af:column ...>
                            </af:column>
                            <af:column ...>
                            </af:column>
                            ...
                        </af:table>
                    </af:panelGroupLayout>

The user asked for functionality like hiding columns or detachment of the table to get a full screen mode. In short he wanted to use the functions of a af:panelCollection together with the automatic setup of af:query and the result table.

This can easily be done by surrounding the af:table component by a af:panelCollection and rewiring the ‘resultComponentId’ property of the af:query, as the af:panelCollection is a naming container.
In JDev select the table with a right click and select ‘Surround with…’

Surround Table with panel collection

and search for the ‘Panel Collection’

Select 'Panel Collection'

and click ‘OK’ to finish this step.
Now we need to change the ‘resultComponentId’ property of the af:query component to account for new the naming container which is added through the af:panelCollection component.
Select the af:query in the design view or in the structure window and open the property inspectors common tab

Change af:query
As you can see JDev shows an error for the ‘resultComponentId’ property as the target has changes its naming container. To change it click the small arrow down at the right hand side of the property, select ‘Edit…’. In the next Dialog look for the af:table component inside the af:panelCollection and select it. Click ‘OK’ to finish the change.
the resulting code looks like (some details are omitted to save space):

                    <af:panelGroupLayout layout="vertical" id="pgl1">
                        <af:panelHeader text="Employees" id="ph1">
                            <af:query id="qryId1" headerText="Search" disclosed="true"
                                      ...
                                      resultComponentId="::pc1:resId1"/>
                        </af:panelHeader>
                        <af:panelCollection id="pc1">
                            <f:facet name="menus"/>
                            <f:facet name="toolbar"/>
                            <f:facet name="statusbar"/>
                            <af:table value="#{bindings.EmployeesView1.collectionModel}" var="row"
                                      ...
                                      rowSelection="single" id="resId1">
                                <af:column sortProperty="#{bindings.EmployeesView1.hints.EmployeeId.name}"
                                           ...
                                </af:column>
                                ...
                                <af:column sortProperty="#{bindings.EmployeesView1.hints.PhoneNumber.name}"
                                ...
                                </af:column>
                            </af:table>
                        </af:panelCollection>
                    </af:panelGroupLayout>

When you run the code you are able to detach the table and hide columns

Resulting page