Wednesday Apr 25, 2012

EJB DataControl - programmatically construct Master-Detail hierarchy

Sometimes, in EJB data control it is necessary to construct Master-Detail relationships across different levels pro-grammatically. One of the most common use cases is construct the master-detail relation based on database tables where tables doesn't have foreign key relationship. So in this article, I'm trying to construct master-detail hierarchy pro-grammatically by taking simple custom_dept, custom_emp tables.

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

Note:- In Bean Data Control, Master-Detail hierarchy can be constructed as a same way but  java bean classes to be created first and then pro-grammatically populate the data in session facade.

Model Diagram:

BMD-Model.png


Note:- Here entities doesn't have any foreign key relationship. DB script for creating the tables and inserting the data required for this application is in application/etc folder.

Implementation Steps

Create Java EE Web Application with entities based on custom_dept and custom_emp tables, then create a session bean and data control for the session bean. Open cusotm_dept entity and create a transient variable called "empCollection" and add the below code.

@Transient
private Collection<CustomEmp> empCollection = new ArrayList();

public void setEmpCollection(Collection<CustomEmp> empCollection) {
	this.empCollection = empCollection;
}

public Collection<CustomEmp> getEmpCollection() {
	return empCollection;
}
Open session facade, add the below code to the session facade and expose the masterDetailFindAll() method in local/remote interface and generate a data control for that.

Note:- Here in the below code "em" is a EntityManager.
public List<CustomDept> masterDetailFindAll() {
String deptQueryString = "select * from custom_dept";
System.out.println(deptQueryString);
Query deptSearchQuery = em.createNativeQuery(deptQueryString, "deptQuery");
List deptResultList = deptSearchQuery.getResultList();
Iterator deptListIterator = deptResultList.iterator();
List<CustomDept> deptList = new ArrayList();
while (deptListIterator.hasNext()) {
	Object deptCol[] = (Object[])deptListIterator.next();
	CustomDept dept = new CustomDept();
	BigDecimal departmentId = (BigDecimal)deptCol[0];
	dept.setDepartmentId(departmentId);
	dept.setDepartmentName((String)deptCol[1]);
	dept.setLocationId((BigDecimal)deptCol[2]);
	String empQueryString =
		"select * from custom_emp emp, custom_dept dept where emp.department_id = dept.department_id and dept.department_id = " +
		departmentId;
	Query empSearchQuery = em.createNativeQuery(empQueryString, "empQuery");
	List empResultList = empSearchQuery.getResultList();
	Iterator empListIterator = empResultList.iterator();
	List<CustomEmp> empList = new ArrayList();
	while (empListIterator.hasNext()) {
		Object empCol[] = (Object[])empListIterator.next();
		CustomEmp emp = new CustomEmp();
		emp.setEmployeeId((BigDecimal)empCol[0]);
		emp.setFirstName((String)empCol[1]);
		emp.setLastName((String)empCol[2]);
		emp.setEmail((String)empCol[3]);
		emp.setJobId((String)empCol[4]);
		emp.setDepartmentId((BigDecimal)empCol[5]);
		empList.add(emp);
	}
	dept.setEmpCollection(empList);
	deptList.add(dept);
 }
  return deptList;
}

In the ViewController create index.jspx page, from data control palette drag and drop masterDetailFindAll()->CustomDept->empCollection->Master-Detail as ADF Master Form, Detail Table.

Run the index.jspx page, now we can traverse through Master-Detail records.

BMD-result.png

Monday Mar 26, 2012

Implement Tree/Details With Taskflow Regions Using EJB

This article describes on Display Tree/Details using taskflow regions.

Use Case Description

Let us take scenario where we need to display Tree/Details, left region contains category hierarchy with items listed in a tree structure (ex:- Region-Countries-Locations-Departments in tree format) and right region contains the Employees list.

In detail, Here User may drills down through categories using a tree until Employees are listed. Clicking the tree node name displays Employee list in the adjacent pane related to particular tree node.

