I need the code for pagination in jsp using oracle. I already tried it with rownum between instead of limit in sql.Please do forward me the code as soon as possible.
JSP Pagination using Mysql database
<%@ 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(); } %> <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>
*<%@ page language="java" import="java.sql.;"%>
<%
int offset = 0;
int ofset = 0;
if (request.getParameter("offset") != null) {
offset = Integer.parseInt(request.getParameter("offset")
.toString());
}
int total_count = 0;
int total_page = 0;
int per_page = 3;
String name = "";
int max_page = 0;
if (offset > 1) { ofset = offset * per_page - per_page; max_page = offset * per_page; } else max_page = per_page; System.out.println("Oracle Connect Example."); Connection conn = null; String color = "#F9EBB3"; System.out.println("offset : " + offset); System.out.println("ofset : " + ofset); System.out.println("per_page : " + per_page); try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "Username", "password"); Statement stcount = conn.createStatement(); String strQuerycount = "Select count(*) from Table_Name"; ResultSet rscount = stcount.executeQuery(strQuerycount); if (rscount.next()) { total_count = rscount.getInt(1); } if (total_count % per_page == 0) { total_page = total_count / per_page; } else { total_page = total_count / per_page + 1; } //out.println(total_count); //out.println(total_page); //out.println(ofset); Statement st = conn.createStatement(); String strQuery = "select * from ( select a.*, rownum rnum from ( SELECT * FROM table_Name order by Coloum_Name ) a where rownum <= "+max_page+" ) where rnum >" +ofset; System.out.println(strQuery); ResultSet rs2 = null; ResultSet rs = st.executeQuery(strQuery); // out.println("offset " + offset); int count = 0; if (offset > 1) count = offset * per_page - per_page; %> <br> <br> <br> <table width="400px" align="center" style="border: 1px solid #000000;"> <tr> <td colspan=8 align="center" style="background-color: ffeeff"><b>Transaction Report</b></td> </tr> <tr style="background-color: efefef;"> <td><b>SNo</b></td> <td><b>Name</b></td> <td><b>Address</b></td> </tr> <% while (rs.next()) { if ((count % 2) == 0) { color = "#eeffee"; } else { color = "#F9EBB3"; } count++; %> <tr style="background-color:<%=color%>;"> <td><%=count%></td> <td><%=rs.getString(1)%></td> <td><%=rs.getString(1)%></td> </tr> <% } %> </table> <br> <br> <table width="100px" align="center" border=0> <tr> <% if (count == 0) { %> <tr style="background-color:<%=color%>;"> <td colspan=8 align="center">No Record</td> </tr> <% } //System.out.println("Connected to the database"); conn.close(); //System.out.println("Disconnected from database"); } catch (Exception e) { e.printStackTrace(); } if (offset > 1) { int previous = offset - 1; %> <td><a href="testpaging.jsp?offset=<%=previous%>">Previous</a></td> <% } if (total_page > 0) { for (int i = 1; i <= total_page; i++) { if (request.getParameter("offset") == null) { offset = 1; } if (i == offset) { %> <td><%=i%></td> <% } else { %> <td><a href="testpaging.jsp?offset=<%=i%>"><%=i%></a></td> <% } } } if (offset < total_page) { int next = offset + 1; %> <td><a href="testpaging.jsp?offset=<%=next%>">Next</a></td> <% } %> </tr> </table>
strong text**
<%@ page language="java" import="java.sql.*;"%>
<%
int offset = 0;
int ofset = 0;
if (request.getParameter("offset") != null) {
offset = Integer.parseInt(request.getParameter("offset")
.toString());
}
int total_count = 0;
int total_page = 0;
int per_page = 3;
String name = "";
int max_page = 0;
if (offset > 1) { ofset = offset * per_page - per_page; max_page = offset * per_page; } else max_page = per_page; System.out.println("Oracle Connect Example."); Connection conn = null; String color = "#F9EBB3"; System.out.println("offset : " + offset); System.out.println("ofset : " + ofset); System.out.println("per_page : " + per_page); try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "Username", "password"); Statement stcount = conn.createStatement(); String strQuerycount = "Select count(*) from Table_Name"; ResultSet rscount = stcount.executeQuery(strQuerycount); if (rscount.next()) { total_count = rscount.getInt(1); } if (total_count % per_page == 0) { total_page = total_count / per_page; } else { total_page = total_count / per_page + 1; } //out.println(total_count); //out.println(total_page); //out.println(ofset); Statement st = conn.createStatement(); String strQuery = "select * from ( select a.*, rownum rnum from ( SELECT * FROM table_Name order by Coloum_Name ) a where rownum <= "+max_page+" ) where rnum >" +ofset; System.out.println(strQuery); ResultSet rs2 = null; ResultSet rs = st.executeQuery(strQuery); // out.println("offset " + offset); int count = 0; if (offset > 1) count = offset * per_page - per_page; %> <br> <br> <br> <table width="400px" align="center" style="border: 1px solid #000000;"> <tr> <td colspan=8 align="center" style="background-color: ffeeff"><b>Transaction Report</b></td> </tr> <tr style="background-color: efefef;"> <td><b>SNo</b></td> <td><b>Name</b></td> <td><b>Address</b></td> </tr> <% while (rs.next()) { if ((count % 2) == 0) { color = "#eeffee"; } else { color = "#F9EBB3"; } count++; %> <tr style="background-color:<%=color%>;"> <td><%=count%></td> <td><%=rs.getString(1)%></td> <td><%=rs.getString(1)%></td> </tr> <% } %> </table> <br> <br> <table width="100px" align="center" border=0> <tr> <% if (count == 0) { %> <tr style="background-color:<%=color%>;"> <td colspan=8 align="center">No Record</td> </tr> <% } //System.out.println("Connected to the database"); conn.close(); //System.out.println("Disconnected from database"); } catch (Exception e) { e.printStackTrace(); } if (offset > 1) { int previous = offset - 1; %> <td><a href="testpaging.jsp?offset=<%=previous%>">Previous</a></td> <% } if (total_page > 0) { for (int i = 1; i <= total_page; i++) { if (request.getParameter("offset") == null) { offset = 1; } if (i == offset) { %> <td><%=i%></td> <% } else { %> <td><a href="testpaging.jsp?offset=<%=i%>"><%=i%></a></td> <% } } } if (offset < total_page) { int next = offset + 1; %> <td><a href="testpaging.jsp?offset=<%=next%>">Next</a></td> <% } %> </tr> </table>
Sushil SG