怎么写分页查询
通过上面的uid 来找到响应数据库中的信息填充到页面上
1.点击对应的选框获取对应的uid
var search = location.search;
alert(search);//?id=5
// 切割字符串,拿到第二个值
var cid = search.split("=")[1];*/
2.通过对应的uid获取到页面的信息
load(cid ,currentPage,rname)
此时我调用一个load方法来获取发送ajax请求加载数据
function load(cid ,currentPage,rname){
//发送ajax请求,请求route/pageQuery,传递cid
$.get("route/pageQuery",{cid:cid,currentPage:currentPage,rname:rname},function (pb) {
//解析pagebean数据,展示到页面上
//1.分页工具条数据展示
//1.1 展示总页码和总记录数
$("#totalPage").html(pb.totalPage);
$("#totalCount").html(pb.totalCount);
/*
<li><a href="">首页</a></li>
<li class="threeword"><a href="#">上一页</a></li>
<li class="curPage"><a href="#">1</a></li>
<li><a href="#">2</a></li>
<li><a href="#">3</a></li>
<li><a href="#">4</a></li>
<li><a href="#">5</a></li>
<li><a href="#">6</a></li>
<li><a href="#">7</a></li>
<li><a href="#">8</a></li>
<li><a href="#">9</a></li>
<li><a href="#">10</a></li>
<li class="threeword"><a href="javascript:;">下一页</a></li>
<li class="threeword"><a href="javascript:;">末页</a></li>
*/
var lis = "";
var fristPage = '<li οnclick="javascipt:load('+cid+',1,\''+rname+'\')"><a href="javascript:void(0)">首页</a></li>';
//计算上一页的页码
var beforeNum = pb.currentPage - 1;
if(beforeNum <= 0){
beforeNum = 1;
}
var beforePage = '<li οnclick="javascipt:load('+cid+','+beforeNum+',\''+rname+'\')" class="threeword"><a href="javascript:void(0)">上一页</a></li>';
//计算上一页的页码
lis += fristPage;
lis += beforePage;
//1.2 展示分页页码
/*
1.一共展示10个页码,能够达到前5后4的效果
2.如果前边不够5个,后边补齐10个
3.如果后边不足4个,前边补齐10个
*/
// 定义开始位置begin,结束位置 end
var begin; // 开始位置
var end ; // 结束位置
//1.要显示10个页码
if(pb.totalPage < 10){
//总页码不够10页
begin = 1;
end = pb.totalPage;
}else{
//总页码超过10页
begin = pb.currentPage - 5 ;
end = pb.currentPage + 4 ;
//2.如果前边不够5个,后边补齐10个
if(begin < 1){
begin = 1;
end = begin + 9;
}
//3.如果后边不足4个,前边补齐10个
if(end > pb.totalPage){
end = pb.totalPage;
begin = end - 9 ;
}
}
for (var i = begin; i <= end ; i++) {
var li;
//判断当前页码是否等于i
if(pb.currentPage == i){
li = '<li class="curPage" οnclick="javascipt:load('+cid+','+i+',\''+rname+'\')"><a href="javascript:void(0)">'+i+'</a></li>';
}else{
//创建页码的li
li = '<li οnclick="javascipt:load('+cid+','+i+',\''+rname+'\')"><a href="javascript:void(0)">'+i+'</a></li>';
}
//拼接字符串
lis += li;
}
/* for (var i = 1; i <= pb.totalPage ; i++) {
var li;
//判断当前页码是否等于i
if(pb.currentPage == i){
li = '<li class="curPage" οnclick="javascipt:load('+cid+','+i+')"><a href="javascript:void(0)">'+i+'</a></li>';
}else{
//创建页码的li
li = '<li οnclick="javascipt:load('+cid+','+i+')"><a href="javascript:void(0)">'+i+'</a></li>';
}
//拼接字符串
lis += li;
}*/
var nextNum = pb.currentPage;
if (nextNum > pb.totalPage){
nextNum = pb.totalPage;
var nextPage = '<li οnclick="load('+cid+','+nextNum+')" class="threeword"><a href="javascript:;">下一页</a></li>';
}else {
nextNum += 1;
var nextPage = '<li οnclick="load('+cid+','+nextNum+')" class="threeword"><a href="javascript:;">下一页</a></li>';
}
var lastPage = '<li οnclick="load('+cid+','+pb.totalPage+')" class="threeword"><a href="javascript:;">末页</a></li>';
lis += nextPage;
lis += lastPage;
//将lis内容设置到 ul
$("#pageNum").html(lis);
/*
<li>
<div class="img"><img src="images/04-search_03.jpg" alt=""></div>
<div class="text1">
<p>【减100元 含除夕/春节出发】广州增城三英温泉度假酒店/自由行套票</p>
<br/>
<p>1-2月出发,网付立享¥1099/2人起!爆款位置有限,抢完即止!</p>
</div>
<div class="price">
<p class="price_num">
<span>¥</span>
<span>299</span>
<span>起</span>
</p>
<p><a href="route_detail.html">查看详情</a></p>
</div>
</li>
*/
//2.列表数据展示
var route_lis = "";
for (var i = 0; i < pb.list.length; i++) {
//获取{rid:1,rname:"xxx"}
var route = pb.list[i];
var li = '<li>\n' +
' <div class="img"><img src="'+route.rimage+'" style="width: 299px;"></div>\n' +
' <div class="text1">\n' +
' <p>'+route.rname+'</p>\n' +
' <br/>\n' +
' <p>'+route.routeIntroduce+'</p>\n' +
' </div>\n' +
' <div class="price">\n' +
' <p class="price_num">\n' +
' <span>¥</span>\n' +
' <span>'+route.price+'</span>\n' +
' <span>起</span>\n' +
' </p>\n' +
' <p><a href="route_detail.html?rid='+route.rid+'">查看详情</a></p>\n' +
' </div>\n' +
' </li>';
route_lis += li;
}
$("#route").html(route_lis);
//定位到页面顶部
window.scrollTo(0,0);
});
}
其中代表的是get请求,给服务器传递的参数 {cid:cid,currentPage:currentPage,rname:rname}
$.get("route/pageQuery",{cid:cid,currentPage:currentPage,rname:rname},function (pb) {}
服务器端接收到信息,然后将其封装为pageBean
对象
package cn.itcast.travel.domain;
import java.util.List;
/**
* @_PackageName:cn.itcast.travel.domain
* @_ClassName:PageBean
* @_Description:
* @_Author:笑老二
* @_data 2020/4/11 13:17
*/
public class PageBean<T> {
private int totalCount;//总记录数
private int totalPage;//总页数
private int currentPage;//当前页码
private int pageSize;//每页显示的条数
private List<T> list;//每页显示的集合
@Override
public String toString() {
return "PageBean{" +
"totalCount=" + totalCount +
", totalPage=" + totalPage +
", currentPage=" + currentPage +
", pageSize=" + pageSize +
", list=" + 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;
}
public PageBean() {
}
public PageBean(int totalCount, int totalPage, int currentPage, int pageSize, List<T> list) {
this.totalCount = totalCount;
this.totalPage = totalPage;
this.currentPage = currentPage;
this.pageSize = pageSize;
this.list = list;
}
}
封装为pageBean
后,调用BaseServlet中的方法writerValue
将其转换为Json
对象返回给客户端
package cn.itcast.travel.web.servlet;
import cn.itcast.travel.domain.PageBean;
import cn.itcast.travel.domain.Route;
import cn.itcast.travel.service.RouteService;
import cn.itcast.travel.service.impl.RouteServiceImpl;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("/route/*")
public class RouteServlet extends BaseServlet {
private RouteService service = new RouteServiceImpl();
/**
* 分页查询
* @param request
* @param response
* @throws ServletException
* @throws IOException
*/
public void pageQuery(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//1.接收参数
String currentPageStr = request.getParameter("currentPage");
String pageSizeStr = request.getParameter("pageSize");
String cidStr = request.getParameter("cid");
//2.处理参数
//类别的id
int cid = 0;
if (cidStr != null && cidStr.length() > 0) {
cid = Integer.parseInt(cidStr);
}
//当前页码数
int currentPage = 0;
if (currentPageStr != null && currentPageStr.length() > 0) {
currentPage = Integer.parseInt(currentPageStr);
} else {
currentPage = 1;
}
//每页显示的条数,如果不传递默认每页显示5条
int pageSize = 0;
if (pageSizeStr != null && pageSizeStr.length() > 0) {
pageSize = Integer.parseInt(currentPageStr);
} else {
pageSize = 5;
}
//3.调用service查询pageBean对象
PageBean<Route> pb = service.pageQuery(cid, currentPage, pageSize);
//4.将pageBean返回为json对象
writerValue(pb,response);
}
}
RouteServiceImpl中的方法查询对应的uid返回数据库中的数据,封装为PageBean对象
package cn.itcast.travel.service.impl;
import cn.itcast.travel.dao.RouteDao;
import cn.itcast.travel.dao.impl.RouteDaoImpl;
import cn.itcast.travel.domain.PageBean;
import cn.itcast.travel.domain.Route;
import cn.itcast.travel.service.RouteService;
import java.util.List;
/**
* @_PackageName:cn.itcast.travel.service.impl
* @_ClassName:RouteServiceImpl
* @_Description:
* @_Author:笑老二
* @_data 2020/4/11 13:37
*/
public class RouteServiceImpl implements RouteService {
private RouteDao routeDao = new RouteDaoImpl();
@Override
public PageBean<Route> pageQuery(int cid, int currentPage, int pageSize) {
//封装pageBean
PageBean<Route> pb = new PageBean<Route>();
//设置当前页码
pb.setCurrentPage(currentPage);
//设置每页显示条数
pb.setPageSize(pageSize);
//设置总记录数
int totalCount = routeDao.findTotalCount(cid);
pb.setTotalCount(totalCount);
//设置当前页面的数据集合
int start = (currentPage - 1) * pageSize;//开始的记录数
List<Route> list = routeDao.findByPage(cid, start, pageSize);
pb.setList(list);
//设置总页数 = 总记录数/每页显示条数
int totalPage = totalCount % pageSize == 0 ? totalCount / pageSize :totalCount / pageSize+1;
pb.setTotalPage(totalPage);
return pb;
}
}
RouteDaoImpl类
package cn.itcast.travel.dao.impl;
import cn.itcast.travel.dao.RouteDao;
import cn.itcast.travel.domain.Route;
import cn.itcast.travel.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;
/**
* @_PackageName:cn.itcast.travel.dao.impl
* @_ClassName:RouteDaoImpl
* @_Description:
* @_Author:笑老二
* @_data 2020/4/11 13:45
*/
public class RouteDaoImpl implements RouteDao {
private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
/**
* 根据cid查询总记录数
*/
@Override
public int findTotalCount(int cid) {
String sql = "select count(*) from tab_route where cid = ?";
return template.queryForObject(sql,Integer.class,cid);
}
/**
* 根据cid,start,pageSize查询当前页面的数据集合
*/
@Override
public List<Route> findByPage(int cid, int start, int pageSzie) {
String sql = "select * from tab_route where cid =? limit ? , ?";
return template.query(sql,new BeanPropertyRowMapper<Route>(Route.class),cid,start,pageSzie);
}
}
数据库