旅游网小案例:旅游线路名称查询

分析

对原用的分页查询进行修改,添加rname字段,对原有的方法进行修改.

• Servlet

/**
     * 分页查询
     *
     * @param request
     * @param response
     * @throws ServletException
     * @throws IOException
     */
    public void pageQuery(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //接收参数
        String currentPageStr = request.getParameter("currentPage");
        String pageSizeStr = request.getParameter("pageSize");
        String cidStr = request.getParameter("cid");
        //接收rname
        String rname = request.getParameter("rname");
        //转码
        rname = new String(rname.getBytes("iso-8859-1"), "utf-8");
        //处理参数,并设置默认值
        int cid = 0;
        if (cidStr != null && cidStr.length() > 0 && !"null".equals(cidStr)) {
            cid = Integer.parseInt(cidStr);
        }
        
        int pageSize = 5;
        if (pageSizeStr != null && pageSizeStr.length() > 0) {
            pageSize = Integer.parseInt(pageSizeStr);
        }

        int currentPage = 1;
        if (currentPageStr != null && currentPageStr.length() > 0) {
            currentPage = Integer.parseInt(currentPageStr);
        }
        //调用Service
        PageBean<Route> routePageBean = routeService.pageQuery(cid, currentPage, pageSize, rname);
        writeValue(routePageBean, response);

    }

获取rname数据,转换编码为UTF-8.预防恶意访问,进行数据的默认值设置.
存在漏洞:cid必须是数字,需要进行数字校验.

• Service

@Override
    public PageBean<Route> pageQuery(int cid, int currentPage, int pageSize, String rname) {
        //创建PageBean
        PageBean<Route> pb = new PageBean<>();
        //注入当前页码
        pb.setCurrentPage(currentPage);
        //注入每页显示条数
        pb.setPageSize(pageSize);
        //查询总记录数
        int totalCount = routeDao.findTotalCount(cid, rname);
        //注入总记录数
        pb.setTotalCount(totalCount);
        //计算开始索引
        int start = (currentPage - 1) * pageSize;
        //查询集合数据
        List<Route> list = routeDao.findByPage(cid, start, pageSize, rname);
        //注入显示数据
        pb.setList(list);
        //计算总页码
        int totalPage = totalCount % pageSize != 0 ? (totalCount / pageSize) + 1 : totalCount / pageSize;
        //注入总页码
        pb.setTotalPage(totalPage);
        //返回PageBean
        return pb;
    }

对PageBean进行数据装配,计算数据并调用dao方法进行获取数据.注入PageBean中返回

• Dao

@Override
    public Integer findTotalCount(int cid, String rname) {
        //        String sql = "select count(*) from tab_route where cid=? and rname like ?";
        String sql = "select count(*) from tab_route where 1=1 ";
        StringBuilder sb = new StringBuilder(sql);
        List params = new ArrayList<>();
        if (cid != 0) {
            sb.append(" and cid=? ");
            params.add(cid);
        }
        if (rname != null && !"".equals(rname) && !"null".equals(rname)) {
            sb.append(" and rname like ?");
            params.add("%" + rname + "%");
        }
        sql = sb.toString();
        return template.queryForObject(sql, Integer.class, params.toArray());
    }

    @Override
    public List<Route> findByPage(int cid, int start, int pageSize, String rname) {
//        String sql = "select * from tab_route where cid=? limit ?,?  ";
        String sql = "select * from tab_route where 1=1 ";
        StringBuilder sb = new StringBuilder(sql);
        List params = new ArrayList();
        if (cid != 0) {
            sb.append(" and cid=? ");
            params.add(cid);
        }
        if (rname != null && !"".equals(rname) && !"null".equals(rname)) {
            sb.append(" and rname like ? ");
            params.add("%" + rname + "%");
        }
        sb.append(" limit ?,? ");
        params.add(start);
        params.add(pageSize);
        sql = sb.toString();
        return template.query(sql, new BeanPropertyRowMapper<>(Route.class), params.toArray());
    }

根据传入的数据,进行判断拼接,进行查询.原因:(因为存在没有搜索rname和分类cid的查询)