8.分页SQL
通用的标准分页sql
每页显示pageSize
条记录:(pageSize:每页显示多少条记录)
第pageNo页:(pageNo-1)*pageSize,pageSize
mysql:从0开始计数,limit 开始,多少条.
第0页:
select * from emp limit 0,10
;
第1页:
select * from emp limit 10,10
;
第n页
select * from emp limit n*10,10
;
在web项目中传递页面数据一般封装成JavaBean对象->pageBean
— 分页实现的5个变量(属性):
1.数据总数 (查数据库 select count(*)...)
2.页面大小(每页显示的数据条数) (用户自定义)
3.总页数 (数据总数%页面大小==0 ? 数据总数/页面大小 :数据总数/页面大小+1)
4.当前页(页码) (用户自定义)
5.当前页的对象集合(实体类集合):每页所显示的所有数据(10个人的信息) List<Student>(查数据库,分页sql)
案例pageBean
package cn.itcast.travel.domain;
import java.util.List;
/**
* 分页对象
*/
public class PageBean<T> {
private int totalCount;//总记录数
private int totalPage;//总页数
private int currentPage;//当前页
private int pageSize;//每页显示的数据条数
private List<T> list;//每页显示的数据集合
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 int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public List<T> getList() {
return list;
}
public void setList(List<T> list) {
this.list = list;
}
}
实现收藏夹分页SQL的功能
页面效果:
实现思路:
1.前提
用户已经登录,点击我的收藏夹
携带用户的uid跳转页面
效果(为了使在收藏夹页面可以获取到uid)
2.根据用户的uid和当前的页面发送ajax请求给后端,并获取到返回的pageBean对象(josn格式)进行处理,展示在指定位置。
js代码
<script>
$(function () {
var uid=getParameter("uid");
var currentPage=getParameter("currentPage");
load(uid,currentPage);
});
function load(uid,currentPage) {
//发送ajax请求,请求route/pageQuery,传递uid
$.get("route/CollectionPageQuery", {uid: uid, currentPage: currentPage}, function (pageBean) {
//解析pageBean数据并在页面上展示
//1.分页的工具条数据显示
$("#totalPage").html(pageBean.totalPage);
$("#totalCount").html(pageBean.totalCount);
//1.1 显示分页页码
var lis = "";
var firstPage = '<li οnclick="javascript:load(' + uid + ',' + 1 + ',);"><a href="javascript:void(0)">首页</a></li>';
//计算上一页
var beforeNum = currentPage - 1;
if (beforeNum <= 0) {
beforeNum = 1;
}
var beforePage = '<li class="threeword" οnclick="javascript:load(' + uid + ',' + beforeNum + ');"><a href="javascript:void(0)">上一页</a></li>';
lis += firstPage;
lis += beforePage;
//1.2 显示10个页码
//定义开始位置begin 结束为止end
var begin;
var end;
if (pageBean.totalPage < 10) {
//总页数小于10
begin = 1;
end = pageBean.totalPage;
} else {
//总页数超过10
begin = pageBean.currentPage - 5;
end = pageBean.currentPage + 4;
if (begin < 1) {//开始为重小于1
begin = 1;
end = begin + 9;
}
if (end > pageBean.totalPage) {//结束位置大于总页数
end = pageBean.totalPage;
begin = end - 9;
}
}
for (var i = begin; i <= end; i++) {
var li;
if (pageBean.currentPage == i) {//判断是不是当前页按钮
li = '<li class="curPage" οnclick="javascript:load(' + uid + ',' + i + ');"><a href="javascript:void(0)">' + i + '</a></li>';
} else {
li = '<li οnclick="javascript:load(' + uid + ',' + i + ');"><a href="javascript:void(0)">' + i + '</a></li>';
}
lis += li;
}
var lastPage = '<li οnclick="javascript:load(' + uid + ',' + pageBean.totalPage + ');" class="threeword"><a href="javascript:void(0)">末页</a></li>';
//计算下一页
var nextNum = currentPage + 1;
if (nextNum >= pageBean.totalPage) {
nextNum = pageBean.totalPage;
}
var nextPage = '<li οnclick="javascript:load(' + uid + ',' + nextNum + ')" class="threeword"><a href="javascript:;">下一页</a></li>';
lis += nextPage;
lis += lastPage;
$("#pageNum").html(lis);
//2.divs数据显示
var divs='';
for (var i = 0; i < pageBean.list.length; i++) {
var route=pageBean.list[i];
var div=' <div class="col-md-3">\n' +
' <a href="route_detail.html?rid='+route.rid+'">\n' +
' <img src="'+route.rimage+'" alt="">\n' +
' <div class="has_border">\n' +
' <h3>'+route.rname+'</h3>\n' +
' <div class="price">网付价<em>¥</em><strong>'+route.price+'</strong><em>起</em></div>\n' +
' </div>\n' +
' </a>\n' +
' </div>';
divs+=div;
}
$("#myroutes").html(divs);
//定位到页面顶部
window.scrollTo(0, 0);
});
}
</script>
3.Servlet获取uid和currentPage调用Service获取PageBean对象,如果没有当前页,定义当前页为1,手动定义页面大小。
/**
* 根据uid进行分页查询
* @param request
* @param response
* @throws ServletException
* @throws IOException
*/
public void CollectionPageQuery(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//1.接受参数
String currentPageStr = request.getParameter("currentPage");
String pageSizeStr = request.getParameter("pageSize");
String uidStr = request.getParameter("uid");
//2.处理参数
int currentPage=0;//当前页,不传递,默认是第一页
int pageSize=0;//每页显示条数,如果不传递,默认是5条
int uid=0;//用户id
if (uidStr!=null && uidStr.length()>0 && !"null".equals(uidStr)){
uid=Integer.parseInt(uidStr);
}
if (pageSizeStr!=null && pageSizeStr.length()>0){
pageSize=Integer.parseInt(pageSizeStr);
} else {
pageSize=8;
}
if (currentPageStr!=null && currentPageStr.length()>0){
currentPage=Integer.parseInt(currentPageStr);
}else {
currentPage=1;
}
//3.调用service获取PageBean对象
PageBean<Route> routePageBean = favoriteService.favoritePageQuery(uid, currentPage, pageSize);
//4.序列化PageBean对象为json,返回
writeValue(routePageBean,response);
}
4.service调用dao层对pageBean进行封装
/**
* 查询收藏
* @return
*/
@Override
public PageBean<Route> favoritePageQuery(int uid,int currentPage,int pageSize) {
//获取收藏的线路对象集合
List<Route> list=dao.findRidsByUid(uid,(currentPage - 1) * pageSize, pageSize);
// List<Route> list = dao.findByPage(cid, (currentPage - 1) * pageSize, pageSize);
//封装pageBean
PageBean<Route> pageBean=new PageBean<Route>();
pageBean.setCurrentPage(currentPage);//设置当前页码
pageBean.setPageSize(pageSize);//设置每页显示条数
int totalCount=dao.findCountByUid(uid);
pageBean.setTotalCount(totalCount);//设置总的记录条数
pageBean.setList(list);//封装list集合进pageBean对象
int totalPage=(totalCount%pageSize)==0 ? totalCount/pageSize : (totalCount/pageSize)+1;
pageBean.setTotalPage(totalPage);//设置总的页数
return pageBean;
}
5.dao层实现查询数据总条数和查询收藏对象集合(利用j内连接jion on
查询)
/**
* 根据uid查询用户收藏线路总个数
* @param uid
* @return
*/
@Override
public int findCountByUid(int uid) {
String sql="SELECT count(*) FROM (select rid from tab_favorite where uid= ? ) e JOIN tab_route t on e.rid=t.rid";
int count= template.queryForObject(sql,Integer.class, uid);
return count;
}
/**
* 根据uid查询用户收藏线路集合
* @param uid
* @return
*/
@Override
public List<Route> findRidsByUid(int uid, int start, int pageSize) {
//定义sql模版
String sql="select t.*,e.rid from ( SELECT rid FROM tab_favorite WHERE uid = ? ) e join tab_route t on e.rid = t.rid limit ?,? ";
List<Route> list = template.query(sql, new BeanPropertyRowMapper<Route>(Route.class), uid,start,pageSize);
return list;
}
6.数据库表
tab_favorite
tab_route