一、数据库设计

1.商品表

  1. CREATE TABLE `goods` (
  2.   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '商品id',
  3.   `goods_name` varchar(16) CHARACTER SET utf8 DEFAULT NULL COMMENT '商品名称',
  4.   `goods_title` varchar(64) CHARACTER SET utf8 DEFAULT NULL COMMENT '商品标题',
  5.   `goods_img` varchar(64) CHARACTER SET utf8 DEFAULT NULL COMMENT '商品图片',
  6.   `goods_detail` longtext CHARACTER SET utf8 COMMENT '商品详情介绍',
  7.   `goods_price` decimal(10,2) DEFAULT NULL COMMENT '商品单价',
  8.   `goods_stock` int(11) DEFAULT '0' COMMENT '商品库存，-1表示无限制',
  9.   PRIMARY KEY (`id`)
  10. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

2.秒杀商品表

CREATE TABLE `miaosha_goods` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '秒杀的商品表',
  `goods_id` bigint(20) DEFAULT NULL COMMENT '商品id',
  `miaosha_price` decimal(10,2) DEFAULT NULL COMMENT '秒杀价',
  `stock_count` int(11) DEFAULT NULL COMMENT '库存数量',
  `start_date` datetime DEFAULT NULL COMMENT '秒杀开始时间',
  `end_date` datetime DEFAULT NULL COMMENT '秒杀结束时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

3.订单表

CREATE TABLE `order_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `goods_id` bigint(20) DEFAULT NULL COMMENT '商品id',
  `delivery_addr_id` bigint(20) DEFAULT NULL COMMENT '收货地址id',
  `goods_name` varchar(16) DEFAULT NULL COMMENT '商品名称',
  `goods_count` int(11) DEFAULT NULL COMMENT '商品数量',
  `goods_price` decimal(10,2) DEFAULT NULL COMMENT '商品单价',
  `order_channel` tinyint(4) DEFAULT '0' COMMENT '1pc，2Android，3ios',
  `status` tinyint(4) DEFAULT '0' COMMENT '订单状态：0新建未支付，1已支付，2已发货，3已收货，4已退款，5已完成',
  `create_date` datetime DEFAULT NULL COMMENT '订单创建时间',
  `pay_date` datetime DEFAULT NULL COMMENT '支付时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;

4.秒杀订单表

CREATE TABLE `miaosha_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `order_id` bigint(20) DEFAULT NULL COMMENT '订单id',
  `goods_id` bigint(20) DEFAULT NULL COMMENT '商品id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

二、商品列表页

1.商品 dao 层

@Mapper
public interface GoodsDao {

    @Select("select g.*,mg.stock_count,mg.start_date,mg.end_date,mg.miaosha_price " +
            "from miaosha_goods mg left join goods g on mg.goods_id = g.id")
    public List<GoodsVo> lisrGoodsVo();
}

2.商品 Service 层

@Autowired
GoodsDao goodsDao;

public List<GoodsVo> listGoodsVo(){
    return goodsDao.lisrGoodsVo();
}

3.商品 Controller 层

@RequestMapping("/to_list")
public String list(Model model , MiaoshaUser user){
    model.addAttribute("user",user);
    //查询商品列表
    List<GoodsVo> goodsList = goodsService.listGoodsVo();
    model.addAttribute("goodsList",goodsList);
    return "goods_list";
}

4.前端 goods_list 页面

<h1 th:text="'hello:'+${user.nickname}" ></h1>
<div class="panel panel-default">
    <div class="panel-heading">秒杀商品列表</div>
    <table class="table" id="goodslist">
        <tr><td>商品名称</td><td>商品图片</td><td>商品原价</td><td>秒杀价</td><td>库存数量</td><td>详情</td></tr>
        <tr  th:each="goods,goodsStat : ${goodsList}">
            <td th:text="${goods.goodsName}"></td>
            <td ><img th:src="@{${goods.goodsImg}}" width="100" height="100" /></td>
            <td th:text="${goods.goodsPrice}"></td>
            <td th:text="${goods.miaoshaPrice}"></td>
            <td th:text="${goods.stockCount}"></td>
            <td><a th:href="'/goods/to_detail/'+${goods.id}">详情</a></td>
        </tr>
    </table>
</div>
</body>

5.运行效果：

image.png

三、商品详情页

1.根据 goodsId 查询商品信息

  • dao层：

    @Select("select g.*,mg.stock_count,mg.start_date,mg.end_date,mg.miaosha_price " +
      "from miaosha_goods mg left join goods g on mg.goods_id = g.id where g.id = #{goodsId}")
GoodsVo getGoodsVoByGoodsId(@Param("goodsId") long goodsId);

  • 判断秒杀状态 ```java long startAt = goods.getStartDate().getTime(); long endAt = goods.getEndDate().getTime(); long now = System.currentTimeMillis();

int miaoshaStatus = 0; int remainSeconds = 0;

if (now < startAt) {//秒杀还没开始，倒计时 miaoshaStatus = 0; remainSeconds = (int) (startAt - now) / 1000; } else if (now > endAt) {//秒杀已经结束 miaoshaStatus = 2; remainSeconds = -1; } else {//秒杀正在进行 miaoshaStatus = 1; remainSeconds = 0; }


- 返回给前端
```java
model.addAttribute("goods", goods);
model.addAttribute("miaoshaStatus", miaoshaStatus);
model.addAttribute("remainSeconds", remainSeconds);

2.前端 js 实现倒计时

function countDown(){
    var remainSeconds = $("#remainSeconds").val();
    var timeout;
    if(remainSeconds > 0){//秒杀还没开始，倒计时
        $("#buyButton").attr("disabled", true);
        timeout = setTimeout(function(){
            $("#countDown").text(remainSeconds - 1);
            $("#remainSeconds").val(remainSeconds - 1);
            countDown();
        },1000);
    }else if(remainSeconds == 0){//秒杀进行中
        $("#buyButton").attr("disabled", false);
        if(timeout){
            clearTimeout(timeout);
        }
        $("#miaoshaTip").html("秒杀进行中");
    }else{//秒杀已经结束
        $("#buyButton").attr("disabled", true);
        $("#miaoshaTip").html("秒杀已经结束");
    }
}

3.运行效果

Snipaste_2020-11-15_10-00-33.png
Snipaste_2020-11-15_09-59-49.png

四、秒杀功能实现

1.秒杀逻辑

  • 判断用户是否登陆

    model.addAttribute("user",user);
if (user == null){
return "login";
}

  • 查询库存是否满足秒杀

    GoodsVo goods = goodsService.getGoodsVoByGoodsId(goodsId);
int stock = goods.getStockCount();
if (stock <= 0){
  model.addAttribute("errmsg", CodeMsg.MIAO_SHA_OVER.getMessage());
  return "miaosha_fail";
}

  • 判断该用户是否已经秒杀过商品，避免重复秒杀

    MiaoshaOrder order = orderService.getMiaoshaOrderByUserIdGoodsId(user.getId(),goodsId);
if (order != null){
  model.addAttribute("errmsg",CodeMsg.REPEATE_MIAOSHA.getMessage());
  return "miaosha_fail";
}

  • 减库存，下订单

    OrderInfo orderInfo = miaoshaService.miaosha(user,goods);

    2.秒杀业务细节实现

  • Service 层实现 reduceStock 方法和 createOrder 方法，使用事务

    @Transactional
public OrderInfo miaosha(MiaoshaUser user, GoodsVo goods) {
  //减库存
  goodsService.reduceStock(goods);
  //创建订单
  return orderService.createOrder(user,goods);
}

  • reduceStock 根据商品的 id 查询到秒杀商品，将库存减1

    public void reduceStock(GoodsVo goods) {
  MiaoshaGoods g = new MiaoshaGoods();
  g.setGoodsId(goods.getId());
  goodsDao.reduceStock(g);
}
//dao层
@Update("update miaosha_goods set stock_count = stock_count - 1 where goods_id = #{goodsId}")
void reduceStock(MiaoshaGoods g);

  • createOrder 根据 user 和 goods 的信息，创建订单和秒杀订单 ```java @Transactional public OrderInfo createOrder(MiaoshaUser user, GoodsVo goods) { OrderInfo orderInfo = new OrderInfo(); orderInfo.setCreateDate(new Date()); orderInfo.setDeliveryAddrId(0L); orderInfo.setGoodsCount(1); orderInfo.setGoodsId(goods.getId()); orderInfo.setGoodsName(goods.getGoodsName()); orderInfo.setGoodsPrice(goods.getMiaoshaPrice()); orderInfo.setOrderChannel(1); orderInfo.setStatus(0); orderInfo.setUserId(user.getId()); long orderId = orderDao.insert(orderInfo); MiaoshaOrder miaoshaOrder = new MiaoshaOrder(); miaoshaOrder.setGoodsId(goods.getId()); miaoshaOrder.setOrderId(orderId); miaoshaOrder.setUserId(user.getId()); orderDao.insertMiaoshaOrder(miaoshaOrder); return orderInfo; }

//插入订单数据，并返回创建的订单id @Insert(“insert into order_info(user_id, goods_id, goods_name, goods_count, goods_price, order_channel, status, create_date)values(“

    + "#{userId}, #{goodsId}, #{goodsName}, #{goodsCount}, #{goodsPrice}, #{orderChannel},#{status},#{createDate} )")

@SelectKey(keyColumn=”id”, keyProperty=”id”, resultType=long.class, before=false, statement=”select last_insert_id()”) public long insert(OrderInfo orderInfo); //插入秒杀订单数据 @Insert(“insert into miaosha_order (user_id, goods_id, order_id)values(#{userId}, #{goodsId}, #{orderId})”) void insertMiaoshaOrder(MiaoshaOrder miaoshaOrder); ```

3.订单详情页展示秒杀结果

image.png