Pagination in Servlet and JSP

Project Description

• This example explains how to write an application using Servlet and JSP which uses pagination to display the results.
• This application uses one table Employee and displays employee details to the user.
• We use Eclipse IDE for Java EE Developers and Apache Tomcat to run the Servlet and JSP.
• As a best practice, we use Singleton (for making database connection), Data Access Object (DAO), Transfer Object (TO) and Model View Controller (MVC) patterns.

Environment Used

• JDK 6 (Java SE 6)
• Eclipse Indigo IDE for Java EE Developers (3.7.1)
• Apache Tomcat 6.x (To install Tomcat refer this page)
• MySQL 5.5 (To install MySQL refer this page)
• MySQL Connector/J 5.1 JAR file
• JSTL JAR file (jstl-1.2.jar)
• Java EE 5 API (Servlet 2.5, JSP 2.1, JSTL 1.2, Expression Language (EL))
• Java Database Connectivity (JDBC) API
• [Optional] For monitoring and analyzing HTTP headers between the browser and web servers, you can use one of these add-ons of Firefox
  • Live HTTP Headers
  • HttpFox

Setting up development environment

If you are new to developing Servlet with Tomcat and Eclipse, you can read this page before proceeding with this example.

Program control flow

What is pagination?

• Fetching millions of records from database consumes almost all CPU power and memory of machine.
• Hence we break millions of records into small chunks showing limited number of records (say 20 or 30) per page. The best example of this is Google search pagination which allows user to navigate to next page by page number and explore limited records per pages.

How to achieve pagination?

Pagination logic can be achieved in many ways, some are

Method 1: Greedy approach

• Fetch all records at once and display it to the user after caching the results. This is known as greedy approach.
• This can be achieved by writing a DAO which returns a List<Object>. Whenever the user needs result, the sub list can be retrieved from the cached list instead of quering the database to fetch the next set of results when the user clicks “Next” link.
• Drawback of this approach is that since the data is being cached it becomes stale. If your application changes the data in the result set you may have to consider the accuracy of your results when you choose this solution.

Method 2: Non-greedy approach

• Get range of records each time a user wants to see by limiting the number of rows in the ResultSet.
• What happens if you have more than millions of records? User may have to wait for a long time to get the results. Here, we limit the result set to fetch only number of records the user wants to see.

We use second method to demonstrate pagination.

Creating Database and table in MySQL

This example uses one table Employee and the description of the table is shown below.
‘Employee’ table

Field	Type	Key	Extra
emp_id	int	Primary Key	auto_increment
emp_name	varchar(255)
salary	double
dept_name	varchar(255)

• Open command prompt (Windows) or Terminal(Linux) and type
  

  mysql –u [your-username] –p

  and press enter and type the password.

• If you are using Windows, you can also use MySQL command line client which will be available in All programs menu.
• For creating a new database, refer this page. In this example, the database name is ‘exampledb‘.
• After creating the database type the command “use <database_name>;”
• For creating a new table, refer this page. In this example, the table name is ‘employee’
• Insert some records in the table. Refer this page for inserting rows using MySQL.

Creating Dynamic Web Project

• Open Eclipse IDE
• For writing Servlet and JSP, we need to create a new Dynamic Web project. Create this project and name it as “JSPPagination“.

Download MySQL connector

• The connector can be downloaded from: http://dev.mysql.com/downloads/connector/j/.
• This tutorial uses 5.1 version. Unzip the connector to a safe location on your computer which contains MySQL Connector J JAR.
• Copy the MySQL Connector J JAR and paste it inside project’s ‘lib’ folder.

Download JSTL JAR File

• JSTL JAR file can be downloaded from: http://download.java.net/maven/1/jstl/jars/
• This tutorial uses jstl-1.2.jar file.
• Copy the JAR file and paste it inside project’s ‘lib’ folder.

Writing Transfer Object class

• In src folder, create a new package and name it as ‘com.theopentutorials.to‘. Create new class in this package as shown below.
• Transfer Object encapsulates business data. To implement this pattern, we write a class with properties defining the table attributes.

Employee.java class

package com.theopentutorials.to;

public class Employee {
	private int employeeId;
	private String employeeName;
	private double salary;
	private String deptName;
	
	public int getEmployeeId() {
		return employeeId;
	}
	public void setEmployeeId(int employeeId) {
		this.employeeId = employeeId;
	}
	public String getEmployeeName() {
		return employeeName;
	}
	public void setEmployeeName(String employeeName) {
		this.employeeName = employeeName;
	}
	public double getSalary() {
		return salary;
	}
	public void setSalary(double salary) {
		this.salary = salary;
	}
	public String getDeptName() {
		return deptName;
	}
	public void setDeptName(String deptName) {
		this.deptName = deptName;
	}
}

Writing Connection Factory class

Before writing DAO class, let’s write a ConnectionFactory class which has database connection configuration statements and methods to make connection to the database. This class uses singleton pattern.

Create a new package in src folder and name it as com.theopentutorials.db and copy the following code.

ConnectionFactory.java

