What's wrong with my pagination code in JSP?

Dear experts,

I've tried the following codes which I have copied from Java Ranch forum and deleted whatever lines that NetBean IDE has given me error signal.

The resulted page is that it can display all 10 records but I only want 5 records to be shown.

Hope someone can advise me how to.

Thanks!

 print("code sample");

<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">
<%@page language="java" import ="java.sql.*" %>
<%
            String access = (String) session.getAttribute("access");
            if (access == null) {
                response.sendRedirect("login.jsp");
            }%>
<%-- Display page --%>
<%          Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            Statement stmt = null;


            boolean isPageSelected = false;
            int lb = 0, ub = 0, pg = 0, totalrecords = 0, recordsPerPage = 5;
            // variables used in paging

            String selected_page = request.getParameter("page");
            if (selected_page == null || selected_page == "" || selected_page.equals("")) {// page not selected
               // isPageSelected = false;
              //  selected_page = "";
              //  lb = 0;
              //  ub = recordsPerPage;
           // } else {// page is selected
                isPageSelected = true;
               // pg = Integer.valueOf(selected_page).intValue();
                 pg = Integer.valueOf(2).intValue();
                lb = recordsPerPage * (pg - 1);
                ub = lb + recordsPerPage;
            }
            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                String url = "jdbc:odbc:ODBC1";
                conn = DriverManager.getConnection(url);
                stmt = conn.createStatement();
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
            String query = "select * from students";

            rs = stmt.executeQuery(query);
            //rs.last(); // Jump to last row
            totalrecords = rs.getRow(); // get the row count
          //  rs.beforeFirst();
            // reset to allow forward cursor processing

            int noofpages = 0;
            if (totalrecords % recordsPerPage == 0) {
                noofpages = totalrecords / recordsPerPage;
            } else {
                noofpages = totalrecords / recordsPerPage + 1;
            }

            int cnt = lb;
            //lb + 1; // move to record
              while (rs.next()) {
                                int id = rs.getInt("ID");      
           %>
                    <tr>
                    <td><%=rs.getString("studentName")%> </td>
                    <td><%=rs.getString("fatherName")%> </td>
                    <td><%=rs.getInt("age")%> </td>
                    <td><%=rs.getString("country")%> </td></tr>
                cnt++;
            } 

%>



<html>
    <head>
        <link rel="stylesheet" style type ="text/css" href ="style.css">
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    </head>
    <body>
        <div id="header">Welcome to CARE counselling</div>
        <div id="center" class="column">
            <h5>Members Record</h5>
            <table border ="1" cellspacing="0" cellspacing="0" align="center" class="bordered">
                <tr><td><b>Student Name</b></td>
                    <td><b>Father Name</b></td>
                    <td><b>Age</b></td>
                    <td><b>Country</b></td>
                </tr>
                <tr>


                   </tr> <%}
                                rs.close();
                                stmt.close();
                                conn.close();
                    %>
            </table>

            <tr>
                <td>Total Number of Members: <%=noofpages - 1%>
                    <br />
                    To return to login page : "<a href="login.jsp">Click here</a>"</td>
            </tr>
            <br />
            <br />

        </div>
    </body>
</html>

    print("code sample");

View Answers

May 25, 2012 at 3:16 PM

JSP Pagination

The given code retrieves 5 records per page from database. Here db22admin is our database. Named the given file 'pagination.jsp'.

<%@ 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;
}
%>
<%
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connectionconn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2admin","root", "root");
 int showRows=5;
int totalRecords=5;
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 db2admin.reg limit "+iPageNo+","+showRows+"";
PreparedStatement ps1=conn.prepareStatement(query1);
ResultSet rs1=ps1.executeQuery();

String query2="SELECT FOUND_ROWS() as cnt";
PreparedStatement ps2=conn.prepareStatement(query2);
ResultSet 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>
<th>USERNAME</th><th>FIRSTNAME</th><th>EMAIL</th><th>COUNTRY</th>
</tr>
<%while(rs1.next()){
%>
<tr><td><%=rs1.getString("USERNAME")%></td>
<td><%=rs1.getString("FIRSTNAME")%></td>
<td><%=rs1.getString("EMAIL")%></td>
<td><%=rs1.getString("COUNTRY")%></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="paging.jsp?iPageNo=<%=prePageNo%>&cPageNo=<%=prePageNo%>">Previous</a>
<%
}
for(i=((cPage*totalRecords)-(totalRecords-1));i<=(cPage*totalRecords);i++){
if(i==((iPageNo/showRows)+1)){%>
<a href="paging.jsp?iPageNo=<%=i%>" style="cursor:pointer;color: red"><b><%=i%></b></a>
<%
}
else if(i<=totalPages){
%>
<a href="paging.jsp?iPageNo=<%=i%>"><%=i%></a>
<%
}
}
if(totalPages>totalRecords && i<totalPages){
%>
<a href="paging.jsp?iPageNo=<%=i%>&cPageNo=<%=i%>">Next</a>
<%
}
}
%>
<b>Rows <%=startResult%> - <%=endResult%> Total Rows <%=totalRows%> </b>
</div></td></tr></table></form></body></html>

May 28, 2012 at 3:59 PM

Hi,

Thanks for posting the above code.

I have a problem here.

As I'm using MsAccess, I still couldn't figure out the code for the String query.

I have tried the following queries but it's not working :

String query1 = "SELECT * FROM students" + iPageNo + "," + showRows + "";

String query1 = "SELECT * FROM students LIMIT 0,5" + iPageNo + "," + showRows + "";

String query1 = "SELECT * FROM (SELECT top " + showRows+" * FROM (SELECT top 12 id, studentName, fatherName, age, country from students))";

But, none is working out.

Hope you can tell me what's the query to use.

Many thanks.