Wednesday Jul 31, 2013

EJB DC - Using JPQL constructor expressions

In JPA 2.0, a new feature is provided - "Constructor Expressions in the SELECT Clause". This feature is mainly useful for queries with multiple Select expressions, where custom result objects is necessary. This feature works with Oracle JDeveloper 12.1.2.0.0

Implementation Steps

Create Fusion Web Application with entities based on Departments and Employees, then create a session bean.

Create a Java class "DeptAndEmp" and add the below code.

public class DeptAndEmp implements Serializable {
    private String departmentName;
    private String email;
    private String firstName;
    private String lastName;
    public DeptAndEmp() {
        super();
    }

    public DeptAndEmp(String departmentName, String email, 
		String firstName, String lastName) {
        this.setDepartmentName(departmentName);
        this.setEmail(email);
        this.setFirstName(firstName);
        this.setLastName(lastName);
    }

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

    public String getDepartmentName() {
        return departmentName;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getEmail() {
        return email;
    }

    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 session bean and paste the below method code and expose the method filterDeptEmpResult() method in local/remote interface.

public List<DeptAndEmp> filterDeptEmpResult() { String qlString = "SELECT NEW model.util.DeptAndEmp(dept.departmentName, emp.email, emp.firstName, emp.lastName) FROM Employees emp, Departments dept where emp.departments.departmentId = dept.departmentId"; TypedQuery<DeptAndEmp> query = em.createQuery(qlString, DeptAndEmp.class); return query.getResultList(); }

Note:- In the Query "model is a package name and TypedQuery is a JPA query that returns a specific type of Object. TypedQuery eliminates the need to cast the query result to a specific type or having to add a SuppressWarnings annotation to eliminate compiler warnings about unchecked conversions. TypedQuery is well explained in Java Persistence Architecture 2.0 - Using The New TypedQuery Interface

Create a Sample Java Client and add the below code in main method.

SessionEJB sessionEJB = (SessionEJB) context.lookup("JPAConstructorApp-Model-SessionEJB#model.SessionEJB");

for (DeptAndEmp deptAndEmp : (List<DeptAndEmp>) sessionEJB.filterDeptEmpResult()) { System.out.println("departmentName = " + deptAndEmp.getDepartmentName()); System.out.println("email = " + deptAndEmp.getEmail()); System.out.println("firstName = " + deptAndEmp.getFirstName()); System.out.println("lastName = " + deptAndEmp.getLastName()); System.out.println("======================================="); }

Deploy the sessionFacade and run the java client. In the console employees details will be displayed as below.

Add-Edit multiple rows using EJB DC

Let us take a scenario where in users wants to add/edit multiple records in the ADF table,  earlier with stateless session bean we need have work around to achieve this scenario.

This scenario can be achieved using stateful session bean, the application-managed transaction model for EJB/POJO data controls which additionally maintains a cache of managed entities and using commit operations user will be able to add/edit multiple records.

Implementation Steps

Create Java EE Web Application with entity based on Dept(sequences enabled on deptId), then create a stateful session bean with Transaction Type as "CMT with Explicit Commit" and data control for the session bean.  "CMT with Explicit Commit" supported in Oracle JDeveloper 12.1.2.0.0

In View controller project, create jspx page. Drop deptFindAll->Table/List View as ADF Table with  multi-selection option enabled.



Run the jspx page (i.e the web page look something like the image shown below). Notice here the commit button will be disabled. Once user click on the create button, commit button will get enabled.



Here we will create two records, so clicking the create button twice. Enter the dept details and click on commit button to save the records. You can also configure @SequenceGenerator/@TableGenerator to auto generate the DeptNo.


Result page should contains newly added records.

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

Thursday Apr 19, 2012

Display Lookup values from related ejb entities using JOIN FETCH

JOIN FETCH - The purpose of JOIN FETCH is to fetch the related objects from the database in a single query. So in this article, I'm trying to explain how can we use jpql JOIN FETCH and expose those attributes to EJB data control layer.

Take a scenario, where we need to build ADF tree based on departments,employees and location tables. While displaying the tree, root node should display departmentName along with city attribute which is stored in related object Location table.

Model Diagram:

EJBJFA-Model.png


In BC4J, this scenario can be implemented using Entity Objects facility provided in View Object layer. These entity objects are used by the view object for accessing the related objects attributes and will be exposed in data control layer automatically.

Same behavior can be implemented in EJB using JOIN FETCH. Using this query improves the efficiency of iteration over the result Departments objects because it eliminates the need for retrieving the associated Location objects separately.So in single query related objects attributes is also fetched from the database.

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 entities based on Departments, Employees and Location table, then create a session bean and data control for the session bean.

Open the departments entity and alter the named query as below.

@NamedQuery(name = "Departments.findAll",
                             query = "select o from Departments o join fetch o.locations")

Note:- The query above returns Departments instances and guarantees that the locations attributes will already be fetched in the returned instances.

Now we need to expose the location attribute values to data control, create a Transient variable called "city" and add the below code in department entity.

@Transient
 private String city;
	
public String getCity() {
  return this.locations.getCity();
}

Note: Can create sample java client to check whether the city attribute value is coming in departments instance before proceeding to data control.

In the ViewController create index.jspx page, from data control palette drop departmentsFindAll->Tree as ADF Tree and in edit tree bindings select the attributes as shown in below image.

EJBJFA-DeptBinding.png


Run the index.jspx page. Now notice root node will display departmentName along with city attribute value also.

EJBJFA-output.png

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

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 Sep 15, 2011

AutoSuggest behavior In ADF Using EJB

AutoSuggest feature somewhat expected feature, nowadays that most of the top sites have implemented this functionality. This feature makes your site as user friendly and easy to navigate in inputText feature.

AutoSuggest behavior in ADF adds a pull-down menu of suggested values to a text field. The user can either click directly on a suggestion to enter it into the field, or navigate the list using the up and down arrow keys, selecting a value using the enter key.

Lets create a Java EE Web Application with Entities based on Departments, edit the Departments.java entity and add the below code.

@NamedQuery(name = "Departments.filteredValues",
            query = "select o from Departments o where o.departmentName like CONCAT(:deptName,'%')

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.

/** select o from Departments o where o.departmentName like CONCAT(:deptName,'%') */
public List<String> getDepartmentsFilteredValues(String deptName) {
   //To store the resultset
   List<String> deptNameResultset = new ArrayList<String>();
   Query query = em.createNamedQuery("Departments.filteredValues").setParameter("deptName", deptName);
   Vector result = (Vector)query.getResultList();
   int resultSize = result.size();
   for (int i = 0; i > resultSize; i++) {
      Departments dept = (Departments)result.get(i);
      deptNameResultset.add(dept.getDepartmentName());
   }
   return deptNameResultset;
}
In the ViewController create a file AutoSuggest.jspx page, from component palette drag and drop <af:inputText/> and in PI palette change the label to Dept Name. Add the autoSuggestBehavior tag to the inputText. Click on the autoSuggestBehavior, in  PI palette click on Edit property for Suggested Items and create a "AutoSuggest" managed bean with scope as "request" as shown in below Image.

AutoSuggestBean.png


Create new method as deptNameResultList and click ok.

AutoSuggestBeanMethod.png


In AutoSuggest.jspx page, go to binding tab and click create binding by selecting methodAction and click ok with parameter blank as shown in below image

FilteredValuesBinding.png


Open AutoSuggest.java managed bean and paste the below code.

public List deptNameResultList(String paramValue) {
  //Store the deptName result set
  List<SelectItem> deptResultList = new ArrayList<SelectItem>();
  //Filter the values using Items List
  List<SelectItem> items = new ArrayList<SelectItem>();
        
  BindingContainer bindings = getBindings();
  //Execute the Method
  OperationBinding operationBinding = bindings.getOperationBinding("getDepartmentsFilteredValues");
  //Populate the deptName parameter 
  operationBinding.getParamsMap().put("deptName", paramValue);
  operationBinding.execute();
  if (operationBinding.getResult() != null) {
      operationBinding.getResult();
      ArrayList result = (ArrayList)operationBinding.getResult();
      int resultSize = result.size();
      for (int i = 0; i < resultSize; i++) {
         deptResultList.add(new SelectItem(result.get(i)));
      }
   }
   for (SelectItem item : deptResultList) {
     if (item.getLabel().startsWith(paramValue)) {
          items.add(item);
     }
   }
  return items;
}

public BindingContainer getBindings() {
   return BindingContext.getCurrent().getCurrentBindingsEntry();
}
Run AutoSuggest.jspx, Dept Name text field will be displayed. As soon as the user has typed a character, a filtered list of suggested values is presented( for ex: C), Now traverse the list by using up and down arrow and select a suggested value from the list and thus applying that value to the inputText component.
 
AutoSuggestResult.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


Tuesday Jul 26, 2011

Using Message-Driven Bean and JMS Queue with ADF

EJB3.0 Message-Driven Bean asynchronously listens to JMS Queue/Topic and processes the messages from its clients.
In this article we will see how Message-Driven Bean listens to JMS Queue and process the message sent to Queue by a client, here I will be using ADF jspx page as our client.
This article will be split into two Sections. First Section, is for creating JMS Queue from WLS console, this is to help those who are not familiar in managing JMS from WLS console. Second Section, shows how to send a message from ADF client (jspx page) to JMS Queue and how to make Message-Driven Bean listen to the JMS Queue.

Section 1: Creating JMS Queue from WLS console

  • You need to have Integrated or Standalone WLS instance up and running.
  • FileSystem type Persistence Store has to be created to store JMS messages.
MessageDrivenBean-JMSQueue_CreatePersistenceStore1.JPG


MessageDrivenBean-JMSQueue_CreatePersistenceStore2.JPG


  • JMS Server is required to manage JMS Queues targeted at it.
MessageDrivenBean-JMSQueue_CreateJMSServer1.JPG


  • Use File Store create above as Persistence Store.
MessageDrivenBean-JMSQueue_CreateJMSServer2.JPG


  • Set 'DefaultServer' (for Integrated WLS) or 'AdminServer' (for Standalone WLS) as its Target Server.
MessageDrivenBean-JMSQueue_CreateJMSServer_TargetServer.JPG


  • JMS System Module is required to hold the queues.

MessageDrivenBean-JMSQueue_CreateJMSModule1.JPG


MessageDrivenBean-JMSQueue_CreateJMSModule2.JPG

  • Target JMS Module to server on which JMS Server is target.
MessageDrivenBean-JMSQueue_CreateJMSModule3.JPG
MessageDrivenBean-JMSQueue_CreateJMSModule3.JPG


MessageDrivenBean-JMSQueue_CreateJMSModule4.JPG

  • Check 'Would you like to add resources to this JMS system module' to include JMS queue to this newly created JMS Module.
MessageDrivenBean-JMSQueue_CreateJMSModule4.JPG


  • Create Sub Deployment and target it to JMS Server.

MessageDrivenBean-JMSQueue_SubDeployment1.JPG


MessageDrivenBean-JMSQueue_SubDeployment2.JPG


MessageDrivenBean-JMSQueue_SubDeployment3.JPG


  • Under Configurations tab click New button to create Connection Factories, Queues resources.
MessageDrivenBean-JMSQueue_CreateJMSModule_Config.JPG


  • Create JMS Connection Factory.
MessageDrivenBean-JMSQueue_ConnectionFactory1.JPG


MessageDrivenBean-JMSQueue_ConnectionFactory2.JPG


  • Now Target the Connection Factory to Sub Deployment created above.
MessageDrivenBean-JMSQueue_ConnectionFactory3.JPG


MessageDrivenBean-JMSQueue_ConnectionFactory4.JPG


  • Similarly create a Queue and target it to Sub Deployment.

MessageDrivenBean-JMSQueue_CreateJMSModule_Queue.JPG


MessageDrivenBean-JMSQueue_Queue1.JPG


MessageDrivenBean-JMSQueue_Queue2.JPG


  • With this we have configured WLS to handle JMS Queue.

Section 2: Send message from ADF client to JMS Queue and use Message-Driven Bean listen to the JMS Queue.

  • In a Custom Application Create Message-Driven Bean
MessageDrivenBean-JMSQueue_CreateMDBWizard.JPG


  • onMessage() of Message-Driven Bean is called by container when it receives message from a client.
  • onMessage() has to be modified accordingly to process the received message.

public void onMessage(Message message) {
  try{
    String sub= message.getStringProperty("subject");
    String mess= message.getStringProperty("message");
    System.out.println("Subject = " + sub);
    System.out.println("Message = " + mess);
   } catch(Exception e){
      e.printStackTrace();
  }
}

  • Create jspx page in View Controller project.
  • Drop two Input Texts from component palette to jspx page for Subject and Message.
  • Drop Command Button from component palette to jspx page.
MessageDrivenBean-JMSQueue_sendMessagePage.JPG


  • Bind Subject, Message input text to backing bean.
  • Create Action binding for send command button.
  • Add below code to send message to JMS queue.


QueueConnectionFactory connectionFactory;
try {
  connectionFactory = (QueueConnectionFactory)new InitialContext().lookup("jms.MDBConnectionFactory1");
  QueueConnection connection = connectionFactory.createQueueConnection();
  connection.start();
  QueueSession queueSession = connection.createQueueSession(false, Session.AUTO_ACKNOWLEDGE);
  Queue queue = (Queue)new InitialContext().lookup("weblogic.wsee.DefaultQueue");
  QueueSender queueSender = queueSession.createSender(queue);
  Message message = queueSession.createMessage();
  message.setJMSType("logMessage");
  message.setLongProperty("time", System.currentTimeMillis());
  message.setStringProperty("subject", this.getIt1().getValue().toString());
  message.setStringProperty("message", this.getIt2().getValue().toString());
  queueSender.send(message);
  queueSession.close();
  connection.close();
} catch (Exception e) {
  e.printStackTrace();
}

  • Run jspx page
  • Give values to  Subject, Message and hit Send button.
  • onMessage() reads this message prints as Server Log.
MessageDrivenBean-JMSQueue_IntegratedWLSMsg.JPG

Monday Jun 27, 2011

Create named criteria in EJB Data control

This article gives the detailed steps on creating named criteria in EJB Data control.Note that this feature is available in Jdev version 11.1.2.0.0

Use Case Description

Suppose we have defined an EJB Entity Object & we would like to filter the Entity object based on some criteria,then this filtering can be achieved by creating named criteria in EJB Data Control.

Implementation steps

Let us suppose that we have created Java EE Web Application with Entities from Emp table

EJB_Entities.JPG



Create session bean,generate data control for the same

sessionbean.jpeg







Edit empFindAll in DataControls.dcx file

datacontrols.JPG


Create simple Named Criteria: deptno>=20

ejb_namedcr1.JPG









Create on '+' icon to create Named Criteria:

ejb_namedcr2.JPG
Refresh the Data Controls & create a new jspx page.Drop EmpCriteria as ADF Query Panel with Table

adfquery.JPG




Run the page,click on search button & we will see that Emp table shows filtered records



Wednesday Jun 22, 2011

Call DB Stored Procedure using @NamedStoredProcedureQuery Injection

Oracle Database Stored Procedure can be called from EJB business layer to perform complex DB specific operations. This approach will avoid overhead from frequent network hits which could impact end-user result. DB Stored Procedure can be invoked from EJB Session Bean business logic using org.eclipse.persistence.queries.StoredProcedureCall API. Using this approach requires more coding to handle the Session and Arguments of the Stored Procedure, thereby increasing effort on maintenance. EJB 3.0 introduces @NamedStoredProcedureQuery Injection to call Database Stored Procedure as NamedQueries.

This blog will take you through the steps to call Oracle Database Stored Procedure using @NamedStoredProcedureQuery.

  • EMP_SAL_INCREMENT procedure available in HR schema will be used in this sample.
  • Create Entity from EMPLOYEES table.
  • Add @NamedStoredProcedureQuery above @NamedQueries to Employees.java with definition as given below -


@NamedStoredProcedureQuery(name="Employees.increaseEmpSal", procedureName = "EMP_SAL_INCREMENT", 
                          resultClass=void.class, resultSetMapping = "", returnsResultSet = false, parameters = {
  @StoredProcedureParameter(name = "EMP_ID", queryParameter = "EMPID"),
  @StoredProcedureParameter(name = "SAL_INCR", queryParameter = "SALINCR")}
)

  • Observe how Stored Procedure's arguments are handled easily in  @NamedStoredProcedureQuery using @StoredProcedureParameter.
  • Expose Entity Bean by creating a Session Facade.
  • Business method need to be added to Session Bean to access the Stored Procedure exposed as NamedQuery.


    public void salaryRaise(Long empId, Long salIncrease) throws Exception {
        try{ 
            Query query = em.createNamedQuery("Employees.increaseEmpSal");
            query.setParameter("EMPID", empId);
            query.setParameter("SALINCR", salIncrease);
            query.executeUpdate();
        } catch(Exception ex){
            throw ex;
        }
    }


  • Expose business method through Session Bean Remote Interface.


void salaryRaise(Long empId, Long salIncrease) throws Exception;


  • Session Bean Client is required to invoke the method exposed through remote interface.

NamedStoredProcedureQuery-CreateSessionBeanClientMenu.JPG



NamedStoredProcedureQuery-CreateSessionBeanClientDialog.JPG


  • Call exposed method in Session Bean Client main method.


  final Context context = getInitialContext();
  SessionEJB sessionEJB = (SessionEJB)context.lookup("Your-JNDI-lookup");
  sessionEJB.salaryRaise(new Long(200), new Long(1000));


  • Deploy Session Bean
  • Run Session Bean Client.
  • Salary of Employee with Id 200 will be increased by 1000.

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