Show Comma Separated String as Detailstamp in Table

In this blog I’ll show how to implement a very specific use case which was asked on the Developer Community (the renamed OTN).

Use case

A table has a comma-separated list of values which are FK for another data store which might be from a different table or WebService. So the list of value can’t directly be related to the master table as an association or view link.

Here is a sample look of such a table or view

The resulting table in the UI should show this data as table and in the detailStamp facet show the data for each number in the list like

Implementation prerequisites

The use case is implemented using JDeveloper 11.1.1.7.0 and uses the HR DB schema.

To make it easy we use the normal HR DB and create a view to show the department together with a comma-separated list of all employeeId, EmpList in the above table. For this we use the SQL code:

CREATE OR REPLACE FORCE VIEW "HR"."DEPT_EMPS" ("DEPT_ID", "DEPARTMENT_NAME", "MANAGER_ID", "LOCATION_ID", "EMP_LIST") AS 
 SELECT DEPT.DEPARTMENT_ID,
 DEPT.DEPARTMENT_NAME,
 DEPT.MANAGER_ID,
 DEPT.LOCATION_ID,
 cclist.EMP_LIST
 FROM DEPARTMENTS DEPT,
 
 ( SELECT EMP_LIST.DEPARTMENT_ID,
 LTRIM (
 MAX (SYS_CONNECT_BY_PATH (EMPLOYEE_ID, ', '))
 KEEP (DENSE_RANK LAST ORDER BY curr),
 ', ')
 AS EMP_LIST
 FROM (SELECT ccr.DEPARTMENT_ID,
 ccs.EMPLOYEE_ID EMPLOYEE_ID,
 ROW_NUMBER ()
 OVER (PARTITION BY ccr.DEPARTMENT_ID
 ORDER BY ccs.EMPLOYEE_ID)
 AS curr,
 ROW_NUMBER ()
 OVER (PARTITION BY ccr.DEPARTMENT_ID
 ORDER BY ccs.EMPLOYEE_ID)
 - 1
 AS prev
 FROM EMPLOYEES ccs, DEPARTMENTS ccr
 WHERE ccs.DEPARTMENT_ID = ccr.DEPARTMENT_ID) EMP_LIST
 GROUP BY EMP_LIST.DEPARTMENT_ID
 CONNECT BY prev = PRIOR curr
 AND EMP_LIST.DEPARTMENT_ID = PRIOR EMP_LIST.DEPARTMENT_ID
 START WITH curr = 1
 ORDER BY EMP_LIST.DEPARTMENT_ID) cclist
 WHERE DEPT.DEPARTMENT_ID = CCLIST.DEPARTMENT_ID;

And the data store for the employees is created as a DB view too:

 CREATE OR REPLACE FORCE VIEW "HR"."EMP_LOOKUP_VW" ("EMPLOYEE_ID", "NAME", "EMP_ID_CH") AS 
 SELECT emp.EMPLOYEE_ID,
 emp.FIRST_NAME || emp.LAST_NAME,
 TO_CHAR(emp.EMPLOYEE_ID)
 
 FROM EMPLOYEES emp;

There is no association or viewlink between the two views. This data model came together with the original test case I used to implement the use case.

Implementation

The implementation starts with generating a normal ADF Web Application. Details on how to do this you find at Why and how to write reproducible test cases.

In the model layer we add EO/VO for the two views we generated to end with the following data model

And the model project

How to implement the use case

Before we start to make changes, let us think about the solution. The problem is that we have a string with a list of values which we want to show as single elements in the detail stamp of a table. We need to split the string into pieces (the employeeId) and iterate over them in the detail stamp of the table.

To iterator over a data collection, we can use a CollectionModel or an ArrayList which JDev converts internally into a CollectionModel.

In this solution, we are adding the array if employee Ids to the DeptEmpsEO as a transient attribute. This will make the array available for each row as part of the row itself.

Step 1

We add a transient attribute to the DeptEmpsEO like

null

We set the type as Object as JDev 11.1.1.9 can handle SQL Array types only. In our case, we build an ArrayList from the string.

Next, we create the Java implementation class for the EO

