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.

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

Model Diagram:

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

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.

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"

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

Create Tree binding for control of Employees result set.

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

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

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.