Pagination and sorting using JSP and Servlets

     This article explains how to build application with pagination and data sorting using only JSP and Servlets. I will describe details about pagination engine, the best practice for database connection, what is Data Transfer Object (DAO) and Transfer Object, how to write servlet and bind it with JSP. Only one jstl.jar you need for project and it should be placed on the WEB-INF/lib directory.
     In this example we will display a list of product’s details like name and price that is bind with appropriate group. Left panel contains a list of group, right panel contains a list of product of the selected group. Near the name of each group display a products count. Some of group can be empty, other contains a thousand of products. Thats why we should display not more that 10 products on the page.
     It’s necessary to show the current page and navigation elements for moving to the next and previous page. Also we will add sorting for products column in ascending and descending order. If the user clicks on the header column(product name or price) the first time that column should be sorted in ascending order and if the action is repeated on the same header column then the column should be sorted in descending order.
     A little bit about pagination and what method we will choose? In our case pagination means that large document will be breaking into separate pages for viewing. Basically it’s a way of making a website with a lot of data more user-friendly and manageable.
     There are different ways of pagination. First way, not very good as for me, based on getting all records at once. Later when user needs result, some part of data can be retrieved from the cache instead of querying the database to fetch the next set of results. The negative side of this approach is that the data becomes stale. And also fetching a lot of data from database requires many resources(CPU and memory). Nobody likes a slow website, faster is always better. And the second way more interesting approach. We get a range of records every time we need and display it to the user, just limit the result of data that we need to display. This approaches solves a lot of problems. And we will build application that way.
     This example uses two tables Group and Product. Here is the link to the mysql database dump file. Well, let’s write a transfer object classes that will encapsulates our business data.

/**
 * A typical Group entity.
 * 
 * @author Dmitry Nikolaenko
 *
 */
public class Group {
    private int groupId;
    private String groupName;
    private int productCountOfGroup;
    
    public int getGroupId() {
        return groupId;
    }
    public void setGroupId(int groupId) {
        this.groupId = groupId;
    }
    public String getGroupName() {
        return groupName;
    }
    public void setGroupName(String groupName) {
        this.groupName = groupName;
    }
    public int getProductCountOfGroup() {
        return productCountOfGroup;
    }
    public void setProductCountOfGroup(int productCountOfGroup) {
        this.productCountOfGroup = productCountOfGroup;
    }
}

/**
 * A typical Product entity.
 * 
 * @author Dmitry Nikolaenko
 *
 */
public class Product {
    private int productId;
    private String productName;
    private int groupId;
    private int productPrice;
    
    public int getProductId() {
        return productId;
    }
    public void setProductId(int productId) {
        this.productId = productId;
    }
    public String getProductName() {
        return productName;
    }
    public void setProductName(String productName) {
        this.productName = productName;
    }
    public int getGroupId() {
        return groupId;
    }
    public void setGroupId(int groupId) {
        this.groupId = groupId;
    }
    public int getProductPrice() {
        return productPrice;
    }
    public void setProductPrice(int productPrice) {
        this.productPrice = productPrice;
    }
}

     Connection factory help us to make connection to the database. And of course it’s a singleton.

/**
 * The factory used to create an instance of Connection.
 * 
 * @author Dmitry Nikolaenko
 *
 */
public class ConnectionFactory {
    String url = "jdbc:mysql://localhost/store";
    String user = "root";
    String password = "root";
    String driverClass = "com.mysql.jdbc.Driver";
    
    private static ConnectionFactory instance = null;

    private ConnectionFactory() {
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * Gets the singleton instance of the factory.
     * 
     * @return the singleton instance
     */
    public static ConnectionFactory getInstance() {
        if (instance == null) {
            instance = new ConnectionFactory();
        }
        return instance;
    }

    /**
     * Creates a new Connection.
     * 
     * @return the new Connection
     * @throws SQLException if an error occurs during creating a connection
     * @throws ClassNotFoundException if an error occurs during creating a connection
     */
    public Connection getConnection() throws SQLException, ClassNotFoundException {
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }
}

Data Access Object or DAO encapsulates database operations.

/**
 * DAO for the Group entity.
 * 
 * @author Dmitry Nikolaenko
 *
 */
public class GroupDAO {
    private Connection connection;
    private Statement stmt;
    
    public GroupDAO() {}
    