In the generated class we exchange the getEmpArray() method with

 

The method gets the comma-separated list and splits it into an employeeId and adds them into an ArrayList of Numbers (oracle.jbo.domain.Number). The resulting arrayList is returned.

As we have added a new attribute to the EO we have to add this attribute to the VO too:

Step 2

As we later only have employee IDs, we need a method to look up more data for the employee. Remember, the data might be from different sources so there is no association or viewlink available we can use like in normal ADFbc applications. We have to build a lookup view for this (or is the data is from a WebService we use this WebService).

We use the second DB view build at the beginning from which we created the EO EmpLookupVw and the VO EmpLookupVwView.

The data this VO presents is very simple as it’s just the employeeId, the first name, and the last name.

In this VO we create a view criteria to lookup a single employee by its Id like

Which uses a bind variable of Integer type

To make this available in the DataControl, we add the VO as special Vo to the ApplicationModule

 

 

 

Now the VO named EmpLookupById always add the viewCriteria when executed. All we have to do is to add the integer parameter for the employeeID we are looking for. In the DataControl we can use the ‘ExecuteWithParam’ method from the VO and set the parameter

Step 3: ViewController

The test case comes with some pages in the ViewController project. However, we use a fresh jspx page to show the solution. We create a new page by dragging a ‘View’ component onto the adfc-config.xml (the main unbounded task flow) and create the file by double-clicking it. In the dialog, we select a ‘Quick Layout’

 

Once the page shows up in the design view we add an af:outputText for the caption and drag the DeptEmpsVO1 view object from the data control onto the center facet of the layout and select to create an ‘ADF Read-only Table’. We show all attributes (if you like you can deselect the EmpArray attribute).

This is the base of the work. Now we enable the ‘Detail Stamp’ facet of the table. In this facet we want to iterate over the comma-separated list of employees.

We add an af:panelGroupLayout into the facet with vertical layout. Inside the panelGroupLayout we add an af:iterator which we use to iterate over the array of employeeId we created in the row as attribute EmpArray. ADF is intelligent and converts the array into a collection. This collection can be used as other collection. We define a name for the variable we can use for each single element of the collection like ‘emp’. Using an af:outputText we can print out the value of the current element of the collection like

<af:iterator id="i1" value="#{row.EmpArray}" var="emp">
                    <af:panelGroupLayout id="pgl2" layout="horizontal">
                      <af:outputText value="- #{emp} -”/>
…

What’s missing is that we like to get more information than just the Id of the employee. So we add another EL to the af:outputText which points to a bean method which will retrieve this additional information. The final facet looks like

  <f:facet name="detailStamp">
 <af:panelGroupLayout id="pgl1" layout="vertical">
 <af:iterator id="i1" value="#{row.EmpArray}" var="emp">
 <af:panelGroupLayout id="pgl2" layout="horizontal">
 <af:outputText value="- #{emp} - #{viewScope.EmpBean.empNameById}" id="ot8"/>
 </af:panelGroupLayout>
 </af:iterator>
 </af:panelGroupLayout>
 </f:facet>

Or as an image:

Finally, we have to create the bean with the method to retrieve the additional data. We create a Java class in the view folder

 

 

 

End add this bean class as managed bean to the adfc-config.xml

Before we go into writing the method to retrieve the data, we have to add the EmpLooupById.ExecuteWithParams method to the pageDef to make it available in the page. The easiest way to do this is to open the EmpLookupById Vo in the Data Control and open the ‘Operations’ node. Select the ExecuteWithParams method and drag it onto the page. We can drop it anywhere as ‘ADF Button’

 

 

 

In the final dialog, we add the value of the parameter as ‘#{emp}’ which is the EL to access the current employeeId when we iterate the array.

This will create the needed pageDef entries. As we don’t need the button, we switch to source mode and delete the button from the page. This will keep the pageDef entries.

Now we open the bean class and write the method to get the additional data for an employee as

Lines 25-38 we get the method binding from the pageDef.

Line 39 retrieves the current value of the employeeId by evaluating the EL ‘#{emp}’ by calling

Lines 40-42 set the value (the employeeId) as parameter to the methods bindId parameter

