Pagination code using Jsp and Servlet and Mysql DataBase

Hi am using a Database MySql and am Developing Pagination code using Technologies servlets and Jsp's.
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 5or 10) per page. 
 Example:
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: 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.

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.

Step1:create a table in Mysql 

create table employee(empid int(11),empname varchar(20),empsalary int(11),empdept varchar(20));

step2:create a javabean class for setting values to database and getting values from dtabase.
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package a1;

/**
 *
 * @author user
 */
public class Employee
{
    public String getDeptName() {
        return deptName;
    }
    public void setDeptName(String deptName) {
        this.deptName = 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 int getSalary() {
        return salary;
    }

    public void setSalary(int salary) {
        this.salary = salary;
    }
    int employeeId;
    String employeeName;
    int salary;
    String deptName;
   
}

step3: creating a factory class for Getting connection from the DataBase.
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package a1;

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:3306/ashok";
    String user = "root";
    String 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;
    }
}

Step4: creating a Dao class for creating a Factory class Object and calling in that method.And create a query and set to javabean object and added to ArrayList Object.

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package a1;

 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;
        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(1));
                employee.setEmployeeName(rs.getString(2));
                employee.setSalary(rs.getInt(3));
                employee.setDeptName(rs.getString(4));
                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;
    }
}

Step5: create a Servlet class and create a dao class Object and call the method and Forwarded to display.jsp page

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package a2;

import a1.Employee;
import a1.EmployeeDAO;
 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();
    }

    @Override
    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);
    }
}

step6:Configred in web.xml file

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
    <servlet>
        <servlet-name>EmployeeServlet</servlet-name>
        <servlet-class>a2.EmployeeServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>EmployeeServlet</servlet-name>
        <url-pattern>/employee.do</url-pattern>
    </servlet-mapping>
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
</web-app>


step7: create a display.jsp file for Displaying records for 5 to 5 records

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


Output:type a url employee.do



next Click on next Button or type on query String as shown Below screen.


 
If you want to go Previous page Click on previous Link or Click on Next Link 


Pagination code using Jsp and Servlet and Mysql DataBase Click Here DownLoad