package com.theopentutorials.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionFactory {
	//static reference to itself
	private static ConnectionFactory instance = 
				new ConnectionFactory();
	String url = "jdbc:mysql://localhost/exampledb";
	String user = "<YOUR_DATABASE_USERNAME>";
	String password = "<YOUR_DATABASE_PASSWORD>";
	String driverClass = "com.mysql.jdbc.Driver"; 
	
	//private constructor
	private ConnectionFactory() {
		try {
			Class.forName(driverClass);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public static ConnectionFactory getInstance()	{
		return instance;
	}
	
	public Connection getConnection() throws SQLException, 
	ClassNotFoundException {
		Connection connection = 
			DriverManager.getConnection(url, user, password);
		return connection;
	}	
}

Fill the username and password for your database and enter your database name in the url string.

Writing DAO class

This class uses Data Access Object (DAO) pattern which encapsulates access to the data source.
EmployeeDAO.java

package com.theopentutorials.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.theopentutorials.db.ConnectionFactory;
import com.theopentutorials.to.Employee;

public class EmployeeDAO {
	Connection connection;
	Statement stmt;
	private int noOfRecords;
		
	public EmployeeDAO() { }
	
	private static Connection getConnection() 
			throws SQLException, 
				ClassNotFoundException 
	{
		Connection con = ConnectionFactory.
				getInstance().getConnection();
		return con;
	}
	
	public List<Employee> viewAllEmployees(
				int offset, 
				int noOfRecords)
	{
		String query = "select SQL_CALC_FOUND_ROWS * from employee limit "
				 + offset + ", " + noOfRecords;
		List<Employee> list = new ArrayList<Employee>();
		Employee employee = null;
		try {
			connection = getConnection();
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				employee = new Employee();
				employee.setEmployeeId(rs.getInt("emp_id"));
				employee.setEmployeeName(rs.getString("emp_name"));
				employee.setSalary(rs.getDouble("salary"));
				employee.setDeptName(rs.getString("dept_name"));
				list.add(employee);
			}
			rs.close();
			
			rs = stmt.executeQuery("SELECT FOUND_ROWS()");
			if(rs.next())
				this.noOfRecords = rs.getInt(1);
		} 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;
	}

	public int getNoOfRecords() {
		return noOfRecords;
	}
}

A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. This LIMIT clause takes two arguments; The first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return.

In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:

Writing Servlet

package com.theopentutorials.servlets;

import java.io.IOException;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.theopentutorials.dao.EmployeeDAO;
import com.theopentutorials.to.Employee;

/**
 * Servlet implementation class EmployeeServlet
 */
public class EmployeeServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	public EmployeeServlet() {
        	super();
	}

	public void doGet(HttpServletRequest request, 
			HttpServletResponse response) 
			throws ServletException, IOException {
		int page = 1;
		int recordsPerPage = 5;
		if(request.getParameter("page") != null)
			page = Integer.parseInt(request.getParameter("page"));
		EmployeeDAO dao = new EmployeeDAO();
		List<Employee> list = dao.viewAllEmployees((page-1)*recordsPerPage,
								 recordsPerPage);
		int noOfRecords = dao.getNoOfRecords();
		int noOfPages = (int) Math.ceil(noOfRecords * 1.0 / recordsPerPage);
		request.setAttribute("employeeList", list);
		request.setAttribute("noOfPages", noOfPages);
		request.setAttribute("currentPage", page);
		RequestDispatcher view = request.getRequestDispatcher("displayEmployee.jsp");
		view.forward(request, response);
	}
}

• In Servlet, we are first getting the value of ‘page’ parameter and storing it in ‘page’ variable. We wanted to display five (5) records per page which we are passing as an argument to viewAllEmployees(offset, 5). We are storing three attributes in the request scope and forwarding the request to a JSP page (displayEmployee.jsp);
  • Employee list avaiable in ‘list’ variable
  • Total number of pages available in ‘noOfPages’ variable
  • Current page available in ‘page’ variable

Writing web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xmlns="http://java.sun.com/xml/ns/javaee" 
	xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" 		
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javae		
	/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
  <display-name>JSPPagination</display-name>
  <servlet>
    <servlet-name>EmployeeServlet</servlet-name>
    <servlet-class>com.theopentutorials.servlets.EmployeeServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>EmployeeServlet</servlet-name>
    <url-pattern>/employee.do</url-pattern>
  </servlet-mapping>
</web-app>

Writing displayEmployee.jsp

This JSP page uses JSP Standard Tag Library (JSTL) along with Expression Language (EL). It retrieves the attributes from request scope and displays the result. To use JSTL libraries, you must include a <taglib> directive in your JSP page.

<%@ 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>Employees</title>
</head>
<body>
	<table border="1" cellpadding="5" cellspacing="5">
		<tr>
			<th>Emp ID</th>
			<th>Emp Name</th>
			<th>Salary</th>
			<th>Dept Name</th>
		</tr>

		<c:forEach var="employee" items="${employeeList}">
			<tr>
				<td>${employee.employeeId}</td>
				<td>${employee.employeeName}</td>
				<td>${employee.salary}</td>
				<td>${employee.deptName}</td>
			</tr>
		</c:forEach>
	</table>

	<%--For displaying Previous link except for the 1st page --%>
	<c:if test="${currentPage != 1}">
		<td><a href="employee.do?page=${currentPage - 1}">Previous</a></td>
	</c:if>

	<%--For displaying Page numbers. 
	The when condition does not display a link for the current page--%>
	<table border="1" cellpadding="5" cellspacing="5">
		<tr>
			<c:forEach begin="1" end="${noOfPages}" var="i">
				<c:choose>
					<c:when test="${currentPage eq i}">
						<td>${i}</td>
					</c:when>
					<c:otherwise>
						<td><a href="employee.do?page=${i}">${i}</a></td>
					</c:otherwise>
				</c:choose>
			</c:forEach>
		</tr>
	</table>
	
	<%--For displaying Next link --%>
	<c:if test="${currentPage lt noOfPages}">
		<td><a href="employee.do?page=${currentPage + 1}">Next</a></td>
	</c:if>

</body>
</html>

First ‘table’ tag displays list of employees with their details. Second ‘table’ tag displays the page numbers.

Folder Structure

The complete folder structure of this example is shown below.

Output

Use Ctrl + F11 in Eclipse to run the Servlet. The URL for requesting the Servlet is
http://localhost:8080/JSPPagination/employee.do