Pagination in jsp using oracle and not sql

Ads
 

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

Ads









Related Tutorials/Questions & Answers:
Pagination in jsp using oracle and not sql
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... Pagination using Mysql database <%@ page language="java" %> <%@ page
oracle database backup using jsp
oracle database backup using jsp   I want to take the backup of oracle database.I want to write the code in jsp ,so that when the page is loaded a backup of the database is stored in one of my local drives.Is it possible to do
Advertisements
Pagination with condition using servlet or jsp
Pagination with condition using servlet or jsp  Hi, Thanks... I retrieved using "String name=request.getParameter("name of that text box")", Then I want perform the pagination based on the string value(I mean pagination
how to save images in oracle using JSP
how to save images in oracle using JSP  how to insert images in oracle using jsp
how to save images in oracle using JSP
how to save images in oracle using JSP  how to insert images in oracle using jsp
fetch record from oracle database using jsp-servlet?
fetch record from oracle database using jsp-servlet?  how can i fetch data from oracle database by using jsp-servlet. i'm using eclipse, tomcat server and oracle database and creating jsp pages and also using servlet
Pagination
Pagination  How to create pagination in jsp with EJB using MS SQL
Retrieve database from the table dynamically in jsp from oracle using servlet
Retrieve database from the table dynamically in jsp from oracle using servlet  Sir, I have created a table in oracle using eclipse, and added few... using java servlet from the database in the jsp page
jsp pagination
jsp pagination  I want to implement pagination on jsp page Each jsp page having some radio buttons, on click of next previous page selected radio buttons are reset. I want to maintain state of selected radio buttons on previous
how to load a table of data from oracle, to a jsp page using hashmap.
how to load a table of data from oracle, to a jsp page using hashmap.  I have a jsp page which ask for project ID,team name,member name according to this data i have to retrieve their details from the database(oracle). I have
how to generate reports from oracle database using jsp and ajax code
how to generate reports from oracle database using jsp and ajax code  Hai masters i am new to this Java world. my team leader ask me to generate sales report data from oracle database to jsp page please any one know how to do
Upload and display image and text records using JSP and Oracle
Upload and display image and text records using JSP and Oracle  Hi all, I'm using JSP and Oracle 10g. I could retrieve and display records from database. But i couldnot retrieve and display image. I want to store
JSP Pagination
I could possibly use??   JSP Pagination pagination.jsp: <%@ page..."); } %> <html><h3>Pagination of JSP page</h3> <body><...JSP Pagination  Hi , I have several JSP's that displays data from
how to read data from excel file through browse and insert into oracle database using jsp or oracle???
how to read data from excel file through browse and insert into oracle database using jsp or oracle???  sir.. i have number of excel sheets which...://www.roseindia.net/answers/viewqa/JSP-Servlet/28123-write-excel-file-into-the-oracle
How to get data from Oracle database using JSP
How to get data from Oracle database using JSP  hello i have a simple problem in jsp in the sense to get data from the database like oracle . I have... data from the database like oracle), I have created one jsp program like
How to Upload a file directly to Oracle database using JSP or Servlets?
How to Upload a file directly to Oracle database using JSP or Servlets?  Hi I want to upload a file(csv or excel) to Oracle 10g Database using JSP...;Upload File to Oracle Database</h2> <form id="form1" enctype
oracle vs sql
oracle vs sql  what is the difference between oracle and sql
Jsp table Pagination
Jsp table Pagination  I tried the code already.But I dont want it with SQL.I want it for oracle database because I'm using oracle.So please help me with this issue. Thanks
Hibernate JSP
In this section, you will learn about Hibernate with JSP using simple pagination example
Pagination Using Displaytag Lib
Pagination Using Displaytag Lib  I am using Struts 1.2 for my web based project. While using display tag lib in jsp for pagination, is there any substitute of using As I dont want to be dependent on any site(s). Please
retrieving from oracle database using jsp combo box
retrieving from oracle database using jsp combo box  hi this is my... name of the server has to display in the process name field from the oracle database please help on this i need code using servlets please help me . <
create,edit and delete in JSP using struts and SQL server2005 as database in jdeveloper?
create,edit and delete in JSP using struts and SQL server2005 as database... to create,edit and delete tasks: taskid, taskname, date, project in JSP and struts and SQL server2005 in JDEVELOPER. I have not worked on struts yet.How should i
Pagination - JSP-Servlet
Pagination  I want to display only 10 records but my arraylist contains for example about 100 records... My jsp page should contain 10 records along with the pagination below.... like prev 1 2 3 4 5 next I am using
Retrieving images from the oracle database using jsp and create that rretrieved image as hyperlink
Retrieving images from the oracle database using jsp and create that rretrieved image as hyperlink  I want to insert images into oracle database.I want to retrieve images using jsp and I want to display on the browser.The
create,edit and delete in JSP using struts and SQL server2005 as database in jdeveloper?
create,edit and delete in JSP using struts and SQL server2005 as database... to create,edit and delete tasks: taskid, taskname, date, project in JSP and struts and SQL server2005 in JDEVELOPER. I have not worked on struts yet.How should i
jsp connection with oracle.
jsp connection with oracle.  I want to connect with jsp to oracle,,,plz send me the codes
jsp connection with oracle.
jsp connection with oracle.  I want to connect with jsp to oracle,,,plz send me the codes
how to insert array data into sql server using jsp
how to insert array data into sql server using jsp  hello, i have problem to insert array data into my sql server from jsp form. beloW is my code... from form, but not insert into my sql database. try { //String
Pagination without using database in php
Pagination without using database in php  How can I show multiple images in multiple rows with pagination in php
Help me to create a sharing text just like facebook using jsp servlet and oracle
Help me to create a sharing text just like facebook using jsp servlet and oracle   I tried to use lot of methods but i couldn't get the proper method... thing is that i want to pass a value to servlet without refreshing the whole jsp
How to get the data from the database (Oracle) in console or in ie using servlet or jsp as Front end
How to get the data from the database (Oracle) in console or in ie using servlet or jsp as Front end  hello i have a simple problem in jsp in the sense to get data from the database like oracle . I have created one jsp

Ads