JDev 11.1.1.7.0: Table Pagination: Beware of the Layout Container

As you might have noticed, JDeveloper 11.1.1.7.0 brought the pagination feature for tables back. Looks a bit different than the old style, but is available again.
When I tested the paging of table I stumbled upon some interesting stuff. Luc Bors (ADF 11.1.1.7 : The return of the paging table (… and more ….)) and Andrejus Baranoski (ADF 11g PS6 – ADF 10g Table Pagination Feature is Back Finally) posted samples using the new pagination feature. However, when I tried it out at first, the pagination did not show up.

First Try: No Pagination

First Try: No Pagination

I checked the settings

  • scrollPolicy=’page’ (yes, this is a new attribute for the table component)
  • autoHeightRows=’0′

But they were set correct. Then I found the message

falling back to scrolling mode since we need parent component to flow and authHeightRows=0

in my log window and things cleared up.

Log Messages

Log Messages

As Luc wrote in his article, you have to put the table in a layout container in flow mode. This I did not (I read it but did not pay attention at first). To say it again, to make pagination work, the table has to be in a layout container in flow mode. This can e.g. a panelGroupLayout or a showDetailHeader.

If you look into the tag doc of the af:table component, the section

Geometry Management

  • This component can be stretched by a parent layout component that stretches its children, e.g. panelStretchLayout.
  • When stretching this component, the only valid setting for autoHeightRows is “-1” (a value of 0 will be treated as -1 when stretched).
  • When NOT stretched, autoHeightRows=”0″ can be used to size the height to the fetch size, which is similar to dimensionsFrom=”children”. Please refer to ‘autoHeightRows’ attribute for more information.
  • This component does not stretch its children.

If the oracle.adf.view.rich.geometry.DEFAULT_DIMENSIONS context-param is set to “auto” in the project’s web.xml, and the autoHeightRows value is set to 0, or is not set, the AFStretchWidth style class will be rendered for this component.

It’s not described clearly in the doc, but as you have to set autoHeightRows=0 to make pagination work, you can’t put a table in paging mode in a layout container in stretch mode. After changing the page, or better adding a new page with a layout container in scroll mode (here a panelGroupLayout) the pagination showed up:

Table with Pagination

Table with Pagination

You can download the sample from ADF EMG Samples page. The sample uses JDeveloper 11.1.1.7.0 and the HR DB schema.

Advertisements

JDeveloper 11.1.1.6.0: Escape QBE Operators in Filterable Tables

You may not have noticed that some words like ‘and’ and ‘or’ do have a special meaning if you use them in a filterable af:table. If a column of a db table contains some text which you like to filter via the Query by Example (QBE) feature of the af:table, you’ll notice that you can’t filter for  ‘and’ and ‘or’. These words are used as SQL operands for character columns,  like  ‘>’ or ‘<‘. There is no property to escape these words do that they are treated as normal words. For a full list of QBE operands check the Table 27-2 Query-by-Example Search Criteria Operators.

In this post we learn how to implement such an escape mechanism. The sample work space, which used the HR DB schema, can be down loaded using the link provided at the end of the post.

The solution for the problem is implemented on the vo the table is based on. Surprisingly no change is needed in the view controller. We only need to overwrite one method on the ViewObjectImpl which is used to generate the where clause part out of the filter criteria.

Before we go into the details let’s look at the running application, the problem, and it’s solution.

Running Test Application

Running Test Application

To show the problem without the need to change to much in the HR db we use the FirstName and the LastName column of the employees table. Here we change one or more entries do that they contain the word ‘and’ and ‘or’. In the image the changed lines are EmployeeId 202 and 203.

Filter for 'Pat and John'

Filter for ‘Pat and John’

As you see in the image above you get no row if you enter e.g. ‘Pat and John’ into the filter column of the first name. The reason is that the QBE feature build two where clause parts out of the one filter value as down in the output below.

FINE: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: Employees.FIRST_NAME value: Pat%
INFO: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: Employees.FIRST_NAME value: Pat%
FINE: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: Employees.FIRST_NAME value: John%
INFO: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: Employees.FIRST_NAME value: John%
FINE: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: FirstName value: Pat%
INFO: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: FirstName value: Pat%
FINE: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: FirstName value: John%
INFO: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: FirstName value: John%

As mentioned before this is a feature and not a bug. However, the are use cases where we need to filter for the words which are treated as operands.
We use a trick to escape the operands. We surround the operands by ‘_’. Then they are not treated as special words any more but as normal text.

