Thursday Apr 05, 2012

EJB Named Criteria - Apply bind variable in Backingbean

EJB Named criteria are predefined and reusable where-clause definitions that are dynamically applied to a ViewObject query. Here we often use to filter the ViewObject SQL statement query based on Where Clause conditions.

Take a scenario where we need to filter the SQL statements query based on Where Clause conditions, instead of playing with SQL statements use the EJB Named Criteria which is supported by default in ADF and set the Bind Variable parameter at run time.

You can download the sample workspace from here
[Runs with Oracle JDeveloper 11.1.2.0.0 (11g R2) + HR Schema]

Implementation Steps

Create Java EE Web Application with entity based on Employees table, then create a session bean and data control for the session bean.

Open the DataControls.dcx file and create sparse xml for as shown below.

ncbva-dcx.png


In sparse xml navigate to Named criteria tab -> Bind Variable section, create binding variable deptId.

ncbva-bindvariable.png


Now create a named criteria and map the query attributes to the bind variable.

ncbva-namedcriteria.png


In the ViewController create index.jspx page, from data control palette drop employeesFindAll->Named Criteria->EmployeesCriteria->Table as ADF Read-Only Filtered Table and create the backingBean as "IndexBean".

Open the index.jspx page and remove the "filterModel" binding from the table, add <af:inputText />, command button and bind them to backingBean. For command button create the actionListener as "applyEmpCriteria" and add below code to the file.

public void applyEmpCriteria(ActionEvent actionEvent) {
   DCIteratorBinding dc = (DCIteratorBinding)evaluteEL("#{bindings.employeesFindAllIterator}");
   ViewObject vo = dc.getViewObject();
   vo.applyViewCriteria(vo.getViewCriteriaManager().getViewCriteria("EmployeesCriteria"));
   vo.ensureVariableManager().setVariableValue("deptId", this.getDeptId().getValue());
   vo.executeQuery();
}

/**
 * Programmtic evaluation of EL
 *
 * @param el EL to evalaute
 * @return Result of the evalutaion
 */
public Object evaluteEL(String el) {
	FacesContext fctx = FacesContext.getCurrentInstance();
	ELContext elContext = fctx.getELContext();
	Application app = fctx.getApplication();
	ExpressionFactory expFactory = app.getExpressionFactory();
	ValueExpression valExp = expFactory.createValueExpression(elContext, el, Object.class);
	return valExp.getValue(elContext);
}
Run the index.jspx page, enter departmentId value as 90 and click in ApplyEmpCriteria button. Now the bind variable for the Named criteria will be applied at runtime in the backing bean and it will re-execute ViewObject query to filter based on where clause condition.

ncbva-filteredresult.png

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