Displaying 10 records(rows) per page where number of records are unknown

Get Started
1

I’m new to programming. I’m trying to create a form that does the following in jsp/javascript

Displays 10 rows, each row containing a record if records exceeds 10, it will display the remaining records on another page.The first form will contain a link to the second form and the second form will contain the link to the first. So basically there could be infinite number of forms containing 10 rows each on each form each displaying a link to the other forms. So each page at the bottom will have something like this <<previous [1][2][3][4][5]next>>.The algorithm seems complicated and I don’t know how to generate another page for displaying the extra records. Is this complicated.Can someone please show me how this is done with the following code…HELP

thanx in advance

getchoo

Here is the code I have currently my apologize for the length. this code is called by another page:

<%@include file=“header.jsp”%>
<%@ page import=“java.sql.*” errorPage=“error.jsp”%>
<%
String connectionURL=
“jdbc:mysql://localhost:3306/studentdatabase?username=username;passwrod=password”;
Connection connection = null;
Statement statement=null;
ResultSet rs=null;
%>

<html>
<head>
<%@include file=“btechreportheader.jsp”%>
</head>
<body bgcolor=“#efefef” text=“#000000” link=“#ffcc99”>
<form name=“btechreport” method=“post” action=“deletestudent.jsp”>
<table width=“100%” border=“1” cellpadding=“2” cellspacing=“0” height=“6”>
<tr bgcolor=“#efefef”>
<td width=“8%” height=“18”>
<div align=“center”><font face=“Verdana, Arial, Helvetica, sans-serif” size=“1”>Date</font></div>
</td>
<td width=“10%” height=“18”>
<div align=“center”><font face=“Verdana, Arial, Helvetica, sans-serif” size=“1”>Student
ID</font></div>
</td>
<td width=“11%” height=“18”>
<div align=“center”><font face=“Verdana, Arial, Helvetica, sans-serif” size=“1”>Student
Name</font></div>
</td>
<td width=“15%” height=“18”>
<div align=“center”><font face=“Verdana, Arial, Helvetica, sans-serif” size=“1”>Email
Address</font></div>
</td>
<td width=“13%” height=“18”>
<div align=“center”><font face=“Verdana, Arial, Helvetica, sans-serif” size=“1”>Course
Taken</font></div>
</td>
<td width=“15%” height=“18”>
<div align=“center”><font face=“Verdana, Arial, Helvetica, sans-serif” size=“1”>Course
Uncompleted</font></div>
</td>
<td width=“6%” height=“18”>
<div align=“center”><font face=“Verdana, Arial, Helvetica, sans-serif” size=“1”>Qualified</font></div>
</td>
<td width=“6%” height=“18”>
<div align=“center”><font face=“Verdana, Arial, Helvetica, sans-serif” size=“1”>Applied</font></div>
</td>
<td width=“6%” height=“18”>
<div align=“center”><font face=“Verdana, Arial, Helvetica, sans-serif” size=“1”>Accepted</font></div>
</td>
<td width=“6%” height=“18”>
<div align=“center”><font face=“Verdana, Arial, Helvetica, sans-serif” size=“1”>Delete</font></div>
</td>
</tr>
<%

String separator = “-”;
String sYear = request.getParameter(“startYear”);
String sMonth= request.getParameter(“startMonth”);
String sDate = request.getParameter(“startDate”);
String startTime = sYear+separator+sMonth+separator+sDate;

String eYear = request.getParameter(“endYear”);
String eMonth= request.getParameter(“endMonth”);
String eDate= request.getParameter(“endDate”);
String endTime = eYear+separator+eMonth+separator+eDate;

Class.forName(“org.gjt.mm.mysql.Driver”).newInstance();
connection = DriverManager.getConnection(connectionURL, “”, “”);
statement = connection.createStatement();