    /**
     * Method return all group in alphabetical order.
     * Entity of the group contains a number of the products and an unique ID.
     * 
     * @return list of groups
     */
    public List<Group> viewAllGroup() {
     String query = "SELECT group_name, p_group.group_id, COUNT(product_id) as product_group_count " +
           "FROM product RIGHT JOIN p_group ON product.group_id=p_group.group_id " +
           "GROUP BY group_name";
     List<Group> list = new ArrayList<Group>();
        Group group;
        try {
            connection = ConnectionFactory.getInstance().getConnection();
            stmt = connection.createStatement();

            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                group = new Group();

                group.setGroupName(rs.getString("group_name"));
                group.setGroupId(rs.getInt("group_id"));
                group.setProductCountOfGroup(rs.getInt("product_group_count"));

                list.add(group);
            }
            rs.close();

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
                if (connection != null)
                    connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }
}

/**
 * DAO for the Product entity.
 * 
 * @author Dmitry Nikolaenko
 *
 */
public class ProductDAO {
    private Connection connection;
    private Statement statement;
    private int productCount;
 
    public ProductDAO() { }
 
    /**
     * Query used to return the products of group.
     * The method allows to get sorted list of products by 
     * name or price in one direction or opposite.
     * 
     * @param group the group of concrete products
     * @param offset value allow to retrieve just a portion of the rows
     * @param recordsOnPage the amount of data per request
     * @param sorting the way of the data sort
     * @param sortingType possible sorting way, for now it's sort by name or price
     * @return list of products
     */
    public List<Product> viewAllProducts(int group, int offset, int recordsOnPage, 
      Sorting sorting, SortingType sortingType) {
     StringBuilder queryBuilder = new StringBuilder();
     queryBuilder.append("SELECT SQL_CALC_FOUND_ROWS * FROM product ");
     if (group > 0) {
      queryBuilder.append("where group_id=").append(group);
     }
     if (!Sorting.DEFAULT.equals(sorting)) {
      queryBuilder.append(" ORDER BY ").append("product_").
       append(sortingType.getValue()).append(" ").append(sorting.getType());
     } 
     queryBuilder.append(" limit ").append(offset).append(", ").append(recordsOnPage);
     
        List<Product> list = new ArrayList<Product>();
        Product product = null;
        try {
            connection = ConnectionFactory.getInstance().getConnection();
            statement = connection.createStatement();

            ResultSet rs = statement.executeQuery(queryBuilder.toString());
            while (rs.next()) {
                product = new Product();
                product.setProductName(rs.getString("product_name"));
                product.setProductPrice(rs.getInt("product_price"));

                list.add(product);
            }
            rs.close();

            rs = statement.executeQuery("SELECT FOUND_ROWS()");
            if (rs.next()) {
                this.productCount = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null)
                    statement.close();
                if (connection != null)
                    connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    /**
     * Return the total count of products for a certain group.
     */
    public int getProductCount() {
        return productCount;
    }
}   

     Servlet processes different request parameters like group of products, current page, desc/asc sorting. Also we are storing a lot of attributes in the request scope and then forwarding request to a JSP. Here is the code of Servlet.

/**
 * Servlet implementation class ProductServlet.
 * 
 * @author Dmitry Nikolaenko
 *
 */
@WebServlet("/ProductServlet")
public class ProductServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
    
    private Sorting sortedPriceColumn = Sorting.ASC;
    private Sorting sortedNameColumn = Sorting.ASC;
    private Sorting activeSortingWay = Sorting.ASC;
    private SortingType activeSortingType = SortingType.NAME;
    
    public ProductServlet() {
        super();
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException {
        int activeGroupId = 0;
        int currentPage = 1;
        int recordsOnPage = 10;
        
        if (request.getParameter("group") != null) {
         activeGroupId = Integer.parseInt(request.getParameter("group"));
        }
        if (request.getParameter("page") != null) {
            currentPage = Integer.parseInt(request.getParameter("page"));
        }
        
        if (request.getParameter("desc") != null) {
         activeSortingWay = Sorting.DESC;
        } else if (request.getParameter("asc") != null) {
         activeSortingWay = Sorting.ASC;
        } 
        else {
         activeSortingWay = Sorting.DEFAULT;
        }
        
        // reverse sort direction logic, for now it's only two possible way - sort by name or price
        String sortParam = request.getParameter(activeSortingWay.getType().toLowerCase());
        if (sortParam != null) {
         activeSortingType = SortingType.safeValueOf(sortParam);
         // verification for change the sorting of product name or product price 
         if (SortingType.NAME.equals(activeSortingType)) {
          // check for the correct change sort direction
          if (sortedNameColumn.equals(activeSortingWay)) {
           sortedNameColumn = Sorting.reverse(sortedNameColumn);
          }
         } else if (SortingType.PRICE.equals(activeSortingType)) {
          // check for the correct change sort direction
          if (sortedPriceColumn.equals(activeSortingWay)) {
           sortedPriceColumn = Sorting.reverse(sortedPriceColumn);
          }
         }
        }
        
        GroupDAO groupDao = new GroupDAO();
        List<Group> groups = groupDao.viewAllGroup();
        request.setAttribute("groupList", groups);
        
        ProductDAO dao = new ProductDAO();
        List<Product> list = dao.viewAllProducts(activeGroupId, (currentPage-1) * recordsOnPage, 
          recordsOnPage, activeSortingWay, activeSortingType);
        int productCount = dao.getProductCount();
        int numberOfPages = (int) Math.ceil(productCount * 1.0 / recordsOnPage);
        
        request.setAttribute("productList", list);
        request.setAttribute("numberOfPages", numberOfPages);
        request.setAttribute("currentPage", currentPage);
        request.setAttribute("activeGroupId", activeGroupId);
        request.setAttribute("productCount", productCount);
        
        request.setAttribute("activeSortingWay", activeSortingWay.getType().toLowerCase());
        request.setAttribute("activeSortingType", activeSortingType.getValue());
        
        request.setAttribute("sortedPriceColumn", sortedPriceColumn.getType().toLowerCase());
        request.setAttribute("sortedNameColumn", sortedNameColumn.getType().toLowerCase());
        
        RequestDispatcher view = request.getRequestDispatcher("displayProduct.jsp");
        view.forward(request, response);
    }
}

     Only one JSP page that retrieves the attributes from request and display the result. Easier to nowhere! :)

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Group and products</title>
<style type="text/css">
  #div0{display:table-cell;width:50%;}
  #div1{float:left;text-align:center;display:table-cell;background-color:#9C8DEB}
  #div2{float:left;text-align:center;display:table-cell;background-color:#41EC88}
  #div3{width:361px; overflow:auto;background-color:yellow;}
</style>
</head>
<body>
    <div id="div0">
        <div id="div1">
            <table border="1" cellpadding="0" cellspacing="0">
                <tr>
                    <th>GROUP_NAME</th>
                </tr>
                <c:forEach var="group" items="${groupList}">
                    <tr>
                        <td><a href="product.do?page=1&group=${group.groupId}">
                                ${group.groupName} (${group.productCountOfGroup})
                        </a></td>
                    </tr>
                </c:forEach>
            </table>
        </div>
        <div id="div2">
            <table border="1" cellpadding="4" cellspacing="0">
                <tr>
                 <th><a href="product.do?page=1&group=${activeGroupId}&${sortedNameColumn}=name">
                     PRODUCT_NAME
                    </a></th>
                    <th><a href="product.do?page=1&group=${activeGroupId}&${sortedPriceColumn}=price">
                     PRODUCT_PRICE
                    </a></th>
                </tr>
         
