Thursday Jan 17, 2013

How to create cascading (depending) auto suggest behavior using BC4J

In continuation to my previous blog on "How to create multi level cascading (dependent) list of values using BC4J", i am trying to leverage a similar usecase using af:autoSuggestBehavior in this article. Though the usecase looks same, the implementation is slightly different for this.

Let us assume a usecase where we have some text fields with auto suggest feature enabled, and their suggested items could be dependent on one other. For ex : Country, State and City.

This could be modeled by having an EO and VO created based on Person table and read-only look-up VOs created based on Country, State and City tables.

model.jpg

VOs.jpg

Implementing the dependency between LOVs is pretty straight forward. However, implementing the dependency between the auto suggest items is not. In order to achieve the dependency, first we would need couple of methods returning current row's CountryId and StateId. We could add them up in the AMImpl class.

public Number getCurrentCountryId(){
return (Number) this.getPersonView1().getCurrentRow().getAttribute("CountryId");
}

public Number getCurrentStateId(){
return (Number) this.getPersonView1().getCurrentRow().getAttribute("StateId");
}

As we need to filter out the States based on the Country and the City based on the Country & State, we would need to modify the query of these two VOs to include a bind variable in the where clause.

whereClause.jpg

For having the auto suggest, we need to have a view criteria defined for all the three look-up VOs (CountryView, StateView and CityView).

CountryVC.jpg

StateVC.jpg

CityVC.jpg

Generate VOImpl classes for Country, State and City VOs (with Include bind variable accessors option checked), and then expose setBindCountryName (in Country VO), setBindStateName (in State VO) and setBindCityName (in City VO) methods as client interfaces.

And the last part on the model is to pass the current row's CountryId and StateId to the Bind Variables defined in the State and City VOs. Also, we need to get the VCs created above to be executed by default (By editing the VO instance in the AM's data model), so that the auto suggest list would be filtered as and when the users type.

CountryViewAM.jpg

StateViewAM.jpg

CityViewAM.jpg

Here, we specify groovy expression for the CountryId and StateId as adf.object.applicationModule.<methodName>. For more information about using groovy expressions, check out this : http://www.oracle.com/technetwork/developer-tools/jdev/introduction-to-groovy-128837.pdf .

With this, we are done with setting up the model layer for the auto suggest dependency.

In the View layer, we would create an ADF Form based on the Person VO, with all the navigation buttons.

PersonJSPX.jpg

In order to construct the onSuggest items, we would create Tree Bindings for Country VO, State VO and City VO, along with method action bindings for the setBindCountryName, setBindStateName and setBindCityName methods.

pagedef.jpg

Now, we could add af:autoSuggestBehavior for CountryId, StateId and CityId fields. Then, add onSuggest methods in backing bean for populating the on suggest items for each fields.

onSuggest method for Country field :

public List onCountrySuggest(String searchCountryName) {
ArrayList<SelectItem> selectItems = new ArrayList<SelectItem>();

System.out.println(searchCountryName);
//get access to the binding context and binding container at runtime
BindingContext bctx = BindingContext.getCurrent();
BindingContainer bindings = bctx.getCurrentBindingsEntry();
//set the bind variable value that is used to filter the View Object
//query of the suggest list. The View Object instance has a View
//Criteria assigned
OperationBinding setVariable = (OperationBinding) bindings.get("setBind_CountryName");
setVariable.getParamsMap().put("value", searchCountryName);
setVariable.execute();
//the data in the suggest list is queried by a tree binding.
JUCtrlHierBinding hierBinding = (JUCtrlHierBinding) bindings.get("CountryView1");


//re-query the list based on the new bind variable values
hierBinding.executeQuery();

//The rangeSet, the list of queries entries, is of type
//JUCtrlValueBndingRef.
List<JUCtrlValueBindingRef> displayDataList = hierBinding.getRangeSet();

for (JUCtrlValueBindingRef displayData : displayDataList){
Row rw = displayData.getRow();
//populate the SelectItem list
selectItems.add(new SelectItem(
(Integer)rw.getAttribute("Id"),
(String)rw.getAttribute("Name")));
}

return selectItems;
}

