<%@ page language="java" import="java.sql.*" %> <html> <head> <title>Display data from database in jsp</title> </head> <body> <table border="0" width="75%" cellspacing="0" cellpadding="0"> <tr> <td width="100%"> <h2><font color="#FF0033">Display data from database in jsp</font></h2> <form method="POST" > <table border="1" width="75%" cellspacing="0" cellpadding="0" bgcolor="#CCFFCC"> <% Connection con = null; String url = "jdbc:mysql://192.168.10.211:3306/";; String db = "amar"; String driver = "com.mysql.jdbc.Driver"; int rows = 0; try{ Class.forName(driver); con = DriverManager.getConnection(url+db,"amar","amar123"); try{ Statement st = con.createStatement(); String query = "select * from user_Register"; ResultSet rs = st.executeQuery(query); while (rs.next()) { rows =rs.getInt(1); %> result set of unknown size Display rows 1-10 range Display links to first (after first range) previous (after first range) 11-20 21-30 next (when applicable) last (when not on last range) refreshes automatically like an eBay or Google search page. <%} out.println("Total row is = "+rows); rs.close(); con.close(); } catch (SQLException ex){ System.out.println("SQL statement is not executed!"); } } catch (Exception e){ e.printStackTrace(); } %> </tr> </table> </form> </td> </tr> </table> </body> </html>
The given jsp code retrieves the record from the database and display 10 records per page. Here is the code: pagination.jsp
<%@ page language="java" %> <%@ 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; Class.forName("com.mysql.jdbc.Driver").newInstance(); conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "root"); ResultSet rs1 = null; ResultSet rs2 = null; PreparedStatement ps1=null; PreparedStatement ps2=null; int showRows=10; int totalRecords=10; 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 student limit "+iPageNo+","+showRows+""; ps1=conn.prepareStatement(query1); rs1=ps1.executeQuery(); 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> <td>Roll No</td> <td>Name</td> <td>Marks</td> <td>Grade</td> </tr> <% while(rs1.next()) { %> <tr> <td><%=rs1.getInt("rollNo")%></td> <td><%=rs1.getString("name")%></td> <td><%=rs1.getInt("marks")%></td> <td><%=rs1.getString("grade")%></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(); } %>
continue..
<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*totalRecords)-(totalRecords-1));i<=(cPage*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>
In the above code,we have taken the database table student(rollNo,name,marks,grade).
For more information, visit the following link:
Ads