Pagination is a common problem in any language. Which lot of friends are facing it. So here i showing you how to create a pagination in jsp page.
What Is Pagination ?
Fetching thousand and millions of records from database is big time consuming and consumes more almost all CPU power with memory of machine.
If we break thousand of records into small chunks (Pages) with showing 10 or 25 some limited number of records, it will load records very faster and smoothly.
Pagination in JSP achieves by total number of rows and with limit the number of rows in ResultSet. If numbers of records are larger than limit specified rows, it will automatically make page index. It is like google pagination or google pagination index.
//----------------------------- index.jsp ------------------------------------//
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%!
public int nullIntconv(String str)
{
int conv=0;
if(str==null)
{
str="0";
}
else if((str.trim()).equals("null"))
{
str="0";
}
else if(str.equals(""))
{
str="0";
}
try{
conv=Integer.parseInt(str);
}
catch(Exception e)
{
}
return conv;
}
%>
<%
Connection conn = null;
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/employee","Username", "password");
ResultSet rsPagination = null;
ResultSet rsRowCnt = null;
PreparedStatement psPagination=null;
PreparedStatement psRowCnt=null;
int iShowRows=5; // Number of records show on per page
int iTotalSearchRecords=10; // Number of pages index shown
int iTotalRows=nullIntconv(request.getParameter("iTotalRows"));
int iTotalPages=nullIntconv(request.getParameter("iTotalPages"));
int pageid=nullIntconv(request.getParameter("pageid"));
int cPageNo=nullIntconv(request.getParameter("cPageNo"));
int iStartResultNo=0;
int iEndResultNo=0;
if(pageid==0)
{
pageid=0;
}
else{
pageid=Math.abs((pageid-1)*iShowRows);
}
String sqlPagination="SELECT SQL_CALC_FOUND_ROWS * FROM employee limit "+pageid+","+iShowRows+"";
psPagination=conn.prepareStatement(sqlPagination);
rsPagination=psPagination.executeQuery();
//// this will count total number of rows
String sqlRowCnt="SELECT FOUND_ROWS() as cnt";
psRowCnt=conn.prepareStatement(sqlRowCnt);
rsRowCnt=psRowCnt.executeQuery();
if(rsRowCnt.next())
{
iTotalRows=rsRowCnt.getInt("cnt");
}
%>
<html>
<head>
<title>Pagination of JSP page</title>
</head>
<body>
<form name="frm">
<input type="hidden" name="pageid" value="<%=pageid%>">
<input type="hidden" name="cPageNo" value="<%=cPageNo%>">
<input type="hidden" name="iShowRows" value="<%=iShowRows%>">
<table width="50%" cellpadding="5" cellspacing="0" border="1" >
<tr>
<th>Emp ID</th>
<th>Name</th>
<th>Salary</th>
<th>Department</th>
</tr>
<%
while(rsPagination.next())
{
%>
<tr>
<td><%=rsPagination.getString("emp_id")%></td>
<td><%=rsPagination.getString("emp_name")%></td>
<td><%=rsPagination.getString("salary")%></td>
<td><%=rsPagination.getString("dept_name")%></td>
</tr>
<%
}
%>
<%
//// calculate next record start record and end record
try{
if(iTotalRows<(pageid+iShowRows))
{
iEndResultNo=iTotalRows;
}
else
{
iEndResultNo=(pageid+iShowRows);
}
iStartResultNo=(pageid+1);
iTotalPages=((int)(Math.ceil((double)iTotalRows/iShowRows)));
}
catch(Exception e)
{
e.printStackTrace();
}
%>
<tr>
<td colspan="4">
<div>
<%
//// index of pages
int i=0;
int cPage=0;
if(iTotalRows!=0)
{
cPage=((int)(Math.ceil((double)iEndResultNo/(iTotalSearchRecords*iShowRows))));
int prePageNo=(cPage*iTotalSearchRecords)-((iTotalSearchRecords-1)+iTotalSearchRecords);
if((cPage*iTotalSearchRecords)-(iTotalSearchRecords)>0)
{
%>
<a href="index.jsp?pageid=<%=prePageNo%>&cPageNo=<%=prePageNo%>"> << Previous</a>
<%
}
for(i=((cPage*iTotalSearchRecords)-(iTotalSearchRecords-1));i<=(cPage*iTotalSearchRecords);i++)
{
if(i==((pageid/iShowRows)+1))
{
%>
<a href="index.jsp?pageid=<%=i%>" style="cursor:pointer;color: red"><b><%=i%></b></a>
<%
}
else if(i<=iTotalPages)
{
%>
<a href="index.jsp?pageid=<%=i%>"><%=i%></a>
<%
}
}
if(iTotalPages>iTotalSearchRecords && i<iTotalPages)
{
%>
<a href="index.jsp?pageid=<%=i%>&cPageNo=<%=i%>"> >> Next</a>
<%
}
}
%>
<b>Rows <%=iStartResultNo%> - <%=iEndResultNo%> Total Result <%=iTotalRows%> </b>
</div>
</td>
</tr>
</table>
</form>
</body>
</html>
<%
try{
if(psPagination!=null){
psPagination.close();
}
if(rsPagination!=null){
rsPagination.close();
}
if(psRowCnt!=null){
psRowCnt.close();
}
if(rsRowCnt!=null){
rsRowCnt.close();
}
if(conn!=null){
conn.close();
}
}
catch(Exception e)
{
e.printStackTrace();
}
%>
No comments:
Post a Comment