Code Robo
Formatter
Comparator
Tester
Converter
Utility
Java Code Complience
Validator
EncoderDecoder
Virtual Service
How To Implement Pagination.
       Talk to EasyAssistant

This page demonstrate what is pagination, its need and how to implement it. . Implementation silightly varies from database to database.

Here one example is given to show the implementation of pagination with MySQL database and spring. Sample principle can be applied for other database too.
What is pagination and why its needed.
Pagination is the way to show the data page by page in UI instead of showing in a single page. Sometimes search result contains many records. Its impossible to show them in a single page.
Mainly pagination is required for the following reasons.
  1. Retrieving all the records (more than 200) from database is performance intensive. It will bring down entire application's performance.
  2. Sending huge data from server to client in one go will take longer time and page response time will be very high. User will not accept it.
  3. Its inconvenient for the the end-user to scroll down and view all the data.
Here one example is given to show how to implement pagination using Spring JdbcTemplate and MySql database. In this example we have 2 database tables. They have One-To-Many relationship between the tables. Here are the 2 tables
  • EMPLOYEE
  • DEPENDANT
We want to fetch list of all the employee and their dependents records page by page whose salary > 1000. Output will be sorted by employee name. So to get the data we need execute following query. But this query will give all the records together. Not page by page.
SELECT
   EMPLOYEE.*,
   DEPENDANT.*
FROM
   EMPLOYEE,
   DEPENDANT 
WHERE
   1 = 1 
   AND EMPLOYEE.EMP_ID = DEPENDANT.EMP_ID
   AND EMPLOYEE.BASIC_SAL > 1000
We will show how to modify this query to get one page data at a time. Look into the DAO implmentation class . If you are fetching records from single table, its littlebit simpler. It can be done using a single SQL query only. But if you are fetching data from multiple tables, then two SQL queries are required (Look into the DAO implementation class) to fetch one page data at a time. Here I am using MySql database, I have used LIMIT keyword (specific to MySql) to select a subset of records. If you use other database, you need to use their corresponding keyword to restrict the records. For Oracle database ROWNUM can be used

1. Database Table Structure
EMPLOYEE TABLE:
 	
CREATE TABLE `employee` (
  `EMPLOYEE_KEY` bigint  PRIMARY KEY, 
  `EMP_ID` varchar(100) DEFAULT NULL,
  `NAME` varchar(100) DEFAULT NULL,
  `DEPT_CODE` varchar(100) DEFAULT NULL,
  `BASIC_SAL` decimal(8,2) DEFAULT NULL,
  `DATE_OF_JOINING` date DEFAULT NULL,
  `ADDRESS` varchar(500) DEFAULT NULL
)
DEPENDANT:
CREATE TABLE `dependant` (
  `DEPENDANT_KEY` bigint  PRIMARY KEY,
  `DEPENDANT_ID` varchar(100) DEFAULT NULL,
  `DEPENDANT_NAME` varchar(100) DEFAULT NULL,
  `EMP_ID` varchar(100) DEFAULT NULL,
  `RELATION_WITH_EMP` varchar(100) DEFAULT NULL,
  `AGE` decimal(5,2) DEFAULT NULL
) 
2. JAVA BEAN CLASSES
Employees:
public class Employees  {
	private List<Employee> employeeList = new ArrayList();
}
Employee :
public class Employee {
	Dependants dependants;
	private String address;
	private Double basicSal;
	private Date dateOfJoining;
	private String deptCode;
	private String empId;
	private Long employeeKey;
	private String name;
}
Dependants:
public class Dependants {
	private List<Dependant< dependantList = new ArrayList();
}
Dependant:
public class Dependant {
	Hospitalisations hospitalisations;
	private String age;
	private Long employeeKey;
	private String dependantId;
	private Long dependantKey;
	private String dependantName;
	private String empId;
	private String relationWithEmp;
	private String tableName;
}
3. DAO Class
DAO Code:
				
package jdbc.example.pagintion;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import jdbc.example.dependant.bean.Dependant;
import jdbc.example.dependant.bean.Dependants;
import jdbc.example.employee.bean.Employee;

