黑马旅游网分页查询分析(上)

需求:

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

结果如下:

代码编写

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

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

  1. 运行maven项目测试
http://192.168.0.101/travel/route/pageQuery?currentPage=2&cid=5&pageSize=5

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

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

测试成功!

 6 total views,  1 views today

页面下部广告