onSuggest method for State field :


public List onStateSuggest(String searchStateName) {
ArrayList<SelectItem> selectItems = new ArrayList<SelectItem>();

System.out.println(searchStateName);
//get access to the binding context and binding container at runtime
BindingContext bctx = BindingContext.getCurrent();
BindingContainer bindings = bctx.getCurrentBindingsEntry();
//set the bind variable value that is used to filter the View Object
//query of the suggest list. The View Object instance has a View
//Criteria assigned
OperationBinding setVariable = (OperationBinding) bindings.get("setBind_StateName");
setVariable.getParamsMap().put("value", searchStateName);
setVariable.execute();
//the data in the suggest list is queried by a tree binding.
JUCtrlHierBinding hierBinding = (JUCtrlHierBinding) bindings.get("StateView1");


//re-query the list based on the new bind variable values
hierBinding.executeQuery();

//The rangeSet, the list of queries entries, is of type
//JUCtrlValueBndingRef.
List<JUCtrlValueBindingRef> displayDataList = hierBinding.getRangeSet();

for (JUCtrlValueBindingRef displayData : displayDataList){
Row rw = displayData.getRow();
//populate the SelectItem list
selectItems.add(new SelectItem(
(Integer)rw.getAttribute("Id"),
(String)rw.getAttribute("Name")));
}

return selectItems;
}

onSuggest method for City field :

public List onCitySuggest(String searchCityName) {
ArrayList<SelectItem> selectItems = new ArrayList<SelectItem>();

System.out.println(searchCityName);
//get access to the binding context and binding container at runtime
BindingContext bctx = BindingContext.getCurrent();
BindingContainer bindings = bctx.getCurrentBindingsEntry();
//set the bind variable value that is used to filter the View Object
//query of the suggest list. The View Object instance has a View
//Criteria assigned
OperationBinding setVariable = (OperationBinding) bindings.get("setBind_CityName");
setVariable.getParamsMap().put("value", searchCityName);
setVariable.execute();
//the data in the suggest list is queried by a tree binding.
JUCtrlHierBinding hierBinding = (JUCtrlHierBinding) bindings.get("CityView1");


//re-query the list based on the new bind variable values
hierBinding.executeQuery();

//The rangeSet, the list of queries entries, is of type
//JUCtrlValueBndingRef.
List<JUCtrlValueBindingRef> displayDataList = hierBinding.getRangeSet();

for (JUCtrlValueBindingRef displayData : displayDataList){
Row rw = displayData.getRow();
//populate the SelectItem list
selectItems.add(new SelectItem(
(Integer)rw.getAttribute("Id"),
(String)rw.getAttribute("Name")));
}

return selectItems;
}

Once after this, we could bind this to the af:autoSuggestBehavior's suggestedItems property

<af:inputText value="#{bindings.CountryId.inputValue}" label="#{bindings.CountryId.hints.label}"
columns="#{bindings.CountryId.hints.displayWidth}"
autoSubmit="true"
shortDesc="#{bindings.CountryId.hints.tooltip}" id="it3">
<af:autoSuggestBehavior suggestedItems="#{viewScope.AutoSuggestBean.onCountrySuggest}"/>
</af:inputText>
<af:inputText value="#{bindings.StateId.inputValue}" label="#{bindings.StateId.hints.label}"
columns="#{bindings.StateId.hints.displayWidth}"
autoSubmit="true" partialTriggers="it3"
shortDesc="#{bindings.StateId.hints.tooltip}" id="it4">
<af:autoSuggestBehavior suggestedItems="#{viewScope.AutoSuggestBean.onStateSuggest}"/>
</af:inputText>
<af:inputText value="#{bindings.CityId.inputValue}" label="#{bindings.CityId.hints.label}"
columns="#{bindings.CityId.hints.displayWidth}"
autoSubmit="true" partialTriggers="it4"
shortDesc="#{bindings.CityId.hints.tooltip}" id="it5">
<af:autoSuggestBehavior suggestedItems="#{viewScope.AutoSuggestBean.onCitySuggest}"/>
</af:inputText>