Filter for 'Pat_and_ John'

Filter for ‘Pat_and_ John’

To make it work we have to remove the ‘_’ in the ViewObject before the query is executed. The output from the method now looks like

 
FINE: 17.02.2013 16:07:22 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 11 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: Employees.FIRST_NAME value: Pat _and_ John
INFO: 17.02.2013 16:07:22 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 11 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: Employees.FIRST_NAME value: Pat and John
FINE: 17.02.2013 16:07:22 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 11 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: FirstName value: Pat _and_ John
INFO: 17.02.2013 16:07:22 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 11 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: FirstName value: Pat and John

For this we overwrite the ‘public String getCriteriaItemClause(ViewCriteriaItem vci)’ method in the ViewObjectImpl.

 
    /**
     * Build the where clause for the criteria item 
     * @param vci
     * @return null if we use hte default where clause, the where clause part if we want to change the default.
     */
    @Override
    public String getCriteriaItemClause(ViewCriteriaItem vci) {
        if (vci != null) {
            AttributeDef attrDef = vci.getAttributeDef();
            String attrName = attrDef.getName();
            // for string attributes check for the '_xxx_' sequence and remove the '_' around hte operator
            if ("java.lang.String".equals(attrDef.getJavaType().getName()) && vci.getValue() != null) {
                String colName = vci.getViewCriteria().isCriteriaForQuery() ? vci.getColumnNameForQuery() : attrName;
                String operator = vci.getOperator();
                String filterVal = (String)vci.getValue();
                _logger.fine("Before matching - Column: " + colName + " value: " + filterVal);
                int i = filterVal.indexOf("_");
                while (i > -1) {
                    int k = -1;
                    String subFilter = null;

                    k = filterVal.indexOf("_", i + 1);
                    if (k > -1) {
                        filterVal = filterVal.replaceFirst("_", "");
                        filterVal = filterVal.replaceFirst("_", "");
                    }
                    i = k;
                }

                _logger.info("Column: " + colName + " value: " + filterVal);
                // handling for STARTSWITH operator
                if (JboCompOper.OPER_STARTS_WITH.equals(operator)) {
                    int columnsValue = vci.getUpperColumnsValue();
                    // check if hte parameters are uppercase
                    String sql = "(%s like '%s%%')";
                    if (columnsValue != -1)
                        sql = "(UPPER(%s) like UPPER('%s%%'))";
                    String clause = String.format(sql, colName, filterVal);
                    return clause;
                }
                // handle other operators here
            }
        }
        return super.getCriteriaItemClause(vci);
    }

As the method is called for every criteria item we have to check if the current criteria is of type string as only string criteria react on the operands.
Then we need to create the where clause part for the criteria our self. The method implements this only for the ‘startswith’ operand!
If you need this for other operators too, you have to add the code to build the where clause at the commented point.

The workspace can be loaded from ADF EMG Samples Project. The sample uses the HR db schema and was developed using JDeveloper 11.1.1.6.0

JDeveloper: Using Static ViewObjects for Lookup Data used by e.g. LOV

This blog shows how to set up a static ViewObject in a model project and use this static data as lookup data in an other ViewObject as LOV. Static lookup data can and should be put into a shared application module, as the data seldom change during the live of the application using the static data. This we’ll leave for another blog. Here we create a model project and create a simple static ViewObject which we then use in another as choice list.
The sample was build using JDeveloper 11.1.1.6.0 and uses the HR schema. To get the workspace use the link provided at the end of this blog.

Use case:
We need some static data as lookup for an other attribute which resides in a view object. In this sample we use the COUNTIRES table from the HR schema which has an foreign key RegionId to the REGIONS table from the HR schema. Instead of using the REGIONS data directly, we setup a static view Object which holds the data to be shown for the foreign key in the UI.
To show the difference, we also setup the lookup data using the REGIONS table data.

Implementation:

We start with setting up the workspace. We use the normal ‘Fusion Web Application’ template. The template creates two projects, one model and one view controller project. once the names for the projects are entered, we start with the model project. We select the model project and select ‘Create Business Components from Table’. After copying the HR Connection to the project, we select the Countries and the Regions as entities

Entity Objects: Countries and Regions

Entity Objects: Countries and Regions

Next we select the two ViewObjects based on the EntityObjects build in the previous step

Updatable ViewObjects

Updatable ViewObjects

as we don’t need read only ViewObjects for this blog we skip this step and finally set a name for the application module