@Repository("getEmpListApiDAO")
public class GetEmpListApiDAO {
	private static Logger log = LoggerFactory.getLogger(GetEmpListApiDAO.class);

	private int pageSize = 2;
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public List<Employee> getEmpListWithPagenation(Employee employee, String lastRecordKeyValue) {

		String pageMinKey;
		String pageMaxKey;

		List<Object> minMaxKyList = getMinMaxKeyFromHeaderTableForAPage(employee, lastRecordKeyValue);

		if (minMaxKyList.isEmpty())
			return new ArrayList<Employee>();
		pageMinKey = "" + minMaxKyList.get(0);
		pageMaxKey = "" + minMaxKyList.get(1);

		String extendedWhereClause = "";
		List<Object> paramList = new ArrayList();

		String str = "";
		str = str + "SELECT\n";
		str = str + "   EMPLOYEE.*,\n";
		str = str + "   DEPENDANT.*\n";
		str = str + "FROM\n";
		str = str + "   EMPLOYEE,\n";
		str = str + "   DEPENDANT \n";
		str = str + "WHERE\n";
		str = str + "   1 = 1 \n";
		str = str + "   AND EMPLOYEE.EMP_ID = DEPENDANT.EMP_ID AND EMPLOYEE.BASIC_SAL > 1000 \n";
		String getEmpListApiQueryWithPagenation = str;

		String orderBy = " ASC ";

		extendedWhereClause = extendedWhereClause + " AND EMPLOYEE.NAME >= '" + pageMinKey + "' AND EMPLOYEE.NAME <= '"
				+ pageMaxKey + "' ";
		extendedWhereClause = extendedWhereClause + " ORDER BY EMPLOYEE.NAME " + orderBy;

		getEmpListApiQueryWithPagenation = getEmpListApiQueryWithPagenation + extendedWhereClause;

		System.out.println("getEmpListApiQueryWithPagenation=" + getEmpListApiQueryWithPagenation);

		return jdbcTemplate.query(getEmpListApiQueryWithPagenation, paramList.toArray(),
				new ResultSetExtractor<List<Employee>>() {
					@Override
					public List<Employee> extractData(ResultSet rs) throws SQLException, DataAccessException {

						List<Employee> list = new ArrayList<Employee>();
						Map<Long, Employee> employeeKeyEmployeeMap = new HashMap<Long, Employee>();
						Map<Long, Dependant> dependantKeyDependantMap = new HashMap<Long, Dependant>();
						while (rs.next()) {

							Long employeeKey = rs.getLong("EMPLOYEE_KEY");
							Employee employee = employeeKeyEmployeeMap.get(employeeKey);
							if (employee == null) {
								employee = new Employee();
								list.add(employee);
								employee.setEmployeeKey(employeeKey);
								log.info("employeeKey=" + employee.getEmployeeKey());
								employee.setEmployeeKey(rs.getLong("EMPLOYEE_KEY"));
								employee.setBasicSal(rs.getDouble("BASIC_SAL"));
								employee.setDateOfJoining(rs.getDate("DATE_OF_JOINING"));
								employee.setDeptCode(rs.getString("DEPT_CODE"));
								employee.setEmpId(rs.getString("EMP_ID"));
								employee.setName(rs.getString("NAME"));
							}
							Long dependantKey = rs.getLong("DEPENDANT_KEY");
							Dependant dependant = dependantKeyDependantMap.get(dependantKey);
							log.info("dependantKeyML=" + dependantKey);
							if (dependant == null) {
								dependant = new Dependant();
								if (employee.getDependants() == null)
									employee.setDependants(new Dependants());
								if (employee.getDependants().getDependantList() == null)
									employee.getDependants().setDependantList(new ArrayList<Dependant>());

								employee.getDependants().getDependantList().add(dependant);
								dependantKeyDependantMap.put(dependantKey, dependant);
								dependant.setDependantKey(dependantKey);
								dependant.setDependantKey(rs.getLong("DEPENDANT_KEY"));
								dependant.setAge(rs.getString("AGE"));
								dependant.setDependantId(rs.getString("DEPENDANT_ID"));
								dependant.setDependantName(rs.getString("DEPENDANT_NAME"));
								dependant.setRelationWithEmp(rs.getString("RELATION_WITH_EMP"));
							}
						}
						return list;
					}
				});
	}

