一、先查旅游分类所有内容

点击了不同的分类后，将来看到的旅游线路不一样的。通过分析数据库表结构，发现，旅游线路表和分类表时一个多对一的关系

查询sql: select * from tab_route where cid=5

二、查询分页数据

查询: select * from 表 where 1=1 and cid=? and rname like ? limit ?,?

三、实现后端代码

RouteServlet

package com.hotdas.travel.web.servlet;import com.hotdas.travel.domain.PageBean;
import com.hotdas.travel.domain.Route;
import com.hotdas.travel.service.RouteService;
import com.hotdas.travel.service.impl.RouteServiceImpl;import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;@WebServlet("/route/*")
public class RouteServlet extends BaseServlet {private RouteService routeService = new RouteServiceImpl();public void queryPage(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException,Exception {//接收参数String currentPageStr = request.getParameter("currentPage");String cidStr = request.getParameter("cid");String pageSizeStr = request.getParameter("pageSize");System.out.println("cid="+cidStr);//有可能通过线路名称查询String rnameStr = request.getParameter("rname");System.out.println("rname="+rnameStr);//判断cidint cid =0;if(cidStr!=null && cidStr.length()>0 && !"null".equals(cidStr)){cid=Integer.parseInt(cidStr);}int currentPage=1;if(currentPageStr!=null && currentPageStr.length()>0){currentPage=Integer.parseInt(currentPageStr);}else{currentPage=1;}int pageSize = 0;if(pageSizeStr!=null && pageSizeStr.length()>0){pageSize=Integer.parseInt(pageSizeStr);}else{pageSize=5;}//调用service查询数据PageBean<Route> pageBean = routeService.queryPage(cid,currentPage,pageSize,rnameStr);writeValue(pageBean,response);}
}

RouteServiceImpl

package com.hotdas.travel.service.impl;import com.hotdas.travel.dao.RouteDao;
import com.hotdas.travel.dao.impl.RouteDaoImpl;
import com.hotdas.travel.domain.PageBean;
import com.hotdas.travel.domain.Route;
import com.hotdas.travel.service.RouteService;import java.util.List;public class RouteServiceImpl implements RouteService {private RouteDao routeDao = new RouteDaoImpl();@Overridepublic PageBean<Route> queryPage(int cid, int currentPage, int pageSize, String rnameStr) {PageBean<Route> pageBean =  new PageBean<>();pageBean.setCurrentPage(currentPage);pageBean.setPageSize(pageSize);//每页的记录集//先求出开始的索引int start = (currentPage-1)*pageSize;List<Route> list = routeDao.queryPageList(cid,start,pageSize,rnameStr);pageBean.setList(list);//总记录数int totalCount = routeDao.queryCount(cid,rnameStr);pageBean.setTotalCount(totalCount);//求出总页数int pageCount = totalCount% pageSize ==0 ? totalCount/pageSize : (totalCount/pageSize)+1;pageBean.setTotalPage(pageCount);return pageBean;}
}

RouteService

package com.hotdas.travel.service;import com.hotdas.travel.domain.PageBean;
import com.hotdas.travel.domain.Route;public interface RouteService {PageBean<Route> queryPage(int cid, int currentPage, int pageSize, String rnameStr);
}

RouteDaoImpl

package com.hotdas.travel.dao.impl;import com.hotdas.travel.dao.RouteDao;
import com.hotdas.travel.domain.Route;
import com.hotdas.travel.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;import java.util.ArrayList;
import java.util.List;public class RouteDaoImpl implements RouteDao {private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());@Overridepublic int queryCount(int cid, String rnameStr) {//定义sqlString sql = "select count(*) from tab_route where 1=1";StringBuffer sb = new StringBuffer(sql);List params = new ArrayList();//判断cidif (cid!=0){sb.append(" and cid=? ");params.add(cid);//cid对应的值}//判断是否通过rname查询if (rnameStr!=null && rnameStr.length()>0){sb.append(" and rname like ? ");params.add("%"+rnameStr+"%");}sql = sb.toString();//执行sql返回return template.queryForObject(sql,Integer.class,params.toArray());}@Overridepublic List<Route> queryPageList(int cid, int start, int pageSize, String rnameStr) {//select * from tab_route where 1=1 cid=? and rname like ? limit ?,?;String sql = "select * from tab_route where 1=1 ";StringBuffer sb = new StringBuffer(sql);List params = new ArrayList();//判断cidif (cid!=0){sb.append(" and cid=? ");params.add(cid);//cid对应的值}//判断是否通过rname查询if (rnameStr!=null && rnameStr.length()>0){sb.append(" and rname like ? ");params.add("%"+rnameStr+"%");}//分页参数sb.append(" limit ?,? ");params.add(start);params.add(pageSize);sql=sb.toString();return template.query(sql,new BeanPropertyRowMapper<Route>(Route.class),params.toArray());}
}

RouteDao

package com.hotdas.travel.dao;import com.hotdas.travel.domain.Route;import java.util.List;public interface RouteDao {int queryCount(int cid, String rnameStr);List<Route> queryPageList(int cid, int start, int pageSize, String rnameStr);
}

PageBean

package com.hotdas.travel.domain;import java.util.List;public class PageBean<T> {private int totalCount;//总记录数private int totalPage;//总页数private int pageSize;//每页显示记录数private int currentPage; //当前页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 getPageSize() {return pageSize;}public void setPageSize(int pageSize) {this.pageSize = pageSize;}public int getCurrentPage() {return currentPage;}public void setCurrentPage(int currentPage) {this.currentPage = currentPage;}public List<T> getList() {return list;}public void setList(List<T> list) {this.list = list;}
}

四、分页与数据展示

修改route_list.html

<!DOCTYPE html>
<html lang="en"><head><meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0"><meta http-equiv="X-UA-Compatible" content="ie=edge"><title>卓越旅游-搜索</title><link rel="stylesheet" type="text/css" href="css/common.css"><link rel="stylesheet" href="css/search.css"><script src="js/jquery-3.3.1.js"></script><script src="js/getParameter.js"></script><script>$(function () {var cid=getParameter("cid");var rname =getParameter("rname");//解码urlif (rname){rname =  window.decodeURIComponent(rname);}//通过ajax访问servlet获取数据库的数据//为了方便记录当前页load(cid,null,rname);});//通过ajax访问servlet获取数据库的数据function load(cid,currentPage,rname) {$.get("route/queryPage",{cid:cid,currentPage:currentPage,rname:rname},function (pb) {//pb其实是pageBean的json数据,需要解释到页面中$("#totalCount").html(pb.totalCount);$("#totalPage").html(pb.totalPage);//  <li><a href="">首页</a></li>// <li class="threeword"><a href="#">上一页</a></li>// <li><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 firstPage='<li><a onclick="javascript:load('+cid+',1,'+rname+');" href="javascript:void(0)">首页</a></li>';//计算上一页var beforNum = pb.currentPage-1;if (beforNum<=0){beforNum=1;}var secondePage='<li class="threeword"><a onclick="javascript:load('+cid+',beforNum,'+rname+');" href="javascript:void(0)">上一页</a></li>';lis+=firstPage;lis+=secondePage;var begin;//开始位置var end;//结束位置if (pb.totalPage<10){begin=1;end=pb.totalPage;}else{//总页数超过10页begin=pb.currentPage-5;end=pb.currentPage+4;//如果前边不够5个,后面补够10个if(begin<1){begin=1;end=begin+9;}//如果右边不够4条,前面补够10个if (end>pb.totalPage){begin=pb.totalPage-9;end=pb.totalPage;}}for(var i=begin;i<=end;i++){//判断是否是当前页if(pb.currentPage==i){li='<li class="curPage"><a onclick="javascript:load('+cid+','+i+','+rname+');" href="javascript:void(0)">'+i+'</a></li>';}else{li='<li><a onclick="javascript:load('+cid+','+i+','+rname+');" href="javascript:void(0)">'+i+'</a></li>';}//把遍历的内容拼串lis+=li;}//下一页var nextPage ='<li class="threeword"><a onclick="javascript:load('+cid+',pb.currentPage+1,'+rname+');" href="javascript:void(0)">下一页</a></li>';var lastPage ='<li><a onclick="javascript:load('+cid+',pb.totalPage,'+rname+');" href="javascript:void(0)">首页</a></li>';lis+=nextPage;lis+=lastPage;$("#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>&yen;</span>//     <span>1199</span>//     <span>起</span>//     </p>//     <p><a href="route_detail.html">查看详情</a></p>//     </div>//     </li>//列表的数据展示var routelis = "";for(var i=0;i<pb.list.length;i++){//{cid=1,rname=xxx}var route = pb.list[i];var li='<li>'+'<div class="img"><img src="'+route.rimage+'" alt=""></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>&yen;</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>';routelis+=li;}$("#route").html(routelis);});}</script>
</head>
<body>
<!--引入头部-->
<div id="header"></div><div class="page_one"><div class="contant"><div class="crumbs"><img src="images/search.png" alt=""><p>卓越旅行><span>搜索结果</span></p></div><div class="xinxi clearfix"><div class="left"><div class="header"><span>商品信息</span><span class="jg">价格</span></div><ul id="route"></ul><div class="page_num_inf"><i></i> 共<span id="totalPage"></span>页<span id="totalCount"></span>条</div><div class="pageNum"><ul id="pageNum"></ul></div></div><div class="right"><div class="top"><div class="hot">HOT</div><span>热门推荐</span></div><ul><li><div class="left"><img src="images/04-search_09.jpg" alt=""></div><div class="right"><p>清远新银盏温泉度假村酒店/自由行套...</p><p>网付价<span>&yen;<span>899</span>起</span></p></div></li><li><div class="left"><img src="images/04-search_09.jpg" alt=""></div><div class="right"><p>清远新银盏温泉度假村酒店/自由行套...</p><p>网付价<span>&yen;<span>899</span>起</span></p></div></li><li><div class="left"><img src="images/04-search_09.jpg" alt=""></div><div class="right"><p>清远新银盏温泉度假村酒店/自由行套...</p><p>网付价<span>&yen;<span>899</span>起</span></p></div></li><li><div class="left"><img src="images/04-search_09.jpg" alt=""></div><div class="right"><p>清远新银盏温泉度假村酒店/自由行套...</p><p>网付价<span>&yen;<span>899</span>起</span></p></div></li><li><div class="left"><img src="images/04-search_09.jpg" alt=""></div><div class="right"><p>清远新银盏温泉度假村酒店/自由行套...</p><p>网付价<span>&yen;<span>899</span>起</span></p></div></li></ul></div></div></div></div><!--引入头部--><div id="footer"></div><!--导入布局js，共享header和footer--><script type="text/javascript" src="js/include.js"></script>
</body></html>

修改header.html

<!-- 头部 start --><script>$(function () {$.post("user/findUser",{},function (data) {if (data.name!="undefined" && data.name!=null){var msg = "欢迎回来，"+data.name;$("#span_username").html(msg);$(".login").css("display","block");}else{$(".login").css("display","none");}});//通过ajax获取分类数据$.get("category/findAll",{},function (data) {var lis = '<li class="nav-active"><a href="index.html">首页</a></li>';//数据遍历for(var i=0;i<data.length;i++){var li = '<li><a href="route_list.html?cid='+data[i].cid+'">'+data[i].cname+'</a></li>';lis +=li;}lis+='<li><a href="favoriterank.html">收藏排行榜</a></li>';//往ul标签下插入html代码块$("#category").html(lis);});});</script><header id="header"><div class="top_banner"><img src="images/top_banner.jpg" alt=""></div><div class="shortcut"><!-- 未登录状态  --><div class="login_out"><a href="login.html">登录</a><a href="register.html">注册</a></div><!-- 登录状态  --><div class="login" style="display:none;"><span id="span_username">欢迎回来，admin</span><a href="myfavorite.html" class="collection">我的收藏</a><a href="javascript:location.href='user/exit';">退出</a></div></div><div class="header_wrap"><div class="topbar"><div class="logo"><a href="/"><img src="images/logo.jpg" alt=""></a></div><div class="search"><input name="rname" type="text" placeholder="请输入路线名称" class="search_input" autocomplete="off"><a href="javascript:;" class="search-button">搜索</a></div><div class="hottel"><div class="hot_pic"><img src="images/hot_tel.jpg" alt=""></div><div class="hot_tel"><p class="hot_time">客服热线(9:00-6:00)</p><p class="hot_num">400-618-9090</p></div></div></div></div></header><!-- 头部 end --><!-- 首页导航 --><div class="navitem"><ul id="category" class="nav">
<!--            <li class="nav-active"><a href="index.html">首页</a></li>-->
<!--            <li><a href="route_list.html">门票</a></li>-->
<!--            <li><a href="route_list.html">酒店</a></li>-->
<!--            <li><a href="route_list.html">香港车票</a></li>-->
<!--            <li><a href="route_list.html">出境游</a></li>-->
<!--            <li><a href="route_list.html">国内游</a></li>-->
<!--            <li><a href="route_list.html">港澳游</a></li>-->
<!--            <li><a href="route_list.html">抱团定制</a></li>-->
<!--            <li><a href="route_list.html">全球自由行</a></li>-->
<!--            <li><a href="favoriterank.html">收藏排行榜</a></li>--></ul></div>