What's wrong with my pagination code in JSP?

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.









Related Tutorials/Questions & Answers:
What's wrong with my pagination code in JSP?
What is wrong with my servlet?
Advertisements
what is wrong with my JSP codes for updating a form?
Not sure whats wrong with my code HELP PLEASE?!?!
What's wrong with my form? - Java Beginners
JSP1
JSP2
What is wrong with this line
what is wrong with this program
please check my code is wrong or ok.it was not work .this is form validation in javascript using jsf page
ModuleNotFoundError: No module named 'jspp'
ModuleNotFoundError: No module named 'JSPy'
what is wrong in this program
what is wrong in this program......
Pagination example with html code
Pagination in PHP code - PHP
Html/JavaScript code - JSP-Servlet
please check my code is wrong or ok.it was not work .this is form validation in javascript using jsf page
Solve my code
Pagination
pagination
Pagination
Pagination
pagination
Pagination
Pagination
Pagination
pagination
pagination
Pagination
pagination
pagination
Pagination
Problem in my code - Development process
Pls review my code - Struts
pls review my code - Struts
this is my code java - Date Calendar
pls review my code - Struts
what is wrong in this program?I have netbeans 7.0 & jdk 7. please do corrections.
What is the best way to learn Java on my own?
wrong year
ModuleNotFoundError: No module named 'my-first-code-in-python'
ModuleNotFoundError: No module named 'my-first-code-in-python'
Form Code To Dispay Webpages On My Site
what is the code for login page in html
what is the code for login page in html
what is the code for login page in html
My Servlet code not working - Development process
Small Bug How to solve in my code
versions of servlets and JSPs?

Ads