黑马旅游网分页查询分析（上）

需求：

将旅游线路进行分类
并从数据库中取出数据进行页面展示

分析

分析数据库

旅游线路表：tab_route
旅游线路分类表：tab_category
可以发现一个分类表可以对应多条路由线路，存在着一对多的关系。

后台模块设计分析：

前台旅游分类：

页面分析（前台和后台的数据交互）：

前台需要通过ajax传到后台的数据：也就是前台展示分页内容需要请求的数据

int currentPage：当前页码
int pageSize：每页显示条数
int cid：旅游分类id

后台需要传到前台的数据：响应前台请求处理后需要返回的数据

int totalCount：总记录数
int totalPage：总页数
int currentPage：当前页码
int pageSize：当前页显示的条数
List list：当前页显示的数据集合

可以通过前台传到后台的数据，计算和写出所有后台返回前台的数据
1）totalCount ：通过sql语句查询数据库获得

select  count(*) from tab_route where cid = ?

2）totalPage ：通过计算公式

totalPage  = totalCount % pageSize == 0 ?  totalCount / pageSize : totalCount / pageSize +1

List list：当前页显示的数据集合 ,通过sql语句返回的list集合查询

 String sql = "select * from tab_route where cid = ? limit ? , ?";
 //例如：表示从0开始查询，查询5个数据（表中数据从0开始）
 SELECT * FROM tab_route WHERE cid=5 LIMIT 0, 5;

结果如下：

代码编写

准备好分页对象的实体类PageBean：

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;
    }

servlet编写

   /**
     * 分页查询
     * @param request
     * @param response
     * @return PageBean
     */
    public void pageQuery(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //1.获取前端请求
        String currentPageStr = request.getParameter("currentPage");
        String pageSizeStr = request.getParameter("pageSize");
        String cidStr = request.getParameter("cid");

        //2.处理参数
        int  currentPage = 0;
        int pageSize = 0;
        int cid = 0;

        //cid
        if( cidStr != null && cidStr.length() > 0){
            cid = Integer.parseInt(cidStr);
        }
        //currentPage
        if( currentPageStr != null || currentPageStr.length() > 0){
            currentPage = Integer.parseInt(currentPageStr);
        }else{
            currentPage = 1;
        }
        //pageSize
        if( pageSizeStr != null || pageSizeStr.length() > 0){
            pageSize = Integer.parseInt(pageSizeStr);
        }else{
            pageSize = 5;
        }
        //3.调用service查询
        RouteService service = new RouteServiceImpl();
        PageBean pageBean = service.pageQuery(currentPage, pageSize, cid);
        writeValue(pageBean,response);
    }

service业务逻辑层编写

public class RouteServiceImpl implements RouteService {
    private RouteDao dao = new RouteDaoImpl();
    /**
     * 目的：封装pageBean对象并返回
     * @param currentPage
     * @param pageSize
     * @param cid
     * @return
     *
     *  1. int totalCount：总记录数
        2. int totalPage：总页数
        3. int currentPage：当前页码
        4. int pageSize：当前页显示的条数
        5. List list：当前页显示的数据集合
     */
    @Override
    public PageBean pageQuery(int currentPage, int pageSize, int cid) {
        //1.创建PageBean对象
        PageBean<Route> pageBean = new PageBean<>();
        //2.设置前台传入的参数
        pageBean.setCurrentPage(currentPage);
        pageBean.setPageSize(pageSize);

        //3.查询dao层数据，将返回数据再次设置进pageBean
        int totalCount = dao.finTotalCount(cid);
        pageBean.setTotalCount(totalCount);
        int start = (currentPage -1 ) * pageSize; //开始每页查询的索引
        List<Route> list = dao.pageList(cid, start, pageSize);
        pageBean.setList(list);

        //4.总页数
        int totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : (totalCount / pageSize)+1;
        pageBean.setTotalPage(totalPage);
        return pageBean;
    }
}

dao持久层编写

