JSP 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.

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();
    }
%>
Bookmark  

 

8 Responses to “JSP Pagination”

  1. Don Jomar says:
    June 3, 2009 at 10:31 am

    Thank you very much for the idea.

  2. Swami says:
    July 11, 2009 at 10:51 pm

    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.

  3. carlos says:
    August 3, 2009 at 11:18 pm

    Thanks for your post, it helps me a lot.

  4. Anand Shankar says:
    November 10, 2009 at 10:23 am

    Thanks a lot to share the ideas.

  5. chris says:
    February 25, 2010 at 6:25 pm

    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

  6. Lenin says:
    March 20, 2010 at 11:22 pm

    It’s Very Useful

  7. Mohanraj says:
    May 29, 2010 at 10:26 pm

    Thanks .

  8. muswanto says:
    August 28, 2010 at 9:13 am

    Thnx..GBU,,^_^

Leave a Reply

Security Code:

 

  Random Post