Application Module

Application Module

Next we create a new ViewObject which we define as ‘Rows populated at design time (Static List)’ which we use as lookup table.

Create Static VO

Create Static VO

Define as Static List

Define as Static List

Define the attributes and their data types. In this case we use an id (Number) and a String type as region name.

Attribute Id

Attribute Id

Attribute RegionName

Attribute RegionName

Next after the attributes for the static view are defined we add the values. Here you can enter as many rows as you like. There also can be more then two attributes. The sample only needs hte two attributes.

Empty Static List

Empty Static List

Filled Static List

Filled Static List

For the sample we define a second view object based on the COUNTRIES table of the HR schema. We use the two identical VO to show how to setup the LOV for the RegionId once using the entity based VO RegionView and once to setup the LOV using the static VO created before.

Create second VO to demonstrate setup of LOV using Static VO

Create second VO to demonstrate setup of LOV using Static VO

VO is based on Countries EO

VO is based on Countries EO

Use all Attributes

Use all Attributes

After the attributes are defiend we setup the LOV for the RegionId. For this we select the RegionId attribute and open the ‘List of Vaule:’ for the RegionId

Define the List Data Source for the LOV

Define the List Data Source for the LOV

Clicking the green plus sign we get the dialog where we define the choice list for the RegionId

Select the Static View as List Source

Select the Static View as List Source

Select Static VO as List Source

Select Static VO as List Source

Select the List Id

Select the List Id

Now as we want to see the region name instead of the Id we switch to the ‘UI Hints’ tab and select the regionName attribute from the list source

Select the RegionName

Select the RegionName

Shuffle it to the 'selected' side

Shuffle it to the ‘selected’ side

The same action are done for the CountriesView, only here we select the RegionsView as list source. Finally we add the new views to the data model of the application module. All other views we delete as we do’t need them.

Final Application Module

Final Application Module

Now we can test run the application module:

Test CountriesView

Test CountriesView

Test Countries4StaticRegionView1

Test Countries4StaticRegionView1

As expected we once see the region name from the regions table and once we get the static data we added to our static VO.

The final step for this blog is to setup a page in hte UI. For this we create one page with an af:panelSplitter. on hte left side we drag the CountriesView1 from the data control and drop it as ‘ADF Table…’. On hte right we drag the Countries4StaticRegionView1 and drop it as ‘ADF Table…’ too. We set the table to select single and allow sorting. Finally we set the table to ‘Click to Edit’ mode so that only on row can be edited at once.

Running Application: CountriesView1

Running Application: CountriesView1

Running Application: Countries4StaticRegionView1

Running Application: Countries4StaticRegionView1

Download:
You can download the workspace of the sample from here: ADF EMG Samples: BlogStaticVOLov.zip. The sample uses the HR schema and was build using JDeveloper 11.1.1.6.0 but should run in 11.1.1.5.0 and 11.1.2.x too.

JDeveloper: Fitler Table on Transient Column

This blog entry is based on a question on the JDeveloper and ADF OTN forum. The use case is that a entity (EO) based view object (VO) is shown as a editable table on a page. One column should show a checkbox which is used to select rows on the VO. On an user action, a button pressed in this sample, the selected rows should be displayed in an other table (read only in this case).

We build this use case using JDeveloper 11gR2 (11.1.2.2.0) using the HR schema. As we only want to show how to solve this use case, we only need the ‘Countries’ table. The sample can be downloaded using the link provided at the end of this blog.

There are two problems to solve here:

  1. showing a checkbox to select rows
  2. filter the second table to only show the marked rows

The solution for the first quest is outlined in ADF Code Corner article 99. Multi Table Row Selection for Deferred Delete by Frank Nimphius. We use a transient attribute on the Countries EO, making sure that the transient radio button is selected

Adding a Transient Attribute

Adding a Transient Attribute

We name the new attribute ‘Selected’ and choose Boolean as type for it. Now open the Counties VO and also add an attribute, this time from the EO

Add Attribute to VO from EO

Add Attribute to VO from EO

New Attribute 'Selected' in VO

New Attribute ‘Selected’ in VO

Now open the UI Hints tab and select ‘Check Box’ as ‘Control Type’. You can also add a label to be used for the attribute in the ui.

Set UI Hints

Set UI Hints