rs=statement.executeQuery(“SELECT students.studentid, students.firstname, students.lastname, students.email,” +
“btechpre.cisy1212, btechpre.cisy2311, btechpre.cisy2313, btechpre.cisy2314, btechpre.cisy2315,” +
“credential.recdate, credential.qualified, credential.accepted, credential.applied FROM students, btechpre, credential " +
“WHERE students.studentid=credential.studentid AND credential.studentid=btechpre.studentid AND credential.recdate BETWEEN '”+startTime+”’ AND ‘“+endTime+”’ ");

//SELECT DATE_FORMAT(date,‘%M %d, %Y’) from data

while(rs.next())
{
String cisy1212=rs.getString(“cisy1212”);
String cisy2311=rs.getString(“cisy2311”);
String cisy2313=rs.getString(“cisy2313”);
String cisy2314=rs.getString(“cisy2314”);
String cisy2315=rs.getString(“cisy2315”);

%>
<tr bgcolor=“#efefef”>
<td width=“8%” height=“18”><%= rs.getString(“recdate”)%></td>
<td width=“10%” height=“18”><%= rs.getString(“studentid”)%>
<input type=“hidden” name=“studentid” value=“<%= rs.getString(“studentid”)%>”></td>
<td width=“11%” height=“18”><%=rs.getString(“firstname”)%>
<%=rs.getString(“lastname”)%></td>
<td width=“15%” height=“18”><a href=“mailto:<%=rs.getString(“email”)%>”><%=rs.getString(“email”)%></a></td>
<td width=“13%” height=“18”> <%
if((cisy1212!=null) || (cisy2311 !=null) ||(cisy2313!=null) ||(cisy2314!=null) ||(cisy2315!=null))
%> <%=cisy1212%>,<%=cisy2311%>,<%=cisy2313%>,<%=cisy2314%>,<%=cisy2315%></td>

<td width=“15%” height=“18”><%=rs.getString(“cisy1212”)%>,<%=rs.getString(“cisy2311”)%>,<%=rs.getString(“cisy2313”)%>,<%=rs.getString(“cisy2314”)%>,<%=rs.getString(“cisy2315”)%></td>
<td width=“6%” height=“18”><%=rs.getString(“qualified”)%></td>
<td width=“6%” height=“18”><%=rs.getString(“applied”)%></td>
<td width=“6%” height=“18”><%=rs.getString(“accepted”)%></td>
<td width=“6%” height=“18”>
<input style=“BACKGROUND-IMAGE: url(file://C:\jakarta-tomcat-4\webapps\CISY2415\images\ rash2.gif); WIDTH: 28px; HEIGHT: 36px; BACKGROUND-COLOR: #efefef” type=submit name=delete size=14 onClick=“alert(‘Do you want to delete the record?’)”>
</td>
</tr>
<% } //end while()
//clean up
if(rs!=null) rs.close();
if(statement!=null) statement.close();
if(connection!=null) connection.close();
%>
</table>

</form>
</body>
</html>
:eek2: :eek2:

2

Hi;

It seems your JSP code is pretty messy as you have long scriptlet within your JSP, I suggest you split your business logic
and the presentation layer. Here are some combinations :

  1. JSP (Web layout/display) + JavaBean (Some object wrapper/business rules) + EJB (Persistancy and business rules)
    2.JSP(Web layout/display) + Custom Tag (Business rules/logic) + JavaBean (Some object wrapper/business rules/database persistancy)

Instead of going thru your lengthy scriptlet, I hereby give my simple example by using 2 JavaBeans
as my db access object and business rules and 1 JSP page as the display of result, a similiar example
as what you wanna do as “<<previous [1][2][3][4][5]next>>”

I have a database table for storing the product item

create table product
(
code varchar(10),
name varchar(254),
price number(14,2),
qoh number(10,2)
);

I use 2 JavaBeans to encapsulate the database call and the logic that needs
to display the specified number of rows in one page, I call it ProductFinder.java
and Product.java

//The Product.java, as data holder
package com.motionk.products;

public class Product
{
private String name;
private String code;
private float qoh;
private float price;

public Product(String code,String name,float price, float qoh)
{
	this.code=code;
	this.name=name;
	this.price=price;
	this.qoh=qoh;
}

public String getCode()
{
	return this.code;
}

public String getName()
{
	return this.name;
}

public float getPrice()
{
	return this.price;
}

public float getQOH()
{
	return this.qoh; // return the qty on hand
}

}

// The ProductFinder.java
package com.motionk.products;
import java.sql.;
import java.util.;

public class ProductFinder
{

private Connection conn; // the database connection object
private Vector v;// the vector that is to hold the result query from
private String errMessage; //the error message

/**
Empty constructor
*/
public ProductFinder()
{
}

/**
Sets the connection object
@param connection
*/
public void setConnection(Connection conn)
{
	this.conn =conn;
}

/**
This method is to find the product by the name
@param name
*/
public synchronzied boolean findProductByName(String name)
            {
	try
	{
		PreparedStatement stm= conn.prepareStatement(
		"SELECT code,name,price,qoh "+
		"FROM product WHERE name LIKE ? "+
		"ORDER BY name");

		stm.setString(1,"%"+name+"%");
		ResultSet rs - stm.executeQuery();
		this.v = new Vector();
		while(rs.next())
		{
			Product p=
			new 				Product(rs.getString("code"),rs.getString("name"),rs.getFloat("price"),

rs.getFloat(“qoh”));
v.addElement(p);

		}
		rs.close();rs=null;
		stm.close(); stm=null;
		if(this.v.size() &gt;0)
			return true;
		else
			return false;
	}
	catch(Exception e)
	{
		this.errMessage=e.getMessage();
		return false;
	}

}

/**
This method is to return the total number of rows
of product since the last call of the findProductByName(name) method
*/
public int getTotalNumberOfRows()
{
	if(v!=null)
	 return this.v.size():
	else
	 return 0;
}

/**
This method is to return an array of the Product object
based on the number of rows specified and the "row to start"
@param rowToStart
@param totalRow2Retrieve
@return the array
*/
public synchronzied Product[] getProducts(int rowToStart, int totalRow2Retrieve)
{
	// check if the row requested and the specified
	// total row to retrieve is within the valid range
	if((rowToStart &gt;=0 && rowToStart &lt; v.size())  && (rowToStart+totalRow2Retrieve) &lt; this.v.size() )
	{
		Vector av0= new Vector();
		for (int i=rowToStart; i &lt;=	(rowToStart+totalRow2Retrieve); i++)
		{
			  av0.addElement(this.v.elementAt(i));
		}
		Product ps[]= new Product[av0.size()];
		av0.copyInto(ps);
		av0=null;
		return ps;
	}
	else if((rowToStart &gt;=0 && rowToStart &lt; v.size()) && (rowToStart+totalRow2Retrieve) &gt; this.v.size() )
	{
		Vector av0= new Vector();
		for (int i=rowToStart; i &lt; this.v.size(); i++)
		{
			  av0.addElement(this.v.elementAt(i));
		}
		Product ps[]= new Product[av0.size()];
		av0.copyInto(ps);
		av0=null;
		return ps;
	
	}		
	else
	{
		Product ps[]= new Product[0];
		// if NOT within the valid range , return an empty array
		return ps;
	
	}

}

// the method to return the error message
public String getErrMessage()
{
	return this.errMessage;
}

}


// now come to the JSP, lets call it product.jsp

<jsp:useBean id=“productFinder” scope=“session” class=“com.motionk.products.ProductFinder” />
<%
// set the connection object
productFinder.setConnection(conn); // I dod NOT show how U obtain your Connection
// object here , you can do it as you did in your scriptlet or using javax.sql.DataSource
// if your platform supports database connection pooling
// now find the products by the name
if(productFinder.findProductByName(request.getParameter(“productName”)))
{
int startRow=0;
int totalRow2retrive=10;
int totalRow = productFinder.getTotalNumberOfRows();
int numOfPgs=totalRow/totalRow2retrive; // the number of pages to display for “[1][2][3][4][5]”
int nextRow=0; // the next start row for “NEXT>>”
int prevRow=0; // the previous start row for “<<PREVIOUS”
try
{
startRow=request.getParameter(“startRow”);
}
catch(NumberFormatException nfe)
{
// ignore
}

	if(totalRow%totalRow2retrieve&gt;0)
	{
		numOfPgs=numOfPgs+1;
	}

	if((startRow+numOfRows-1) &lt;

(totalRow-1))
{
nextRow=startRow+numOfRows;
}
if((startRow-numOfRows>=0))
{
prevRow=startRow-numOfRows;
}

%>
<!-- to display the “[1][2][3][4][5]next>>” here –>
<p align=“left”>
<%
int nextSRow=startRow+numOfRows;
for(int pg=1; pg <= numOfPgs ; pg++)
{
int psr=(pg-1)*numOfRows;
%>
<a class=“button”
style=“text-decoration:none;color:<%if(startRow==psr){out.print(”#FF0000;font-size:12pt;font-weight:bolder;“);}else{out.print(”#000000");}%>"
href=“product.jsp?startRow=<%=psr%>&name=<%=request.getParameter(“productName”)%>”>
<%=pg%>
</a>
</b>
</font>
<%
}
if(nextSRow< (totalRow-1))
{
%>
 
<a class=“button” style=“text-decoration:none;color:#000000”
href=“product.jsp?startRow=<%=nextSRow%>&name=<%=request.getParameter(“productName”)%>”>
Next >>>
</a>
</font>
<%
}
%>
</p>
<%
// to display the product in the current page
Product ps= productFinder.getProducts(startRow,totalRow2retrive);
for(int i=0; i < ps.length; i++)
{

%>
<p align=“left”>
Code : <%=ps[i].getCode()%><br>
Name : <%=ps[i].getName()%><br>
QOH : <%=ps[i].getQOH()%><br>
Price : <%=ps[i].getPrice()%><br>
<%
}

 }
else
{
// if error found , display the error

%>
<%=productFinder.getErrMessage()%>
<%
}
%>

There are a lots of other ways to do that, the example given assume that your JDBC driver is NOT JDBC2-compliant, if your JDBC driver is JDBC2 complaint
, you can navigate (next/previous) to each row of the ResultSet instead of calling the next() method to advance to the next row, this might allow you to skip the extra work that needs to extract a ResultSet to a Vector or Collection object. I drafted this example very quickly, it might contain some logic mistake but the concept is there. Hope it helps !!

ketyung
Visit http://www.motionk.com if you need
more advice on J2EE