分页_商品列表_商品搜索

最新推荐文章于 2024-10-07 09:24:46 发布

weixin_33862041

最新推荐文章于 2024-10-07 09:24:46 发布

阅读量84

点赞数

文章标签： javascript ViewUI

原文链接：http://www.cnblogs.com/happy-kate/p/3214846.html

版权

分页：

select * from product limit 3, 2;从第三条往后取两条数据（不包括第三条,从1开始数起）

String sql = "select * from product limit " + (pageNo-1)*pageSize + "," + pageSize;

计算总共有多少页的算法：

总记录数：totalRecords=select count(*) from tableName;

总页数第一种算法：

if(totalReocrds%pageSize!=0)

　　tatalPages=totalRecords/pageSize+1;

else

　　totalPages=totalRecords/pageSize;

总页数第二种算法：

totalPages=(totalRecords+pageSize-1)/pageSize;

ProductMySqlDAO中的函数：

//得到总共多少页，其中pageSize为函数参数

public int getPageCount(int pageSize) {
  Connection conn = null;
  ResultSet rsCount = null;
  conn = DB.getConn();
  int pageCount = 0;
  rsCount = DB.executeQuery(conn, "select count(*) from product");
  try {
   rsCount.next();

　if(rsCount.getInt(1)%pageSize!=0)

　　pageCount=rsCount.getInt(1)/pageSize+1;

　else

　　pageCount=rsCount.getInt(1)/pageSize;
  } catch (SQLException e1) {
   e1.printStackTrace();
  } finally {
   DB.closeResultSet(rsCount);
   DB.closeConn(conn);
  }
  return pageCount;
}

//得到每页的产品，其中pageNo与pageSize为参数

public List<Product> getProducts(int pageNo, int pageSize) {
  List<Product> products = new ArrayList<Product>();
  Connection conn = null;
  ResultSet rs = null;
  String sql = "select * from product limit " + (pageNo-1)*pageSize + "," + pageSize;
  conn = DB.getConn();
  rs = DB.executeQuery(conn, sql);
  try {
   while(rs.next()) {
    Product p = new Product();
    p.setId(rs.getInt("id"));

　 ........
    products.add(p);
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   DB.closeResultSet(rs);
   DB.closeConn(conn);
  }
  return products;
}

商品列表jsp的设计：

<%!
final static int PAGE_SIZE = 3;
%>
<%
String strPageNo = request.getParameter("pageno");
int pageNo = 1;
int pageCount = 0;
if(strPageNo != null) {
pageNo = Integer.parseInt(strPageNo);
}
if(pageNo < 1) {
pageNo = 1;
}
%>
<%
List<Product> products = new ArrayList<Product>();
pageCount = ProductMgr.getInstance().getPageCount(PAGE_SIZE);
if(pageNo > pageCount) {
pageNo = pageCount;
}
products = ProductMgr.getInstance().getProducts(pageNo, PAGE_SIZE);
%>

body中的设计

　　........
      </tr>
    <%
    for(Iterator<Product> it = products.iterator(); it.hasNext();) {
      Product p = it.next();
    %>
    <tr>
    <td><%=p.getId() %></td>

　　........
      </tr>
    <%
    }
       %>
    </table>
    <center>
    第<%=pageNo %>页
     
    共<%=pageCount %>页
     
    <a href="admin/productlist.jsp?pageno=1">第一页</a>
     
    <a href="admin/productlist.jsp?pageno=<%=pageNo-1 %>">上一页</a>
     
    <a href="admin/productlist.jsp?pageno=<%=pageNo+1 %>">下一页</a>
     
    <a href="admin/productlist.jsp?pageno=<%=pageCount %>">最后一页</a>
    </center>
</body>

商品搜索jsp的设计：

......

----------------------------------------------------------------------------------------------------------------------------------------

......
      }
  }
  -->
</script>

----------------------------------------------------------------------------------------------------------------------------------------

