How to edit this pagination code so that it will show 5 records and next 5 records?
Dear experts,
I've copied the following pagination code, modified and adapted into my programs.
But, it is not working in such a way that the table will display five records only and then after user click next five, it will show the next five records.
I hope someone can advise me how to change the below coding in order for above to happen.
Many thanks.
Code :
<%@page language="java" import ="java.sql.*" %>
<%@ page import="java.util.*" %>
<form name ="form" action="jclevel.jsp" method="post" >
<%
String columnName = "";
int count = 0;
int increment = 1;
int numRows = 0;
String startIndexString = request.getParameter("id");
if (startIndexString == null) {
startIndexString = "1";
}
int startIndex = Integer.parseInt(startIndexString);%>
<% Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
Statement stmt = null;
try {
// Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// conn = DriverManager.getConnection("jdbc:odbc:PracticalODBC");
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/1", "root", "abc");
stmt = conn.createStatement();
} catch (Exception e) {
System.out.println(e.getMessage());
}
rs = stmt.executeQuery("SELECT * FROM members");
%>
<html>
<table border="1" width="84%" align="center" bgcolor="#66CCFF">
<tr>
<th>ID</th>
<th>IC No</th>
<th>Name</th>
<th>Subjects</th>
<th>Contact No</th>
<th>Location</th>
<%
while(rs.next()){
%>
<tr>
<td><%=rs.getString("id")%></td>
<td><%=rs.getString("strICNO")%></td>
<td><%=rs.getString("strName")%></td>
<td><%=rs.getString("strSubject")%></td>
<td><%=rs.getString("strContact")%></td>
<td><%=rs.getString("strLocation")%></td>
</tr>
<%
}
List list = new ArrayList();
for (int i = 0; i < 100; i++) {
}
numRows = list.size();
out.println(" total no. of records : " + numRows);
int numRecordsPerPage = 5;
out.println(" Num of Records per page : " + numRecordsPerPage + "\n");
int numPages = 0;
numPages = numRows / numRecordsPerPage;
int remain = numRows % numRecordsPerPage;
if (remain != 0) {
numPages = numPages + 1;
}
out.println(" \n no. of pages : " + numPages);
if ((startIndex + numRecordsPerPage) <= numRows) {
increment = startIndex + numRecordsPerPage;
} else {
if (remain == 0) {
increment = startIndex + numRecordsPerPage;
} else {
increment = startIndex + remain;
}
}
%>
</table>
Displaying Records:
<% if (startIndex + numRecordsPerPage < numRows) {%>
<%= " " + startIndex%> - <%= increment - 1%>
<%} else {%>
<%= " " + startIndex%> - <%= numRows%>
<%}%>
<%if (startIndex != 1) {%>
<a href="jclevel.jsp?startIndex=<%=startIndex - numRecordsPerPage%>">Previous</a>
<%}%>
<%increment += numRecordsPerPage;%>
<%if (startIndex + numRecordsPerPage <= numRows) {%>
<a href="jclevel.jsp?startIndex=<%=startIndex + numRecordsPerPage%>">Next</a>
<%}%>
<p> </p>
</form>
</body>
<%
conn.close();
rs.close();
stmt.close();
%>
Re: How to edit this pagination code so that it will show 5 records and next 5 record
A few words of advice...a) better practice to avoid mixind your sql statements with your view. Create a class or a bean which does this for you. b) Don't select everything from the database...use a prepared statement, and use the LIMIT syntax to provide the start and length to retrieve only what you wish to display, and use a SELECT count(*) to get the total number - much faster and less resource heavy than selecting all the entries for every page load. c) Why is there en empty for loop in there?
Re: How to edit this pagination code so that it will show 5 records and next 5 record
Quote:
Originally Posted by
copeg
A few words of advice...a) better practice to avoid mixind your sql statements with your view. Create a class or a bean which does this for you. b) Don't select everything from the database...use a prepared statement, and use the LIMIT syntax to provide the start and length to retrieve only what you wish to display, and use a SELECT count(*) to get the total number - much faster and less resource heavy than selecting all the entries for every page load. c) Why is there en empty for loop in there?
OK. I have modified my codes to include Beans but there are many errors including the starting heading
<%@ page language="java" import="java.sql.* %>
<jsp:useBean id ="members" class="databaseAccess.MembersBean" scope="request"/>
I hope someone can tell me where I'd gone wrong. Am really not familiar how to use Beans so would appreciate some guidance.
Code :
<%@ page language="java" import="java.sql.* %>
<jsp:useBean id ="memberss" class="databaseAccess.MembersBean" scope="request"/>
<%! int numPages = 5; %>
<%
MembersBean member = (MembersBean) application.getAttribute("id");
if (tutor == null) {
synchronized (application) {
member = new MemberssBean();
application.setAttribute("MembersBean", member);
}
}
%>
<%
int count = 0;
int totalCols = 0;
int increment = 1;
int numRows = 0;
String startIndexString = (String) request.getParameter("startIndex");
int startIndex = 0;
if (startIndexString != null) {
try {
startIndex = Integer.parseInt(startIndexString);
} catch (Exception ignore) {
}%>
<%
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306'''''''');
stmt = conn.createStatement();
} catch (Exception e) {
System.out.println(e.getMessage());
totalCols = 4;
rs = stmt.executeQuery("SELECT * FROM members");
%>
<html>
<form name ="form" action="jclevel.jsp" method="post">
<table border="1" width="84%" align="center" bgcolor=#66CCFF bordercolor=#000000 height="137">
<tr>
<td><p align="center"><b>Members Information</b></td>
</tr>
<tr>
<td>ID</td>
<td>Name</td>
<td>NRIC</td>
<td>Subjects</td>
<td>Address</td>
</tr>
<%
for (int j = 0; j <= totalCols && rs.next(); j++) {
%>
<tr bgcolor="#FFFFFF" onMouseOver="this.bgColor='gold';" onMouseOut="this.bgColor='#FFFFFF';">
<td><%=j = j + 1%></td>
<a href="jclevel.jsp?id=<%=rs.getString("id")%>" Title="View" onClick="return popup(this, 'jclevel')">
<%=rs.getString("id")%>
</a>
<tr>
<td><%=rs.getString(member.strFullName()%></td>
<td><%=rs.getString(member.strNRIC()%></td>
<td><%=rs.getString(member.strSubject()%></td>
<td><%=rs.getString(member.strAddress()%></td>
</tr>
</table>
<%
}
List list = new ArrayList();
Items[] items = member.getItems(startIndex, NumPages);
for (int i = 0; i < items.length; i++) {
list.add("item" + i);
}
numRows = list.size();
out.println(" total no. of records : " + numRows);
int numRecordsPerPage = 5;
out.println(" Num of Records per page : " + numRecordsPerPage + "\n");
numPages = numRows / numRecordsPerPage;
int remain = numRows % numRecordsPerPage;
if (remain != 0) {
numPages = numPages + 1;
}
out.println(" \n no. of pages : " + numPages);
if ((startIndex + numRecordsPerPage) <= numRows) {
increment = startIndex + numRecordsPerPage;
} else {
if (remain == 0) {
increment = startIndex + numRecordsPerPage;
} else {
increment = startIndex + remain;
}
}
for (count = startIndex; count < increment; count++) {
%><tr><%
for (int i = 1; i <= totalCols; i++) {
%><td><% out.println(list.get(count - 1));%></td><%
}
%></tr><%
}
%>
Displaying Records:
<% if (startIndex > 0) {
int prevPosition = startIndex - numPages;
if (prevPosition < 0) {
prevPosition = 0;
}
out.println("<td> <a herf=\"/jclevel.jsp?startIndex=" + prevPosition + "\"> & lt; < Prev</a></td>");
}
int nextPosition = startIndex + numPages;
if (tutor.itemsAvailable(nextPosition)) {
out.println("<td> <a herf=\"/jclevel.jsp?startIndex=" + nextPosition + "\"> & gt; > Next</a></td>");
}
%>
</form>
</body>
</html>
%>
<%
conn.close();
rs.close();
stmt.close();
%>
List list = new ArrayList();
Items[] items = member.getItems(startIndex, NumPages);
for (int i = 0; i < items.length; i++) {
list.add("item" + i);
The List Array part I'm really stuck cos for the example they are not linked to a database and they are using Arrary so I hope someone can tell me this part how should I modify.
Code :
package databaseAccess;
/**
*
*
*/
public class MembersBean {
private String fullName;
private String address;
private String nric;
private String subject;
public String getAddress() {
return address;
}
public String getContact() {
return contact;
}
public String getEmail() {
return email;
}
public String getFullName() {
return fullName;
}
public String getNric() {
return nric;
}
public String getSubject() {
return subject;
}
public void setAddress(String address) {
this.address = address;
}
public void setContact(String contact) {
this.contact = contact;
}
public void setEmail(String email) {
this.email = email;
}
public void setFullName(String fullName) {
this.fullName = fullName;
}
public void setNric(String nric) {
this.nric = nric;
}
public void setSubject(String subject) {
this.subject = subject;
}
public MembersBean(String fullName, String address, String nric,
String contact, String Subjects) {
this.fullName = fullName;
this.email = email;
this.address = address;
this.nric = nric;
public MembersBean() {
}}
Please. Hope someone can correct my mistakes.