Implementation Steps

The script for creating the tables and inserting the data required for this application

CreateSchema.sql


Lets create a Java EE Web Application with Entities based on Regions, Countries, Locations, Departments and Employees table. Create a Stateless Session Bean and data control for the Stateless 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> empFilteredByTreeNode(String treeNodeType, String paramValue) {
        String queryString = null;
        try {
            if (treeNodeType == "null") {
                queryString = "select * from Employees emp ORDER BY emp.employee_id ASC";
            } else if (Pattern.matches("[a-zA-Z]+[_]+[a-zA-Z]+[_]+[[0-9]+]+", treeNodeType)) {
                queryString = "select * from employees emp INNER JOIN departments dept\n" +
                        "ON emp.department_id = dept.department_id JOIN locations loc\n" +
                        "ON dept.location_id = loc.location_id JOIN countries cont\n" +
                        "ON loc.country_id = cont.country_id JOIN regions reg\n" +
                        "ON cont.region_id = reg.region_id and reg.region_name = '" + paramValue +
                        "' ORDER BY emp.employee_id ASC";
            } else if (treeNodeType.contains("regionsFindAll_bc_countriesList_1")) {
                queryString = "select * from employees emp INNER JOIN departments dept \n" +
                        "ON emp.department_id = dept.department_id JOIN locations loc \n" +
                        "ON dept.location_id = loc.location_id JOIN countries cont \n" +
                        "ON loc.country_id = cont.country_id and cont.country_name = '" + paramValue +
                        "' ORDER BY emp.employee_id ASC";
            } else if (treeNodeType.contains("regionsFindAll_bc_locationsList_1")) {
                queryString =
                        "select * from employees emp INNER JOIN departments dept ON emp.department_id = dept.department_id JOIN locations loc ON dept.location_id = loc.location_id and loc.city = '" +
                        paramValue + "' ORDER BY emp.employee_id ASC";

            } else if (treeNodeType.trim().contains("regionsFindAll_bc_departmentsList_1")) {
                queryString =
                        "select * from Employees emp INNER JOIN Departments dept ON emp.DEPARTMENT_ID = dept.DEPARTMENT_ID and dept.DEPARTMENT_NAME = '" +
                        paramValue + "'";
            }
        } catch (NullPointerException e) {
            System.out.println(e.getMessage());
        }   
        return em.createNativeQuery(queryString, Employees.class).getResultList();
    }

In the ViewController project, create two ADF taskflow with page Fragments and name them as FirstTaskflow and SecondTaskflow respectively. Open FirstTaskflow,from component palette drop view(Page Fragment) name it as TreeList.jsff. Open SeconfTaskflow, from component palette drop view(Page Fragment) name it as EmpList.jsff and create two paramters in its overview parameters tab as shown in below image.
TaskflowParameters.png

Open TreeList.jsff , from data control palette drop regionsFindAll->Tree as ADF Tree. In Edit Tree Binding dialog, for Tree Level Rules select the display attributes as follows:-
model.Regions - regionName
model.Countries - countryName
model.Locations - city
model.Departments - departmentName

EditTreeBinding.png


In structure panel, click on af:Tree - t1 and select selectionListener with edit property. Create a "TreeBean" managed bean with scope as "session" as shown in below Image.

TreeBean.png


Create new method as getTreeNodeSelectedValue and click ok.

getTreeNodeSelectedValue.png


Open TreeBean managed bean and add the below code:

private String treeNodeType;
private String paramValue;

