Pagination in JSP and Servlet
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 10 or 20) 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.
Steps for creating pagination in jsp and servlet.
Step 1 : First You have to create employee table in mysql database.
The structure of employee table is as
CREATE TABLE `employee` (
`emp_id` int(10) NOT NULL,
`emp_name` varchar(50) DEFAULT NULL,
`salary` double DEFAULT NULL,
`department` varchar(50) DEFAULT NULL,
`state` varchar(50) DEFAULT NULL,
`city` varchar(50) DEFAULT NULL,
`country` varchar(50) DEFAULT NULL,
PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
`emp_id` int(10) NOT NULL,
`emp_name` varchar(50) DEFAULT NULL,
`salary` double DEFAULT NULL,
`department` varchar(50) DEFAULT NULL,
`state` varchar(50) DEFAULT NULL,
`city` varchar(50) DEFAULT NULL,
`country` varchar(50) DEFAULT NULL,
PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Step 2 : Download mysql connector and jstl jar and include in WEB-INF/lib folder
Step 3 : Writing transfer object class as Employee
package test.paging;
public class Employee {
private int id;
private String name;
private double salary;
private String department;
private String state;
private String city;
private String country;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getState() {
return state;
}
public void setState(String state) {
this.state = state;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
}
Step 4 : Writing Connection Factory Class
package test.paging;
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/test";
String user = "root";
String password = "admin1&$$31223";
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;
}
}
Step 5 : Writing DAO Class
package test.paging;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
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;
System.out.println("EmployeeDAO.viewAllEmployees()"+query);
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.setId(rs.getInt("emp_id"));
employee.setName(rs.getString("emp_name"));
employee.setSalary(rs.getDouble("salary"));
employee.setDepartment(rs.getString("department"));
employee.setState(rs.getString("state"));
employee.setCity(rs.getString("city"));
employee.setCountry(rs.getString("country"));
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;
}
}
Step 6 : Writing Servlet :
package test.paging;
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;
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("display.jsp");
view.forward(request, response);
}
}
Step 7 : Writing web.xml as
<?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>test.paging.EmployeeServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>EmployeeServlet</servlet-name>
<url-pattern>/employee.do</url-pattern>
</servlet-mapping>
</web-app>
Step 8 : Writing display.jsp
<%@ 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>Employee ID</th>
<th>Empployee Name</th>
<th>Salary</th>
<th>Department</th>
<th>State</th>
<th>City</th>
<th>Country</th>
</tr>
<c:forEach var="employee" items="${employeeList}">
<tr>
<td>${employee.id}</td>
<td>${employee.name}</td>
<td>${employee.salary}</td>
<td>${employee.department}</td>
<td>${employee.state}</td>
<td>${employee.city}</td>
<td>${employee.country}</td>
</tr>
</c:forEach>
</table>
<table border="0" cellpadding="0" cellspacing="0">
<td>
<%--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> -->
<%--For displaying Next link --%>
<c:if test="${currentPage lt noOfPages}">
<!-- <td> --><a href="employee.do?page=${currentPage + 1}"> Next</a><!-- </td> -->
</c:if>
</td>
</table>
</body>
</html>
Step 9 : Deploy all files in tomcat container
Step 10 : Start the tomcat
Step 11 : Access the page in browser
http://localhost:8080/paging/employee.do
Step 12 : The Output are :
| Employee ID | Empployee Name | Salary | Department | State | City | Country |
|---|---|---|---|---|---|---|
| 6 | shyama | 72363.0 | media sales | up | bihar | india |
| 7 | rahima | 387483.0 | techops | up | bihar | india |
| 8 | rahima | 387483.0 | up | india | ||
| 9 | rahima | 387483.0 | up | india | ||
| 10 | rahima | 387483.0 | up | india |
| Previous 1 2 3 Next Thanks & Regards |
