Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Answers Viewqa JSP-Servlet Pagination in jsp using oracle and not sql
Login         

View Questions and Answers by Category

Advertisements


 
Have Programming Question? Ask it here!
 
 
 


premika
Pagination in jsp using oracle and not sql
3 Answer(s)      3 years and 10 months ago
Posted in : JSP-Servlet


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.


Advertisement
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



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
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
Advertisements
jsp page connectivity with oracle - SQL
connectivity jsp with oracle. Please send the code for solving problem. thanks ...: a) If you are using oracle oci driver,you have to use: Connection connection... and password. b) If you are using oracle thin driver,you have to use: Connection
Pagination
Pagination  How to create pagination in jsp with EJB using MS SQL
Pagination in jsp - JSP-Servlet
Pagination in jsp  I need an example of pagination in JSP using display tag
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
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 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
pagination in jsp - JDBC
pagination in jsp  Hi, I have millions of records in my oracle d.... Simple pagination in jsp... ------------------   Simple pagination in jsp Simple
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
oracle - SQL
oracle  what is meant by redo log file?  Hi Friend, In Oracle, a redo log file is used with each database. It records all changes made... to replay SQL statements. Thanks
Oracle - SQL
D:\oracle\ora90\BIN\imp type this command if your are using oracle 9i...Oracle  I have one .dmp file. I want to import this file into oracle 9i and oracle 10g. wat is procedure and stepts. thanks u  Open
Oracle - SQL
Oracle  Oracle Begginers , if Downloadable Notes are there Please mention the site
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
oracle - SQL
oracle  what is the difference between sql *plus and isql *plus
oracle - SQL
oracle  can you suggest me a book regarding oracle and java even..  Hi friend, For more information on Oracle and Java Books visit to : http://www.roseindia.net/programming/books/Freejavabooks.shtml http
oracle - SQL
) from employee)); In Oracle, you can use the following query: select max
pagination - JSP-Servlet
friend, Simple pagination in jsp Simple pagination in JSP... Simple pagination in jsp Displaying Records...pagination  Hi, could u tell me how i will handle pagination
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
Jsp pagination - JSP-Servlet
tell me the solution  Hi friend, For Jsp pagination application visit to : http://www.roseindia.net/jsp/paging.shtml Thanks...Jsp pagination  Iam doing a online exam application for this i need
oracle vs sql
oracle vs sql  what is the difference between oracle and sql
pagination
pagination   Simple way for pagination in jsp using java script to display data without send database
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
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
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 in hibernate with jsp
pagination in hibernate with jsp  Hi, plz give me example on pagination .   Hi Friend, Visit Here Thanks
pagination problem - JSP-Servlet
pagination problem  Thanks sir for posting the answer.But i have 2...(),insert() methods using or importing employee class.finally i created a class using servlets in that i called a these methods,to call this servlet i created
pagination problem - JSP-Servlet
pagination problem   Thanks sir for posting the answer.But i have 2...(),insert() methods using or importing employee class.finally i created a class using servlets in that i called a these methods,to call this servlet i created
pagination in hibernate with jsp
pagination in hibernate with jsp  Hi, plz give me example on pagination .   Hi Friend, We have used MySql database with JSP...>Pagination of JSP page</h3> <body> <form> <input type
Pagination
Pagination  How to apply pagination in JSP. Please help
 

 

 

DMCA.com