	public List<Object> getMinMaxKeyFromHeaderTableForAPage(Employee employee, String lastRcordKeyValue) {

		List<Object> paramList = new ArrayList();

		if (lastRcordKeyValue == null)
			lastRcordKeyValue = "0";

		String str = "";
		str = str + "SELECT\n";
		str = str + "   EMPLOYEE.*,\n";
		str = str + "   DEPENDANT.*\n";
		str = str + "FROM\n";
		str = str + "   EMPLOYEE,\n";
		str = str + "   DEPENDANT \n";
		str = str + "WHERE\n";
		str = str + "   1 = 1 \n";
		str = str + "   AND EMPLOYEE.EMP_ID = DEPENDANT.EMP_ID EMPLOYEE.BASIC_SAL > 1000 \n";
		String getEmpListApiQueryWithPagenation = str;

		String orderBy = " ASC ";

		String extendedWhereClause = " ";
		extendedWhereClause = extendedWhereClause + " AND EMPLOYEE.NAME >= '" + lastRcordKeyValue + "' ";

		extendedWhereClause = extendedWhereClause + " ORDER BY EMPLOYEE.NAME " + orderBy + " LIMIT " + pageSize + " ";

		getEmpListApiQueryWithPagenation = getEmpListApiQueryWithPagenation + extendedWhereClause;

		System.out.println("minMaxSql=" + getEmpListApiQueryWithPagenation);
		return jdbcTemplate.query(getEmpListApiQueryWithPagenation, paramList.toArray(),
				new ResultSetExtractor<List<Object>>() {
					@Override
					public List<Object> extractData(ResultSet rs) throws SQLException, DataAccessException {

						Object minKey = null;
						Object maxKey = null;
						List<Object> list = new ArrayList<Object>();
						int count = 1;

						while (rs.next()) {
							if (count == 1) {
								minKey = rs.getString("NAME");
								count++;
							}
							maxKey = rs.getString("NAME");
						}

						if (minKey == null)
							return list;

						list.add(minKey);
						list.add(maxKey);
						return list;
					}
				});
	}

}
4. Service Class
Service Code:
			 
package jdbc.example.pagintion;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.TransactionException;

import jdbc.example.employee.bean.Employee;

@Service("getEmpListApiService")
public class GetEmpListApiService {
	@Autowired
	private GetEmpListApiDAO getEmpListApiDAO;
	private static Logger log = LoggerFactory.getLogger(GetEmpListApiService.class);

	public List<Employee> getEmplistWithPagenation(Employee employee, String lastRecordKey)
			throws TransactionException {
		try {
			return getEmpListApiDAO.getEmpListWithPagenation(employee, lastRecordKey);

		} catch (Exception ex) {
			throw ex;
		}
	}
}
					
						

Here only service, bean classes and DAO code is given. Entire example code will be available for download very soon.
Please provide your feedback here


User Comments:
anonymous (2022-04-16) :
We are using Oracle database and in our table primary key is non-numeric(VARCHAR2). How should we implementation pagination.?
    Reply;
easycodeforall: (2022-04-21)
Alwyas we may not have the numeric column as primary key. Our pagination purpose is just to fetch only 200 records (page size) from the database instead of fetching all the matching records. Pagination depends on which database we are using. 1.) If we are using Oracle database, we can use rownum pseudo column to fetch only a page size (200) records. 2.) If we are using sqlserver, we can use" set rowcount 200" command to restrict to fetch only 200 records. 3.) If we are using MySQL, we can use 'LIMIT' keyword to fetch 200 records. example. - SELECT * FROM TABLE_NAME LIMIT 200
    Reply


Post Your Comment:
Name :
Email ( Optional) :
Comments / Suggestion (* Required) It is required: :
: