Pagination in Servlet and JSP

11 May 2012 By Nithya Vasudevan 114,237 views 87 Comments
94 Flares Twitter 5 Facebook 74 Google+ 15 94 Flares ×

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

FieldTypeKeyExtra
emp_idintPrimary Keyauto_increment
emp_namevarchar(255)
salarydouble
dept_namevarchar(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

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

Tags: , , , , , , , , , , , , ,

  • aktharpasha

    well ,this is good for pagination this i want to use my project thank u for u servicing………………

    • Rahul

      how to create employee.do file and where in netscap editor

  • Suddha Satwa

    I think in connection factory class you should casting the Connection and import com.mysql.jdbc.Connection; as:-

    public Connection getConnection() throws SQLException,ClassNotFoundException
    {
    Connection connection = (Connection) DriverManager.getConnection(url,user,password);
    return connection;
    }

    • http://theopentutorials.com Praveen Macherla

      I think you have imported Mysql Connection class. Do not use that. Use java.sql.Connection class.

  • kk

    hi,

    i am creating a online examination system.
    i am not able to get the question from database 1 question per page along with the options and then how to save the user response to the database.

    • http://theopentutorials.com Praveen Macherla

      Tell us what you have done so far and where you are facing the problem. Show us sscce.

  • kk

    i have created a servlet to fetch question from database and then stored the questions with options in arraylist and then using the array list in jsp to reterieve the questions with options.
    but this way my jsp contains scriptlets, which is not good, so i want to know how can i use beans for the same.

  • anbukiruba

    thank u for u sharing information… Its real good… for pagination,
    Singleton , DAO

  • EdM

    Tried all that also against an oracle database.
    It worked smoothly, after changing driver manager and sql query, of course.
    Great work, thanks.

  • Valdir Lima

    Simply wonderful. It worked 100%. Excellent.

  • Waqas

    Wonderful tutorial there but i was trying to run the code on a glassfish server and ended up getting this error

    java.lang.ClassNotFoundException: com.theopentutorials.servlets.EmployeeServlet

    ——————————————————————————–

    here is my web.xml

    JSPPagination

    JSPPagination

    EmployeeServlet
    com.theopentutorials.servlets.EmployeeServlet

    EmployeeServlet
    /employee.do

    really appriciate your help

  • tapan

    wht abut 400 records it display 1,2,3,4,5,6,7,8,9,10,11…..so on

    • http://theopentutorials.com Nithya Vasudevan

      Increase the value of the variable “recordsPerPage” in Servlet. This example displays 5 records per page.

  • Fredrick Ondieki

    Very clear explanation

  • Khyathi

    Explanation is very good:-)

  • riteshh05

    Hi,Nithya
    I am getting error while running . I am using EclipseJuno and Tomcat 7.
    Error it shows n browser is
    HTTP Status 500 – The absolute uri: http://java.sun.com/jsp/jstl/core cannot be resolved in either web.xml or the jar files deployed with this application.
    Please help.
    Ritesh Kumar

    • http://theopentutorials.com Praveen Macherla

      You have to use jstl 1.2 jar. Download that jar and put in web-inf/lib.

  • Azahrudhin

    Good explanation with the example.
    I have deployed the application on tomcat I am DB connection every thing is fine at back end side but unable to load the jsp. I am using jstl1.2.jar only.

    java.lang.NumberFormatException: For input string: “${noOfPages}
    JSP unable to load the values of $
    At Server side I have below Info

    INFO: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.

    appreciate your help in.

    • http://theopentutorials.com Praveen Macherla

      Your code might be wrong. Check noOfPages setAttribute in servlet.

  • Azahrudhin

    Same example I have copied and tried to run from servlet I am able to print the value

    System.out.println(request.getAttribute(“noOfPages”));

    • http://theopentutorials.com Praveen Macherla

      Difficult to help without code. Post it somewhere (stackoverflow or some other site) and link it here. Will see what is the problem.

    • simran

      you have to use the resquestScope parameter in Expression Language.
      ${requestScope['noOfPages']}

  • Amogh

    hello,
    i developed a search form which called the servlet .it even passed the searching parameter to the servlet. when i click page 2 i get a 404 file not found error. i had changed action =”employee.do” to action==”./EmployeeServlet”. even im getting the required display. but when i click on page 2. it says it cant find employee.do?page=2.
    please help how to i change coding in displayEmploee.jsp >?????

    • http://theopentutorials.com Praveen Macherla

      If you change the action url then change it in all occurrences.

  • Freddy

    Great writeup Praveen! Many thanks for your contribution to the community!

  • Dhaval

    Very good and nice flow too.

    Thanks a lot keep it up.

  • Murali Krishnan

    Very good Explanation. Easily understandable. I am a newbie to JSP. But still am able to understand this easily. Thanks a lot.

  • sb

    I am also getting the same error in jsp.
    There are no errors in servlet/java files.

    java.lang.NumberFormatException: For input string: “${noOfPages}

    • http://theopentutorials.com Praveen Macherla

      Your setAttribute code for “noOfPages” might be wrong. Check the code thoroughly.

  • sb

    Please help me.Thanks.

  • Ranjit

    Your tutorial is very good..but i am getting problem that below table it does not shows the pagination numbers like 1 2 3 and also does not shows the prev and next link..
    One more issue is that can you please tell the query of limit in oracle..
    please give reply early as possible..
    Thanks in advance.

  • petes

    This looks nice but what a nightmare this is for someone new (for example I have no clue why would you need a JSP when you could use servlets instead, which in theory are almost the same).

    I was considering doing a user registering system with a single servlet (or chained servlets) but then someone told me it wasn’t a good idea, now I’m just paralized. I’d like to ask if a single servlet could be enough for a new website that shouldn’t have many users at the beginning (at least meanwhile I decipher all) and what are the dangers?

    • http://theopentutorials.com Praveen Macherla

      You can do this with servlets alone. But that is not the way we do it unless your website has just a small number of pages. Even for small sites a combination of servlets and jsp’s is better. Servlets act mainly as controllers whereas JSP provides the front end. Have a look at MVC design pattern to get a better understanding.

  • Shakeel Ahmed

    Thanx for providing tthis service and this may useful to many persons

  • adarsh m

    I run this program in NetBeans IDE 7.1.2.There is no error.But data is not display.Database contain 15 items.

  • jack

    the above code shows me an error , i dono where i went wrong .., it does’nt show me any records which is in table, it shows simply column names alone.

    com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘-10, 5′ at line 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3243)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3172)
    at com.mysql.jdbc.Statement.executeQuery(Statement.java:1197)
    at com.theopentutorials.dao.EmployeeDAO.viewAllEmployees(EmployeeDAO.java:38)
    at com.theopentutorials.servlets.EmployeeServlet.doGet(EmployeeServlet.java:32)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:164)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:462)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:562)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:395)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:250)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:188)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:302)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

    • http://theopentutorials.com Nithya Vasudevan

      It’s MySQL syntax error. Your limit clause seems to be wrong. It should not be -10

  • Sreebanraj

    Hello Nithya Vasudevan,
    This Example is so clear and transparent i developed pagination using your example… I have never saw such a clear steps in any sites Thanks for your service.

    Thanks and Regards,
    SREEBANRAJ.R

  • Sainath Bysani

    This example of which you had given is very clear. I am able to print the DB values in my action class.

    Thanks and Regards,

    Sainath Bysani

  • Vinila

    Very nice and useful information explaining step by step approach.
    Thanks for sharing the info.

  • jack

    WARNING: Unknown element (deferred-value) in attribute
    java.lang.NumberFormatException: For input string: “${noOfPages}”
    at java.lang.NumberFormatException.forInputString(Unknown Source)
    at java.lang.Integer.parseInt(Unknown Source)
    at java.lang.Integer.valueOf(Unknown Source)
    at org.apache.jasper.compiler.JspUtil.coerceToInt(JspUtil.java:753)
    at org.apache.jasper.compiler.Generator$GenerateVisitor.convertString(Generator.java:2966)
    at org.apache.jasper.compiler.Generator$GenerateVisitor.evaluateAttribute(Generator.java:2764)
    at org.apache.jasper.compiler.Generator$GenerateVisitor.generateSetters(Generator.java:2875)
    at org.apache.jasper.compiler.Generator$GenerateVisitor.generateCustomStart(Generator.java:2188)
    at org.apache.jasper.compiler.Generator$GenerateVisitor.visit(Generator.java:1686)
    at org.apache.jasper.compiler.Node$CustomTag.accept(Node.java:1442)
    at org.apache.jasper.compiler.Node$Nodes.visit(Node.java:2166)
    at org.apache.jasper.compiler.Node$Visitor.visitBody(Node.java:2216)
    at org.apache.jasper.compiler.Node$Visitor.visit(Node.java:2222)
    at org.apache.jasper.compiler.Node$Root.accept(Node.java:457)
    at org.apache.jasper.compiler.Node$Nodes.visit(Node.java:2166)
    at org.apache.jasper.compiler.Generator.generate(Generator.java:3322)
    at org.apache.jasper.compiler.Compiler.generateJava(Compiler.java:199)
    at org.apache.jasper.compiler.Compiler.compile(Compiler.java:296)
    at org.apache.jasper.compiler.Compiler.compile(Compiler.java:277)
    at org.apache.jasper.compiler.Compiler.compile(Compiler.java:265)
    at org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:564)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:302)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
    at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:834)
    at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:640)
    at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1286)
    at java.lang.Thread.run(Unknown Source)

  • Nilesh

    Dear Nithya,
    Thanks for sharing knowledge with us in a simpler way. I worked with this example and is working fine. But i tried to run same example on jdk 1.5 and Tomcat 5.x, and its not working. The error message shows exception as “The server encountered an internal error () that prevented it from fulfilling this request.”

    Please do needful.

    With Regards,
    Nilesh G

  • Anand

    Really very helpful. post this type of good tutorial….

  • sourav

    Thank u so much nitya…and in on word “it’s awesome”

  • shyam

    Hi Nitya, Thank You so much for your good Explanation. I am facing problem with indexing. I am fetching large amount of data and number of pages are 200.
    all the page numbers are displaying in main jsp page itself without Next button. It means 1 2 3 4 5 —– 200 Next. But i want to display 1 2 3 4 5 6 7 8 9 10 then next.

    So can you please suggest.

  • muni

    how to set the no of records per page

    very good explenation
    thank you

  • Saurabh

    i am using sql server in that this in dao i have wright a query
    select SQL_CALC_FOUND_ROWS CANDIDATEID, TESTNO, CREATED_ON, ACTIVATION_STATUS from CANDIDATE_LOGIN limit” + offset + “, ” + noOfRecords);

    but it is not working and giving error “Incorrect syntax near ’5′” can you please help me on this?

    • http://theopentutorials.com Praveen Macherla

      SQL_CALC_FOUND_ROWS works only in MySQL. There may be other alternatives in SQL server. If not you can use count() to get the total rows instead of SQL_CALC_FOUND_ROWS and FOUND_ROWS()

  • Ashiwn

    HI, How to i do pagination via oracle where first query will execute and then i wnat count of result of that query?
    simply i want to ask how to i do pagination in jsp via oracle? can any known ple send me code or idea ,suggestion it will appreciated highly….Thanks in advnace

    • http://theopentutorials.com Nithya Vasudevan

      Use this link to setup JDBC Oracle connection
      SQL_CALC_FOUND_ROWS works only in MySQL. There may be other alternatives in Oracle server. If not you can use count() to get the total rows instead of SQL_CALC_FOUND_ROWS and FOUND_ROWS().

  • Pooja

    Hi Nithya, Thanks for giving example. It is very well described. But I’m facing one problem.
    Actually, I’m using SqlServer 2008 instead of MySql, So I configured every thing properly, still when I run it, I get only Table header with a Previous hyperlink at bottom (No data, no next link), It seemed some problem with querying from Db, I tried to debug, but for 1st time request debug is not reaching even in sevlet, then I click on previous, and debug reaches and transaction fails with saying :-

    com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost/Exercise, port 1433 has failed. Error: “null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
    at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:241)
    at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2243)
    at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:491)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1309)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
    at java.sql.DriverManager.getConnection(DriverManager.java:582)
    at java.sql.DriverManager.getConnection(DriverManager.java:185)
    at com.exercise.pagination.db.ConnectionFactory.getConnection(ConnectionFactory.java:33)

    Though I have checked Configuration manager also, I’m having proper port enabled, also I’m successfully running another application with similar DB connection, it works there. But its not working, Is tehre any specific reason, or what wrong am I doing?

    Thanks in advance.

  • Pooja

    Also, Its good if you can suggest, similar syntax in SqlServer 2008 for SQL_CALC_FOUND_ROWS.

  • http://cyansquare.co.nr Nitin

    really good explanation !
    nice work !!

  • Lijo

    Very good tutorial you saved me..Thanks a lot.

  • Nikhil

    nice tutorial……..Java lovers needs it.Thank You

  • Lalit

    Very good Example with nice explnation.Keep it up…..

  • Swetha

    Awesome work.. explaining step by step procedure.. but i am not able to get the results. It gives the requested resource is not available. I dono where there is a problem. I would be greatful if you help me. Thank you.

  • Jerry Rogersome

    So, if I understand your example, you are storing a subset of the entire results of the query in a List of employees? Then feeding that to the jsp to display on the front end?

  • Gahininath

    I execute code same as above but it gives following error..

    java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘-10, 5′ at line 1
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2851)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1534)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1625)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:2291)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:2226)
    at com.mysql.jdbc.Statement.executeQuery(Statement.java:1159)
    at com.dao.EmployeeDAO.viewAllEmployees(EmployeeDAO.java:43)
    at com.servlet.EmployeeServlet.doGet(EmployeeServlet.java:34)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:270)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:191)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:227)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:211)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:817)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:623)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:444)
    at java.lang.Thread.run(Unknown Source)

  • shivdeep

    hi,

    i m facing a problem while fetching my results form db…m not able to display my fields retrived from DB in my table.whwn i run my project it shows me the following error:

    HTTP Status 404 – /JSPPagination/

    type Status report

    message /JSPPagination/

    description The requested resource (/JSPPagination/) is not available.

    m not able to find the error .pls help me ….

    Thx
    Shivdeep

  • Mudassir

    Really helpful. Thanks.

  • hemu

    very nice…thnx
    i want to use dynamic drop down box in employee table like..
    5
    10
    15
    20
    30
    40
    50
    how can i use…pls help me

  • sarir

    Thanks sir,you saved me lot of time and i really must say hatsoff…Doing really awesome work…..

  • KARAN

    I am using oracle XE .Can any one suggest me the query for oracle ??

  • rizwan

    Excellent example for the beginners in a simple and easy to understand way
    Please keep sending good posts like this

  • Nikunj

    It is too good for beginners .
    Thanks a lot.

  • Igor

    Hello Nithya.

    It is one of the most detailed and elegant explanation I’ve ever found. Cool and awesome!
    Right from this moment I am getting the fan of your blog and your teaching style.

    With regards,
    Igor Orlov

  • dj

    Thanks………….. Infonya Sangan membantu Banget.

  • Har

    Best in class

  • Hung Anh

    Man thanks a lot your code and comments are really easy to understand. Nice work man!

  • tvviem

    How to create Filter function from this example pagination? Thank you.

  • mrunal

    Hello ,,

    Thanks for the tutorial , I want to show all records field as well on my jsp page , Will you please tell me how to do that?

    Thanks.

  • Bharat

    Hi,
    Have you got solution for your query?

  • Amol Bhave

    Could you please add the code if user want to sort list by employee’s first name or last Name, or department name or based on salary?

  • Ashutosh

    Thanks for explanation.

    I have also uploaded jsp paging logic source code on following link. https://sourceforge.net/projects/paginglogic/

    Please refer as it may help you

  • Mr Lewis

    Worked like a charm…!! Thank you for unselfishly posting :)

  • sandy

    I get this error. I followed the same procedure you mentioned.

    HTTP Status 500 – The absolute uri: http://java.sun.com/jsp/jstl/core cannot be resolved in either web.xml or the jar files deployed with this application

    ——————————————————————————–

    type Exception report

    message The absolute uri: http://java.sun.com/jsp/jstl/core cannot be resolved in either web.xml or the jar files deployed with this application

    description The server encountered an internal error that prevented it from fulfilling this request.

    exception

    org.apache.jasper.JasperException: The absolute uri: http://java.sun.com/jsp/jstl/core cannot be resolved in either web.xml or the jar files deployed with this application
    org.apache.jasper.compiler.DefaultErrorHandler.jspError(DefaultErrorHandler.java:56)
    org.apache.jasper.compiler.ErrorDispatcher.dispatch(ErrorDispatcher.java:445)
    org.apache.jasper.compiler.ErrorDispatcher.jspError(ErrorDispatcher.java:117)
    org.apache.jasper.compiler.TagLibraryInfoImpl.generateTLDLocation(TagLibraryInfoImpl.java:311)
    org.apache.jasper.compiler.TagLibraryInfoImpl.(TagLibraryInfoImpl.java:152)
    org.apache.jasper.compiler.Parser.parseTaglibDirective(Parser.java:410)
    org.apache.jasper.compiler.Parser.parseDirective(Parser.java:475)
    org.apache.jasper.compiler.Parser.parseElements(Parser.java:1427)
    org.apache.jasper.compiler.Parser.parse(Parser.java:138)
    org.apache.jasper.compiler.ParserController.doParse(ParserController.java:242)
    org.apache.jasper.compiler.ParserController.parse(ParserController.java:102)
    org.apache.jasper.compiler.Compiler.generateJava(Compiler.java:198)
    org.apache.jasper.compiler.Compiler.compile(Compiler.java:373)
    org.apache.jasper.compiler.Compiler.compile(Compiler.java:353)
    org.apache.jasper.compiler.Compiler.compile(Compiler.java:340)
    org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:646)
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:357)
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

    note The full stack trace of the root cause is available in the Apache Tomcat/7.0.42 logs.

    ——————————————————————————–

    Apache Tomcat/7.0.42

  • pawan kumar

    i have an error in jsp page in same example

    javax.servlet.ServletException: javax/el/ValueExpression
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    com.theopentutorials.servlets.EmployeeServlet.doGet(EmployeeServlet.java:39)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:627)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:729)

    can you plz explain how can we remove it….plz

  • Naveed Ali

    Great Job!!!!!

    Thank you sOOOOOO much.

    i have one question that is why we invoke stmt.executeQuery(FOUND_ROWS() ) to get the total no. of records. cann’t we get total no of records from the first stmt.executeQuery () that we invoke earlier.

  • Srikkanth Sri

    Good Job. Very useful article. My problem is I want to show only 4-5 page numbers at any given point of time becoz sometimes there may be many number of pages …any help?

  • umesh

    what is the tomcat version and eclipse on which u tried?

    bcz I’m getting below exception
    (server tomcat 6.0)
    SEVERE: Parse error in application web.xml file at jndi:/localhost/JSPPagination/WEB-INF/web.xml

    org.xml.sax.SAXParseException; systemId: jndi:/localhost/JSPPagination/WEB-INF/web.xml; lineNumber: 16; columnNumber: 21; Error at (16, 21: Invalid Employee.do in servlet mapping

    at org.apache.tomcat.util.digester.Digester.createSAXException(Digester.java:2806)

    at org.apache.tomcat.util.digester.Digester.createSAXException(Digester.java:2832)

  • Thanan

    Great … Thanks Pal

  • chadaram vamsi

    If i have 100 pages of date how can i display that page numbers up to only because it not good to 100 page numbers in jsp page

  • Ali Alihandro

    Amazing, dude! Big thanks!

  • Pingback: Responsive Table in JSP with Footable.js | EraMaX Blog

  • Rajendra

    Hi i’m not getting the results pls help …i hav uploaded the screen shots

  • prasanna babu R

    awsome work…very kind of you