This concludes the first quest in the model layer. The second problem is to only show those rows in a second table which are marked (check box selected) in the first table. For the selection we added a new transient attribute ‘Selected’. This boolean attribute we now use to create a view criteria (VC) to filter the row set.
Open the VO CountrieView and select the ‘Query’ node. Add a VC using the green plus sign, name the VC ‘CountriesSelectedVC’ and select the transient ‘Selected’ attribute to equal ‘true’, which is the value the check box get if it’s marked. It’s essential that we set the ‘Query Execution Mode’ to ‘In Memory’ as the VC uses transient data. The framework give you a warning if you don’t obey this rule.

ViewCriteria to Filter Selected Rows

ViewCriteria to Filter Selected Rows

Now that we are able to filter all rows which have the ‘Selected’ attribute set, we have to set up the application modules data model. We use the CountriesView VO to show all rows including the check box to select some or all rows and use the same CountriesView VO, now with the VC applied, to show only the marked rows.
For this we add the CountriesView VO once to the data model as ‘CountriesView1’ and then add the same VO again to the data model as ‘CountriesViewSelected’. To apply the VC ‘CountriesSelectedVC’we defined earlier, we select the ‘Edit’ button in the top right corner of the data model. In the dialog we select the CountriesSelectedVC VC and shuffle it to the selected side. This adds the defined VC as where clause to the VO.

Add VC to VO

Add VC to VO

If you run the model project in the application module tester you’ll see that it works as expected.

The final task is to set up the UI. We use a single page and put a splitter onto a panel stretch layout. The top part shows the editable table with the check box, the lower splitter facet the row which are selected in the upper table. The image below shows the running application

Final Application after Start

Final Application after Start

Select some rows

Some Rows are Selected

Some Rows are Selected

After selecting some rows by marking the check box we need to issue an action to see the result. Click the ‘refresh’ button which is bound to the ‘execute’ method of the CountriesViewSelected view from the Data Control. All left to do is to set up a partial trigger in the table showing the selected records pointing to the ‘refresh’ button.

<af:table value="#{bindings.CountriesViewSelected.collectionModel}" var="row"
          rows="#{bindings.CountriesViewSelected.rangeSize}"
          emptyText="#{bindings.CountriesViewSelected.viewable ? 'No data to display.' : 'Access Denied.'}"
          fetchSize="#{bindings.CountriesViewSelected.rangeSize}" rowBandingInterval="0"
          id="t2" partialTriggers="::ctb1">

Now the final result look like

Final Application

Final Application

You can download the sample workspace, build with JDeveloper 11.1.2.2.0 and depending on the HR db schema, from here: BlogFilterTableOnColumn.zip.doc
Please rename the file to ‘.zip’ after downloading it!

I set up a second version of the sample which uses the toolbar button set to partial submit. At first it looks like the button in the toolbar doesn’t work as there is no change in the selected table section. The problem is that the check box which selects the rows doesn’t auto submit the values. So the query executed by the toolbar button only sees the old marks. If you set the selectBooleanCheckbox autoSubmit property to true it works as expected.
The new version of the sample can be loaded here BlogFilterTableOnColumnV2.zip

JDeveloper & ADF: Multiple Cascading Tables

In a former blog post JDeveloper 11.1.2.1: Cascading Tables I showed how to cascade two tables. Lately I saw a couple of request on the OTN JDeveloper & ADF forum on how to do this with multiple tables.
This blog extends the sample given in the old post to show how to add another cascading table. For this we need to add an other entity view object pair to the project, build the needed association and view links to the existing entity and view object, change the data model of the application module and finally change the UI to show the new cascadeing table.

Lets start to add the new JobHistory entity and JobHistoryView view object. For this you can open the ‘New Business Components from Tables…’ wizzard on the existing business components node in the application

Add new Business Objects

Add new Business Objects


Follow the wizard…
Select new Table

Select new Table

Select new ViewObject

Select new ViewObject

Don't add new ApplicationModule

Don't add new ApplicationModule

The result of this operation should look like this:

Result of the Wizard

Result of the Wizard

Now that we only added one table, the framework did not pick up the foreign key relationship between the Employees and the JobHistory entities. We have to build the needed association and view link ourself…

Build New Association

Build New Association

... Name it ...

... Name it ...

... Select the Attributes ...

... Select the Attributes ...

... Remove the not needed Navigation from JobHistory to Employee ...

... Remove the not needed Navigation from JobHistory to Employee ...

Step to the end of the wizard and click ‘Finish’, save your work. Next we create the ViewLink based on just created EmpJhistFkAssoc…

Create ViewLink ...

Create ViewLink ...

... Name it ...

