Effective datatable paging and sorting

Fast menu
Effective datatable paging and sorting at DAO level
Preparations
Backing Bean
Example DTO
Example DAO
Example JSF

Effective datatable paging and sorting at DAO level

In the 2 year old article Using datatables I wrote how to sort and page a JSF h:dataTable. Those are useful and nice if the dataset is small. But it is in fact less efficient as it first loads the whole data list from the database and uses Java to do the sorting and paging. It is much more efficient if you let the database do all the task. A self-respected database can sort the results much faster than Java can do. Querying a sublist from the database consumes much less memory in Java than when you query the complete list from the database. This all will make it much faster when you have a database with at least hundreds of rows.

Sorting using SQL can easily be done with the standardized ORDER BY clause. The way to obtain a subset of results differs per database. This article is targeted on MySQL. In MySQL you can obtain a subset of results with the LIMIT x, y clause. PostgreSQL uses LIMIT x OFFSET y. In Oracle you need to execute a ORDER BY subquery first and then use the ROWNUM clause on its results (SELECT * FROM (SELECT * FROM table ORDER BY column) WHERE ROWNUM BETWEEN x AND y). For MSSQL and DB2 you'll need to write a (w)hacky SQL query or to create a stored procedure. Consult Google or database specific documentation for details.

Back to top

Preparations

Next to a standard JSF implementation, we need the Tomahawk component library as it offers us the t:dataList and t:saveState components. The t:dataList is needed to display a collection of links with page numbers. It is preferred above JSTL's c:forEach, because it does its work more efficient. The t:saveState is needed to cache the displayed list and some important paging and sorting variables for the next request. It is preferred above h:inputHidden, because it does its work more efficient and it doesn't require a converter for non-standard object types. You can even cache a complete bean for the subsequent request, with which you can simulate a "conversation scope".

Integrating Tomahawk isn't that hard, you can even do that on a Sun Mojarra environment. You just need to add at least the following JAR's to the classpath, e.g. /WEB-INF/lib. The version numbers doesn't matter that much, as long as you get the newest.

• tomahawk-1.1.7.jar


• commons-fileupload-1.2.jar


• commons-io-1.4.jar


• commons-logging-1.1.1.jar


• commons-el.jar

The Tomahawk JAR is the Tomahawk component library itself which under each contains the t:dataList and t:saveState components. The commons JAR's are required by other components and/or the core of the Tomahawk component library.

Back to top

Backing Bean

Here is how the basic backing bean code look like. It is request scoped. If you're interested, an example of the DAOFactory can be found here: DAO tutorial - the data layer.

package mypackage;

import java.io.Serializable;
import java.util.List;

import javax.faces.component.UICommand;
import javax.faces.event.ActionEvent;

import mydao.DAOException;
import mydao.DAOFactory;
import mydao.MyDataDAO;
import mymodel.MyData;

/**
 * The example backing bean for effective datatable paging and sorting.
 *
 * @author BalusC
 * @link http://balusc.blogspot.com/2008/10/effective-datatable-paging-and-sorting.html
 */
public class MyBean implements Serializable {

    // Properties ---------------------------------------------------------------------------------

    // DAO.
    private static MyDataDAO dao = DAOFactory.getInstance("javabase").getMyDataDAO();

    // Data.
    private List dataList;
    private int totalRows;

    // Paging.
    private int firstRow;
    private int rowsPerPage;
    private int totalPages;
    private int pageRange;
    private Integer[] pages;
    private int currentPage;

    // Sorting.
    private String sortField;
    private boolean sortAscending;

    // Constructors -------------------------------------------------------------------------------

    public MyBean() {
        // Set default values somehow (properties files?).
        rowsPerPage = 10; // Default rows per page (max amount of rows to be displayed at once).
        pageRange = 10; // Default page range (max amount of page links to be displayed at once).
        sortField = "id"; // Default sort field.
        sortAscending = true; // Default sort direction.
    }

    // Paging actions -----------------------------------------------------------------------------

    public void pageFirst() {
        page(0);
    }

    public void pageNext() {
        page(firstRow + rowsPerPage);
    }

    public void pagePrevious() {
        page(firstRow - rowsPerPage);
    }

    public void pageLast() {
        page(totalRows - ((totalRows % rowsPerPage != 0) ? totalRows % rowsPerPage : rowsPerPage));
    }

    public void page(ActionEvent event) {
        page(((Integer) ((UICommand) event.getComponent()).getValue() - 1) * rowsPerPage);
    }

    private void page(int firstRow) {
        this.firstRow = firstRow;
        loadDataList(); // Load requested page.
    }

    // Sorting actions ----------------------------------------------------------------------------

