Pagination in Servlets from a Resultset

Hi all,
I know this is a frequently asked question. But I would like to know if I can find sample code for Previous, Next display of records from the database.
In this Architecture I cannot use the pagination taglibs. I have to do everyhting in servlets.
Can anyone give me links or a sample code.

Thanks

Look up the SQL "LIMIT" keyword.

Thanks for the reply Ben Souther.

But in Oracle LIMIT keyword is used only for BULK fetch or bulk inserts.
WHat my idea was to fetch the entire resultset into a ArrayList and Loop through.
It would be very helpful if I could get a sample code for this.

Thanks a lot

Couldn't provide you a sample code. pardon.

Yes but in oracle you can use ROWNUM field in your condition. Suppose if you have a million record inside your table then fetching all would be time consuming and may result in a bottleneck. Wouldn't it better to fetch a small no of record which you really want to show in your first page. Try to use ROWNUM.

cheers.

Look out this code

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
public class Page1 extends HttpServlet {

public void service(HttpServletRequest req,HttpServletResponse res) throws ServletException,IOException {

res.setContentType("text/html");
PrintWriter out=res.getWriter();
try {
Statement st=null;
Connection con= null;
ResultSet rs=null;

String from,to,page;

int start=0;
int end=0;

int record=20;
int disp=0;

int tcnt=0;
int tcntm=0;

from=req.getParameter("from");
out.println("From " + from);

to=req.getParameter("to");
out.println("To " + to);

page=req.getParameter("page");
out.println(" page " + page);

if( (from==null) || (from.equals("")) ) {
from="1";
out.println("From ***" + from);
}

if( (to==null) || (to.equals("")) ){
to=java.lang.String.valueOf(record);
out.println("To **** "+ to);
}
if( (page==null) || (page.equals("")) ){
page="1";
out.println("page*** " + from);
}

Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc

racle:thin:@172.16.2.70:1521

crdev","workflownew","workflownew1");
//out.println("Connection "+ con);
st=con.createStatement();

out.println("<html>");
out.println("");
out.println("<head>");
out.println("</head>");
out.println("<body bgcolor=\"white\" topmargin=\"0\" leftmargin=\"0\">");
out.println("<form name=notepad action=/page method=post>");
out.println("<table width=100% bgcolor=#debc82><tr><td align=right width=100%> </td></tr></table>");
//select makerid,queuelabel from ow_queue;
rs=st.executeQuery("select count(*) from ow_queue");

while(rs.next()){
tcnt=rs.getInt(1);
}

rs.close();

double ff=0.00;
ff=tcnt/(record+0.0);
//out.println("FF " + ff);
int ii=tcnt/record;
//out.println("II " + ii);

if(ff>ii) {
ii=ii+1;
// out.println("Increment " + ii);
}
out.println("record" + record);
out.println("tcnt " + tcnt);

if(record>tcnt){
out.println("From page111 ");
to=java.lang.String.valueOf(tcnt);
}
if(Integer.parseInt(to)>tcnt){
out.println("From page222 ");
to=java.lang.String.valueOf(tcnt);
}

if(tcnt==0) {
out.println("<table width=100%><tr>");
out.println("<td align=left width=70%></td>");
out.println("<td align=\"right\" width=\"30%\" hight=\"25\"> </td></tr></table>");
} else {
out.println("<table width=100%><tr>");
out.println("<td align=left width=70%></td><td align=\"right\" width=\"30%\" hight=\"25\">Showing "+from+" - "+to+" of "+tcnt+"</td></tr></table>");
}

int incre=1;
int selected=record;
out.println("Selected " + selected);
for(int j=1;j<=ii;j++) {

if(j<ii){
incre=incre+record;
}
selected=selected+record;
//out.println("After selected " + selected);
disp=j;
//out.println("disp... " + disp);

}

java.lang.String query="select queuelabel,APPLICATIONID from emp";

rs=st.executeQuery(query);
java.lang.String s1s1=null;
java.lang.String sno=null;
int i=1;
boolean flag=false;
int rcount=Integer.parseInt(from);
out.println("RCount " + rcount);
int ij=1;

while(rs.next())
{
flag=true;
if( (ij>=rcount) && ( rcount<=Integer.parseInt(to)) ){
if(i==1){

out.println("<TR bgColor=F5E7BD>");
out.println("<th> </th> <Th width=\"303\" align=left> ");
out.println("QueueLabel ");
out.println(" </Th>");
out.println(" <Th width=\"303\" align=left> ");
out.println(" ApplicationID ");
out.println(" </Th>");
out.println(" <th width=300 nowrap align=center> ");
out.println("</Th></TR>");
out.println(" ");
}

sno=rs.getString("QUEUELABEL");
java.lang.String sss = rs.getString("APPLICATIONID");
out.println("<tr>");
out.println(" <td width=\"303\" align=left>"+ sno +"</td>");
out.println(" <td width=\"300\" align=center>"+sss+"</td>");
out.println("</tr>");
rcount++;
i++;
}

ij++;
}

if(tcnt>record) {

if(Integer.parseInt(page)==1) {

out.println("<a href=/Page1?page="+(Integer.parseInt(page)+1)+"&from="+(Integer.parseInt(from)+record)+"&to="+(Integer.parseInt(to)+(record))+">Next</a> ");

} else {

if(disp==Integer.parseInt(page)) {
out.println("<a href=/Page1?page="+(Integer.parseInt(page)-1)+"&from="+(Integer.parseInt(from)-record)+"&to="+(Integer.parseInt(from)-1)+">Previous</a>");

} else {
out.println("<a href=/Page1?page="+(Integer.parseInt(page)-1)+"&from="+(Integer.parseInt(from)-record)+"&to="+(Integer.parseInt(to)-(record))+">Previous</a> |");

if(disp==Integer.parseInt(page)+1) {

out.println("<a href=/Page1?page="+(Integer.parseInt(page)+1)+"&from="+(Integer.parseInt(from)+record)+"&to="+tcnt+">Next</a>");

} else {
out.println("<a href=/Page1?page="+(Integer.parseInt(page)+1)+"&from="+(Integer.parseInt(from)+record)+"&to="+(Integer.parseInt(to)+(record))+">Next</a>");
}
}
}
}

out.println("</body>");
out.println("</form>");
out.println("</html>");
}
catch(Exception e)
{
e.printStackTrace(out);
}

}

}

Search for and try using the "Display" tags. There an open source tag library that handles lists very elegantly. I believe that this will have the code you're looking for.

-Jim