目录
一、先查旅游分类所有内容
点击了不同的分类后,将来看到的旅游线路不一样的。通过分析数据库表结构,发现,旅游线路表和分类表时一个多对一的关系
查询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);
//判断cid
int 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();
@Override
public 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());
@Override
public int queryCount(int cid, String rnameStr) {
//定义sql
String sql = "select count(*) from tab_route where 1=1";
StringBuffer sb = new StringBuffer(sql);
List params = new ArrayList();
//判断cid
if (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());
}
@Override
public 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();
//判断cid
if (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");
//解码url
if (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>¥</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>¥</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>¥<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>¥<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>¥<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>¥<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>¥<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>