最终效果

数据库表设计

在商品表中添加所属一级分类字段，少了冗余，避免需要多次查询一级分类下的子分类来获取商品信息

实现懒加载

前端监听滚动条触底，当距离底部一定距离时，向后端发送请求加载下一条主分类下的6条最新商品信息并展示

1、编写SQL查询

SELECT
    f.id AS rootCatId,
    f.`name` AS rootCatName,
    f.slogan AS slogan,
    f.cat_image AS catImage,
    f.bg_color AS bgColor,
    i.id AS itemId,
    i.item_name AS itemName,
    ii.url AS itemUrl,
    i.created_time AS createdTime
FROM
    category f             #分类表
LEFT JOIN
    items i                #分类表关联商品表，用于找对于一级分类下的商品
ON
    f.id = i.root_cat_id   
LEFT JOIN
    items_img ii           #商品表关联商品图片表，用于找商品主图
ON 
    i.id = ii.item_id
WHERE
    f.type = 1             #type = 1表示该分类是一级分类
AND
    i.root_cat_id = 7      #商品对于的一级分类id
AND
    ii.is_main = 1         #商品图片is_main = 1 表示该图片为商品主图
ORDER BY
    i.created_time
DESC
LIMIT 0,6

2、分析创建VO

根据查询我们看到分类信息都是一样的，所以我们需要创建一个分类VO，在分类VO下包含6条最新商品的VO List信息。
给出前端信息如下：

"data": [
    {
      "rootCatId": 4,       // ==================== 一级分类 ====================
      "rootCatName": "素食/卤味",
      "slogan": "香辣甜辣麻辣，辣了才有味",
      "catImage": "http://122.152.205.72:88/foodie/category/duck.png",
      "bgColor": "#82ceff",
      "simpleItemList": [   // 👇👇👇👇👇👇👇👇一级分类下的商品信息👇👇👇👇👇👇👇👇
        {
          "itemId": "snacks-1005",
          "itemName": "【天天吃货】农家乐农家菜 香喷喷野生笋干",
          "itemUrl": "http://122.152.205.72:88/foodie/snacks-1005/img1.png",
          "createdTime": "2019-09-09 14:45:34"
        },
        {
          "itemId": "snacks-1002",
          "itemName": "【天天吃货】武汉鸭胗 卤味齐全 香辣麻辣",
          "itemUrl": "http://122.152.205.72:88/foodie/snacks-1002/img1.png",
          "createdTime": "2019-09-09 14:45:34"
        }
        //...                // 👆👆👆👆👆👆👆一级分类下的商品信息👆👆👆👆👆👆👆
      ]
    }
  ]

创建分类NewItemsVO

package com.shiers.pojo.vo;
import java.util.List;
/**
 * 最新商品VO
 *
 * @author shierS
 * @date 2021/5/29
 */
public class NewItemsVO {
    private Integer rootCatId;
    private String rootCatName;
    private String slogan;
    private String catImage;
    private String bgColor;
    private List<SimpleItemVO> simpleItemList;   //👈👈👈分类下的商品信息
    //省略get、set方法
}

创建商品SimpleItemVO

package com.shiers.pojo.vo;
/**
 * 6个最新商品的简单数据类型
 *
 * @author shierS
 * @date 2021/5/30
 */
public class SimpleItemVO {
    private String itemId;
    private String itemName;
    private String itemUrl;
    private String createdTime;
    //省略get、set方法
}

3、编写mapper

<resultMap id="myNewItemsVO" type="com.shiers.pojo.vo.NewItemsVO">
  <id column="rootCatId" property="rootCatId"/>
  <result column="rootCatName" property="rootCatName"/>
  <result column="slogan" property="slogan"/>
  <result column="catImage" property="catImage"/>
  <result column="bgColor" property="bgColor"/>
  <!--
     collection 标签：用于定义关联的lsit集合类型的封装规则
     property:对应三级分类的list属性名
     ofType:集合的类型，三级分类vo
  -->
  <collection property="simpleItemList" ofType="com.shiers.pojo.vo.SimpleItemVO">
    <id column="itemId" property="itemId"/>
    <result column="itemName" property="itemName"/>
    <result column="itemUrl" property="itemUrl"/>
    <result column="createdTime" property="createdTime"/>
  </collection>
</resultMap>
<select id="getSixNewItemsLazy" resultMap="myNewItemsVO" parameterType="Map">
  SELECT f.id    AS rootCatId,
  f.`name`       AS rootCatName,
  f.slogan       AS slogan,
  f.cat_image    AS catImage,
  f.bg_color     AS bgColor,
  i.id           AS itemId,
  i.item_name    AS itemName,
  ii.url         AS itemUrl,
  i.created_time AS createdTime
  FROM
  category f
  LEFT JOIN items i ON f.id = i.root_cat_id
  LEFT JOIN items_img ii ON i.id = ii.item_id
  WHERE
  f.type = 1
  AND
  i.root_cat_id = #{paramsMap.rootCatId}
  AND
  ii.is_main = 1
  ORDER BY
  i.created_time
  DESC
  LIMIT 0,6
</select>

4、编写service

@Transactional(propagation = Propagation.SUPPORTS)
@Override
public List<NewItemsVO> getSixNewItemsLazy(Integer rootCatId) {
    Map<String, Object> map = new HashMap<>();   //使用map来存放rootCatId
    map.put("rootCatId",rootCatId);
    return categoryMapperCustom.getSixNewItemsLazy(map); 
}

5、编写Controller

@ApiOperation(value = "查询首页每个一级分类下的6条商品数据", notes = "查询首页每个一级分类下的6条商品数据", httpMethod = "GET")
@GetMapping("/sixNewItems/{rootCatId}")
public MyJSONResult sixNewItems(
    @ApiParam(name = "rootCatId", value = "一级分类id", required = true)
    @PathVariable Integer rootCatId) {
    if (rootCatId == null) {
        return MyJSONResult.errorMsg("分类不存在");
    }
    List<NewItemsVO> list = categoryService.getSixNewItemsLazy(rootCatId);
    return MyJSONResult.ok(list);
}

架构师之路

实现首页一级分类下的最新6条商品懒加载