    public void sort(ActionEvent event) {
        String sortFieldAttribute = (String) event.getComponent().getAttributes().get("sortField");

        // If the same field is sorted, then reverse order, else sort the new field ascending.
        if (sortField.equals(sortFieldAttribute)) {
            sortAscending = !sortAscending;
        } else {
            sortField = sortFieldAttribute;
            sortAscending = true;
        }

        pageFirst(); // Go to first page and load requested page.
    }

    // Loaders ------------------------------------------------------------------------------------

    private void loadDataList() {

        // Load list and totalCount.
        try {
            dataList = dao.list(firstRow, rowsPerPage, sortField, sortAscending);
            totalRows = dao.count();
        } catch (DAOException e) {
            throw new RuntimeException(e); // Handle it yourself.
        }

        // Set currentPage, totalPages and pages.
        currentPage = (totalRows / rowsPerPage) - ((totalRows - firstRow) / rowsPerPage) + 1;
        totalPages = (totalRows / rowsPerPage) + ((totalRows % rowsPerPage != 0) ? 1 : 0);
        int pagesLength = Math.min(pageRange, totalPages);
        pages = new Integer[pagesLength];

        // firstPage must be greater than 0 and lesser than totalPages-pageLength.
        int firstPage = Math.min(Math.max(0, currentPage - (pageRange / 2)), totalPages - pagesLength);

        // Create pages (page numbers for page links).
        for (int i = 0; i < pagesLength; i++) {
            pages[i] = ++firstPage;
        }
    }

    // Getters ------------------------------------------------------------------------------------

    public List getDataList() {
        if (dataList == null) {
            loadDataList(); // Preload page for the 1st view.
        }
        return dataList;
    }

    public int getTotalRows() {
        return totalRows;
    }

    public int getFirstRow() {
        return firstRow;
    }

    public int getRowsPerPage() {
        return rowsPerPage;
    }

    public Integer[] getPages() {
        return pages;
    }

    public int getCurrentPage() {
        return currentPage;
    }

    public int getTotalPages() {
        return totalPages;
    }

    // Setters ------------------------------------------------------------------------------------

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

}

Define it as usual in the faces-config.xml:

    
        myBean
        mypackage.MyBean
        request
    

Back to top

Example DTO

Here is the basic DTO example. It's nothing special. It's just a dummy DTO with three fields: ID, Name and Value.

package mymodel;

import java.io.Serializable;

/**
 * MyData. The example DTO (Data Transfer Object).
 *
 * @author BalusC
 * @link http://balusc.blogspot.com/2008/10/effective-datatable-paging-and-sorting.html
 */
public class MyData implements Serializable {

    // Properties ---------------------------------------------------------------------------------

    private Long id;
    private String name;
    private Integer value;

    // Constructors -------------------------------------------------------------------------------

    public MyData() {
        // Keep default constructor alive.
    }

    public MyData(Long id, String name, Integer value) {
        this.id = id;
        this.name = name;
        this.value = value;
    }

    // Getters ------------------------------------------------------------------------------------

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public Integer getValue() {
        return value;
    }

    // Setters ------------------------------------------------------------------------------------

    public void setId(Long id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setValue(Integer value) {
        this.value = value;
    }

}

Back to top

Example DAO

The basic DAO example. Note that you cannot set the ORDER BY field and direction as PreparedStatement value. That's why it uses the String#format() for it. Keep SQL injection risks in mind. As long as the client can't control the values, you don't need to be afraid.

For more information and examples of the DAO layer and the DAOUtil class, you may find this article useful: DAO tutorial - the data layer.

package mydao;

import static mydao.DAOUtil.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import mymodel.MyData;

/**
 * This class represents a SQL Database Access Object for the {@link MyData} DTO.
 *
 * @author BalusC
 * @link http://balusc.blogspot.com/2008/10/effective-datatable-paging-and-sorting.html
 */
public final class MyDataDAO {

    // Constants ----------------------------------------------------------------------------------

    private static final String SQL_LIST_BY_ORDER_AND_LIMIT =
        "SELECT id, name, value FROM mydata ORDER BY %s %s LIMIT ?, ?";
    private static final String SQL_COUNT =
        "SELECT count(*) FROM mydata";

    // Properties ---------------------------------------------------------------------------------

    private DAOFactory daoFactory;

    // Constructors -------------------------------------------------------------------------------

    /**
     * Construct MyData DAO for the given DAOFactory. Package private so that it can be constructed
     * inside the DAO package only.
     * @param daoFactory The DAOFactory to construct this MyData DAO for.
     */
    MyDataDAO(DAOFactory daoFactory) {
        this.daoFactory = daoFactory;
    }

    // Actions ------------------------------------------------------------------------------------

