Tuesday, July 1, 2014

Paging In JSP and SQL Server 2008

·         <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
·         pageEncoding="ISO-8859-1"%>
·         <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
·         <jsp:useBean id="db" class="com.mssql.test.MssqlDB"></jsp:useBean>
·         <html>
·         <head>
·         <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
·         <title>Insert title here</title>
·         <style type="text/css">
·         .tg  {border-collapse:collapse;border-spacing:0;}
·         .tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
·         .tg th{font-family:Arial, sans-serif;background-color:orange; font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
·         </style>
 
·         </head>
·         <body>
·         <%@ page import="java.sql.ResultSet" %>
·         <%!
·         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;
·         }
·         %>
·         <%
 
 
·         ResultSet rsPagination = null;
·         ResultSet rsRowCnt = 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 account,name,phone FROM (SELECT *, PageNumber = CEILING( row_number() over (order by account)/"+iTotalSearchRecords+")  FROM guest) MyQuery WHERE MyQuery.PageNumber = "+iPageNo+"";
 
·         rsPagination= db.getResultset(sqlPagination);
 
·         //// this will count total number of rows
·         String sqlRowCnt="SELECT count(*) as cnt from guest";
·         rsRowCnt= db.getResultset(sqlRowCnt);
 
·         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="70%" class="tg" cellpadding="0" cellspacing="0" border="1" >
·         <tr >
·         <th class="tg-031e">Account</th>
·         <th class="tg-031e">Name</th>
·         <th class="tg-031e">Phone</th>
·         </tr>
·         <%
·         while(rsPagination.next())
·         {
·         %>
·         <tr>
·         <td><%=rsPagination.getString("account")%></td>
·         <td><%=rsPagination.getString("name")%></td>
·         <td><%=rsPagination.getString("phone")%></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="PagingTable.jsp?iPageNo=<%=prePageNo%>&cPageNo=<%=prePageNo%>"> << Previous</a>
·         <%
·         }
 
·         for(i=((cPage*iTotalSearchRecords)-(iTotalSearchRecords-1));i<=(cPage*iTotalSearchRecords);i++)
·         {
·         if(i==((iPageNo/iShowRows)+1))
·         {
·         %>
·         <a href="PagingTable.jsp?iPageNo=<%=i%>" style="cursor:pointer;color: red"><b><%=i%></b></a>
·         <%
·         }
·         else if(i<=iTotalPages)
·         {
·         %>
·         <a href="PagingTable.jsp?iPageNo=<%=i%>"><%=i%></a>
·         <%
·         }
·         }
·         if(iTotalPages>iTotalSearchRecords && i
·         {
·         %>
·         <a href="PagingTable.jsp?iPageNo=<%=i%>&cPageNo=<%=i%>"> >> Next</a>
·         <%
·         }
·         }
·         %>
·         <b>Rows <%=iStartResultNo%> - <%=iEndResultNo%> Total Result <%=iTotalRows%> </b>
·         </div>
·         </td>
·         </tr>
·         </table>
·         </form>
·         </body>
·         </html>
·         <%
·         try{
 
·         if(rsPagination!=null){
·         rsPagination.close();
·         }
 
·         if(rsRowCnt!=null){
·         rsRowCnt.close();
·         }
 
 
·         }
·         catch(Exception e)
·         {
·         e.printStackTrace();
·         }
·         %>
·         </body>
    </html>
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)