一. 效果预览
二. 实现基本分页
2.1 分页sql
--每页3条 取第二页 --由于伪列不能作用与大于符号也不能作用于between....and --因此需要将伪列----->名列 select * from( select a.*,rownum as rid from tb_goods a where gname like '%商%' ) b where b.rid between 5 and 8
2.2 万能公式
/* *如果每页3条即pageSize=3 *当pageIndex=1:1-3 *当pageIndex=2:4-6 *当pageIndex=3:7-9 * *如果每页5条即pageSize=5 *当pageIndex=1:1-5 *当pageIndex=2:6-10 *当pageIndex=3:11-15 */ (pageIndex-1)*pageSize+1-------(pageIndex*pageSize)
2.3 首页&上一页&下一页实现
①底层代码
/** * 模糊查询的分页 * @param pageIndex 设置第几页 * @param pageSize 每页多少条数据 * @param str 关键字 * @param colName 列名 * @return 商品集合 */ public List<Goods> page(int pageIndex,int pageSize,String str,String colName){ List<Goods> ls = new ArrayList<>(); try { con=DBHelper.getCon(); String sql="select * from(\r\n" + " select a.*,rownum as rid from tb_goods a where "+colName+" like '%"+str+"%' order by gid desc \r\n" + ")b where b.rid between ? and ?"; ps=con.prepareStatement(sql); //给占位符赋值 ps.setInt(1, (pageIndex-1)*pageSize+1); ps.setInt(2, (pageIndex*pageSize)); //System.out.println("SQL:"+sql); 打印出来为null 在前端要判断 goodsServlet rs=ps.executeQuery(); while(rs.next()) { Goods g = new Goods(); g.setGid(rs.getInt(1)); g.setGname(rs.getString(2)); g.setGprice(rs.getDouble(3)); g.setGinfo(rs.getString(4)); g.setGtime(rs.getString(5)); g.setGpath(rs.getString(6)); g.setGxl(rs.getInt(7)); g.setTid(rs.getInt(8)); ls.add(g); } } catch (Exception e) { e.printStackTrace(); }finally { DBHelper.myClose(con, ps, rs); } return ls; } int pageIndex=1;//当前页 第几页 int pageSize=3;//每页多少条数据 //接收参数 String pid = req.getParameter("pid"); if(pid!=null) {//说明点了"首页"/"上一页"/"下一页"/"末页" //改变页码 pageIndex=Integer.parseInt(pid); } //拿到集合 List<Goods> ls = igd.page(pageIndex, pageSize); //把当前页码存起来 req.setAttribute("pageIndex,"pageIndex) }
②前端代码
<!-- 分页 --> <nav aria-label="Page navigation example"> <ul class="pagination justify-content-center pagination-lg"> <li class="page-item" ><a class="page-link" href="goods.do?pid=1">首页</a></li> <li class="page-item " id="prev"><a class="page-link" href="goods.do?pid=$goods.do?pid=${pageIndex-1}">上一页</a></li> <li class="page-item "><a class="page-link text-info"><span id="ys"></span>/<span id="max"></span></a></li> <li class="page-item " id="next"><a class="page-link" href="goods.do?pid=1"${pageIndex+1}>下一页</a></li> <li class="page-item"><a class="page-link" href="goods.do?pid=${max}">末页</a></li> </ul> </nav>
2.4 末页实现&优化
①底层代码
/** * 获取总行数 * @param str 表名等 * @return 总行数 */ public int getRows(String str) { int n = 0; try { con=DBHelper.getCon(); String sql="select count(*) from "+str; ps=con.prepareStatement(sql); rs=ps.executeQuery(); if(rs.next()) { n=rs.getInt(1); } } catch (Exception e) { e.printStackTrace(); }finally { DBHelper.myClose(con, ps, rs); } return n; } ---【在业务逻辑层biz做处理】--- /* *拿到最大页码 *@param tabName 表名等 * @param pageSize 每页多少条数据 *@return 最大页码 */ public int getMax(String str,int pageSize) { //拿到总行数 int rows = igd.getRows(str); //计算总页数 int max = rows/pageSize;//10/3=3 if(rows%pageSize!=0) {//除不尽 max++;//多加一页 } return max; } //拿到最大页码 int max = igd.getMax("tb_goods ",pageSize) //把最大页码存起来 req.setAttribute("max", max);
②前端优化
三.实现模糊查询（下拉框）的分页
3.1 确定sql语句
--查询简介中含有'商'的商品 每页2条 取第二页 select * from( select a.*,rownum as rid from tb_goods a where gname like '%商%' ) b where b.rid between 5 and 8 --查询简介中含有'你'的商品 每页3条 取第二页 select * from( select a.*,rownum as rid from tb_goods a where gname like '%你%' ) b where b.rid between 1 and 3
3.2 代码实现
①底层代码
/** * 模糊查询的分页 * @param pageIndex 设置第几页 * @param pageSize 每页多少条数据 * @param str 关键字 * @param colName 列名 * @return 商品集合 */ public List<Goods> page(int pageIndex,int pageSize,String str,String colName){ List<Goods> ls = new ArrayList<>(); try { con=DBHelper.getCon(); String sql="select * from(\r\n" + " select a.*,rownum as rid from tb_goods a where "+colName+" like '%"+str+"%' order by gid desc \r\n" + ")b where b.rid between ? and ?"; ps=con.prepareStatement(sql); //给占位符赋值 ps.setInt(1, (pageIndex-1)*pageSize+1); ps.setInt(2, (pageIndex*pageSize)); //System.out.println("SQL:"+sql); 打印出来为null 在前端要判断 goodsServlet rs=ps.executeQuery(); while(rs.next()) { Goods g = new Goods(); g.setGid(rs.getInt(1)); g.setGname(rs.getString(2)); g.setGprice(rs.getDouble(3)); g.setGinfo(rs.getString(4)); g.setGtime(rs.getString(5)); g.setGpath(rs.getString(6)); g.setGxl(rs.getInt(7)); g.setTid(rs.getInt(8)); ls.add(g); } } catch (Exception e) { e.printStackTrace(); }finally { DBHelper.myClose(con, ps, rs); } return ls; }
②前端优化
//拿到模糊查询分页后的商品集合 List<Goods> ls = igd.page(pageIndex, pageSize, str, xl);
