Pagination in jsp using oracle and not sql

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.

View Answers

February 21, 2011 at 12:35 PM

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>

August 28, 2013 at 1:17 PM

*<%@ 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**

August 28, 2013 at 1:22 PM

<%@ 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

Pagination in jsp using oracle and not sql

Questions by Category