分页_商品列表_商品搜索
分页:
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中的设计
<body>
<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>
第<%=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的设计:
<form action="admin/productsearch.jsp" name="complexsearch" method="get" onsubmit="checkdata()">
<input type="hidden" name="action" value="complex">
......
----------------------------------------------------------------------------------------------------------------------------------------
<script type="text/javascript">
<!--
function checkdata() {
with(document.forms["complexsearch"]) {
if(lownormalprice.value == null || lownormalprice.value == ""){
lownormalprice.value = -1;
}
......
}
}
-->
</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;
}