Finally, our output would be

output1.jpg

output2.jpg

output3.jpg

Monday Nov 14, 2011

Combo LOV - How to display Description for Selected Value instead of ID

Problem

The common problem that any one working with Combo LOV in a faces page faces is that the list of values component shows Id for selected value inspite of the dropdown showing Description for Options.
Unfortunately, this is a known issue with ADF faces for which there is already an enhancement request which we would expect to see in future releases.

Solution

In this article, I discuss a way to overcome this and display Description rather than Id.
In simple words, The workaround is to define LOV on a transient attribute instead of a View Attribute(Probably a Foreign Key attribute) and set the selected value back to View attribute on change of the LOV and make use of this transient attribute instead of View attribute wherever Combo LOV is needed.

Usecase

I take a simple usecase with well known Emp and Dept tables to display an Emp form with Deptno Combo box List of Values with selected value of LOV being the Department name instead of Deptno
Combo_LOV.png

Here are the detailed steps for the same:

- Add a transient attribute to EmpView with the name "Dname"
Transient_Dname_Attr.png

- Define Combo LOV matching Dname and Deptno both (Deptno matching helps in setting value back to Deptno view attribute on change of Dname LOV value)
- Keep a note of List Data Source name(i.e, DeptViewAccessor in screenshot)
Dname_Combo_LOV.png

- Finally, Set the Department name of respective Deptno as default value to transient attribute(To show current department as selected) using below groovy expression:
Dname_Groovy.png



Now, when you run the page you should be able to see the combo lov showing Department name for the selected value and change in LOV automatically updates Deptno attribute.

Incase, anyone don't like to have groovy, they can overwrite getDname method in RowImpl and add this code there.

Monday May 09, 2011

Simulating dependent LOV using EJB Native Query

This Use case simulates dependent LOV. Consider a case where we need to retrieve employees where Department and Employees entities are associated by One to Many mapping. Here employees table has more number of records, if the entire records are  displayed in single ADF table. It will be difficult for user to search, Filter or traverse to the exact record.

Model Diagram: Employees, Departments table schema.
ModelDiagram.png

Below Employees details page has more number of records, User will not be able to see all the records at the same time on web page. User has to scroll down to find the exact record.

MoreRecords.PNG


Toolbar filters is one of the solution for navigating to the records, Toolbar filter enables the user to filter the rows in a table. Using toolbar filters performance issues can be improved by reducing result set size while loading the page.

We will try to achieve the toolbar filters by simulating dependent LOV using EJb Native Query. From the above Employees Details Page, select the departmentId and jobId as toolbar filters.

First, create entities based on Department, Employees, then create a stateless session bean and data control for the session bean. Add the below code to the session bean and expose the method in local/remote interface and generate a data control for that.

Note:- Here in the below code "em" is a EntityManager.

public List<Employees> EmployeesFilteredResult(Long departmentId,
                                                   String jobId) {
        String queryString = null;
        if (departmentId == null &amp;&amp; jobId == null) {
            queryString =
                    "select * from Employees where department_id like '%' and job_id like '%'";
        } else if (departmentId != null &amp;&amp; jobId == null) {
            queryString =
                    "select * from Employees where department_id = " + departmentId +
                    " and job_id like '%'";
        } else if (departmentId != null &amp;&amp; jobId != null) {
            queryString =
                    "select * from Employees where department_id = " + departmentId +
                    " and job_id = '" + jobId + "'";
        }
        System.out.println(queryString);
        Query genericSearchQuery =
            em.createNativeQuery(queryString, Employees.class);
        return genericSearchQuery.getResultList();
}

public List<String> EmployeesDistinctJobByDept(Long departmentId) {
        List<String> empResultList = new ArrayList<String>();
        try {
            String queryString =
                "select distinct JOB_ID from Employees where department_id = " +
                departmentId;
            System.out.println(queryString);
            Query genericSearchQuery =
                em.createNativeQuery(queryString, "DistinctSearch");
            List resultList = genericSearchQuery.getResultList();
            Iterator resultListIterator = resultList.iterator();
            while (resultListIterator.hasNext()) {
                Object col[] = (Object[])resultListIterator.next();
                empResultList.add((String)col[0]);
            }
            return empResultList;
        } catch (NullPointerException npe) {
            return empResultList;
        }
}

