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.
User can go through next page by page index number and explore limited records in per pages.
MySql function SQL_CALC_FOUND_ROWS gives us total number of rows in sql query with limit clause, FOUND_ROWS() function fetch number count in result.
<%@ 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/DataBase","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 iPageNo=nullIntconv(request.getParameter("iPageNo")); int cPageNo=nullIntconv(request.getParameter("cPageNo")); int iStartResultNo=0; int iEndResultNo=0; if(iPageNo==0) { iPageNo=0; } else{ iPageNo=Math.abs((iPageNo-1)*iShowRows); } String sqlPagination="SELECT SQL_CALC_FOUND_ROWS * FROM tableName limit "+iPageNo+","+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="iPageNo" value="<%=iPageNo%>"> <input type="hidden" name="cPageNo" value="<%=cPageNo%>"> <input type="hidden" name="iShowRows" value="<%=iShowRows%>"> <table width="100%" cellpadding="0" cellspacing="0" border="0" > <tr> <td>Name</td> <td>Batch</td> <td>Address</td> </tr> <% while(rsPagination.next()) { %> <tr> <td><%=rsPagination.getString("sName")%></td> <td><%=rsPagination.getString("batchs")%></td> <td><%=rsPagination.getString("address")%></td> </tr> <% } %> <% //// calculate next record start record and end record try{ if(iTotalRows<(iPageNo+iShowRows)) { iEndResultNo=iTotalRows; } else { iEndResultNo=(iPageNo+iShowRows); } iStartResultNo=(iPageNo+1); iTotalPages=((int)(Math.ceil((double)iTotalRows/iShowRows))); } catch(Exception e) { e.printStackTrace(); } %> <tr> <td colspan="3"> <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?iPageNo=<%=prePageNo%>&cPageNo=<%=prePageNo%>"> << Previous</a> <% } for(i=((cPage*iTotalSearchRecords)-(iTotalSearchRecords-1));i<=(cPage*iTotalSearchRecords);i++) { if(i==((iPageNo/iShowRows)+1)) { %> <a href="index.jsp?iPageNo=<%=i%>" style="cursor:pointer;color: red"><b><%=i%></b></a> <% } else if(i<=iTotalPages) { %> <a href="index.jsp?iPageNo=<%=i%>"><%=i%></a> <% } } if(iTotalPages>iTotalSearchRecords && i<iTotalPages) { %> <a href="index.jsp?iPageNo=<%=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(); } %>
Loading ...
Link to Us
Thank you very much for the idea.
Thanks for the logic. I have a query.
When I try implement this logic with iShowRows=5 & iTotalSearchRecords=10; I am always getting cPage and prePageNo as 1 and -9 respectively. Due this Prev & Next alone is not working. Please clarify.
Thanks for your post, it helps me a lot.
Thanks a lot to share the ideas.
What’s that i shld do if i’m using oracle 11. is there any alternative for SQL_CALC_FOUND_ROWS and FOUND_ROWS() MySql functions?
Thanks in advance
It’s Very Useful
Thanks .
Thnx..GBU,,^_^
Can any one please tell me from where iTotalRows and iTotalPages are initialised
request.getParameter(\"iTotalRows\");
request.getParameter(\"iTotalPages\"));
can any one please post index.jsp
Thank u.
function fnPage(CurrentPage){
document.frmProduct.CurrentPage.value=CurrentPage;
document.frmProduct.action=”";
document.frmProduct.submit();
}
function fnPagetxt(totalpage){
var page=document.frmProduct.pageNo.value;
if(isNaN(page)){
alert(”Please Enter a Number”);
document.frmProduct.pageNo.value=”";
return false;
}
if(page>totalpage ){
alert(”Please Enter a Number <="+totalpage);
document.frmProduct.pageNo.value="";
return false;
}
if(page0″);
document.frmProduct.pageNo.value=”";
return false;
}
document.frmProduct.CurrentPage.value=page;
document.frmProduct.action=”";
document.frmProduct.submit();
}
String CurrPage=(request.getParameter(”CurrentPage”)==null?”1″:request.getParameter(”CurrentPage”));
int noOfpage=0;
int CurrentPageNo=0;
int RecordNo=0;
int Start=0;
int End=0;
int[] dropdown = new int[] {10,25,50};
int recordsperpage=Integer.parseInt(SelectedDrop);
if(!CurrPage.equals(”")){
CurrentPageNo=Integer.parseInt(CurrPage);
}
if(!SelectedBUC.equals(”") && !BillingYear.equals(”"))
{
totalrecords=fabrutil.getRecordCount(SelectedBUC,BillingYear);
noOfpage=totalrecords/recordsperpage;
if((totalrecords%recordsperpage)!=0){
noOfpage=noOfpage+1;
}
}
if(!SelectedDecCode.equals(”") && !SelectedBUC.equals(”") && !BillingYear.equals(”") && !SelectedLocation.equals(”"))
{
RecordNo=fabrutil.getRecordNumber(SelectedBUC,BillingYear,SelectedDecCode,SelectedLocation);
CurrentPageNo=RecordNo/recordsperpage;
if((recordsperpage*CurrentPageNo)!=RecordNo)
{
CurrentPageNo=CurrentPageNo+1;
}
}
String StartIndex=((CurrentPageNo-1)*recordsperpage+1)+”";
String EndIndex=(CurrentPageNo*recordsperpage)+”";
if(CurrentPageNo==noOfpage){
EndIndex=totalrecords+”";
}
if(CurrentPageNo<4 || noOfpage1 && noOfpage(noOfpage-3)){
Start=noOfpage-4;
End=noOfpage;
}else{
Start=CurrentPageNo-2;
End=CurrentPageNo+2;
}
0){
if(Start>0){
%>
Records – of
Page of
Records Per Page
<%for(int i=0;i
<option value="” selected >
1)
{ %>
|< First
5){ %>
<a href="#" onclick='javascript:fnPage("”);’ align=”left”> < Previous 5
<% }
for (int i=Start; i
<a href="#" onclick='javascript:fnPage("”);’>
<%} } if(End
<a href="#" onclick='javascript:fnPage("”);’ align=”left”> Next 5>
<%}if(End
<a href="#" onclick='javascript:fnPage("”);’> Last >|
=10){%>
Page No(<= )
<input type="button" name="GO" value="GO" onclick="fnPagetxt();”>
where do these parameter be initialized??
int iTotalRows=nullIntconv(request.getParameter(”iTotalRows”));
int iTotalPages=nullIntconv(request.getParameter(”iTotalPages”));
int iPageNo=nullIntconv(request.getParameter(”iPageNo”));
int cPageNo=nullIntconv(request.getParameter(”cPageNo”));
When i try above code i get an exception which is like this:—
org.apache.jasper.JasperException: An exception occurred processing JSP page /jsp/pagination.jsp at line 66
63:
64: String sqlPagination=\"SELECT SQL_CALC_FOUND_ROWS * FROM REG limit \"+iPageNo+\",\"+iShowRows+\"\";
65:
66: psPagination=conn.prepareStatement(sqlPagination);
67: rsPagination=psPagination.executeQuery();
68:
69: //// this will count total number of rows
Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:524)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:435)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:320)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
Iv been looking for a nice pagination script for several hours and finally i come to yours
THANK GOD!!! very nice
can anybody tell me that from where you are retrieving rows from database??? plz tell me that from where you are bringing Name, Batch and Address? From where you are bringing these:
Hi Swamy
index.jsp is the same page what you are viewing
Pagination of JSP page
<input type="hidden" name="iPageNo" value="”>
<input type="hidden" name="cPageNo" value="”>
<input type="hidden" name="iShowRows" value="”>
Name
Batch
Address
<%
//// calculate next record start record and end record
try{
if(iTotalRows
0)
{
%>
<a href="sample.jsp?iPageNo=&cPageNo=”> << Previous
<%
}
for(i=((cPage*iTotalSearchRecords)-(iTotalSearchRecords-1));i
<a href="sample.jsp?iPageNo=” style=”cursor:pointer;color: red”>
<%
}
else if(i
<a href="sample.jsp?iPageNo=”>
iTotalSearchRecords && i
<a href="sample.jsp?iPageNo=&cPageNo=”> >> Next
Rows – Total Result
Great Tutorial…..u explained everything in a very easiest and possible way
Please guys am new to J2EE I want to paginate a large result set with JSTL from an SQL query am using MySQL for eg. if i have 50 records in my DB and i need only 10 records at a time then i will have a next and previous links to go through the records my sql query looks like this
SELECT * FROM employee LIMIT 0, 10
${employee.firstname}