Problems with paging i created

[John Schretz]

Hi all,

I am having some issues with the paging i created. Most of the time for most pages it works correctly
However on certain pages if you click on the last page link showed on the screen (say we have 1 2 3 4) and i click on page 4
the last of the items are shown but it creates another bunch of page links when it should stop at 4 and go no further.

THe count per page i am using is 24

Below is my servlet, DAO, and jsp file

TIA
John

    

SERVLET
public class DisplayProductsDept extends HttpServlet{

	protected void doGet(HttpServletRequest request, 
			HttpServletResponse response)
	throws ServletException, IOException
	{        
		String id = request.getParameter("catCode");
		String view = request.getParameter("viewall");
		String offset = request.getParameter("offset");
		String crit = request.getParameter("crit");

		if(offset == null){
			offset = "0";
		}

		HttpSession session = request.getSession();

		ServletContext context =  (ServletContext) session.getServletContext(); 

		SiteSettings settings = (SiteSettings) context.getAttribute("settings");

		int count = settings.getCatItemCount();

		int itemCount = 0;

		if(view == null){
			itemCount = ProductDB.selectDeptItemCount(id);
		}

		request.setAttribute("itemCount", itemCount);
		request.setAttribute("count", count);

		List<Product> products = null;

		if(view != null && view.equalsIgnoreCase("1")){
			products = ProductDB.selectProductsByDept(crit,offset,count, view);
		}
		else{
			products = ProductDB.selectProductsByDept(id,offset,count, view);
		}


		Product department = ProductDB.selectCatName(id);

		System.out.println(itemCount);
		System.out.println(offset);
		System.out.println(products.size());

		request.setAttribute("curpage", offset);
		request.setAttribute("products", products);
		request.setAttribute("department", department);
		request.setAttribute("vendor", id);

		if(products != null){
			request.setAttribute("count", products.size());
		}

		request.setAttribute("dept", id);
		String url = "";
		if(view != null && view.equalsIgnoreCase("1")){
			url = "/viewallDept.jsp";
		}
		else{
			url = "/categoryDept.jsp";
		}

		RequestDispatcher dispatcher =
			getServletContext().getRequestDispatcher(url);
		dispatcher.forward(request, response);
	}
}