Lines 43-48 execute the method and check for any errors. If there is an error, the stack trace is printed into the log and the method return “Not found!”.

Lines 50-56 if the call the ExecuteWithParams method returns without an error, the current row of the EmpLookupById VO points to the employee we are looking for. We get the iterator from the pagedef and from the currentRow we read the ‘Name’ attribute. The value of this attribute we return and it will be printed in the af:outputText in the page.

Running the page now will produce

You can download the test case from GitHub BlogCommaSeparatedListDetail. It uses the HR DB schema with some additional DB views created. The scripts are part of the workspace. The application is developed using JDev 11.1.1.7.0.

Advertisements

JDev12c: Searching an af:tree

On the JDev & ADF OTN space I got a question on how to search an af:tree and select and disclose the nodes found matching the search criteria.

Problem description

We like to search an af:tree component for string values and if we find the value we like to select the node where we found the string we searched for. If the node where we found the string is a child node we disclose the node to make it visible.

Final sample Application

I started with building a sample application and show the final result here:

selection_935

We see a tree and a check box and a search field. The checkbox is used to search only the data visible in the tree or the whole data model the tree is build on. The difference is that you build the tree from view objects which can hold more attributes than you like to show in the tree node. This is the case with the sample tree as we see when we search for e.g. ‘sa’ in the visible data

selection_936

When we unmark the check box and repeat the search we get

selection_937

As you see we found another node ‘2900 1739 Geneva’ which doesn’T have the searched string ‘sa’. A look into the data model, the row behind this node shows

selection_938

We see that the street address which we don’t show in the node has the search string. To show that the search works for every node we set the search field to ‘2’ and get hits in different levels

selection_939

The sample application can be downloaded from GitHub. For details on this see the end of this blog.

Implementation

Now that we saw the running final application let’s look at how to implement this. We start by creating a small ADF Fusion Web Application. Is you like to you can start by following the steps given in  Why and how to write reproducible test cases.

Model Layer

Once the base application is created we setup the data model we use to build the tree. For this sample we use ‘Regions’, ‘Countries’ and ‘Location’ of the HR DB schema. To build the model we can use the ‘Create Business Components from Table’ wizard and end up with

selection_942

As you see I’ve renamed the views. The names now show what you’ll see when you use them. We only have one top level view object ‘RegionsView’ which will be the root of our tree in the UI. The child view are used to show detailed data.

View Controller

For the view controller layer we start by a simple page from the ‘Quick Layout’ section

selection_943

Now we add a title and add an af:splitter to the content area. Here we set the width of the first facet to 250 px to have enough room for the search field. We start with building the af:tree from the data control by dragging the ‘RegionsView’ from the data control onto the content area and dropping it as af:tree

Here we don’t select to show all attributes available but only a few.  Later we see that we can search the whole data model and not just only the visible data. Finally we bind the tree to a bean attribute to have access to the tree from the bean when we have searched it. This is a pure convenience, we could search the component tree each time we need the component to avoid the binding to a bean attribute.  When we create the bean we name it ‘TreeSelectionBean’ and set its scope to ‘Request’.  The bean will end in the adfc-config.xml

selection_950

the final code for the af:tree looks like

<af:tree value="#{bindings.RegionsView.treeModel}" var="node"
selectionListener="#{bindings.RegionsView.treeModel.makeCurrent}"
rowSelection="single" id="t1"
binding="#{TreeSelectionBean.tree}">
  <f:facet name="nodeStamp">
    <af:outputText value="#{node}" id="ot2"/>
  </f:facet>
</af:tree>

Now we create two pageDef variables as java.lang.String to hold the search string and the selection for the check box. If you need more information on how to create pageDef variables see Creating Variables and Attribute Bindings to Store Values Temporarily in the PageDef.

selection_949

In the first facet we add a check box and an af:inputText inside an af:panelGroupLayout and bind the value properties to the pageDef variables as

<af:panelGroupLayout id="pgl2" layout="vertical">
  <af:selectBooleanCheckbox text="node only" label="Seach" id="sbc1"