... Name it ...

... Select the EmpJhistFkAssoc for both ViewObjects  ...

... Select the EmpJhistFkAssoc for both ViewObjects ...

At this point you can click ‘Finish’ as no other change need to be made in this wizard. After this we add the new JobHistoryView to the application modules data model as a dependent ViewObject (on EmployeesView). Open the BCTAppModule application module and select the ‘Data Model’ node

Open BCTAppModule and select the ViewObjects...

Open BCTAppModule and select the ViewObjects...

… and hit the shuttle arrow to put the JobHistoryView under the EomployeesView3…

Final Data Model

Final Data Model

Save your work and compile the model project. Next we change the UI to show the job history for the selected employee. If you open and refresh the data model of the ViewController project you see the new JobHistory1 under the EmployeesView3…

Data Model in ViewController...

Data Model in ViewController...

Now we open the existing Dep.jsff page fragment to add an other af:panelCollection under the existing two

...add af:panelCollection ...

...add af:panelCollection ...

drop the JobHistoryView1 from the data control onto the new af:panelCollection as ‘Read-Only Table’…

... Drop JobHistoryView1 into panelCollection as Read-Only Table ...

... Drop JobHistoryView1 into panelCollection as Read-Only Table ...

... Setup Table as 'Single Selection' and allow Sorting ...

... Setup Table as 'Single Selection' and allow Sorting ...

Finally we need to setup the partial triggers for the JobHistroy table so that the table reacts of changes of the departments and employees table…

Setup Partial Triggers...

Setup Partial Triggers...

... React on Department and Employee changes...

... React on Department and Employee changes...

Save your work and run the application. That should open the Page with three cascading tables visible. If we change the department to e.g. ‘Sales’ and select the employee with the id 176 we should see

Final Cascading Table Application

Final Cascading Table Application

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

JDev: Custom selectionListener for ViewObjects in ‘RangePaging’ mode

Lately a question on the Oracle JDev forum came up, asking for a solution for a problem with a ViewObject in ‘RangePaging’ mode and a single selection af:table defined on this ViewObject. The problem is to get the current selected row in such a case. Under some circumstances (which are not always reproducible) using the default

#{bindings.YOUR_VIEWNAME.collectionModel.makeCurrent} 

doesn’t mark the selected record and a call in a bean to get the selected record returns null:

BindingContext lBindingContext = BindingContext.getCurrent();
BindingContainer lBindingContainer = lBindingContext.getCurrentBindingsEntry();
DCBindingContainer bindingsIte = (DCBindingContainer) lBindingContainer ;
DCIteratorBinding dciter = bindingsIte.findIteratorBinding("YOUR_VIEWNAMEIterator");
Row row = dciter.getCurrentRow();
if (row == null) {
    return null;    // no current row
}

For ViewObjects in ‘Scrollable’ mode you get the selected record without any problem. ViewObjectes in ‘RangePaging’ mode are mostly used for tables which contain many rows and the use case doesn’t allow to filter the result set to a reasonably number. The ‘RangePaging’ option is a tuning parameter in the ViewObject definition

Set a ViewObject to 'RangePaging' mode

Set a ViewObject to 'RangePaging' mode


