根据数据库查询并显示在html

根据数据库查询并显示在html

Route实体类：

private int rid;//线路id，必输 private String rname;//线路名称，必输 private double price;//价格，必输 private String routeIntroduce;//线路介绍 private String rflag; //是否上架，必输，0代表没有上架，1代表是上架 private String rdate; //上架时间 private String isThemeTour;//是否主题旅游，必输，0代表不是，1代表是 private int count;//收藏数量 private int cid;//所属分类，必输 private String rimage;//缩略图 private int sid;//所属商家 private String sourceId;//抓取数据的来源id private String cname;//分类名称 private String sname;//商家名称 private String consphone;//商家电话 private String address;//商家地址

DAO中的查询函数。这里因为Route连接两个外键，所以加两个inner join语句。每次写的时候一定要在navicat中试过。不然基本都错 sping中的RowMapper可以将数据中的每一行数据封装成用户定义的类。

public List<Route> selectByOrderKey(int x)throws SQLException{ List<Route> routes = new ArrayList<>(); String sql = null; if(x==1)//人气 { sql = " select * from (tab_route INNER JOIN tab_seller ON tab_route.sid = tab_seller.sid) " +"INNER JOIN tab_category ON tab_route.cid = tab_category.cid order by count DESC" ;}//人气 if(x==2)//日期 { sql = "select * from (tab_route INNER JOIN tab_seller ON tab_route.sid = tab_seller.sid) " + " INNER JOIN tab_category ON tab_route.cid = tab_category.cid order by rdate DESC ";}//日期 if(x==3)//主题 { sql = "SELECT * FROM (tab_route INNER JOIN tab_seller ON tab_route.sid = tab_seller.sid) " +" INNER JOIN tab_category ON tab_route.cid = tab_category.cid " +" Where tab_route.isThemeTour = 1 ";}//主题 RowMapper<Route> rowMapper = new BeanPropertyRowMapper<Route>(Route.class);//都这么写 routes = template.query(sql, rowMapper ); return routes; } //根据类型分类查询路线对象(国内5 国外4) public List<Route> selectByRouteCid(int cid)throws SQLException{ List<Route> routes = new ArrayList<>(); String sql=" SELECT * FROM (tab_route INNER JOIN tab_seller ON tab_route.sid = tab_seller.sid) " +" INNER JOIN tab_category ON tab_route.cid = tab_category.cid " +" Where tab_route.cid = ? "; RowMapper<Route> rowMapper = new BeanPropertyRowMapper<Route>(Route.class); routes = template.query(sql, rowMapper, cid ); return routes; }

人气旅游 主题旅游 最新旅游的Service类

public class RouteService { private RouteDao routeDao = new RouteDao(); public List<Route> selectByRouteCid(int cid) throws SQLException{ return this.routeDao.selectByRouteCid(cid); } public List <Route> selectByOrderKey(int x)throws SQLException{ return this.routeDao.selectByOrderKey(x); } } @WebServlet("/SelectKind") public class SelectKind extends HttpServlet { protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String s=request.getParameter("routeKand"); RouteService routeService=new RouteService(); List<Route> routeList=null; System.out.println(s); if(s.equals("1")){ try { routeList=routeService.selectByOrderKey(1); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } for (Route route : routeList) { System.out.println(route); } }else if(s.equals("2")){ try { routeList=routeService.selectByOrderKey(2); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } for (Route route : routeList) { System.out.println(route); } }else{ try { routeList=routeService.selectByOrderKey(3); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } ObjectMapper mapper = new ObjectMapper(); String json = mapper.writeValueAsString(routeList); response.setContentType("application/json;charset=utf-8"); response.getWriter().write(json); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { this.doPost(request, response); } }

境内游 境外游的Service类

@WebServlet("/SelectIO") public class SelectIO extends HttpServlet { protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String s=request.getParameter("routeIO");//html的value传值 RouteService routeService=new RouteService(); List<Route> routeList=null; System.out.println(s); if(s.equals("1")){ try { routeList=routeService.selectByRouteCid(5); } catch (SQLException e) { e.printStackTrace(); }//国内 for (Route route : routeList) { System.out.println(route); } }else if(s.equals("2")){ try { routeList=routeService.selectByRouteCid(4); } catch (SQLException e) { e.printStackTrace(); }//国外 for (Route route : routeList) { System.out.println(route); } } ObjectMapper mapper = new ObjectMapper(); String json = mapper.writeValueAsString(routeList); response.setContentType("application/json;charset=utf-8"); response.getWriter().write(json); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { this.doPost(request, response); } }

html中的相关代码

<form id="selectForm" > //表单 <input type="hidden" name="routeKand" id="routeKand" value="1"> </form> <form id="selectIOForm" > <input type="hidden" name="routeIO" id="routeIO" value="1"> </form> $(function (){ $("#routeKand").attr('value',1); $.post("SelectKind",$("#selectForm").serialize(),function(data){ for(var i=1;i<=data.length;i++){ var s1="XpopularityPrice"; var s2="XpopularityRname"; var s3="XpopularityImg"; s1="#"+s1+i; s2="#"+s2+i; s3="#"+s3+i; $(s1).html(data[i-1].price); $(s2).html(data[i-1].routeIntroduce); $(s3).attr('src',data[i-1].rimage); <!--页面跳转--> var s4="XpopularityRid"; s4="#"+s4+i; var stress="route_detail.html?values="+data[i-1].rid; $(s4).attr('href',stress); } }); <!--国内--> $("#routeIO").attr('value',1); $.post("SelectIO",$("#selectIOForm").serialize(),function(data){ <!--图片--> $("#guonei1").attr('src',data[0].rimage); $("#guonei2").attr('src',data[1].rimage); $("#guonei3").attr('src',data[2].rimage); $("#guonei4").attr('src',data[3].rimage); $("#guonei5").attr('src',data[4].rimage); $("#guonei6").attr('src',data[5].rimage); $("#guonei1name").html(data[0].routeIntroduce); $("#guonei2name").html(data[1].routeIntroduce); $("#guonei3name").html(data[2].routeIntroduce); $("#guonei4name").html(data[3].routeIntroduce); $("#guonei5name").html(data[4].routeIntroduce); $("#guonei6name").html(data[5].routeIntroduce); $("#guonei1price").html(data[0].price); $("#guonei2price").html(data[1].price); $("#guonei3price").html(data[2].price); $("#guonei4price").html(data[3].price); $("#guonei5price").html(data[4].price); $("#guonei6price").html(data[5].price); }); $("#routeIO").attr('value',2); $.post("SelectIO",$("#selectIOForm").serialize(),function(data){ $("#guowai1").attr('src',data[0].rimage); $("#guowai2").attr('src',data[1].rimage); $("#guowai3").attr('src',data[2].rimage); $("#guowai4").attr('src',data[3].rimage); $("#guowai5").attr('src',data[4].rimage); $("#guowai6").attr('src',data[5].rimage); $("#guowai1name").html(data[0].rname); $("#guowai2name").html(data[1].rname); $("#guowai3name").html(data[2].rname); $("#guowai4name").html(data[3].rname); $("#guowai5name").html(data[4].rname); $("#guowai6name").html(data[5].rname); $("#guowai1price").html(data[0].price); $("#guowai2price").html(data[1].price); $("#guowai3price").html(data[2].price); $("#guowai4price").html(data[3].price); $("#guowai5price").html(data[4].price); $("#guowai6price").html(data[5].price); }); }); $(function (){ $("#Xpopularity").click(function(){ $("#routeKand").attr('value',1); $.post("SelectKind",$("#selectForm").serialize(),function(data){ for(var i=1;i<=data.length;i++){ var s1="XpopularityPrice"; var s2="XpopularityRname"; var s3="XpopularityImg"; s1="#"+s1+i; s2="#"+s2+i; s3="#"+s3+i; $(s1).html(data[i-1].price); $(s2).html(data[i-1].routeIntroduce); $(s3).attr('src',data[i-1].rimage); <!--页面跳转--> var s4="XpopularityRid"; s4="#"+s4+i; var stress="route_detail.html?values="+data[i-1].rid; $(s4).attr('href',stress); } }); }); }); $(function (){ $("#Xnewest").click(function(){ $("#routeKand").attr('value',2); $.post("SelectKind",$("#selectForm").serialize(),function(data){ for(var i=1;i<=data.length;i++){ var s1="XnewestPrice"; var s2="XnewestRname"; var s3="XnewestImg"; s1="#"+s1+i; s2="#"+s2+i; s3="#"+s3+i; $(s1).html(data[i-1].price); $(s2).html(data[i-1].routeIntroduce); $(s3).attr('src',data[i-1].rimage); } }); }); }); $(function (){ $("#Xtheme").click(function(){ $("#routeKand").attr('value',3); $.post("SelectKind",$("#selectForm").serialize(),function(data){ for(var i=1;i<=data.length;i++){ var s1="XthemePrice"; var s2="XthemeRname"; var s3="XthemeImg"; s1="#"+s1+i; s2="#"+s2+i; s3="#"+s3+i; $(s1).html(data[i-1].price); $(s2).html(data[i-1].routeIntroduce); $(s3).attr('src',data[i-1].rimage); } }); }); });