value="#{bindings.myNodeOnly1.inputValue}"/>
  <af:inputText label="Search for" id="it1" value="#{bindings.mySearchString1.inputValue}"/>
  <af:button text="Select" id="b1"
actionListener="#{TreeSelectionBean.onSelection}"/>
</af:panelGroupLayout>

The final thing to do is to wire the button to a bean method which does all the hard work. In the code above this is done with an actionListener which is pointing to the same bean created for the tree binding.


<span></span>public void onSelection(ActionEvent actionEvent) {
<span></span>JUCtrlHierBinding treeBinding = null;
// get the binding container
<span></span>BindingContainer bindings = BindingContext.getCurrent().getCurrentBindingsEntry();
<span></span> // get an ADF attributevalue from the ADF page definitions
<span></span> AttributeBinding attr = (AttributeBinding) bindings.getControlBinding("mySearchString1");
 String node = (String) attr.getInputValue();

// nothing to search!
 // clear selected nodes
<span></span> if (node == null || node.isEmpty()){
<span></span> RichTree tree = getTree();
<span></span> RowKeySet rks = new RowKeySetImpl();
<span></span> tree.setDisclosedRowKeys(rks);
 //refresh the tree after the search
<span></span> AdfFacesContext.getCurrentInstance().addPartialTarget(getTree());

return;
 }

<span></span> // get an ADF attributevalue from the ADF page definitions
<span></span> AttributeBinding attrNodeOnly = (AttributeBinding) bindings.getControlBinding("myNodeOnly1");
<span></span> String strNodeOnly = (String) attrNodeOnly.getInputValue();
<span></span> // if not initializued set it to false!
<span></span> if (strNodeOnly == null) {
<span></span> strNodeOnly = "false";
 }
<span></span> _logger.info("Information: search node only: " + strNodeOnly);

<span></span>//Get the JUCtrlHierbinding reference from the PageDef
<span></span> // For JDev 12c use the next two lines to get the treebinding
<span></span> TreeModel tmodel = (TreeModel) getTree().getValue();
<span></span> treeBinding = (JUCtrlHierBinding) tmodel.getWrappedData();
<span></span> // For JDev 11g use the next two lines to get the treebinding
<span></span> // CollectionModel collectionModel = (CollectionModel)getTree().getValue();
<span></span> // treeBinding = (JUCtrlHierBinding)collectionModel.getWrappedData();
<span></span> _logger.info("Information tree value:" + treeBinding);

//Define a node to search in. In this example, the root node
 //is used
<span></span> JUCtrlHierNodeBinding root = treeBinding.getRootNodeBinding();
 //However, if the user used the "Show as Top" context menu option to
 //shorten the tree display, then we only search starting from this
 //top mode
<span></span> List topNode = (List) getTree().getFocusRowKey();
<span></span> if (topNode != null) {
 //make top node the root node for the search
<span></span> root = treeBinding.findNodeByKeyPath(topNode);
 }
<span></span> RichTree tree = getTree();
<span></span> RowKeySet rks = searchTreeNode(root, node.toString(), strNodeOnly);
<span></span> tree.setSelectedRowKeys(rks);
 //define the row key set that determines the nodes to disclose.
<span></span> RowKeySet disclosedRowKeySet = buildDiscloseRowKeySet(treeBinding, rks);
<span></span> tree.setDisclosedRowKeys(disclosedRowKeySet);
 //refresh the tree after the search
<span></span> AdfFacesContext.getCurrentInstance().addPartialTarget(tree);
 }

In line 4-7 we get the value the user entered into the search field. Lines 9-19 check if the user has given a search string. If not we clear the currently selected nodes from the tree by creating a new empty RowKeySet and setting this to the tree.

If he got a search string we check if we should search the visible data only or the whole data model. This is done by getting the value from the check box (lines 21-28). Now we data from the tree (lines 30-37).

One thing we have to check before starting the search is if the user has used the ‘show as top’ feature of the tree. This would mean that we only search beginning from the current top node down (lines 39-49).

The search is done in a method

private RowKeySet searchTreeNode(JUCtrlHierNodeBinding node, String searchString, String nodeOnly)

