How to do pagination in JSP with Servlet JDBC and MYSQL

How to do pagination in JSP is very interesting concept let see step by step.

What is Pagination?

Pagination in programming refers to dividing an extensive data set into smaller, more manageable chunks called pages. It is commonly used when working with databases or APIs to efficiently handle large amounts of information.

Advantages of Pagination?

Using Pagination large data sets can be managed easily.

It improves performance and reduces system resources

Steps to develop Pagination in JSP Servlet

  1. Set page Size to show data (ex. 10 records per page)
  2. Fetch the total number of records from the database (ex 250 records in the database)
  3. Calculate the total number of pages that can be displayed (ex 25 pages)
  4. Get the Page number From the user (ex page no 5)
  5. Calculate starting value for the page and show data up to page size to the user (ex show record numbers 50 to 60)

Technology Used

  1. MySQL /MariaDB version: 10.4.28
  2. JAVA SE-17
  3. Apache Tomcat 10.1.1

Database Setup

For the database, we used mysql-country-list from GitHub copied countries_detailed.sql and past on our MySQL Database.

The basic fields of the database are as below

CREATE TABLE countries_detailed (
  id int(5) NOT NULL,
  countryCode char(2) NOT NULL DEFAULT '',
  countryName varchar(100) NOT NULL DEFAULT '',
  currencyCode char(3) DEFAULT NULL,
  fipsCode char(2) DEFAULT NULL,
  isoNumeric char(4) DEFAULT NULL,
  north varchar(30) DEFAULT NULL,
  south varchar(30) DEFAULT NULL,
  east varchar(30) DEFAULT NULL,
  west varchar(30) DEFAULT NULL,
  capital varchar(30) DEFAULT NULL,
  continentName varchar(100) DEFAULT NULL,
  continent char(2) DEFAULT NULL,
  languages varchar(100) DEFAULT NULL,
  isoAlpha3 char(3) DEFAULT NULL,
  geonameId int(10) DEFAULT NULL,
  countryPhoneCode varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The database contains total of 250 records.

JSP Servlet Project SetUp

How to do pagination in JSP Project Explorer
How to do pagination in JSP Project Explorer

Create a folder and file structure as above.

Dependency required

jakarta.servlet.jsp.jstl-3.0.0.jar
jakarta.servlet.jsp.jstl-api-3.0.0.jar
mysql-connector-java-8.0.30.jar

The source code are below

CountryServlet.java

package com.ebhor.controller;
import java.io.IOException;
import java.util.List;
import com.ebhor.dao.CountryDAO;
import com.ebhor.model.Country;
import jakarta.servlet.RequestDispatcher;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
@WebServlet("/country-list")
public class CountryServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	CountryDAO dao = new CountryDAO();
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		int pageNo = 0;
		int start = 0;
		String p1 = request.getParameter("p");
		int pageSize = 10;
		if (p1 == null) {
			pageNo = 1;
		} else {
			pageNo = Integer.parseInt(p1);
			start = (pageNo - 1) * pageSize;
		}
		List countryList = dao.fetchAll(start, pageSize);
		request.setAttribute("countries", countryList);
		double totalCount = dao.totalCount();
		long totalPages = (long) Math.ceil(totalCount / pageSize);
		request.setAttribute("totalCount", countryList);
		request.setAttribute("totalPages", totalPages);
		request.setAttribute("pageSize", pageSize);
		request.setAttribute("p", pageNo);
		RequestDispatcher requestDispatcher = request.getRequestDispatcher("/country.jsp");
		requestDispatcher.forward(request, response);
	}
}

JDBC Code for Pagination

ConnectionFactory.java

This class returns a connection object.

package com.ebhor.dao;

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

public class ConnectionFactory {

	public static Connection getConnection() {
		Connection c = null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			c = DriverManager.getConnection("jdbc:mysql://localhost:3306/ebhor", "root", "");
		} catch (ClassNotFoundException e) {
			System.out.println("ClassNotFoundException " + e);
		} catch (SQLException e) {
			System.out.println("SQLException " + e);
		}
		return c;
	}
}

CountryDAO.java

JDBC PreparedStatemnt is used to fetch data from the database.

In the countries_detailed table, we have a total of 250 records we are selecting only a few records based on the calculation. limit is used in SQL query is used to select only a few records.

