黑马旅游网分页查询分析

需求：

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

分析

分析数据库

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

后台模块设计分析：

前台旅游分类：

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

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

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

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

1. int totalCount：总记录数
2. int totalPage：总页数
3. int currentPage：当前页码
4. int pageSize：当前页显示的条数
5. List list：当前页显示的数据集合

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

select  count(*) from tab_route where cid = ?

2）totalPage ：通过计算公式

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

3. 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;

结果如下：

代码编写

1. 准备好分页对象的实体类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;
    }

2. 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);
    }

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

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

5. 运行maven项目测试

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

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

6. 测试前台返回的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}]}

测试成功！