Effective datatable paging and sorting

WARNING - OUTDATED CONTENT!

This article is targeted on JSF 1.2. For JSF 2.0, many component libraries offer builtin "lazy loading" capability for effective paging and sorting, such as PrimeFaces <p:dataTable>.

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<MyData> 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<MyData> 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:

    <managed-bean>
        <managed-bean-name>myBean</managed-bean-name>
        <managed-bean-class>mypackage.MyBean</managed-bean-class>
        <managed-bean-scope>request</managed-bean-scope>
    </managed-bean>

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<MyData> 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<MyData> 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"%>

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

<f:view>
    <html xmlns="http://www.w3.org/1999/xhtml">
        <head>
            <title>Effective datatable paging and sorting at DAO level</title>
        </head>
        <body>
            <h:form id="form">

                <%-- The sortable datatable --%>
                <h:dataTable value="#{myBean.dataList}" var="item">
                    <h:column>
                        <f:facet name="header">
                            <h:commandLink value="ID" actionListener="#{myBean.sort}">
                                <f:attribute name="sortField" value="id" />
                            </h:commandLink>
                        </f:facet>
                        <h:outputText value="#{item.id}" />
                    </h:column>
                    <h:column>
                        <f:facet name="header">
                            <h:commandLink value="Name" actionListener="#{myBean.sort}">
                                <f:attribute name="sortField" value="name" />
                            </h:commandLink>
                        </f:facet>
                        <h:outputText value="#{item.name}" />
                    </h:column>
                    <h:column>
                        <f:facet name="header">
                            <h:commandLink value="Value" actionListener="#{myBean.sort}">
                                <f:attribute name="sortField" value="value" />
                            </h:commandLink>
                        </f:facet>
                        <h:outputText value="#{item.value}" />
                    </h:column>
                </h:dataTable>

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

                <%-- The paging links --%>
                <t:dataList value="#{myBean.pages}" var="page">
                    <h:commandLink value="#{page}" actionListener="#{myBean.page}"
                        rendered="#{page != myBean.currentPage}" />
                    <h:outputText value="<b>#{page}</b>" escape="false"
                        rendered="#{page == myBean.currentPage}" />
                </t:dataList>
                <br />

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

                <%-- Cache bean with data list, paging and sorting variables for next request --%>
                <t:saveState value="#{myBean}" />
            </h:form>
        </body>
    </html>
</f:view>

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

Uploading files with JSF

WARNING - OUTDATED CONTENT!

This article is targeted on JSF 1.2.

For JSF 2.0/2.1 with Tomahawk, please checkout my answer on this Stack Overflow question.

For JSF 2.0/2.1 on Servlet 3.0 with a custom component, please checkout this article.

For JSF 2.2, just use its native file upload component in flavor of <h:inputFile> whose value can be tied to a javax.servlet.http.Part property, see also my answer on this Stack Overflow question.

Fast menu Upload and store files Integrating Tomahawk in Mojarra Basic use example

Upload and store files

Downloading files is made relatively easy using a FileServlet, but uploading files is a bit harder. Entering/selecting the raw absolute file path in h:inputText and sending it to the server so that it can be used in a File object isn't going to work, as the server doesn't have access to the client's file system. That will work only if the server as well as the client runs on the same machine and that wouldn't occur in real life.

To browse and select a file for upload you basically need a HTML input type="file" field in the form. As stated in the HTML specification you have to use the POST method and the enctype attribute of the form have to be set to "multipart/form-data". Unfortunately the Sun JSF Reference Implementation Mojarra doesn't provide a component out of the box which renders a input type="file" field. But the MyFaces Tomahawk component library, which can also be integrated in Sun JSF Reference Implementation Mojarra, provides us the t:inputFileUpload component.

Back to top

Integrating Tomahawk in Mojarra

Assuming that you already have a 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.6.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:inputFileUpload component and the ExtensionsFilter. The commons-fileupload and commons-io JAR's are required for the file upload. They contains a multipart/form-data parser and several I/O utilities respectively. The commons-logging and commons-el JAR's are required by the core of the Tomahawk component library.

After adding the JAR's, open your web.xml and add the ExtensionsFilter to it. It should filter multipart/form-data requests and make use of the commons-fileupload to parse the request. To get uploading files work in JSF, it should at least be mapped on the servlet name of the FacesServlet, which may differ per environment. By default it is "Faces Servlet". Just check the servlet name in its <servlet> definition.

<filter>
    <filter-name>Extensions Filter</filter-name>
    <filter-class>org.apache.myfaces.webapp.filter.ExtensionsFilter</filter-class>