package com.ebhor.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.ebhor.model.Country;
public class CountryDAO {
	Connection con = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	public List fetchAll(int start, int pageSize) {
		List countryList = new ArrayList();
		con = ConnectionFactory.getConnection();
		try {
			String query = "select * from countries_detailed order by id limit ?,?";
			ps = con.prepareStatement(query);
			ps.setInt(1, start);
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
			while (rs.next()) {
				Country country = new Country();
				country.setId(rs.getInt("id"));
				country.setCountryCode(rs.getString("countryCode"));
				country.setCountryName(rs.getString("countryName"));
				country.setCurrencyCode(rs.getString("countryCode"));
				country.setFipsCode(rs.getString("fipsCode"));
				country.setIsoNumeric(rs.getString("isoNumeric"));
				country.setCapital(rs.getString("capital"));
				country.setContinentName(rs.getString("continentName"));
				country.setContinent(rs.getString("continent"));
				country.setLanguages(rs.getString("languages"));
				country.setIsoAlpha3(rs.getString("isoAlpha3"));
				countryList.add(country);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		return countryList;
	}
	public long totalCount() {
		long count = 0;
		con = ConnectionFactory.getConnection();
		try {
			String query = "select count(*) as count from countries_detailed";
			ps = con.prepareStatement(query);
			rs = ps.executeQuery();
			while (rs.next()) {
				count = rs.getLong("count");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		return count;
	}
}

Model class for Fetching data

This class contains fields related to a database table. We have selected only few columns you can increase or decrease as per need.

package com.ebhor.model;

import java.io.Serializable;

public class Country implements Serializable {

	private static final long serialVersionUID = -1878384027132407705L;
	private int id;
	private String countryCode;
	private String countryName;
	private String currencyCode;
	private String fipsCode;
	private String isoNumeric;
	private String capital;
	private String continentName;
	private String continent;
	private String languages;
	private String isoAlpha3;

	
	public Country() {
	}


	public Country(int id, String countryCode, String countryName, String currencyCode, String fipsCode,
			String isoNumeric, String capital, String continentName, String continent, String languages,
			String isoAlpha3) {
		super();
		this.id = id;
		this.countryCode = countryCode;
		this.countryName = countryName;
		this.currencyCode = currencyCode;
		this.fipsCode = fipsCode;
		this.isoNumeric = isoNumeric;
		this.capital = capital;
		this.continentName = continentName;
		this.continent = continent;
		this.languages = languages;
		this.isoAlpha3 = isoAlpha3;
	}


	@Override
	public String toString() {
		return "Country [id=" + id + ", countryCode=" + countryCode + ", countryName=" + countryName + ", currencyCode="
				+ currencyCode + ", fipsCode=" + fipsCode + ", isoNumeric=" + isoNumeric + ", capital=" + capital
				+ ", continentName=" + continentName + ", continent=" + continent + ", languages=" + languages
				+ ", isoAlpha3=" + isoAlpha3 + "]";
	}


	public int getId() {
		return id;
	}


	public void setId(int id) {
		this.id = id;
	}


	public String getCountryCode() {
		return countryCode;
	}


	public void setCountryCode(String countryCode) {
		this.countryCode = countryCode;
	}


	public String getCountryName() {
		return countryName;
	}


	public void setCountryName(String countryName) {
		this.countryName = countryName;
	}


	public String getCurrencyCode() {
		return currencyCode;
	}


	public void setCurrencyCode(String currencyCode) {
		this.currencyCode = currencyCode;
	}


	public String getFipsCode() {
		return fipsCode;
	}


	public void setFipsCode(String fipsCode) {
		this.fipsCode = fipsCode;
	}


	public String getIsoNumeric() {
		return isoNumeric;
	}


	public void setIsoNumeric(String isoNumeric) {
		this.isoNumeric = isoNumeric;
	}


	public String getCapital() {
		return capital;
	}


	public void setCapital(String capital) {
		this.capital = capital;
	}


	public String getContinentName() {
		return continentName;
	}


	public void setContinentName(String continentName) {
		this.continentName = continentName;
	}


	public String getContinent() {
		return continent;
	}


	public void setContinent(String continent) {
		this.continent = continent;
	}


	public String getLanguages() {
		return languages;
	}


	public void setLanguages(String languages) {
		this.languages = languages;
	}


	public String getIsoAlpha3() {
		return isoAlpha3;
	}


	public void setIsoAlpha3(String isoAlpha3) {
		this.isoAlpha3 = isoAlpha3;
	}


	public static long getSerialversionuid() {
		return serialVersionUID;
	}
	
	
	
	
}

Country.jsp Jsp Page to show pagination data

On clicking on each page, it call the servlet URL /country-list.

along with the URL, we pass the query string /country-list?p=2.

p represents the page no based on the page number we calculate from which count selects a record and from where to select it.

In Servlet we calculate the start below

start = (pageNo - 1) * pageSize;

query string p is stored in pageNo and pageSize is defined in Servlet.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="utf-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>



Country Pagination Example






	

		

			

			

				
					
List of Countries

					
								

						
							

								#
								Country Name
								Country Code
								Currency Code
								Fips Code
								Iso Numeric
								Capital
								Continent Name
								Continent
								Languages
								IsoAlpha3
							

						
						
							

									${((p-1)*pageSize)+(loop.index+1)}
									${country.countryName}
									${country.countryCode}
									${country.currencyCode}
									${country.fipsCode}
									${country.isoNumeric}
									${country.capital}
									${country.continentName}
									${country.continent}
									${country.languages}
									${country.isoAlpha3}
								

							
						
					

					
				
				
					
No Record Found

Web.xml to Show URL

welcome file tag is used to show the servlet on run.



  ServletJDBC
  
    country-list

Result

It will show following output

Download Source Code

How To Do Pagination In JSP Source CodeDownload

Hope you Understand How to do pagination in JSP.