前台开发----热销商品品通过连接数据库显示在热销推荐
创建IndexServlet处理首页请求
index.html效果
1 <!DOCTYPE html> 2 <html> 3 <head> 4 <title>商品列表</title> 5 <meta name="viewport" content="width=device-width, initial-scale=1"> 6 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> 7 <link type="text/css" rel="stylesheet" href="css/bootstrap.css"> 8 <link type="text/css" rel="stylesheet" href="css/style.css"> 9 <script type="text/javascript" src="js/jquery.min.js"></script> 10 <script type="text/javascript" src="js/bootstrap.min.js"></script> 11 <script type="text/javascript" src="layer/layer.js"></script> 12 <script type="text/javascript" src="js/cart.js"></script> 13 </head> 14 <body> 15 16 17 18 19 20 21 22 <!--header--> 23 <div class="header"> 24 <div class="container"> 25 <nav class="navbar navbar-default" role="navigation"> 26 <div class="navbar-header"> 27 <button type="button" class="navbar-toggle" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1"> 28 <span class="sr-only">Toggle navigation</span> 29 <span class="icon-bar"></span> 30 <span class="icon-bar"></span> 31 <span class="icon-bar"></span> 32 </button> 33 <h1 class="navbar-brand"><a href="index.action">蛋糕店</a></h1> 34 </div> 35 <!--navbar-header--> 36 <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1"> 37 <ul class="nav navbar-nav"> 38 <li><a href="index.action" class="active">首页</a></li> 39 <li class="dropdown"> 40 <a href="#" class="dropdown-toggle " data-toggle="dropdown">商品分类<b class="caret"></b></a> 41 <ul class="dropdown-menu multi-column columns-2"> 42 <li> 43 <div class="row"> 44 <div class="col-sm-12"> 45 <h4>商品分类</h4> 46 <ul class="multi-column-dropdown"> 47 48 <li><a class="list" href="goods.action?typeid=5">经典系列</a></li> 49 50 <li><a class="list" href="goods.action?typeid=4">法式系列</a></li> 51 52 <li><a class="list" href="goods.action?typeid=3">儿童系列</a></li> 53 54 <li><a class="list" href="goods.action?typeid=2">零食系列</a></li> 55 56 <li><a class="list" href="goods.action?typeid=1">冰淇淋系列</a></li> 57 58 </ul> 59 </div> 60 </div> 61 </li> 62 </ul> 63 </li> 64 <li><a href="top.action?typeid=2" >热销</a></li> 65 <li><a href="top.action?typeid=3" >新品</a></li> 66 67 <li><a href="register.action?flag=-1" >注册</a></li> 68 <li><a href="login.action?flag=-1" >登录</a></li> 69 70 71 <li><a href="../admin.jsp" target="_blank">后台管理</a></li> 72 </ul> 73 <!--/.navbar-collapse--> 74 </div> 75 <!--//navbar-header--> 76 </nav> 77 <div class="header-info"> 78 <div class="header-right search-box"> 79 <a href="javascript:;"><span class="glyphicon glyphicon-search" aria-hidden="true"></span></a> 80 <div class="search"> 81 <form class="navbar-form" action="search.action"> 82 <input type="text" class="form-control" name="name"> 83 <button type="submit" class="btn btn-default" aria-label="Left Align">搜索</button> 84 </form> 85 </div> 86 </div> 87 88 <div class="header-right cart"> 89 <a href="cart.action"> 90 <span class="glyphicon glyphicon-shopping-cart" aria-hidden="true"><span class="card_num">0</span></span> 91 </a> 92 </div> 93 <div class="header-right login"> 94 <a href="my.action"><span class="glyphicon" aria-hidden="true"></span></a> 95 </div> 96 <div class="clearfix"> </div> 97 </div> 98 <div class="clearfix"> </div> 99 </div> 100 </div> 101 <!--//header--> 102 103 104 <!--banner--> 105 106 <div class="banner"> 107 <div class="container"> 108 <h2 class="hdng"><a href="detail.action?goodid=7">爱之玫瑰</a><span></span></h2> 109 <p>今日精选推荐</p> 110 <a class="banner_a" href="javascript:;" onclick="buy(7)">立刻购买</a> 111 <div class="banner-text"> 112 <a href="detail.action?goodid=7"> 113 <img src="picture/7-1.jpg" alt="爱之玫瑰" width="350" height="350"> 114 </a> 115 </div> 116 </div> 117 </div> 118 119 <!--//banner--> 120 121 <div class="subscribe2"></div> 122 123 <!--gallery--> 124 <div class="gallery"> 125 <div class="container"> 126 <div class="alert alert-danger">热销推荐</div> 127 <div class="gallery-grids"> 128 129 <div class="col-md-4 gallery-grid glry-two"> 130 <a href="detail.action?goodid=6"> 131 <img src="picture/6-1.jpg" class="img-responsive" alt="夜礼服" width="350" height="350"/> 132 </a> 133 <div class="gallery-info galrr-info-two"> 134 <p> 135 <span class="glyphicon glyphicon-eye-open" aria-hidden="true"></span> 136 <a href="detail.action?goodid=6">查看详情</a> 137 </p> 138 <a class="shop" href="javascript:;" onclick="buy(6)">立刻购买</a> 139 <div class="clearfix"> </div> 140 </div> 141 <div class="galy-info"> 142 <p>经典系列 > 夜礼服</p> 143 <div class="galry"> 144 <div class="prices"> 145 <h5 class="item_price">¥ 299</h5> 146 </div> 147 <div class="clearfix"></div> 148 </div> 149 </div> 150 </div> 151 152 <div class="col-md-4 gallery-grid glry-two"> 153 <a href="detail.action?goodid=9"> 154 <img src="picture/9-1.jpg" class="img-responsive" alt="草莓冰淇淋" width="350" height="350"/> 155 </a> 156 <div class="gallery-info galrr-info-two"> 157 <p> 158 <span class="glyphicon glyphicon-eye-open" aria-hidden="true"></span> 159 <a href="detail.action?goodid=9">查看详情</a> 160 </p> 161 <a class="shop" href="javascript:;" onclick="buy(9)">立刻购买</a> 162 <div class="clearfix"> </div> 163 </div> 164 <div class="galy-info"> 165 <p>冰淇淋系列 > 草莓冰淇淋</p> 166 <div class="galry"> 167 <div class="prices"> 168 <h5 class="item_price">¥ 299</h5> 169 </div> 170 <div class="clearfix"></div> 171 </div> 172 </div> 173 </div> 174 175 <div class="col-md-4 gallery-grid glry-two"> 176 <a href="detail.action?goodid=3"> 177 <img src="picture/3-1.jpg" class="img-responsive" alt="留恋之恋" width="350" height="350"/> 178 </a> 179 <div class="gallery-info galrr-info-two"> 180 <p> 181 <span class="glyphicon glyphicon-eye-open" aria-hidden="true"></span> 182 <a href="detail.action?goodid=3">查看详情</a> 183 </p> 184 <a class="shop" href="javascript:;" onclick="buy(3)">立刻购买</a> 185 <div class="clearfix"> </div> 186 </div> 187 <div class="galy-info"> 188 <p>经典系列 > 留恋之恋</p> 189 <div class="galry"> 190 <div class="prices"> 191 <h5 class="item_price">¥ 229</h5> 192 </div> 193 <div class="clearfix"></div> 194 </div> 195 </div> 196 </div> 197 198 <div class="col-md-4 gallery-grid glry-two"> 199 <a href="detail.action?goodid=5"> 200 <img src="picture/5-1.jpg" class="img-responsive" alt="芒果列车" width="350" height="350"/> 201 </a> 202 <div class="gallery-info galrr-info-two"> 203 <p> 204 <span class="glyphicon glyphicon-eye-open" aria-hidden="true"></span> 205 <a href="detail.action?goodid=5">查看详情</a> 206 </p> 207 <a class="shop" href="javascript:;" onclick="buy(5)">立刻购买</a> 208 <div class="clearfix"> </div> 209 </div> 210 <div class="galy-info"> 211 <p>经典系列 > 芒果列车</p> 212 <div class="galry"> 213 <div class="prices"> 214 <h5 class="item_price">¥ 269</h5> 215 </div> 216 <div class="clearfix"></div> 217 </div> 218 </div> 219 </div> 220 221 <div class="col-md-4 gallery-grid glry-two"> 222 <a href="detail.action?goodid=2"> 223 <img src="picture/2-1.jpg" class="img-responsive" alt="浪漫甜心" width="350" height="350"/> 224 </a> 225 <div class="gallery-info galrr-info-two"> 226 <p> 227 <span class="glyphicon glyphicon-eye-open" aria-hidden="true"></span> 228 <a href="detail.action?goodid=2">查看详情</a> 229 </p> 230 <a class="shop" href="javascript:;" onclick="buy(2)">立刻购买</a> 231 <div class="clearfix"> </div> 232 </div> 233 <div class="galy-info"> 234 <p>经典系列 > 浪漫甜心</p> 235 <div class="galry"> 236 <div class="prices"> 237 <h5 class="item_price">¥ 229</h5> 238 </div> 239 <div class="clearfix"></div> 240 </div> 241 </div> 242 </div> 243 244 <div class="col-md-4 gallery-grid glry-two"> 245 <a href="detail.action?goodid=1"> 246 <img src="picture/1-1.jpg" class="img-responsive" alt="玫瑰花园" width="350" height="350"/> 247 </a> 248 <div class="gallery-info galrr-info-two"> 249 <p> 250 <span class="glyphicon glyphicon-eye-open" aria-hidden="true"></span> 251 <a href="detail.action?goodid=1">查看详情</a> 252 </p> 253 <a class="shop" href="javascript:;" onclick="buy(1)">立刻购买</a> 254 <div class="clearfix"> </div> 255 </div> 256 <div class="galy-info"> 257 <p>经典系列 > 玫瑰花园</p> 258 <div class="galry"> 259 <div class="prices"> 260 <h5 class="item_price">¥ 229</h5> 261 </div> 262 <div class="clearfix"></div> 263 </div> 264 </div> 265 </div> 266 267 </div> 268 269 <div class="clearfix"></div> 270 <div class="alert alert-info">新品推荐</div> 271 <div class="gallery-grids"> 272 273 <div class="col-md-3 gallery-grid "> 274 <a href="detail.action?goodid=14"> 275 <img src="picture/14-1.jpg" class="img-responsive" alt="意大利芝士饼干"/> 276 </a> 277 <div class="gallery-info"> 278 <p> 279 <span class="glyphicon glyphicon-eye-open" aria-hidden="true"></span> 280 <a href="detail.action?goodid=14">查看详情</a> 281 </p> 282 <a class="shop" href="javascript:;" onclick="buy(14)">立刻购买</a> 283 <div class="clearfix"> </div> 284 </div> 285 <div class="galy-info"> 286 <p>零食系列 > 意大利芝士饼干</p> 287 <div class="galry"> 288 <div class="prices"> 289 <h5 class="item_price">¥ 39</h5> 290 </div> 291 <div class="clearfix"></div> 292 </div> 293 </div> 294 </div> 295 296 <div class="col-md-3 gallery-grid "> 297 <a href="detail.action?goodid=13"> 298 <img src="picture/13-1.jpg" class="img-responsive" alt="蜂蜜蛋糕"/> 299 </a> 300 <div class="gallery-info"> 301 <p> 302 <span class="glyphicon glyphicon-eye-open" aria-hidden="true"></span> 303 <a href="detail.action?goodid=13">查看详情</a> 304 </p> 305 <a class="shop" href="javascript:;" onclick="buy(13)">立刻购买</a> 306 <div class="clearfix"> </div> 307 </div> 308 <div class="galy-info"> 309 <p>零食系列 > 蜂蜜蛋糕</p> 310 <div class="galry"> 311 <div class="prices"> 312 <h5 class="item_price">¥ 36</h5> 313 </div> 314 <div class="clearfix"></div> 315 </div> 316 </div> 317 </div> 318 319 <div class="col-md-3 gallery-grid "> 320 <a href="detail.action?goodid=12"> 321 <img src="picture/12-1.jpg" class="img-responsive" alt="青森芝士条"/> 322 </a> 323 <div class="gallery-info"> 324 <p> 325 <span class="glyphicon glyphicon-eye-open" aria-hidden="true"></span> 326 <a href="detail.action?goodid=12">查看详情</a> 327 </p> 328 <a class="shop" href="javascript:;" onclick="buy(12)">立刻购买</a> 329 <div class="clearfix"> </div> 330 </div> 331 <div class="galy-info"> 332 <p>零食系列 > 青森芝士条</p> 333 <div class="galry"> 334 <div class="prices"> 335 <h5 class="item_price">¥ 36</h5> 336 </div> 337 <div class="clearfix"></div> 338 </div> 339 </div> 340 </div> 341 342 <div class="col-md-3 gallery-grid "> 343 <a href="detail.action?goodid=11"> 344 <img src="picture/11-1.jpg" class="img-responsive" alt="半熟芝士"/> 345 </a> 346 <div class="gallery-info"> 347 <p> 348 <span class="glyphicon glyphicon-eye-open" aria-hidden="true"></span> 349 <a href="detail.action?goodid=11">查看详情</a> 350 </p> 351 <a class="shop" href="javascript:;" onclick="buy(11)">立刻购买</a> 352 <div class="clearfix"> </div> 353 </div> 354 <div class="galy-info"> 355 <p>零食系列 > 半熟芝士</p> 356 <div class="galry"> 357 <div class="prices"> 358 <h5 class="item_price">¥ 38</h5> 359 </div> 360 <div class="clearfix"></div> 361 </div> 362 </div> 363 </div> 364 365 <div class="col-md-3 gallery-grid "> 366 <a href="detail.action?goodid=10"> 367 <img src="picture/10-1.jpg" class="img-responsive" alt="玫瑰舒芙蕾"/> 368 </a> 369 <div class="gallery-info"> 370 <p> 371 <span class="glyphicon glyphicon-eye-open" aria-hidden="true"></span> 372 <a href="detail.action?goodid=10">查看详情</a> 373 </p> 374 <a class="shop" href="javascript:;" onclick="buy(10)">立刻购买</a> 375 <div class="clearfix"> </div> 376 </div> 377 <div class="galy-info"> 378 <p>零食系列 > 玫瑰舒芙蕾</p> 379 <div class="galry"> 380 <div class="prices"> 381 <h5 class="item_price">¥ 28</h5> 382 </div> 383 <div class="clearfix"></div> 384 </div> 385 </div> 386 </div> 387 388 389 </div> 390 </div> 391 </div> 392 <!--//gallery--> 393 394 <!--subscribe--> 395 <div class="subscribe"></div> 396 <!--//subscribe--> 397 398 399 400 401 402 403 404 <!--footer--> 405 <div class="footer"> 406 <div class="container"> 407 <div class="text-center"> 408 <p>SiKi学院 www.sikiedu.com © All rights Reseverd</p> 409 </div> 410 </div> 411 </div> 412 <!--//footer--> 413 414 415 </body> 416 </html>
由于index页面初始时数据都设为固定的了，那么我们需要通过连接数据库来进行获取数据，因此我们需要通过servlet来进行请求数据，将所请求到的数据显示到页面上，新建一个servlet类，命名为IndexServlet.java,还需修改web.xml
web.xml
我们创建了一个servlet类，我们只需要doGet方法，如图所示：
运行结果：
由这段代码输出到页面上的：
response.getWriter().append("Served at: ").append(request.getContextPath());
如果需要在jsp中使用标签库，则需在声明下引入标签库：
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
我们需要将index.html页面变为index.jsp,因此我新建了一个命名为index.jsp的页面将index.html复制在jsp的声明下：
在IndexServlet.java通过下面的代码进行请求转发到index.jsp页面：
request.getRequestDispatcher("index.jsp").forward(request,response);
分析查询热销商品的SQL语句
由于导航栏与脚本在每一个脚本中都需要使用，因此把导航栏和脚本分别做为两个jsp，方便其他页面通过include来调用。
<jsp:include page="/header.jsp"></jsp:include>
数据的取得需要通过service层与dao层获得，因此在两层中分别创建相应的类进行取得：
显示热销商品的SQL语句：
select g.id,g.name,g.cover,g.price,t.name typename from recommend r,goods g,type t where type=2 and r.goods_id and g.type_id=t.id
使用该SQL语句在数据库中查询的效果：
创建表对应的数据模型类
由于创建数据表时需创建对应的数据模型类，我们先去把对应的数据模型类创建出来，总共热销商品这里有三个模型类：goods,recommend、type
1 package com.guiyan.model; 2 3 public class Goods { 4 private int id; 5 private String name; 6 private String image1; 7 private String image2; 8 private float price; 9 private String intro; 10 private int stock; 11 private Type type;//表示所属的系列 12 public int getId() { 13 return id; 14 } 15 public void setId(int id) { 16 this.id = id; 17 } 18 public String getName() { 19 return name; 20 } 21 public void setName(String name) { 22 this.name = name; 23 } 24 public String getImage1() { 25 return image1; 26 } 27 public void setImage1(String image1) { 28 this.image1 = image1; 29 } 30 public String getImage2() { 31 return image2; 32 } 33 public void setImage2(String image2) { 34 this.image2 = image2; 35 } 36 public float getPrice() { 37 return price; 38 } 39 public void setPrice(float price) { 40 this.price = price; 41 } 42 public String getIntro() { 43 return intro; 44 } 45 public void setIntro(String intro) { 46 this.intro = intro; 47 } 48 public int getStock() { 49 return stock; 50 } 51 public void setStock(int stock) { 52 this.stock = stock; 53 } 54 public Type getType() { 55 return type; 56 } 57 public void setType(Type type) { 58 this.type = type; 59 } 60 public Goods(int id, String name, String image1, String image2, float price, String intro, int stock, Type type) { 61 super(); 62 this.id = id; 63 this.name = name; 64 this.image1 = image1; 65 this.image2 = image2; 66 this.price = price; 67 this.intro = intro; 68 this.stock = stock; 69 this.type = type; 70 } 71 public Goods() { 72 super(); 73 } 74 75 76 }
1 package com.guiyan.model; 2 3 public class Recommend { 4 private int id; 5 private int type; 6 private Goods goods; 7 public int getId() { 8 return id; 9 } 10 public void setId(int id) { 11 this.id = id; 12 } 13 public int getType() { 14 return type; 15 } 16 public void setType(int type) { 17 this.type = type; 18 } 19 public Goods getGoods() { 20 return goods; 21 } 22 public void setGoods(Goods goods) { 23 this.goods = goods; 24 } 25 public Recommend(int id, int type, Goods goods) { 26 super(); 27 this.id = id; 28 this.type = type; 29 this.goods = goods; 30 } 31 public Recommend() { 32 super(); 33 } 34 35 36 }
1 package com.guiyan.model; 2 3 public class Type { 4 private int id; 5 private String name; 6 public int getId() { 7 return id; 8 } 9 public void setId(int id) { 10 this.id = id; 11 } 12 public String getName() { 13 return name; 14 } 15 public void setName(String name) { 16 this.name = name; 17 } 18 public Type() { 19 super(); 20 } 21 22 public Type(int id, String name) { 23 super(); 24 this.id = id; 25 this.name = name; 26 } 27 28 29 }
加入带参数与空的构造函数是为了后面的使用。
创建Service和Dao层的热销商品查询方法
在utils层中需要写数据库的connection：
package com.guiyan.utils; import java.sql.Connection; import java.sql.SQLException; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class DBUtil { private static DataSource ds = new ComboPooledDataSource(); public static DataSource getDataSource() { return ds; } public static Connection getConnection() throws SQLException { return ds.getConnection(); } }
在Dao层负责数据的增、删、改、查，只进行抛出异常
1 package com.guiyan.dao; 2 3 import java.sql.SQLException; 4 import java.util.List; 5 import java.util.Map; 6 7 import org.apache.commons.dbutils.QueryRunner; 8 9 import org.apache.commons.dbutils.handlers.MapListHandler; 10 11 import com.guiyan.utils.DBUtil; 12 13 public class GoodsDao { 14 15 public List<Map<String, Object>> getHotGoodsList() throws SQLException { 16 17 18 QueryRunner r=new QueryRunner(DBUtil.getDataSource()); 19 String sql="select g.id,g.name,g.cover,g.price,t.name typename from recommend r,goods g,type t where type=2 and r.goods_id and g.type_id=t.id"; 20 return r.query(sql,new MapListHandler()); 21 22 } 23 24 }
在Service中负责异常处理
1 package com.guiyan.service; 2 3 import java.sql.SQLException; 4 import java.util.List; 5 import java.util.Map; 6 7 import com.guiyan.dao.GoodsDao; 8 9 public class GoodsService { 10 private GoodsDao gDao=new GoodsDao(); 11 12 public List<Map<String, Object>> getHotGoodsList() { 13 List<Map<String, Object>> list=null; 14 try { 15 list = gDao.getHotGoodsList(); 16 } catch (SQLException e) { 17 // TODO Auto-generated catch block 18 e.printStackTrace(); 19 } 20 21 22 23 return list; 24 25 26 } 27 28 }
在servlet负责热销商品的取得
private GoodsService gService=new GoodsService(); //取得热销商品 List<Map<String, Object>> list=gService.getHotGoodsList(); request.setAttribute("hotList", list);
展示热销商品
在index.jsp需引用标签库
将index.jsp中的热销推荐中展示的商品留下一个即可
效果：
最终通过数据库热销商品显示在页面上的效果：
通过连接数据库热销商品显示的index.jsp代码：
1 <!--gallery--> 2 <div class="gallery"> 3 <div class="container"> 4 <div class="alert alert-danger">热销推荐</div> 5 <div class="gallery-grids"> 6 7 <c:forEach items="${hotList}" var="g"> 8 <div class="col-md-4 gallery-grid glry-two"> 9 <a href="detail.action?goodid=6"> 10 <img src="${pageContext.request.contextPath }${g.cover}" class="img-responsive" alt="${g.name}" width="350" height="350"/> 11 </a> 12 <div class="gallery-info galrr-info-two"> 13 <p> 14 <span class="glyphicon glyphicon-eye-open" aria-hidden="true"></span> 15 <a href="detail.action?goodid=6">查看详情</a> 16 </p> 17 <a class="shop" href="javascript:;" onclick="buy(6)">立刻购买</a> 18 <div class="clearfix"> </div> 19 </div> 20 <div class="galy-info"> 21 <p>${g.typeName}> ${g.name}</p> 22 <div class="galry"> 23 <div class="prices"> 24 <h5 class="item_price">${g.price}</h5> 25 </div> 26 <div class="clearfix"></div> 27 </div> 28 </div> 29 </div> 30 31 32 </c:forEach> 33 34 35 36 </div>
总的代码：
1 <!--gallery--> 2 <div class="gallery"> 3 <div class="container"> 4 <div class="alert alert-danger">热销推荐</div> 5 <div class="gallery-grids"> 6 7 <c:forEach items="${hotList}" var="g"> 8 <div class="col-md-4 gallery-grid glry-two"> 9 <a href="detail.action?goodid=6"> 10 <img src="${pageContext.request.contextPath }${g.cover}" class="img-responsive" alt="${g.name}" width="350" height="350"/> 11 </a> 12 <div class="gallery-info galrr-info-two"> 13 <p> 14 <span class="glyphicon glyphicon-eye-open" aria-hidden="true"></span> 15 <a href="detail.action?goodid=6">查看详情</a> 16 </p> 17 <a class="shop" href="javascript:;" onclick="buy(6)">立刻购买</a> 18 <div class="clearfix"> </div> 19 </div> 20 <div class="galy-info"> 21 <p>${g.typeName}> ${g.name}</p> 22 <div class="galry"> 23 <div class="prices"> 24 <h5 class="item_price">${g.price}</h5> 25 </div> 26 <div class="clearfix"></div> 27 </div> 28 </div> 29 </div> 30 31 32 </c:forEach> 33 34 35 36 </div>