In the ViewController create a file DependentLOV.jspx page, from Data Control palette  and drop Panel Collection component, drop ADF toolbar inside the Panel Collection area, drop Panel Group Layout inside ADF toolbar. From DataControl palette drag and drop departmentsFindAll->Single Selection as ADF Select One Choice, In Edit List Binding select departmentName as Display Attribute.

DeptEditList.PNG

Select the departmentName select one choice, In Property Inspector make AutoSubmit: true, edit ValueChangeListener and create a DependentLOV.java managed bean with scope as "sessionScope"and create new method "selectedDeptIdValue", Open DependentLOV.java and paste the below code.

    private String deptIdValue;
    private String JobIdValue;
    public void setDeptIdValue(String deptIdValue) {
        this.deptIdValue = deptIdValue;
    }

    public String getDeptIdValue() {
        return deptIdValue;
    }

    public void setJobIdValue(String JobIdValue) {
        this.JobIdValue = JobIdValue;
    }

    public String getJobIdValue() {
        return JobIdValue;
    }

Right click on DependentLOV.jspx go to page definition. Create control binding by selecting attributeValues in Insert Item dialog window.

DeptIdListBinding.PNG


From DataControl palette drag and drop EmployeesDistinctJobByDept->return->element as Single Selection->ADF Select One Choice inside ADF toolbar, In Edit Action Binding select the parameter value as #{DependentLOV.deptIdValue}

EmployeesDistinctJobByDeptId.PNG


Select the jobId select one choice, In Property Inspector make AutoSubmit: true and set partialTriggers to departmentName select one choice . Edit ValueChangeListener and create a new method "selectedJobIdValue".

From DataControl palette drag and drop EmployeesFilteredResult->Employees as Table->ADF Read-only Table and select the columns to be displayed. In Edit Action Binding select the parameter as below

EmployeesFilteredValue.PNG


Select the employee table, In Property Inspector set partialTriggers to both departmentName and jobId select one choice. Open DependentLOV.java and overwrite the below code.

public void selectedDeptIdValue(ValueChangeEvent valueChangeEvent) {
        Integer stateIndex = (Integer)valueChangeEvent.getNewValue();
        BindingContainer bindings =
            BindingContext.getCurrent().getCurrentBindingsEntry();
        JUCtrlListBinding listBinding =
            (JUCtrlListBinding)bindings.get("departmentsFindAll");
        Object assignedId = null;
        if (listBinding != null) {
            assignedId =
                    listBinding.getDCIteratorBinding().getRowAtRangeIndex(stateIndex.intValue()).getAttribute("departmentId");
            Long deptId = Long.parseLong(assignedId.toString());
            this.setDeptIdValue(deptId);
        }
        OperationBinding operationBinding =
            bindings.getOperationBinding("EmployeesDistinctJobByDept");
        Object result = operationBinding.execute();
        this.setJobIdValue(null);
    }

    public void selectedJobIdValue(ValueChangeEvent valueChangeEvent) {
        Integer stateIndex = (Integer)valueChangeEvent.getNewValue();
        DCBindingContainer bindings =
            (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
        DCIteratorBinding tableIter =
            bindings.findIteratorBinding("EmployeesDistinctJobByDeptIter");
        RowSetIterator tableRowSetIter = tableIter.getRowSetIterator();
        Object iterRow = null;
        iterRow =
                tableRowSetIter.getRowAtRangeIndex(stateIndex.intValue()).getAttribute("element");
        this.setJobIdValue(iterRow.toString());
} 
Run the DependentLOV.jspx and select the Dept Name from Dept selection box. JobId selection box will be updated with JobId's associated with Dept Name and Employees table will be refreshed with records for the selected Dept Name. On select of both dept name and job id table will be refreshed with the respective records.

TableFilterResult.PNG


About

Tips and Tricks from Oracle's JDeveloper & ADF QA

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today