I run into this condition myself and use the following work around:

  1. remove the current selectionlistener from the table (#{bindings.YOUR_VIEWNAME.collectionModel.makeCurrent})
  2. define a new selection listener (use the small arrow on the right side) in a bean of your choice. The scope of the bean has to be view or pageflow depending on where you need access to the selected row
  3. in the new selectionListener you get the selected row from the event, get the key of the row and store it in a bean attribute
  4. when you need the selected row you use the stored row key and work with this. If you need attributes from the row you have to query the row again, as you only have the key

If you only need the key of the row you can e.g. pass this key to a service method defined in the application module or the ViewObject. Here is a sample of such a selection listener:

public void singleSelectionListener(SelectionEvent selectionEvent) {
        RowKeySet rksAdd = selectionEvent.getAddedSet();
        if (rksAdd.isEmpty())
            return;  // no selection
 
        Object[] it = rksAdd.toArray();
        // as this is for single selection there should only be one, but...
        for (Object obj: (List) it[0]) {
            mLogger.fine("Selected :" + obj);  // log selected row
            Key k = (Key) obj;   // the object is the row key
            Object[] kv = k.getKeyValues();  // get the key value for later
            // strore the key value in a bean attribute: mLastSelectedOID is defined in the bean
            mLastSelectedOID = (Integer) kv[0]; // store the key value (if the key has multiple parts you need to store them all)
        }
    }

The variable mLastSelectedOID is defined in the bean. The type of the attribute depends on the type the primary key of the table has. If you like you can generate getter/setter methods for the attribute and use them instead of assigning the value directly.

JDeveloper 11.1.2.1: Cascading Tables

Lately a user on OTN JDeveloper and ADF forum ask how to cascade to tables instead of two LOV components. My first thought was to use an af:treeTable, however, this would give the user a different experience then you get from a cascading LOV. In the end I build a small test case using the HR db schema using the departments as master table and the employees of the selected departments as detail table. At the end of the post you’ll find the link to the sample workspace.
The sample is very simple as it only has the departments with the cascading employees view as data model.

Data model

Data model

The view controller is simple too. Its consists of only one page which holds a region. Inside the region are two panelCollection components, one holding the departments as table (read only, single selection mode) and one holding the cascading employees table (read only, single selection mode).

Region holding the cascading tables

Region holding the cascading tables

The magic which make the sample work, is the partial trigger which is used on the employee table and is listening on the departments table. The selection of the employees is done in the model via the viewLink which is automatically setup when you create the business components from the HR tables.

 PartialTrigger

PartialTrigger

When you run the sample, which was build using JDev 11.1.2.1.0, you see the that the first row of the departments table is selected and the employees of this department in the lower table.

Start of sample

Start of sample

If you select an other row in the department table you see the different employees in the lower table

Selection of an other department

Selection of an other department

To summarize this blog, I can say that the implementation of the use case did not need one line of java code. The solution was easy to archive by only using a declarative approach.

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

JDev11.1.2.1.0: Handling images/files in ADF (Part 2)

This blog article is part 2 of a series of posts showing how to deal with images or files in an ADF application. Each of the techniques to do this are described in other blog posts or documents, but I couldn’t find a sample doing it all together in on sample application.
The goal of this series is to provide a sample showing how to upload a file from a client to the server, store the data on the server, make it available for later retrieval and show the data in the user interface (form and table).

    Part 1 gives an overview of the sample application I’m going to build and how to set it up
    Part 2 shows how to upload a file, store it and download it back to the client
    Part 3 implements two techniques to show the data (image) on the user interface
    Part 4 backport of the sample to JDeveloper 11gR1
    Part 5 implements a technique to show the uploaded file right after upload without the need to commit first


Uploading, downloading and storing of data in a blob

In this part of the series I show how to upload a file from the client to the server and store the data in a blob column in a db. The tables I’m using are CATALOG and IMAGES. The DML to define the tables and can be found in PART 1 of the series or in the sample workspace at the end of this part.
Lets start with uploading a file from client to the server. ADF rich faces provide the tag af:inputFile to allow uploading of data.

&lt;af:inputFile label=&quot;Select new file&quot; id=&quot;if1&quot; autoSubmit=&quot;true&quot;
              valueChangeListener=&quot;#{ImageBean.uploadFileValueChangeEvent}&quot;/&gt;

As you see the tag has set its autoSubmit property to true to allow direct upload of data. The real work is done in the valueChangeListener which is bound to a backing bean in request scope. The value the event carries allows access to the data and give us the original filename and mime type.

    public void uploadFileValueChangeEvent(ValueChangeEvent valueChangeEvent)
    {
        // The event give access to an Uploade dFile which contains data about the file and its content
        UploadedFile file = (UploadedFile) valueChangeEvent.getNewValue();
        // Get the original file name
        String fileName = file.getFilename();
        // get the mime type 
        String contentType = ContentTypes.get(fileName);
        // get the current roew from the ImagesView2Iterator via the binding
        DCBindingContainer lBindingContainer =
            (DCBindingContainer) BindingContext.getCurrent().getCurrentBindingsEntry();
        DCIteratorBinding lBinding = lBindingContainer.findIteratorBinding(&quot;ImagesView2Iterator&quot;);
        Row newRow = lBinding.getCurrentRow();
        // set the file name
        newRow.setAttribute(&quot;ImageName&quot;, fileName);
        // create the BlobDomain and set it into the row
        newRow.setAttribute(&quot;ImageData&quot;, createBlobDomain(file));
        // set the mime type
        newRow.setAttribute(&quot;ContentType&quot;, contentType);
    }

    private BlobDomain createBlobDomain(UploadedFile file)
    {
        // init the internal variables
        InputStream in = null;
        BlobDomain blobDomain = null;
        OutputStream out = null;

        try
        {
            // Get the input stream representing the data from the client
            in = file.getInputStream();
            // create the BlobDomain datatype to store the data in the db
            blobDomain = new BlobDomain();
            // get the outputStream for hte BlobDomain
            out = blobDomain.getBinaryOutputStream();
            // copy the input stream into the output stream
            /*
             * IOUtils is a class from the Apache Commons IO Package (http://www.apache.org/)
             * Here version 2.0.1 is used
             * please download it directly from http://projects.apache.org/projects/commons_io.html
             */ 
            IOUtils.copy(in, out);
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        catch (SQLException e)
        {
            e.fillInStackTrace();
        }

        // return the filled BlobDomain
        return blobDomain;
    }

Please note the I use the Apache Commons IO package in the version 2.0.1 which you need to download from the Apache Software Foundation web side. The use the class IOUtils which allow easy copying of streams.
If your are using an older version of JDeveloper you may need to add the usesUpload property to the af:form tag. In the current JDev version it should work automatically (but please check it).

&lt;af:form id=&quot;f1&quot; usesUpload=&quot;true&quot;&gt;

If you use fragments (as in this sample) you need to check the jspx or jsf page which is holding the af:form tag (Catalog.jsf), as the fragments don’t have a form tag.
By default, Oracle ADF 11g application allows to upload maximum 2 MB size files. This maximum can be configured in the web.xml file if you need to upload files bigger then 2 MB. For this you need to specify the context parameters

    org.apache.myfaces.trinidad.UPLOAD_MAX_MEMORY
    org.apache.myfaces.trinidad.UPLOAD_MAX_DISK_SPACE
    org.apache.myfaces.trinidad.UPLOAD_TEMP_DIR

For more information about the parameters and how they work check the doc Oracle® Fusion Middleware Web User Interface Developer’s Guide for Oracle Application Development Framework.

Now to the download part. This is handled in ADF via the af:fileDownloadActionListener tag. The tag is a client listener tag and is therefor applied to a command ui tag. In the sample I use a af:commandButton:

&lt;af:commandButton text=&quot;Download Data&quot; id=&quot;cb3&quot;
                  visible=&quot;#{bindings.ImageData.inputValue ne null}&quot;
                  binding=&quot;#{ImageBean.downloadButton}&quot;&gt;
          &lt;af:fileDownloadActionListener contentType=&quot;#{bindings.ContentType.inputValue}&quot;
                                         filename=&quot;#{bindings.ImageName.inputValue}&quot;
                                         method=&quot;#{ImageBean.downloadImage}&quot;/&gt;
&lt;/af:commandButton&gt;

The real work is done in the downloadImage method in the managed bean. The signature of the method is

public void downloadImage(FacesContext facesContext, OutputStream outputStream)

This allows you to access to the FacesContext and the output stream which you use to pipe the data to the client.

    public void downloadImage(FacesContext facesContext, OutputStream outputStream)
    {
        BindingContainer bindings = BindingContext.getCurrent().getCurrentBindingsEntry();

        // get an ADF attributevalue from the ADF page definitions
        AttributeBinding attr = (AttributeBinding) bindings.getControlBinding(&quot;ImageData&quot;);
        if (attr == null)
        {
            return;
        }

        // the value is a BlobDomain data type
        BlobDomain blob = (BlobDomain) attr.getInputValue();

        try
        {   // copy hte data from the BlobDomain to the output stream 
            IOUtils.copy(blob.getInputStream(), outputStream);
            // cloase the blob to release the recources
            blob.closeInputStream();
            // flush the outout stream
            outputStream.flush();
        }
        catch (IOException e)
        {
            // handle errors
            e.printStackTrace();
            FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, e.getMessage(), &quot;&quot;);
            FacesContext.getCurrentInstance().addMessage(null, msg);
        }
    }

This is all you need to do to download a blob from the db and send it back to the client.

I like to mention one other function of the sample application. If you hit the ‘Cancel’ button in the insert or edit image page I use a rollback to get the original data back and remove all changes made in the form. A rollback resets all current row pointers of the iterators. To avoid that the user sees the first row of the catalog table the rollback has to be handled in a special way. You have to save the current row (of the catalog iterator), do the rollback and reset the current row back to the saved one. This is done in the bean method

public String cancel_action() {...}

which you find in the ImageBean class.

This concludes part 2. In part three I implement two techniques to show the data (image) on the user interface (page)

The sample application workspace is build with JDeveloper 11.1.2.1.0 and can be loaded from here BlogUploadDownload_P2.zip
Please rename the file to ‘.zip’ after downloading it!
The Commons IO package in the version 2.0.1 you can download from the Apache Software Foundation apache web side

To be continued…

JDev11.1.2.1.0: Handling images/files in ADF (Part 1)

This blog article is part 1 of a series of posts showing how to deal with images or files in an ADF application. Each of the techniques to do this are described in other blog posts or documents, but I couldn’t find a sample doing it all together in on sample application.
The goal of this series is to provide a sample showing how to upload a file from a client to the server, store the data on the server, make it available for later retrieval and show the data in the user interface (form and table).

    Part 1 gives an overview of the sample application I’m going to build and how to set it up
    Part 2 shows how to upload a file, store it and download it back to the client
    Part 3 implements two techniques to show the data (image) on the user interface
    Part 4 backport of the sample to JDeveloper 11gR1
    Part 5 implements a technique to show the uploaded file right after upload without the need to commit first


Overview of the sample application

The sample allows to create and manage catalogs. A catalog has a unique id, a name and may contain files and images which the user can upload into a catalog and download from it. All this is implemented in a simple way, so no fancy layout, only bare functionality. Here is a screen shot of the sample application after part 2 is finished:

Sample app end of part 2

Sample app end of part 2

As you see the UI is nothing I would use in a real world application, but for this sample it does the trick.
To create a new catalog you click the ‘New Catalog’ button and can fill in a name for the new catalog. The id is automatically assigned via a groovy expression which calls a sequence defined in the db. In the catalog screen you see the catalog together with all images added to this catalog. Here you can remove the whole catalog. The image data is deleted too in this case.

Create Catalog

Create Catalog

Once you have a catalog created you can add images or other files to it by using the ‘New Image’ button.

Create Image

Create Image

When adding a new image to a catalog you can specify a name for the image, the content type which will be read from the file once you hit the upload button. The image id is assigned by a groovy expression, the catalog id is populated by the master record, the catalog. As there is no visible image of the data in this version, an output text shows you if data has already been uploaded (Image Data available/not available).
This concludes the short run through the sample application.
The following db diagram shows the two tables involved (CATALOG and IMAGES) and their 1:* relationship. For reference I added the two sequences which generate the primary key for the tables.

DB Diagram

DB Diagram

Next is the DML to generate the two tables and the sequences. You can add the tables to an existing DB or create a new schema and add them their. If you later download the source code you’ notice, that I added the DML to the well known HR schema. If you use an other schema, you have to change the db connection accordingly.

CREATE TABLE CATALOG 
(
  CATALOG_ID NUMBER(12) NOT NULL 
, CATALOG_NAME VARCHAR2(200 CHAR) NOT NULL 
, CONSTRAINT CATALOG_PK PRIMARY KEY 
  (
    CATALOG_ID 
  )
  ENABLE 
);

CREATE UNIQUE INDEX CATALOG_PK ON CATALOG (null ASC);

CREATE SEQUENCE CATALOG_SEQ INCREMENT BY 1 START WITH 100 NOCACHE;

CREATE TABLE IMAGES 
(
  IMAGE_ID NUMBER(12) NOT NULL 
, IMAGE_NAME VARCHAR2(200 CHAR) NOT NULL 
, CONTENT_TYPE VARCHAR2(50 CHAR) 
, IMAGE_DATA BLOB 
, CATALOG_ID NUMBER(12) NOT NULL 
, CONSTRAINT IMAGES_PK PRIMARY KEY 
  (
    IMAGE_ID 
  )
  ENABLE 
);

CREATE UNIQUE INDEX IMAGES_PK ON IMAGES (null ASC);

ALTER TABLE IMAGES
ADD CONSTRAINT IMAGES_CATALOG_FK FOREIGN KEY
(
  CATALOG_ID 
)
REFERENCES CATALOG
(
  CATALOG_ID 
)
ENABLE;

CREATE SEQUENCE IMAGE_SEQ INCREMENT BY 1 START WITH 100 NOCACHE;

Finally here are the task flows which build the first part of the sample application:

Task flows

Task flows

The start page ‘Catalog’ contains a region (catalog-task-flow-description.xml) which is used to add or edit catalogs and to add or edit images for a catalog.

This concludes part 1. In part two I describe in detail how to implement the file upload and download feature and store the data in a blob column in the db.

To be continued…

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.