    /**
     * Returns list of MyData items starting at the given first index with the given row count,
     * sorted by the given sort field and sort order.
     * @param firstRow First index of rows to be returned.
     * @param rowCount Amount of rows to be returned.
     * @param sortField Field to sort the data on.
     * @param sortAscending Whether to sort data ascending or not.
     * @return list of MyData items starting at the given first index with the given row count,
     * sorted by the given sort field and sort order.
     * @throws DAOException If something fails at DAO level.
     */
    public List list(int firstRow, int rowCount, String sortField, boolean sortAscending)
        throws DAOException
    {
        Object[] values = { firstRow, rowCount };

        String sortDirection = sortAscending ? "ASC" : "DESC";
        String sql = String.format(SQL_LIST_BY_ORDER_AND_LIMIT, sortField, sortDirection);
        List dataList = new ArrayList<>();

        try (
            Connection connection = daoFactory.getConnection();
            PreparedStatement statement = prepareStatement(connection, sql, false, values);
            ResultSet resultSet = statement.executeQuery();
        ) {
            while (resultSet.next()) {
                dataList.add(mapMyData(resultSet));
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        }

        return dataList;
    }

    /**
     * Returns total amount of rows in table.
     * @return Total amount of rows in table.
     * @throws DAOException If something fails at DAO level.
     */
    public int count() throws DAOException {
        int count = 0;

        try (
            Connection connection = daoFactory.getConnection();
            PreparedStatement statement = connection.prepareStatement(SQL_COUNT);
            ResultSet resultSet = statement.executeQuery();
        ) {
            if (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        }

        return count;
    }

    /**
     * Map the current row of the given ResultSet to MyData.
     * @param resultSet The ResultSet of which the current row is to be mapped to MyData.
     * @return The mapped MyData from the current row of the given ResultSet.
     * @throws SQLException If something fails at database level.
     */
    private static MyData mapMyData(ResultSet resultSet) throws SQLException {
        return new MyData(
            resultSet.getLong("id"),
            resultSet.getString("name"),
            resultSet.getObject("value") != null ? resultSet.getInt("value") : null
        );
    }

}

Back to top

JSF file

And now the JSF file, it has a sortable datatable, a bunch of paging buttons (first, previous, next and last), the status of current page and total pages, a bunch of links pointing to a specific page and finally a input field where you can specify the amount of rows to be displayed at once.

<%@taglib uri="http://java.sun.com/jsf/core" prefix="f"%>
<%@taglib uri="http://java.sun.com/jsf/html" prefix="h"%>
<%@taglib uri="http://myfaces.apache.org/tomahawk" prefix="t"%>

 html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">


     xmlns="http://www.w3.org/1999/xhtml">
        
            
        
        
             id="form">

                <%-- The sortable datatable --%>
                 value="#{myBean.dataList}" var="item">
                    
                         name="header">
                             value="ID" actionListener="#{myBean.sort}">
                                 name="sortField" value="id" />
                            
                        
                         value="#{item.id}" />
                    
                    
                         name="header">
                             value="Name" actionListener="#{myBean.sort}">
                                 name="sortField" value="name" />
                            
                        
                         value="#{item.name}" />
                    
                    
                         name="header">
                             value="Value" actionListener="#{myBean.sort}">
                                 name="sortField" value="value" />
                            
                        
                         value="#{item.value}" />
                    
                

                <%-- The paging buttons --%>
                 value="first" action="#{myBean.pageFirst}"
                    disabled="#{myBean.firstRow == 0}" />
                 value="prev" action="#{myBean.pagePrevious}"
                    disabled="#{myBean.firstRow == 0}" />
                 value="next" action="#{myBean.pageNext}"
                    disabled="#{myBean.firstRow + myBean.rowsPerPage >= myBean.totalRows}" />
                 value="last" action="#{myBean.pageLast}"
                    disabled="#{myBean.firstRow + myBean.rowsPerPage >= myBean.totalRows}" />
                 value="Page #{myBean.currentPage} / #{myBean.totalPages}" />
                


                <%-- The paging links --%>
                 value="#{myBean.pages}" var="page">
                     value="#{page}" actionListener="#{myBean.page}"
                        rendered="#{page != myBean.currentPage}" />
                     value="#{page}" escape="false"
                        rendered="#{page == myBean.currentPage}" />
                
                


                <%-- Set rows per page --%>
                 for="rowsPerPage" value="Rows per page" />
                 id="rowsPerPage" value="#{myBean.rowsPerPage}" size="3" maxlength="3" />
                 value="Set" action="#{myBean.pageFirst}" />
                 for="rowsPerPage" errorStyle="color: red;" />

                <%-- Cache bean with data list, paging and sorting variables for next request --%>
                 value="#{myBean}" />
            
        
    

Save it as paging.jsp or so and invoke it by http://localhost:8080/playground/paging.jsf, assuming that your development server runs at port 8080 and the playground environment's context root is called 'playground'.

That's all!

Back to top

Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair.

(C) October 2008, BalusC

Source:http://balusc.blogspot.com/2008/10/effective-datatable-paging-and-sorting.html