</filter>
<filter-mapping>
    <filter-name>Extensions Filter</filter-name>
    <servlet-name>Faces Servlet</servlet-name>
</filter-mapping>

It is not required, but you can configure the ExtensionsFilter with one or more of the following useful init-param settings which you can put in the <filter> tag:

    <init-param>
        <description>
            Set the size limit for uploaded files.
                Format: 10  - 10 bytes
                        10k - 10 KB
                        10m - 10 MB
                        1g  - 1 GB
        </description>
        <param-name>uploadMaxFileSize</param-name>
        <param-value>100m</param-value>
    </init-param>
    <init-param>
        <description>
            Set the threshold size - files below this limit are stored 
            in memory, files above this limit are stored on disk.
                Format: 10  - 10 bytes
                        10k - 10 KB
                        10m - 10 MB
                        1g  - 1 GB
        </description>
        <param-name>uploadThresholdSize</param-name>
        <param-value>100k</param-value>
    </init-param>
    <init-param>
        <description>
            Set the path where the intermediary files will be stored.
        </description>
        <param-name>uploadRepositoryPath</param-name>
        <param-value>/temp</param-value>
    </init-param>

Back to top

Basic use example

Here is a basic use example of a JSF file and the appropriate backing bean which demonstrates the working of the t:inputFileUpload.

<%@ 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" %>

<!DOCTYPE html>

<f:view>
    <html lang="en">
        <head>
            <title>File upload test</title>
        </head>
        <body>
            <h:form id="uploadForm" enctype="multipart/form-data">
                <h:panelGrid columns="3">
                    <h:outputLabel for="file" value="Select file" />
                    <t:inputFileUpload id="file" value="#{myBean.uploadedFile}" required="true" />
                    <h:message for="file" style="color: red;" />

                    <h:panelGroup />
                    <h:commandButton value="Submit" action="#{myBean.submit}" />
                    <h:message for="uploadForm" infoStyle="color: green;" errorStyle="color: red;" />
                </h:panelGrid>
            </h:form>

            <h:outputLink value="file/#{myBean.fileName}" rendered="#{myBean.fileName != null}">
                Download back
            </h:outputLink>
        </body>
    </html>
</f:view>

Please note the enctype of the h:form. This is required to be able to POST binary data. Also note that the download link makes use of the FileServlet. Make sure that it points to the same directory as where the file is uploaded. You can configure it as an init-param.

Here is how the appropriate backing bean (request scoped) look like:

package mypackage;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;

import org.apache.commons.io.FilenameUtils;
import org.apache.commons.io.IOUtils;
import org.apache.myfaces.custom.fileupload.UploadedFile;

public class MyBean {

    // Init ---------------------------------------------------------------------------------------

    private UploadedFile uploadedFile;
    private String fileName;

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

    public void submit() {

        // Just to demonstrate what information you can get from the uploaded file.
        System.out.println("File type: " + uploadedFile.getContentType());
        System.out.println("File name: " + uploadedFile.getName());
        System.out.println("File size: " + uploadedFile.getSize() + " bytes");

        // Prepare filename prefix and suffix for an unique filename in upload folder.
        String prefix = FilenameUtils.getBaseName(uploadedFile.getName());
        String suffix = FilenameUtils.getExtension(uploadedFile.getName());
        
        // Prepare file and outputstream.
        File file = null;
        OutputStream output = null;
        
        try {
            // Create file with unique name in upload folder and write to it.
            file = File.createTempFile(prefix + "_", "." + suffix, new File("c:/upload"));
            output = new FileOutputStream(file);
            IOUtils.copy(uploadedFile.getInputStream(), output);
            fileName = file.getName();

            // Show succes message.
            FacesContext.getCurrentInstance().addMessage("uploadForm", new FacesMessage(
                FacesMessage.SEVERITY_INFO, "File upload succeed!", null));
        } catch (IOException e) {
            // Cleanup.
            if (file != null) file.delete();

            // Show error message.
            FacesContext.getCurrentInstance().addMessage("uploadForm", new FacesMessage(
                FacesMessage.SEVERITY_ERROR, "File upload failed with I/O error.", null));

            // Always log stacktraces (with a real logger).
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(output);
        }
    }

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

    public UploadedFile getUploadedFile() {
        return uploadedFile;
    }

    public String getFileName() {
        return fileName;
    }

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

    public void setUploadedFile(UploadedFile uploadedFile) {
        this.uploadedFile = uploadedFile;
    }

}

Note: make "c:/upload" at least configureable. Maybe as a property in a propertiesfile which could also be shared by the FileServlet.

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) February 2008, BalusC