<%!
final static int PAGE_SIZE = 3;
%>
<%
List<Category> categorys = Category.getCategorys();
List<Product> products = new ArrayList<Product>();
%>
<%
String action = request.getParameter("action");
if(action != null && action.equals("complex")) {
  String categoryId = request.getParameter("categoryid");
  int[] listId = new int[1];
  listId[0] = Integer.parseInt(categoryId);
  String keyword = request.getParameter("keyword");
  Double lowNormalPrice = Double.parseDouble(request.getParameter("lownormalprice"));
  ........
  Timestamp startPdate;
  String strStartPdate = request.getParameter("startpdate");
  if(strStartPdate == null || strStartPdate == "") {
   startPdate = null;
  }
  else {
   startPdate = Timestamp.valueOf(strStartPdate);
  }

........
  int pageNo = 1;
  if(request.getParameter("pageno") != null && !request.getParameter("pageno").equals("")){
   pageNo = Integer.parseInt(request.getParameter("pageno"));
  }
  products = ProductMgr.getInstance().searchProducts(listId, keyword,........);
%>
<center>搜索结果</center>
<table align="center" border="2">
    <tr>
      <td>产品ID</td>

      .........
     </tr>
    <%
    for(Iterator<Product> it = products.iterator(); it.hasNext();) {
      Product p = it.next();
    %>
    <tr>
    <td><%=p.getId() %></td>

       .........
      </tr>
    <%
    }
       %>
    </table>
    <center>
     <a href="productsearch.jsp?action=<%=action %>&categoryid=<%=categoryId %>&......&pageno=<%=pageNo+1 %>&pagesize=<%=PAGE_SIZE %>"">下一页</a>
    </center>
<%
}
%>

当计算搜索到的记录共有多少页时，

将products = ProductMgr.getInstance().searchProducts(listId, keyword,........);改为

int pageCount = 0;
pageCount = ProductMgr.getInstance().searchProducts(products, listId, keyword,........);

其中products作为传递参数，此函数的设计如下：

public int searchProducts(List<Product> products, int[] categoryId, String keyword,........) {
  int pageCount = 0;
  Connection conn = null;
  ResultSet rs = null;
  ResultSet rsCount = null;
  String sql = "select * from product where 1=1";
  conn = DB.getConn();
  if(categoryId != null && categoryId.length>0) {
   sql += " and categoryid in (";
   for(int i=0; i<categoryId.length; i++){
    if(i == categoryId.length -1) {
     sql += categoryId[i];
    }
    else {
     sql += categoryId[i];
     sql += ",";
    }
   }
   sql += ")";
  }
  if(keyword != null && !keyword.trim().equals("")) {
   sql += " and name like '%" + keyword +"%' or descr like '%" + keyword + "%'";
  }
  if(lowNormalPrice > 0 && highNormalPrice >0 &&lowNormalPrice != highNormalPrice && lowNormalPrice < highNormalPrice) {
   sql += " and normalprice >= " + lowNormalPrice + "and normalprice <= " + highNormalPrice;
  }
  if(lowMemberPrice > 0 && highMemberPrice >0 &&lowMemberPrice != highMemberPrice && lowMemberPrice < highMemberPrice) {
   sql += " and memberprice >= " + lowNormalPrice +"and memberprice <= " + highNormalPrice;
  }
  if(startPdate != null && endPdate != null && startPdate.compareTo(endPdate)>0) {
   sql += " and pdate between to_date(" + startPdate + ",'yyyy-MM-dd') and to_date(" + endPdate + ",'yyyy-MM-dd')";
  }
  String sqlCount = sql.replaceFirst("select \\*", "select count(*)");
System.out.println("sqlCount----" + sqlCount);
  sql += " limit " + (pageNo-1)*pageSize + " ," + pageSize;
System.out.println(sql);
  rsCount = DB.executeQuery(conn, sqlCount);
  try {
   rsCount.next();
   pageCount = (rsCount.getInt(1) + pageSize -1)/pageSize;
  } catch (SQLException e1) {
   e1.printStackTrace();
  }

  rs = DB.executeQuery(conn, sql);
  try {
   while(rs.next()) {
    Product p = new Product();
    p.setId(rs.getInt("id"));

　 ........
    products.add(p);
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   DB.closeResultSet(rs);
   DB.closeConn(conn);
  }
  return pageCount;
}

转载于:https://www.cnblogs.com/happy-kate/p/3214846.html