Wednesday, April 18, 2012

Alphabet filter using EJB NamedQuery

Let us take scenario where the table has more records. It will be difficult for user to search or travel to the exact record.

Model Diagram:

Here in the above model diagram, Employees table schema.

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


Here In the above scenario, It will be difficult to search for the required records. Suppose if the records starts with alphabet "W" then user as to select scroll till the end page. Performance issues can be improved using using alphabet, and other toolbar filters by reducing result set size while loading the page.

Alphabet filters will be one of the solution for navigating to the records. Create a Entity based on Employees table and add one named query, create a session bean and data control for that.
@NamedQuery(name = "Employees.filterByAlphabet",
query = "select o from Employees o where o.email like CONCAT(:email,'%')")
Create a FilterByAlphabet.jspx page and drop the getEmployeesFilterByAlphabet->result DC as Table->ADF Read Only Table with email column sort option enabled. Surround the employee table with panel collection and toolbar with inside the panel. Add the below code inside the toolbar.

<af:commandLink text="All" id="cl1"
  actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:spacer width="15" height="10" id="s1"/>
<af:commandLink text="A" id="cl2"
  actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="B" id="cl3" actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="C" id="cl4" actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="D" id="cl5" actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="E" id="cl6" actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="F" id="cl7" actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="G" id="cl8" actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="H" id="cl9" actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="I" id="cl10"
  actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="J" id="cl11"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="K" id="cl12"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="L" id="cl13"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="M" id="cl14"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="N" id="cl15"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="O" id="cl16"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="P" id="cl27"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="Q" id="cl28"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="R" id="cl29"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="S" id="cl30"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="T" id="cl31"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="U" id="cl32"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="V" id="cl33"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="W" id="cl34"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="X" id="cl35"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="Y" id="cl36"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
<af:commandLink text="Z" id="cl37"
    actionListener="#{FilterByAlphabet.getSelectedAlphabet}"/>
Create a FilterByAlphabet managed bean with scope as "sessionScope" add the below code :

private String filteredValue;
public void setFilteredValue(String filteredValue) {
          this.filteredValue = filteredValue;
}
public String getFilteredValue() {
          return filteredValue;
}
public void getSelectedAlphabet(ActionEvent actionEvent) { RichCommandLink rcl = (RichCommandLink)actionEvent.getComponent(); if (rcl.getText().equals("All")) { this.setFilteredValue(""); } else { this.setFilteredValue(rcl.getText()); } }
Go to the page definition and edit the getEmployeesFilterByAlphabet action binding and map the value to #{FilterByAlphabet.filteredValue}



Run the FilterByAlphabet.jspx and Selecting a letter adds an <attribute> Starts With condition to the query whose result set is displayed in the table. Clicking the alphabet "A" letter will results the records that starts with "A". So user can filter the records based on alphabet choice.

No comments:

Post a Comment