                <c:forEach var="product" items="${productList}">
                    <tr>
                        <td>${product.productName}</td>
                        <td>${product.productPrice}</td>
                    </tr>
                </c:forEach>
            </table>
        </div>
    </div>
 
  <div> 
   <c:if test="${numberOfPages > 1}">
       <div style="float: left;">
              <!--For displaying previous link except for the 1st page -->
                <c:if test="${currentPage != 1}">
                    <td><a href="product.do?page=${currentPage - 1}&group=${activeGroupId}
                            &${activeSortingWay}=${activeSortingType}">Previous</a></td>
                </c:if>
            </div>
      
       <div id="div3" style="float: left;"> 
                <%--For displaying Page numbers.
                The when condition does not display a link for the current page--%>
                <table border="1" cellpadding="3" cellspacing="0">
                    <tr>
                        <c:forEach begin="1" end="${numberOfPages}" var="i">
                            <c:choose>
                                <c:when test="${currentPage eq i}">
                                    <td>${i}</td>
                                </c:when>
                                <c:otherwise>
                                    <td><a href="product.do?page=${i}&group=${activeGroupId}
                                            &${activeSortingWay}=${activeSortingType}">${i}</a></td>
                                </c:otherwise>
                            </c:choose>
                        </c:forEach>
                    </tr>
                </table>
            </div>
         
         <div style="float: left;">
                <%--For displaying Next link except for the last page --%>
                <c:if test="${currentPage lt numberOfPages}">
                    <td><a href="product.do?page=${currentPage + 1}&group=${activeGroupId}
                            &${activeSortingWay}=${activeSortingType}">Next</a></td>
                </c:if>
            </div>
        </c:if>
    </div>
</body>
</html>

     In summary, here is the project structure.

     And here you can find the package with different project screenshots.
Here is the link to the source code on Github.