public void getTreeNodeSelectedValue(SelectionEvent selectionEvent) {
        RichTree tree = (RichTree)selectionEvent.getSource();
        RowKeySet addedSet = selectionEvent.getAddedSet();
        Iterator i = addedSet.iterator();
        TreeModel model = (TreeModel)tree.getValue();
        model.setRowKey(i.next());
        JUCtrlHierNodeBinding node = (JUCtrlHierNodeBinding)tree.getRowData();
        //oracle.jbo.Row
        Row rw = node.getRow();
        Object selectedTreeNode = node.getAttribute(0);

        Object treeListType = node.getBindings();
        String treeNodeType = treeListType.toString();

        this.setParamValue(selectedTreeNode.toString());
        this.setTreeNodeType(treeNodeType);
    }

    public void setTreeNodeType(String treeNodeType) {
        this.treeNodeType = treeNodeType;
    }

    public String getTreeNodeType() {
        return treeNodeType;
    }

    public void setParamValue(String paramValue) {
        this.paramValue = paramValue;
    }

    public String getParamValue() {
        return paramValue;
    }<br />
Open EmpList.jsff , from data control palette drop empFilteredByTreeNode->Employees->Table as ADF Read-only Table. After selecting the  Employees result set, in Edit Action Binding dialog window pass the pageFlowScope parameters as shown in below Image.

EmpEditActionBinding.png


In empList.jsff page, click Binding tab and click on Create Executable binding and select Invoke action and follow as shown in below image.

executeEmpFiltered.png



Edit executeEmpFiltered invoke action properties and set the Refresh to ifNeeded, So when ever the page needs the method will be executed.

executeEmpFilteredInvoke.png


Create Main.jspx page with page template as Oracle Three Column Layout. Drop FirstTaskflow as Region in start facet and drop SecondTaskflow as Region in center facet, Edit task Flow Binding dialog window pass the Input Paramters as shown in below Image.

EditTaskFlowBinding.png


Run the Main.jspx, tree will be displayed in left region and emp details will displyaed on the right region. Click on the Americas in tree node, all emp related to the Americas related will be displayed.

ResultNew.png


Click on Americas->United States of America->South San Francisco->Accounting, only employee belongs to the Accounting department will be displayed.

SelectedResult.png

Tuesday Mar 20, 2012

Achieve Named Criteria with multiple tables in EJB Data control

In EJB create a named criteria using sparse xml and in named criteria wizard, only attributes related to the that particular entities will be displayed.  So here we can filter results only on particular entity bean.

Take a scenario where we need to create Named Criteria based on multiple tables using EJB. In BC4J we can achieve this by creating view object based on multiple tables. So in this article, we will try to achieve named criteria based on multiple tables using EJB.

Implementation Steps

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

Create a Java Bean, name as CustomBean and add below code to the file. Here in java bean from both Departments and Employees tables three fields are taken.

public class CustomBean {
    private BigDecimal departmentId;
    private String departmentName;
    private BigDecimal locationId;
    private BigDecimal employeeId;
    private String firstName;
    private String lastName;

    public CustomBean() {
      super();
    }

    public void setDepartmentId(BigDecimal departmentId) {
        this.departmentId = departmentId;
    }

