条件和分页
1 条件查询
Dao
这里有两种方式处理模糊查询,
- 用集合封装sql语句
- 直接把if里面的? 改成传参的方式
public List<Product> findProductListByCondition(Condition condition) throws SQLException { QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource()); List<String> list = new ArrayList<String>(); String sql = "select * from product where 1=1 "; if(condition.getPname()!=null && !condition.getPname().trim().equals("")){ sql+=" and pname like "+"'%"+condition.getPname()+"%'"; //list.add("%"+condition.getPname()+"%"); } if(condition.getIsHot()!= null && !condition.getIsHot().trim().equals("")){ sql+=" and is_hot= "+condition.getIsHot().trim(); //list.add(condition.getIsHot().trim() ); } if(condition.getCid()!= null && !condition.getCid().trim().equals("")){ sql+=" and cid= "+condition.getCid(); //list.add(condition.getCid()); } List<Product> productList = runner.query(sql, new BeanListHandler<Product>(Product.class)/*list.toArray()*/); return productList; }
Jsp
1 回显 (下拉列表显示你最后选择的)
$(function(){ $("#isHot option[value='${condition.isHot}']").prop("selected",true); $("#cid option[value='${condition.cid}']").prop("selected",true); });
2 模糊查询后的显示
<c:forEach items="${productList }" var="pro" varStatus="vs"> <tr onmouseover="this.style.backgroundColor = 'white'" onmouseout="this.style.backgroundColor = '#F5FAFE';"> <td style="CURSOR: hand; HEIGHT: 22px" align="center" width="18%">${vs.count }</td> <td style="CURSOR: hand; HEIGHT: 22px" align="center" width="17%"> <img width="40" height="45" src="${pageContext.request.contextPath }/${pro.pimage }"> </td> <td style="CURSOR: hand; HEIGHT: 22px" align="center" width="17%">${pro.pname }</td> <td style="CURSOR: hand; HEIGHT: 22px" align="center" width="17%">${pro.shop_price }</td> <td style="CURSOR: hand; HEIGHT: 22px" align="center" width="17%">${pro.is_hot==1?"是":"否" }</td> <td align="center" style="HEIGHT: 22px"><a href="${ pageContext.request.contextPath }/adminUpdateProductUI?pid=${pro.pid}"> <img src="${pageContext.request.contextPath}/images/i_edit.gif" border="0" style="CURSOR: hand"> </a></td> <td align="center" style="HEIGHT: 22px"> <a href="javascript:void(0);" onclick="delProduct('${pro.pid}')"> <img src="${pageContext.request.contextPath}/images/i_del.gif" width="16" height="16" border="0" style="CURSOR: hand"> </a> </td> </tr> </c:forEach>
2 分页
难点:是bean 和service
bean
public class PageBean<T> { //当前页 private int currentPage; //当前页显示的条数 private int currentCount; //总条数 private int totalCount; //总页数 private int totalPage; //每页显示的数据 private List<T> productList = new ArrayList<T>(); public int getCurrentPage() { return currentPage; } public void setCurrentPage(int currentPage) { this.currentPage = currentPage; } public int getCurrentCount() { return currentCount; } public void setCurrentCount(int currentCount) { this.currentCount = currentCount; } public int getTotalCount() { return totalCount; } public void setTotalCount(int totalCount) { this.totalCount = totalCount; } public int getTotalPage() { return totalPage; } public void setTotalPage(int totalPage) { this.totalPage = totalPage; } public List<T> getProductList() { return productList; } public void setProductList(List<T> productList) { this.productList = productList; }
Service
ProductDao dao = new ProductDao(); //目的：就是想办法封装一个PageBean 并返回 PageBean pageBean = new PageBean(); //1、当前页private int currentPage; pageBean.setCurrentPage(currentPage); //2、当前页显示的条数private int currentCount; pageBean.setCurrentCount(currentCount); //3、总条数private int totalCount; int totalCount = dao.getTotalCount(); pageBean.setTotalCount(totalCount); //4、总页数private int totalPage; /* * 总条数 当前页显示的条数 总页数 * 10 4 3 * 11 4 3 * 12 4 3 * 13 4 4 * * 公式：总页数=Math.ceil(总条数/当前显示的条数) * */ int totalPage = (int) Math.ceil(1.0*totalCount/currentCount); pageBean.setTotalPage(totalPage); //5、每页显示的数据private List<T> productList = new ArrayList<T>(); /* * 页数与limit起始索引的关系 * 例如 每页显示4条 * 页数 其实索引 每页显示条数 * 1 0 4 * 2 4 4 * 3 8 4 * 4 12 4 * * 索引index = (当前页数-1)*每页显示的条数 * */ int index = (currentPage-1)*currentCount; List<Product> productList = dao.findProductListForPageBean(index,currentCount); pageBean.setProductList(productList); return pageBean;/
dao
//获得全部的商品条数 public int getTotalCount() throws SQLException { QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource()); String sql = "select count(*) from product"; Long query = (Long) runner.query(sql, new ScalarHandler()); return query.intValue(); } //获得分页的商品数据 public List<Product> findProductListForPageBean(int index,int currentCount) throws SQLException { QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource()); String sql = "select * from product limit ?,?"; return runner.query(sql, new BeanListHandler<Product>(Product.class), index,currentCount); }
Jsp
显示商品
<c:forEach items="${pageBean.productList }" var="product"> <div class="col-md-2" style="height:250px"> <a href="product_info.htm"> <img src="${pageContext.request.contextPath }/${product.pimage}" width="170" height="170" style="display: inline-block;"> </a> <p> <a href="product_info.html" style='color: green'>${product.pname }</a> </p> <p> <font color="#FF0000">商城价：¥${product.shop_price }</font> </p> </div> </c:forEach>
分页
<!--分页 -->
<div style=" 380px; margin: 0 auto; margin-top: 50px;">
<ul class="pagination" style="text-align: center; margin-top: 10px;">
<!-- 上一页 -->
<!-- 判断当前页是否是第一页 -->
<c:if test="${pageBean.currentPage==1 }">
<li class="disabled">
<a href="javascript:void(0);" aria-label="Previous">
<span aria-hidden="true">«</span>
</a>
</li>
</c:if>
<c:if test="${pageBean.currentPage!=1 }">
<li>
<a href="${pageContext.request.contextPath }/productList?currentPage=${pageBean.currentPage-1}" aria-label="Previous">
<span aria-hidden="true">«</span>
</a>
</li>
</c:if>
<c:forEach begin="1" end="${pageBean.totalPage }" var="page">
<!-- 判断当前页 -->
<c:if test="${pageBean.currentPage==page }">
<li class="active"><a href="javascript:void(0);">${page}</a></li>
</c:if>
<c:if test="${pageBean.currentPage!=page }">
<li><a href="${pageContext.request.contextPath }/productList?currentPage=${page}">${page}</a></li>
</c:if>
</c:forEach>
<!-- 判断当前页是否是最后一页 -->
<c:if test="${pageBean.currentPage==pageBean.totalPage }">
<li class="disabled">
<a href="javascript:void(0);" aria-label="Next">
<span aria-hidden="true">»</span>
</a>
</li>
</c:if>
<c:if test="${pageBean.currentPage!=pageBean.totalPage }">
<li>
<a href="${pageContext.request.contextPath }/productList?currentPage=${pageBean.currentPage+1}" aria-label="Next">
<span aria-hidden="true">»</span>
</a>
</li>
</c:if>
</ul>
</div>
<!-- 分页结束 -->
3 源码
链接：https://pan.baidu.com/s/1zsWljXJsGrf-ET0t6kgySw 密码：2n4r
