目录
--由于伪列不能作用与大于符号也不能作用于between....and
二. 实现基本分页 --每页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
/*
*如果每页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)
/**
* 模糊查询的分页
* @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>
/**
* 获取总行数
* @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);
--查询简介中含有'商'的商品 每页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
/**
* 模糊查询的分页
* @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);