    public BigDecimal getDepartmentId() {
        return departmentId;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setLocationId(BigDecimal locationId) {
        this.locationId = locationId;
    }

    public BigDecimal getLocationId() {
        return locationId;
    }

    public void setEmployeeId(BigDecimal employeeId) {
        this.employeeId = employeeId;
    }

    public BigDecimal getEmployeeId() {
        return employeeId;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getLastName() {
        return lastName;
    }
}


Open the sessionEJb file and 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<CustomBean> getCustomBeanFindAll() {
     String queryString =
            "select d.department_id, d.department_name, d.location_id, e.employee_id, e.first_name, e.last_name from departments d, employees e\n" +
            "where e.department_id = d.department_id";
     Query genericSearchQuery = em.createNativeQuery(queryString, "CustomQuery");
     List resultList = genericSearchQuery.getResultList();
     Iterator resultListIterator = resultList.iterator();
     List<CustomBean> customList = new ArrayList();
     while (resultListIterator.hasNext()) {
         Object col[] = (Object[])resultListIterator.next();
         CustomBean custom = new CustomBean();
         custom.setDepartmentId((BigDecimal)col[0]);
         custom.setDepartmentName((String)col[1]);
         custom.setLocationId((BigDecimal)col[2]);
         custom.setEmployeeId((BigDecimal)col[3]);
         custom.setFirstName((String)col[4]);
         custom.setLastName((String)col[5]);
         customList.add(custom);
     }
     return customList;
}

Open the DataControls.dcx file and create sparse xml for customBean. In sparse xml navigate to Named criteria tab -> Bind Variable section, create two binding variables deptId,fName.

BindVariables.png


In sparse xml navigate to Named criteria tab ->Named criteria, create a named criteria and map the query attributes to the bind variables.

CustomBeanCriteria.png


In the ViewController create a file jspx page, from data control palette drop customBeanFindAll->Named Criteria->CustomBeanCriteria->Query as ADF Query Panel with Table. Run the jspx page and enter values in search form with departmentId as 50 and firstName as "M". Named criteria will filter the query of a data source and display the result like below.

NamedCriteriaResult.png

Thursday Jul 28, 2011

Custom Table Pagination Using EJB Native Query

Let us take scenario where the table has more records. 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 navigate or traverse to the exact record. This can be achieved by implementing pagination, Pagination is an important aspect when displaying large number of records. This blog would be of help if you are building applications that render large number of records in a table. With pagination, the number of records displayed can be controlled into several manageable chunks, thus making it easy to locate the records of interest.

Model Diagram:
Employees.png

Here in the above model diagram, Employees table schema.

Let us consider the above Employees table has more number of records, User will not be able to see all the records at the same time on web page.
For ex:- Employees Details Page

Employee More Record.png


We use the af:iterator tag to implement the custom table with pagination. This tag renders a collection in the same fashion as the af:table tag does. Same as af:table tag, af:iterator can be based on table binding available in page definition. It iterates over data collection and renders data rows.

First, create entities based on  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.

 /**
  * Returns list of employee list starting at the given first index with the given max row count.
  * @return list of  employee list starting at the given first index with the given max row count.
  */
   public List<Employees> employeesByLimit(int firstRow, int maxRow) {
        String queryString = "select * from Employees order by employee_id ASC";
        return em.createNativeQuery(queryString,
                                    Employees.class).setMaxResults(maxRow).setFirstResult(firstRow).getResultList();
   }

 /**
  * Returns total amount of rows in table.
  * @return Total amount of rows in table.
  */
   public int employeesTotalRows() {
        String queryString = "select * from Employees order by employee_id ASC";
        Query query = em.createNativeQuery(queryString);
        List results = query.getResultList();
        return results.size();
   }

In the ViewController create a file CustomPagination.jspx page, right click and "Go to Page Definition", CustomPaginationPageDef.xml file will be created. 

Create a CustomPagination managed bean with scope as "sessionScope" add the below code:

    private int firstRow = 0;
    private int rowsPerPage = 10;
    private int totalRows;
    private int totalPages;
    private int currentPage = 1;

    public CustomPagination() {
        this.loadList();
    }

    public void loadList() {
        /**
         * Returns total amount of rows in table.
         * @return Total amount of rows in table.
         */
        BindingContainer bindings = BindingContext.getCurrent().getCurrentBindingsEntry();
        AttributeBinding attr = (AttributeBinding)bindings.getControlBinding("EmployeesTotalRowCount");
        String val = attr.getInputValue().toString();
        int rows = Integer.parseInt(val);
        this.setTotalRows(rows);

        double val1 = ((double)this.getTotalRows() / this.getRowsPerPage());
        int totalPagesCal = (int)Math.ceil(val1);
        this.setTotalPages((totalPagesCal != 0) ? totalPagesCal : 1);

    }

    public void firstActionListener(ActionEvent actionEvent) {
        this.setCurrentPage(1);
        this.setFirstRow(0);
    }

    public void previousActionListener(ActionEvent actionEvent) {
        this.setCurrentPage(this.getCurrentPage() - 1);
        this.setFirstRow(this.getFirstRow() - this.getRowsPerPage());
    }

    public void nextActionListener(ActionEvent actionEvent) {
        this.setCurrentPage(this.getCurrentPage() + 1);
        this.setFirstRow(this.getFirstRow() + this.getRowsPerPage());

    }

    public void lastActionListener(ActionEvent actionEvent) {
        this.setCurrentPage(this.getTotalPages());
        this.setFirstRow(this.getTotalRows() -
                         ((this.getTotalRows() % this.getRowsPerPage() != 0) ? this.getTotalRows() %
                          this.getRowsPerPage() : this.getRowsPerPage()));
    }

    public boolean isBeforeDisabled() {
        return this.getFirstRow() == 0;
    }

    public boolean isAfterDisabled() {
        return this.getFirstRow() >= this.getTotalRows() - this.getRowsPerPage();
    }

    public void setFirstRow(int firstRow) {
        this.firstRow = firstRow;
    }

    public int getFirstRow() {
        return firstRow;
    }

    public void setRowsPerPage(int rowsPerPage) {
        this.rowsPerPage = rowsPerPage;
    }

    public int getRowsPerPage() {
        return rowsPerPage;
    }

    public void setTotalRows(int totalRows) {
        this.totalRows = totalRows;
    }

    public int getTotalRows() {
        return totalRows;
    }

    public void setTotalPages(int totalPages) {
        this.totalPages = totalPages;
    }

    public int getTotalPages() {
        return totalPages;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getCurrentPage() {
        return currentPage;
    }

Open CustomPagination.jspx, click on Binding tab and and click on Create control binding and select methodAction for employeesTotalRows as shown in below image.


EmployeesTotalRows_ActionBinding.png


Open CustomPaginationPageDef.xml and add the below code snippet inside "variableIterator" tag.

<variable Type="int" Name="employeesTotalRows_Return" IsQueriable="false" IsUpdateable="0" DefaultValue="${bindings.employeesTotalRows.result}"/>
Open CustomPagination.jspx,  click on Binding tab and click on Create control binding and select attributeValues and create attribute binding for employeesTotalRows_Return and in Property Inspector change the id to "EmployeesTotalRowCount"

EmployeesTotalRows_AttributeBinding.png


Click on Create control binding and select methodAction for employeesByLimit as shown in below image

EmpBinding.png


Create Tree binding for control of Employees result set.

EmpEditTreeBinding.png


Click on Create Executable binding and select Invoke action and follow as shown in below image.

InvokeAction.png


Edit TotalRows invoke actiion and set the Refresh to prepareModel, so when ever page loads employeesTotalRows method will get executed.


prepareModel.png


Go to CustomPagination.jspx Source tab and copy the below code snippet. As mentioned above af:iterator tag to implement the custom table with pagination.

<af:group id="g1">
	<af:panelGroupLayout id="pgl1" layout="scroll">
		<af:spacer width="10" height="10" id="s16"/>
		<af:panelGroupLayout id="pgl9" layout="horizontal">
			<af:spacer width="10" height="10" id="s9"/>
			<af:panelGroupLayout id="pgl10" inlineStyle="width:75px;" layout="horizontal">
				<af:outputText value="Employeed Id" id="ot1" inlineStyle="font-weight:bold;"/>
			</af:panelGroupLayout>
			<af:spacer width="10" height="10" id="s7"/>
			<af:panelGroupLayout id="pgl7" inlineStyle="width:75px;" layout="horizontal">
				<af:outputText value="First Name" id="ot6" inlineStyle="font-weight:bold;"/>
			</af:panelGroupLayout>
			<af:spacer width="10" height="10" id="s10"/>
			<af:panelGroupLayout id="pgl11" inlineStyle="width:75px;" layout="horizontal">
				<af:outputText value="Last Name" id="ot4" inlineStyle="font-weight:bold;"/>
			</af:panelGroupLayout>
			<af:spacer width="10" height="10" id="s11"/>
			<af:panelGroupLayout id="pgl12" inlineStyle="width:75px;" layout="horizontal">
				<af:outputText value="Email" id="ot7" inlineStyle="font-weight:bold;"/>
			</af:panelGroupLayout>
			<af:spacer width="10" height="10" id="s12"/>
			<af:panelGroupLayout id="pgl15" inlineStyle="width:75px;" layout="horizontal">
				<af:outputText value="Salary" id="ot10" inlineStyle="font-weight:bold;"/>
			</af:panelGroupLayout>
		</af:panelGroupLayout>
		<af:separator id="s15"/>
		<af:spacer width="10" height="10" id="s2"/>
		<af:iterator id="i1" value="#{bindings.result.collectionModel}" var="row">
			<af:panelGroupLayout id="pgl2" layout="horizontal">
				<af:spacer width="10" height="10" id="s3"/>
				<af:panelGroupLayout id="pgl3" layout="horizontal" inlineStyle="width:75px;">
					<af:outputText value="#{row.employeeId}" id="ot8"/>
				</af:panelGroupLayout>
				<af:spacer width="10" height="10" id="s13"/>
				<af:panelGroupLayout id="pgl13" layout="horizontal" inlineStyle="width:75px;">
					<af:outputText value="#{row.firstName}" id="ot11"/>
				</af:panelGroupLayout>
				<af:spacer width="10" height="10" id="s4"/>
				<af:panelGroupLayout id="pgl4" layout="horizontal" inlineStyle="width:75px;">
					<af:outputText value="#{row.lastName}" id="ot9"/>
				</af:panelGroupLayout>
				<af:spacer width="10" height="10" id="s6"/>
				<af:panelGroupLayout id="pgl5" layout="horizontal" inlineStyle="width:75px;">
					<af:outputText value="#{row.email}" id="ot2"/>
				</af:panelGroupLayout>
				<af:spacer width="10" height="10" id="s8"/>
				<af:panelGroupLayout id="pgl8" inlineStyle="width:75px;" layout="horizontal">
					<af:outputText value="#{row.salary}" id="ot3"/>
				</af:panelGroupLayout>
			</af:panelGroupLayout>
			<af:spacer width="10" height="10" id="s1"/>
		</af:iterator>
		<af:panelGroupLayout id="pgl6">
			<af:commandButton text="First" id="cb1"
							  actionListener="#{CustomPagination.firstActionListener}"
							  partialTriggers="i1" disabled="#{CustomPagination.beforeDisabled}"/>
			<af:commandButton text="Prev" id="cb2"
							  actionListener="#{CustomPagination.previousActionListener}"
							  partialTriggers="i1" disabled="#{CustomPagination.beforeDisabled}"/>
			<af:commandButton text="Next" id="cb3"
							  actionListener="#{CustomPagination.nextActionListener}"
							  partialTriggers="i1" disabled="#{CustomPagination.afterDisabled}"/>
			<af:commandButton text="Last" id="cb4"
							  actionListener="#{CustomPagination.lastActionListener}"
							  partialTriggers="i1" disabled="#{CustomPagination.afterDisabled}"/>
			<af:spacer width="10" height="10" id="s5"/>
			<af:outputText value="Page #{CustomPagination.currentPage} / #{CustomPagination.totalPages}"
						   id="ot5"/>
		</af:panelGroupLayout>
	</af:panelGroupLayout>
</af:group>

Run the CustomPagination.jspx, Now It always displays 10 rows (configurable) in the CustomPagination.java page. The page provides buttons to navigate between pages and shows current page number. If user is moves to second or third page, navigation buttons for

previous page will be enabled, If we navigate to the last page, navigation buttons for next navigation become disabled and If we navigate to the first page, First and Prev buttons should be disabled.

FinalResult.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