public class RouteDaoImpl implements RouteDao {
    private JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    /**
     * 通过旅游分类id查询
     * @param cid
     * @return
     */
    @Override
    public int finTotalCount(int cid) {
        String sql = "select count(*) from tab_route where cid=?";
        Integer integer = jdbcTemplate.queryForObject(sql, Integer.class, cid);
        return integer;
    }
    /**
     * 查询每页显示的集合
     * @param cid
     * @param start
     * @param pageSize
     * @return
     */
    @Override
    public List<Route> pageList(int cid, int start, int pageSize) {
        String sql = "select * from tab_route where cid= ? limit ?,?";
        List<Route> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Route>(Route.class), cid,start, pageSize);
        return query;
    }
}

运行maven项目测试

http://192.168.0.101/travel/route/pageQuery?currentPage=2&cid=5&pageSize=5

传入：
currentPage=2 ：当前页
&cid=5& ：旅游分类id
pageSize=5 :当前显示条数

测试前台返回的json数据

{"totalCount":513,"totalPage":103,"currentPage":2,"pageSize":5,"list":[{"rid":6,"rname":"【宁夏银川 双飞4天】 银川中华回乡文化园 中卫沙坡头 宁夏回族自治区博物馆 西关清真寺","price":1299.0,"routeIntroduce":"中卫升级1晚豪华酒店！走进【宁夏沙坡头】，感受西北大漠风情、体会“大漠孤烟直，长河落日圆”的塞上风光！","rflag":"1","rdate":"2018-02-09 01:13:17","isThemeTour":"0","count":0,"cid":5,"rimage":"img/product/small/m312cf97538027a813a4fb0bd6980a7fea.jpg","sid":1,"sourceId":"16968","category":null,"seller":null,"routeImgList":null},{"rid":7,"rname":"【¥1099秒杀 约惠华东五市+乌镇+木渎+灵山大佛 双飞6天 无锡进上海出】升级2晚豪华酒店","price":1099.0,"routeIntroduce":"畅玩乌镇水乡和木渎水乡，游览上海迪士尼小镇，其中升级入住2晚豪华酒店，品尝杭州特色宴“乾隆御茶宴”，送品尝金秋肥美大闸蟹一只！","rflag":"1","rdate":"2018-02-09 01:13:17","isThemeTour":"0","count":0,"cid":5,"rimage":"img/product/small/m370365f2ea91b638695218a6df13e3819.jpg","sid":1,"sourceId":"14974","category":null,"seller":null,"routeImgList":null},{"rid":8,"rname":"【¥1099秒杀 特惠华东五市+乌镇+木渎+灵山大佛 双飞6天 上海进杭州出】升级2晚豪华酒店","price":1099.0,"routeIntroduce":"升级两晚豪华酒店，畅游双水乡乌镇+木渎古镇，南京中山陵，杭州西湖！","rflag":"1","rdate":"2018-02-09 01:13:17","isThemeTour":"0","count":0,"cid":5,"rimage":"img/product/small/m30971b3d22ab37242636088780cfb8eb5.jpg","sid":1,"sourceId":"13580","category":null,"seller":null,"routeImgList":null},{"rid":9,"rname":"【豪叹喜来登 桂林 阳朔西街高铁4天纯玩 高级团】漓江 西山公园 象鼻山 银子岩","price":1299.0,"routeIntroduce":"1晚指定入住福朋喜来登酒店，船游全程大漓江，游览世界溶洞奇观银子岩","rflag":"1","rdate":"2018-02-09 01:13:17","isThemeTour":"0","count":0,"cid":5,"rimage":"img/product/small/m35c17b5b73d618bbdd2abe231f1307838.jpg","sid":1,"sourceId":"2702","category":null,"seller":null,"routeImgList":null},{"rid":10,"rname":"【春节 湖南郴州莽山+东江湖+冰雪森林温泉 高铁3天 顶贵团】欢迎年宴+温泉养生宴+农家菜【金狗闹郴晚·冰天雪地泡温泉】","price":2399.0,"routeIntroduce":"莽山景区入住“森林中的皇宫”之美誉的莽山森林温泉酒店；市区入住享“酒店就是景区”美誉的国际奢华品牌酒店---温德姆至尊豪庭酒店！","rflag":"1","rdate":"2018-02-09 01:13:17","isThemeTour":"0","count":0,"cid":5,"rimage":"img/product/small/m3c9823bc50368af0fe83eff14a5587c0a.jpg","sid":1,"sourceId":"20523","category":null,"seller":null,"routeImgList":null}]}

测试成功！