DAO
public static ArrayList<Product> selectProductsByDept(String deptCode, String offset, int count, String view)
	{
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		String query = "SELECT DISTINCT A.DESC1, A.DESC2, A.PRICE1, B.COMPANYNAME, D.IMAGE_FILE_MAIN_M, " +
		"D.IMAGE_FILE_MAIN_L, C.DEPARTMENTNAME "+
		"FROM PRODUCT A "+
		"LEFT JOIN VENDORS B ON(A.VENDORCODE = B.VENDORCODE) "+
		"LEFT JOIN DEPARTMENT C ON(A.DEPARTMENTCODE = C.DEPARTMENTCODE) "+
		"LEFT JOIN PRODUCT_IMAGE D ON(A.IMAGE_ID = D.IMAGE_ID) "+
		"WHERE C.DEPARTMENTCODE = ? " +
		"AND A.SELL_ON_WEB = 'Y' AND B.SHOW_ON_SITE = 'Y' ORDER BY A.ITEMNUMBER DESC "+
		"LIMIT ?, ?";

		String query2 = "SELECT DISTINCT A.DESC1, A.DESC2, A.PRICE1, B.COMPANYNAME, D.IMAGE_FILE_MAIN_M, " +
		"D.IMAGE_FILE_MAIN_L, C.DEPARTMENTNAME "+
		"FROM PRODUCT A "+
		"LEFT JOIN VENDORS B ON(A.VENDORCODE = B.VENDORCODE) "+
		"LEFT JOIN DEPARTMENT C ON(A.DEPARTMENTCODE = C.DEPARTMENTCODE) "+
		"LEFT JOIN PRODUCT_IMAGE D ON(A.IMAGE_ID = D.IMAGE_ID) "+
		"WHERE C.DEPARTMENTCODE = ? " +
		"AND A.SELL_ON_WEB = 'Y' AND B.SHOW_ON_SITE = 'Y' ORDER BY A.ITEMNUMBER DESC";

		try
		{
			if(view == null){
				ps = connection.prepareStatement(query);
			}
			else if(view != null && view.equalsIgnoreCase("1")){
				ps = connection.prepareStatement(query2);
			}
			ps.setString(1, deptCode);

			if(view == null){
				ps.setInt(2, Integer.parseInt(offset));
				ps.setInt(3, count);
			}

			System.out.println(ps);
			rs = ps.executeQuery();
			ArrayList<Product> products = new ArrayList<Product>();
			while (rs.next())
			{
				Product p = new Product();

				p.setPrice1(rs.getDouble("PRICE1"));
				p.setMimageM(rs.getString("IMAGE_FILE_MAIN_M"));
				p.setMimageL(rs.getString("IMAGE_FILE_MAIN_L"));
				p.setBrand(rs.getString("COMPANYNAME"));
				p.setDesc2(rs.getString("DESC2"));
				p.setDesc1(rs.getString("DESC1"));

				products.add(p);
			}
			return products;
		}
		catch(SQLException e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			UtilDB.closeResultSet(rs);
			UtilDB.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
	}  
	
	
	JSP FILE
	                  <!-- DO PAGING HERE-->
                     
                      <c:if test="${itemCount > count}">
                      <a >
                      <img   border="0" /></a>
                      
                      <c:if test="${curpage > 1}">
                      <a >
					  <img   border="0"/></a>
                      </c:if>
                      
                      <c:set var="endcount" value="${itemCount / count}" scope="page"/>
                      <c:if test="${endcount % 2 !=0}">
                      <c:set var="endcount" value="${(itemCount / count) + 1}" scope="page"/>
                      </c:if>
                      
                      <c:forEach begin="1" end="${endcount}" step="1" var="page">
                      
                      <c:choose>
                      <c:when test="${(page-1)*count == curpage}">
                      <b>${page}</b>
                      </c:when>
                      <c:otherwise>
                      <a   rel="nofollow">${page}</a>
                      </c:otherwise> 
                      </c:choose>                   
                      </c:forEach>
                      <c:if test="${(curpage+count) < itemCount}">
                       <a >
					  <img  border="0" /></a>
                      </c:if>
                      </c:if>
                      
                      <!--END DO PAGING HERE--> 

[Bauke Scholtz]

Crossposted: http://forums.sun.com/thread.jspa?threadID=5358615
Please read this: http://faq.javaranch.com/java/BeForthrightWhenCrossPostingToOtherSites

[Ankit Garg]

I think this condition is creating the problems.

if(products != null){  
            request.setAttribute("count", products.size());  
} 

Here you are setting the count attribute for the second time. And this will hurt you only when the size of products is < 24 i.e. the actual number of records to be shown on each page...

[John Schretz]

Bauke Scholtz wrote:Crossposted: http://forums.sun.com/thread.jspa?threadID=5358615
Please read this: http://faq.javaranch.com/java/BeForthrightWhenCrossPostingToOtherSites

Sorry this is an urgent issue that i really need help on. I figure the more people that see it the better.

Any help would be greatly appreciated. i did not know that crossposting was shunned upon

thanks
john

[John Schretz]

Ankit Garg wrote:I think this condition is creating the problems.

if(products != null){  
            request.setAttribute("count", products.size());  
} 

Here you are setting the count attribute for the second time. And this will hurt you only when the size of products is < 24 i.e. the actual number of records to be shown on each page...

The first count i am setting is the count per page iam am using 24

the second is the count of the array

which one should i remove?

Why exactly though would that cause a problem? Is it doubling the size by setting it twice?

[Ankit Garg]

First of all remove the code which I pointed out then it will run fine.

When you set the attribute for the second time, the value that was set earlier will be removed. This creates problems. Suppose the last page has 12 records. When you set count for the second time, then it will behave as if 12 records should be shown on each page. So the loop in your JSP will run double times. So if 4 page numbers should have been printed, then 8 will be displayed...

[John Schretz]

Ankit Garg wrote:First of all remove the code which I pointed out then it will run fine.

When you set the attribute for the second time, the value that was set earlier will be removed. This creates problems. Suppose the last page has 12 records. When you set count for the second time, then it will behave as if 12 records should be shown on each page. So the loop in your JSP will run double times. So if 4 page numbers should have been printed, then 8 will be displayed...

ok, just trying to fully understand

if i remove the line

   
if(products != null){     
            request.setAttribute("count", products.size());     
}  
 

then the only count attribute i will be setting is:

int count = settings.getCatItemCount(); // Which is always going to be 24

So you are saying that i should always set the count to 24?

sorry, i just wnat to understand the fix and not just fix it.

thanks very much for the help

John

[Ankit Garg]

Yes! You are right. If you change the value of count, then the calculation in the jsp

<c:set var="endcount" value="${itemCount / count}" scope="page"/>

will fail. Suppose the size of products is 12 i.e. the last page has 12 records and total there are 96 records. Then total number of pages should be 4. But since you set count to 12, so itemCount / count will be 96 / 12 i.e. 8. So it will display 8 page numbers...

[John Schretz]

Ankit Garg wrote:Yes! You are right. If you change the value of count, then the calculation in the jsp

<c:set var="endcount" value="${itemCount / count}" scope="page"/>

will fail. Suppose the size of products is 12 i.e. the last page has 12 records and total there are 96 records. Then total number of pages should be 4. But since you set count to 12, so itemCount / count will be 96 / 12 i.e. 8. So it will display 8 page numbers...

ok, so is the problem in the jsp or the servlet?
Should i still remove that line?

thanks again
john

[Ankit Garg]

John just remove that line and then the code will run fine. The problem is in the servlet dude...

[John Schretz]

Ankit Garg wrote:John just remove that line and then the code will run fine. The problem is in the servlet dude...

lol, i hear ya. I dont want to sound stupid, i am new to jsp/ servlets, but have been a core devloper for a few years.
I just want to understand what i am fixing, so i feel i learned something in the process.

Thanks very much for the help.
John