<p><%@ page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %></p> <p><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <%@ page import="dataBase.*" %> <%@ page import="java.sql.*" %> <%! public int nullIntconvert(String str){ int num=0; if(str==null){ str="0"; } else if((str.trim()).equals("null")){ str="0"; } else if(str.equals("")) { str="0"; } try{ num=Integer.parseInt(str); } catch(Exception e){ } return num; } %> <% Connection conn = null; //Here using package for database Connection and it is correct... conn=DbConnection.getConnection(); ResultSet rs1 = null; ResultSet rs2 = null; PreparedStatement ps1=null; PreparedStatement ps2=null;</p> <p>int showRows=5; int totalRecords=5; int totalRows=nullIntconvert(request.getParameter("totalRows")); int totalPages=nullIntconvert(request.getParameter("totalPages")); int iPageNo=nullIntconvert(request.getParameter("iPageNo")); int cPageNo=nullIntconvert(request.getParameter("cPageNo"));</p> <p>int startResult=0; int endResult=0; if(iPageNo==0){ iPageNo=0; } else{ iPageNo=Math.abs((iPageNo-1)*showRows); } String query1="SELECT SQL<em>CALC</em>FOUND_ROWS * FROM db2admin.reglimit "+iPageNo+","+showRows+""; ps1=conn.prepareStatement(query1); rs1=ps1.executeQuery();</p> <p>String query2="SELECT FOUND_ROWS() as cnt"; ps2=conn.prepareStatement(query2); rs2=ps2.executeQuery(); if(rs2.next()){ totalRows=rs2.getInt("cnt"); } %> <html><h3>Pagination of JSP page</h3> <body> <form> <input type="hidden" name="iPageNo" value="<%=iPageNo%>"> <input type="hidden" name="cPageNo" value="<%=cPageNo%>"> <input type="hidden" name="showRows" value="<%=showRows%>"> <table width="100%" cellpadding="0" cellspacing="0" border="1" > <tr> <th>Name</th><th>Address</th><th>Contact</th><th>Email</th> </tr> <% while(rs1.next()){ %> <tr><td><%=rs1.getString("USERNAME")%></td> <td><%=rs1.getString("FIRSTNAME")%></td> <td><%=rs1.getInt("EMAIL")%></td> <td><%=rs1.getString("COUNTRY")%></td> </tr> <%}%> <% try{ if(totalRows<(iPageNo+showRows)){ endResult=totalRows; } else{ endResult=(iPageNo+showRows); } startResult=(iPageNo+1); totalPages=((int)(Math.ceil((double)totalRows/showRows))); } catch(Exception e){ e.printStackTrace(); } %> <tr><td colspan="3"> <div> <% int i=0; int cPage=0; if(totalRows!=0){ cPage=((int)(Math.ceil((double)endResult/(totalRecords*showRows)))); int prePageNo=(cPage*totalRecords)-((totalRecords-1)+totalRecords); if((cPage*totalRecords)-(totalRecords)>0){ %> <a href="pagination.jsp?iPageNo=<%=prePageNo%>&cPageNo=<%=prePageNo%>">Previous</a> <% } for(i=((cPage<em>totalRecords)-(totalRecords-1));i<=(cPage</em>totalRecords);i++){ if(i==((iPageNo/showRows)+1)){%> <a href="pagination.jsp?iPageNo=<%=i%>" style="cursor:pointer;color: red"><b><%=i%></b></a> <% } else if(i<=totalPages){ %> <a href="pagination.jsp?iPageNo=<%=i%>"><%=i%></a> <% } } if(totalPages>totalRecords && i<totalPages){ %> <a href="pagination.jsp?iPageNo=<%=i%>&cPageNo=<%=i%>">Next</a> <% } } %> <b>Rows <%=startResult%> - <%=endResult%> Total Rows <%=totalRows%> </b> </div></td></tr></table></form></body></html></p>
<%@ page import="java.sql.*" %> <%! public int nullIntconvert(String str){ int num=0; if(str==null){ str="0"; } else if((str.trim()).equals("null")){ str="0"; } else if(str.equals("")) { str="0"; } try{ num=Integer.parseInt(str); } catch(Exception e){ } return num; } %> <% Class.forName("com.mysql.jdbc.Driver").newInstance(); Connectionconn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2admin","root", "root"); int showRows=5; int totalRecords=5; int totalRows=nullIntconvert(request.getParameter("totalRows")); int totalPages=nullIntconvert(request.getParameter("totalPages")); int iPageNo=nullIntconvert(request.getParameter("iPageNo")); int cPageNo=nullIntconvert(request.getParameter("cPageNo")); int startResult=0; int endResult=0; if(iPageNo==0){ iPageNo=0; } else{ iPageNo=Math.abs((iPageNo-1)*showRows); } String query1="SELECT SQL_CALC_FOUND_ROWS * FROM db2admin.reg limit "+iPageNo+","+showRows+""; PreparedStatement ps1=conn.prepareStatement(query1); ResultSet rs1=ps1.executeQuery(); String query2="SELECT FOUND_ROWS() as cnt"; PreparedStatement ps2=conn.prepareStatement(query2); ResultSet rs2=ps2.executeQuery(); if(rs2.next()){ totalRows=rs2.getInt("cnt"); } %> <html><h3>Pagination of JSP page</h3> <body><form> <input type="hidden" name="iPageNo" value="<%=iPageNo%>"> <input type="hidden" name="cPageNo" value="<%=cPageNo%>"> <input type="hidden" name="showRows" value="<%=showRows%>"> <table width="100%" cellpadding="0" cellspacing="0" border="1" > <tr> <th>USERNAME</th><th>FIRSTNAME</th><th>EMAIL</th><th>COUNTRY</th> </tr> <%while(rs1.next()){ %> <tr><td><%=rs1.getString("USERNAME")%></td> <td><%=rs1.getString("FIRSTNAME")%></td> <td><%=rs1.getString("EMAIL")%></td> <td><%=rs1.getString("COUNTRY")%></td> </tr> <%}%> <%try{ if(totalRows<(iPageNo+showRows)){ endResult=totalRows; } else{ endResult=(iPageNo+showRows); } startResult=(iPageNo+1); totalPages=((int)(Math.ceil((double)totalRows/showRows))); } catch(Exception e){ e.printStackTrace(); } %> <tr><td colspan="3"> <div> <% int i=0; int cPage=0; if(totalRows!=0){ cPage=((int)(Math.ceil((double)endResult/(totalRecords*showRows)))); int prePageNo=(cPage*totalRecords)-((totalRecords-1)+totalRecords); if((cPage*totalRecords)-(totalRecords)>0){ %> <a href="paging.jsp?iPageNo=<%=prePageNo%>&cPageNo=<%=prePageNo%>">Previous</a> <% } for(i=((cPage*totalRecords)-(totalRecords-1));i<=(cPage*totalRecords);i++){ if(i==((iPageNo/showRows)+1)){%> <a href="paging.jsp?iPageNo=<%=i%>" style="cursor:pointer;color: red"><b><%=i%></b></a> <% } else if(i<=totalPages){ %> <a href="paging.jsp?iPageNo=<%=i%>"><%=i%></a> <% } } if(totalPages>totalRecords && i<totalPages){ %> <a href="paging.jsp?iPageNo=<%=i%>&cPageNo=<%=i%>">Next</a> <% } } %> <b>Rows <%=startResult%> - <%=endResult%> Total Rows <%=totalRows%> </b> </div></td></tr></table></form></body></html>
Ads