Wednesday Aug 03, 2011

Passing parameter to EJB Method in taskflow

This article describes on passing parameter to EJB Method in taskflow.

Use Case Description

Suppose we have a table displaying Dept records.Table also has column link & clicking on this link displays a region in popup.This region accepts deptno parameter,executes EJB Method & displays respective Emp details in a popup.

Implementation steps

Let us suppose that we have created Java EE Web Application with Entities from Emp & Dept table .Also add 'ADF Faces Components 11' under ViewController->project properties-> JSP Tag Libraries.

Edit Emp.java & create a custom named query that returns Emp Object for the respective deptno :


 @NamedQuery(name = "findEmpByDeptno", query = "select o from Emp o
 where o.dept.deptno = :deptNo")
Let us create Stateful Session Bean and expose the Named Query through the Session Facade.Our use case deals with passing parameters to EJB method in taskflow.So we will define simple EJB method that just accepts deptno parameter & stores its value in private variable:
   
 public void passDeptno(BigDecimal deptNo) 
 {
 this.setDepartNo(deptNo);
 }

Now that we have separate method to accept parameter we will modify findEmpByDeptno namedQuery as below:

 public List<Emp> findEmpByDeptno() 
{
 
 return em.createNamedQuery("findEmpByDeptno").setParameter
 ("deptNo", this.getDepartNo()).getResultList();
}

Generate Data control for the above Session Bean & expose the above method through the Session Facade.

Create a ADF taskflow with page Fragments & create an inputParameter 'inputParameter1' in its overview parameters tab:

adf_taskflow.JPG










taskflow_param.JPG
Drop passDeptno(BigDecimal) as Method Call activity with value as
#{pageFlowScope.inputParameter1}.Drop findEmpByDeptno()->Operations->execute operation as Method Call activity & then drop  emp view activity on the taskflow.Define all control flows.

taskflow2.jpeg
Create emp.jsff page fragment & drop findEmpByDeptno()->Emp as ADF Table with single row selection

emp_tab.JPG


Now we will create main.jspx page & drop deptFindAll as ADF table with single row selection on this.We will also create deptno attribute bindings in page definition file:

attribute.JPG
Insert a new column inside the table that displays Emp Details.Drop the above created taskflow as region inside the column.Pass '#{sessionScope.deptno}' value for inputParameter1 & set the taskflow's ifNedded property to 'Refresh'.Here we are using setPropertyListener to pass parameter to taskflow method:

<af:column id="c4" headerText="Emp details">
<af:commandLink text="Emp details" id="cl1">
<af:showPopupBehavior popupId="p1"/>
</af:commandLink>
<af:popup id="p1" contentDelivery="lazyUncached">
<af:dialog id="d2" title="Emp Details" type="none">
<af:region
 value="#{bindings.taskflowdefinition1.regionModel}"
id="r1"/>
</af:dialog>
<af:setPropertyListener type="popupFetch"
from="#{bindings.deptno.inputValue}"
to="#{sessionScope.deptno}"/>
</af:popup>
</af:column>

Run the page,it shows Dept records.Select any dept record & click on 'Emp Details' link.It shows popup with its respective Emp records


Monday May 09, 2011

Native Query using the SQL 'IN' clause

Use-Case Description:

Consider a case where we need to retrieve employees where Department and Employees entities are associated by One to Many mapping, the retrieval is using complex queries with a complex result set.

Ex: JPQL with "IN clause" where we need to pass series of values in one parameter, say list type. As the Java Persistence Query Language does not support passing a list of items as an input/named parameter, we are limited by the JPQL language to use "IN clause" in named queries.

Here is one solution to overcome this limitation, this can be achieved by creating native SQL queries to run complex queries and also handle complex result sets.

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: Parameters in the example below are hard coded.

The code below describes one way of creating a native query and defining a result set that can map to an entity. Notice that the result set is mapped to the Employees entity class.

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

 public List<Employees> NativeQuery() {
        String queryString =
            "select * from Employees where department_id in (10, 20, 40, 50) order by department_id ASC";
        return em.createNativeQuery(queryString,
                                    Employees.class).getResultList();
    }

Here is another way of creating a native query using custom code to generate a custom result set.

 public List<Employees> NativeQueryCustomCode() {
        String queryString =
            "select * from Employees where department_id in (10, 20, 40, 50) order by department_id ASC";
        Query genericSearchQuery =
            em.createNativeQuery(queryString, "InQuery");
        List resultList = genericSearchQuery.getResultList();
        Iterator resultListIterator = resultList.iterator();
        List<Employees> employeesList = new ArrayList();
        while (resultListIterator.hasNext()) {
            Object col[] = (Object[])resultListIterator.next();
            Employees employees = new Employees();
            BigDecimal employeeId = (BigDecimal)col[0];
            employees.setEmployeeId(employeeId.longValue());
            employees.setFirstName((String)col[1]);
            employees.setLastName((String)col[2]);
            employees.setEmail((String)col[3]);
            employees.setPhoneNumber((String)col[4]);
            employees.setJobId((String)col[6]);
            BigDecimal salary = (BigDecimal)col[7];
            employees.setSalary(salary.doubleValue());
            employees.setCommissionPct((Double)col[8]);

            Departments departments = new Departments();
            BigDecimal departmentId = (BigDecimal)col[10];
            departments.setDepartmentId(departmentId.longValue());
            employees.setDepartments(departments);

            employeesList.add(employees);
        }
        return employeesList;
}


In the ViewController create a file NativeQuery.jspx, from the DataControl palette drag and drop NativeQuery->Employees as ADF Read-only Table and select the columns to be displayed.

Run NativeQuery.jspx, Employees who belong to the departments with Id (10,20,30,40) should only be displayed. 

NativeQuery.PNG

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