Issue with count of pages on paging
The paging below works except sometimes the count of pages shows an extra page with no values in it. It seems to just add that extra page to the end.
Please advise where I can correct this issue because I have played around with the number settings and cant seem to correct it:
<!DOCTYPE HTML PUBLIC"-//W3C//DTD HTML 4.01 Transitional//EN""http://www.w3.org/TR/html4/loose.dtd">
<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" errorPage="" %>
<html>
<head>
<title>Untitled</title>
</head>
<body>
<%
int mynum = 3;
int pages;
Connection ocon =null;
Class.forName("org.gjt.mm.mysql.Driver");
ocon = DriverManager.getConnection("jdbc:mysql://localhost/dbOne?user=theuser&password=thepw");
Statement stmtt = ocon.createStatement();
ResultSet rc = stmtt.executeQuery("Select count(*) from user ");
rc.next();
pages = rc.getInt(1);
out.println("Count of Records : " + pages +"<br>");
int cPage;
cPage = (pages / mynum) + 1;
if ((cPage * mynum) + 1 >= pages)
{
cPage++;
}
out.println("Count of Pages : " + (cPage - 1) +"<br>"
);
int p;
for(p = 1; p < cPage ; p++)
{
out.println("<a href=pag.jsp?mv=" + p +">"+ p +"</a> | ");
}
out.println("<hr>");
// paging
int cpage;
int currentRs;
String pt;
pt = request.getParameter("mv");
if (pt ==null)
{
currentRs = 0;
}
else
{
cpage = Integer.parseInt((String)pt);
currentRs = mynum * (cpage - 1);
out.println(cpage +"<br>");
}
Connection con =null;
Class.forName("org.gjt.mm.mysql.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/dbOne?user=theuser&password=thepw");
Statement stmt = con.createStatement();
String sql;
sql ="Select * from user LIMIT "+ currentRs +"," + mynum;
ResultSet rs = stmt.executeQuery(sql);
while (rs.next())
{
String myn = rs.getString("lastname");
out.println("<br>" + myn +"<br>");
}
%>
</body>
</html>
[3776 byte] By [
teser2a] at [2007-11-27 3:54:27]
# 1
Use the data from the examples you have to see why this calculation isn't correct:cPage = (pages / mynum) + 1;
I suppose that "pages" is the number of things you are going to display and "mynum" is the number of things per page and "cPage" is the number of pages that result from that. The variable names are horrible if that's the case, but I will start by assuming those are the meanings.
Suppose that "pages" is 24 and "mynum" is 6. Then you would want "cPage" to be 4, wouldn't you? But that isn't what you get, and it's pretty clear why not. I suggest you need something more like this:cPage = pages / mynum;
if (pages % mynum != 0) cPage += 1;
See how that works? First find the number of full pages you need, then if there's a partial page needed at the end you add 1 to the page count.
# 2
[nobr]Thanks for the info and it now works great.
I assume for JSP this is okay to open up the database connections the way this program is running but was wondering how do I correctly close the connections.
My attempt below seems to freeze the page where it wont come up.
Please advise because I have tried several attempts of closing the connections but everytime the page doesnt come up. If I get rid of my try/catch/finally blocks the page works great.
int mynum = 4;
int pages;
try
{
Connection ocon = null;
Class.forName("org.gjt.mm.mysql.Driver");
ocon = DriverManager.getConnection("jdbc:mysql://localhost/dbOne?user=theuser&password=thepw");
Statement stmtt = ocon.createStatement();
ResultSet rc = stmtt.executeQuery("Select count(*) from user ");
rc.next();
pages = rc.getInt(1);
out.println("Count of Records : " + pages + "<br>");
int cPage;
cPage = pages / mynum;
if (pages % mynum != 0)
{
cPage += 1;
}
if ((cPage * mynum) + 1 >= pages)
{
cPage++;
}
out.println("Count of Pages : " + (cPage - 1) + "<br>"
);
int p;
for(p = 1; p < cPage ; p++)
{
out.println("<a href=pag.jsp?mv=" + p + ">"+ p +"</a> | ");
}
out.println("<hr>");
// paging
int cpage;
int currentRs;
String pt;
pt = request.getParameter("mv");
if (pt == null)
{
currentRs = 0;
}
else
{
cpage = Integer.parseInt((String)pt);
currentRs = mynum * (cpage - 1);
out.println(cpage + "<br>");
}
Connection con = null;
Class.forName("org.gjt.mm.mysql.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/dbOne?user=theuser&password=thepw");
Statement stmt = con.createStatement();
String sql;
sql = "Select * from user LIMIT "+ currentRs +"," + mynum;
ResultSet rs = stmt.executeQuery(sql);
//rs.absolute(10);
while (rs.next())
{
String myn = rs.getString("lastname");
out.println("<br>" + myn + "<br>");
}
}
catch(Exception e)
{
out.println("Error: " + e);
}
finally
{
ocon.close();
stmtt.close();
rc.close();
con.close();
stmt.close();
rs.close();
}
%>
[/nobr]