this we pass the start node, the search string and a flag if we want to search the whole data model or only the visible part. The method returns a RowKeySet containing the keys to the rows containing the search string (line 51-52). This list of row keys we set to the tree as selected rows (line 54). As we would like to disclose all rows which we have found, we have to do one more step. This step uses the row key and traverses upward in the tree to add all parent node until the node is found where we started the search (line 53-55). This is necessary as you only see a disclosed child node in a tree if the parent node is disclosed too. For this we you a helper method (line 54) and set the row keys as disclosed rows in the tree.


 /**
<span></span> * Helper method that returns a list of parent node for the RowKeySet
<span></span> * passed as the keys argument. The RowKeySet can be used to disclose
 * the folders in which the keys reside. Node that to disclose a full
<span></span> * branch, all RowKeySet that are in the path must be defined
 *
<span></span> * @param treeBinding ADF tree binding instance read from the PageDef
 * file
<span></span> * @param keys RowKeySet containing List entries of oracle.jbo.Key
<span></span> * @return RowKeySet of parent keys to disclose
 */
<span></span> private RowKeySet buildDiscloseRowKeySet(JUCtrlHierBinding treeBinding, RowKeySet keys) {
<span></span> RowKeySetImpl discloseRowKeySet = new RowKeySetImpl();
<span></span> Iterator iter = keys.iterator();
 while (iter.hasNext()) {
<span></span> List keyPath = (List) iter.next();
<span></span> JUCtrlHierNodeBinding node = treeBinding.findNodeByKeyPath(keyPath);
<span></span> if (node != null && node.getParent() != null && !node.getParent().getKeyPath().isEmpty()) {
 //store the parent path
<span></span> discloseRowKeySet.add(node.getParent().getKeyPath());
 //call method recursively until no parents are found
<span></span> RowKeySetImpl parentKeySet = new RowKeySetImpl();
<span></span> parentKeySet.add(node.getParent().getKeyPath());
<span></span> RowKeySet rks = buildDiscloseRowKeySet(treeBinding, parentKeySet);
<span></span> discloseRowKeySet.addAll(rks);
 }
 }
<span></span> return discloseRowKeySet;
 }

This concludes the implementation of the sear in a tree.

Download

The sample application uses the HR DB schema and can be downloaded from GitHub

The sample was build using JDev 12.2.1.2.

 

JDeveloper: Navigation after Return from Bounded Task Flow

A question on the OTN JDeveloper and ADF ‘Space’ asked for a sample and/or tutorial on how to navigate after a bounded task flow, based on pages, returns from its work.
I setup a sample application to show how this works. The application is built using JDeveloper 11.1.1.7.0 and uses the HR DB schema. The sample can be loaded using the link provided at the end of the post.
Before we start let’s take a look at the running application:


Now that we have seen how the application works let’s check out how it’s implemented.

We start with an unbounded task flow (adfc-config.xml) which look like

Unbounded Task Flow: adfc-config.xml

Unbounded Task Flow: adfc-config.xml


We see that the application is built from two pages, a ‘Start’ page and an ‘End’ page. The ‘Start’ page can call a bounded task flow employee-btf. The start page holds one button ‘Start’ which calls the bounded task flow which is shown below.

Bounded Task Flow: employee-btf.xml

Bounded Task Flow: employee-btf.xml

This task flow shows a table of employees and allows to navigate to a detail page where the employee data can be changed. Depending on the result of the change, we navigate back to the start page (when the rollback button is clicked) or we navigate to the ‘end’ page if the changes are committed (using the commit button). The buttons are calling the navigation case named ‘start’ or ‘end’ which are task flow return cases which are looking like

Task Flow Return: Start

Task Flow Return: Start

or

Task Flow Return: End

Task Flow Return: End

As you see the return for ‘start’ calls a parent navigation ‘start’ which is implemented using a ‘wild card’ navigation ‘*’. Same is true for ‘end’ task flow return call. Once the navigation is given back to the parent task flow, it looks for a navigation with the name of the outcome ‘start’ or ‘end’ and executes the navigation.

The sample can be loaded from the ADF EMG Samples workspace BlogUBTBTFNavigation.zip