自定义mapper实现

功能使用说明.首页加载分类数据的方式

(1)先渲染一级分类
(2)懒加载二级分类，（如鼠标hover一级分类时才开始加载对应的二级数据）

一、数据库表结构 category 分类表

create table `foodie-shop-dev`.category
(
    id        int auto_increment comment '主键'
        primary key,
    name      varchar(32) not null comment '分类名称',
    type      int         not null comment '分类类型',
    father_id int         not null comment '父id',
    logo      varchar(64) null comment '图标',
    slogan    varchar(64) null comment '口号',
    cat_image varchar(64) null comment '分类图',
    bg_color  varchar(32) null comment '背景颜色'
)
    comment '商品分类 ' charset = utf8mb4;

View Code

INSERT INTO `category` VALUES (1, '甜点/蛋糕', 1, 0, 'img/cake.png', '每一道甜品都能打开你的味蕾', 'http://localhost:88/foodie/category/cake.png', '#fe7a65');
INSERT INTO `category` VALUES (2, '饼干/膨化', 1, 0, 'img/cookies.png', '嘎嘣脆，一听到声音就开吃', 'http://localhost:88/foodie/category/cookies.png', '#f59cec');
INSERT INTO `category` VALUES (3, '熟食/肉类', 1, 0, 'img/meat.png', '食肉者最爱绝佳美食', 'http://localhost:88/foodie/category/meat.png', '#b474fe');
INSERT INTO `category` VALUES (4, '素食/卤味', 1, 0, 'img/luwei.png', '香辣甜辣麻辣，辣了才有味', 'http://localhost:88/foodie/category/duck.png', '#82ceff');
INSERT INTO `category` VALUES (5, '坚果/炒货', 1, 0, 'img/jianguo.png', '酥脆无比，休闲最佳', 'http://localhost:88/foodie/category/nut.png', '#c6a868');
INSERT INTO `category` VALUES (6, '糖果/蜜饯', 1, 0, 'img/sweet.png', '甜味是爱美者的最爱', 'http://localhost:88/foodie/category/mango.png', '#6bdea7');
INSERT INTO `category` VALUES (7, '巧克力', 1, 0, 'img/chocolate.png', '美容养颜，男女都爱', 'http://localhost:88/foodie/category/chocolate.png', '#f8c375');
INSERT INTO `category` VALUES (8, '海鲜/海味', 1, 0, 'img/lobster.png', '吃货们怎么能少了海鲜呢？', 'http://localhost:88/foodie/category/crab.png', '#84affe');
INSERT INTO `category` VALUES (9, '花茶/果茶', 1, 0, 'img/tea.png', '绿茶红茶怎能少得了', 'http://localhost:88/foodie/category/tea.png', '#ff9229');
INSERT INTO `category` VALUES (10, '生鲜/蔬果', 1, 0, 'img/food.png', '新鲜少不了，每日蔬果生鲜', 'http://localhost:88/foodie/category/meat2.png', '#6cc67c');
INSERT INTO `category` VALUES (11, '蛋糕', 2, 1, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (12, '点心', 2, 1, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (13, '饼干', 2, 2, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (14, '薯片', 2, 2, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (15, '虾条', 2, 2, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (16, '肉铺', 2, 3, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (17, '肉松', 2, 3, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (18, '香肠', 2, 3, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (19, '豆干', 2, 4, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (20, '干笋', 2, 4, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (21, '鸭脖', 2, 4, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (22, '坚果', 2, 5, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (23, '锅巴', 2, 5, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (24, '糖果', 2, 6, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (25, '蜜饯', 2, 6, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (26, '巧克力', 2, 7, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (27, '果冻', 2, 7, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (29, '海鲜', 2, 8, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (30, '海味', 2, 8, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (31, '茶叶', 2, 9, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (32, '奶茶', 2, 9, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (33, '果茶', 2, 9, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (34, '蔬菜', 2, 10, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (35, '水果', 2, 10, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (36, '肉类', 2, 10, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (37, '蒸蛋糕', 3, 11, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (38, '软面包', 3, 11, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (39, '脱水蛋糕', 3, 11, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (40, '马卡龙', 3, 11, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (41, '甜甜圈', 3, 11, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (42, '三明治', 3, 11, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (43, '铜锣烧', 3, 11, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (44, '肉松饼', 3, 12, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (45, '华夫饼', 3, 12, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (46, '沙琪玛', 3, 12, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (47, '鸡蛋卷', 3, 12, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (48, '蛋饼', 3, 12, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (49, '凤梨酥', 3, 12, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (50, '手撕面包', 3, 12, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (51, '苏打饼干', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (52, '夹心饼干', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (53, '闲饼干', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (54, '甜饼干', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (55, '威化饼干', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (56, '酥饼干', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (57, '曲奇', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (58, '无糖饼干', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (59, '早餐饼干', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (60, '薯条', 3, 14, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (61, '薯片', 3, 14, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (62, '山药薯片', 3, 14, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (63, '网红薯片', 3, 14, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (64, '蛋黄薯片', 3, 14, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (65, '虾条', 3, 15, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (66, '怀旧虾条', 3, 15, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (67, '8090后虾条', 3, 15, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (68, '进口虾条', 3, 15, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (69, 'DIY虾条', 3, 15, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (73, '猪肉脯', 3, 16, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (74, '牛肉脯', 3, 16, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (75, '鸡肉脯', 3, 16, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (76, '散装肉脯', 3, 16, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (77, '猪肉干', 3, 16, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (78, '牛肉干', 3, 16, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (79, '牛肉粒', 3, 16, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (80, '牛肉松', 3, 17, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (81, '猪肉松', 3, 17, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (82, '儿童肉松', 3, 17, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (83, '腊味香肠', 3, 18, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (84, '广式香肠', 3, 18, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (85, '火腿肠', 3, 18, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (86, '烤肠', 3, 18, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (87, '鸡肉肠', 3, 18, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (88, '鱼肠', 3, 18, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (89, '酒味香肠', 3, 18, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (90, '香菇', 3, 19, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (91, '零食', 3, 19, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (92, '豆腐干', 3, 19, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (93, '辣条', 3, 19, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (94, '泡椒脆笋', 3, 20, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (95, '野生笋干', 3, 20, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (96, '扁尖笋', 3, 20, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (97, '农家笋', 3, 20, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (98, '卤味鸭脖', 3, 21, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (99, '麻辣鸭脖', 3, 21, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (100, '武汉鸭脖', 3, 21, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (101, '鸭锁骨', 3, 21, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (102, '鸭胗', 3, 21, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (103, '鸭翅', 3, 21, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (104, '网红坚果', 3, 22, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (105, '瓜子', 3, 22, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (106, '碧根果', 3, 22, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (107, '松子', 3, 22, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (108, '山核桃', 3, 22, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (109, '开心果', 3, 22, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (110, '腰果', 3, 22, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (111, '网红锅巴', 3, 23, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (112, '蛋黄锅巴', 3, 23, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (113, '水果糖', 3, 24, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (114, '薄荷糖', 3, 24, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (115, '夹心糖', 3, 24, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (116, '奶糖', 3, 24, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (117, '喜糖', 3, 24, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (118, '麦芽糖', 3, 24, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (119, '话梅', 3, 25, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (120, '果干', 3, 25, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (121, '草莓干', 3, 25, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (122, '芒果干', 3, 25, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (123, '菠萝干', 3, 25, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (124, '山楂糕', 3, 25, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (125, '黑巧克力', 3, 26, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (126, '白巧克力', 3, 26, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (127, '夹心巧克力', 3, 26, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (128, '榛果巧克力', 3, 26, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (129, '日本生巧', 3, 26, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (130, '果肉布丁', 3, 27, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (131, '果冻碎碎冰', 3, 27, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (132, '果汁果冻', 3, 27, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (133, '帝王蟹', 3, 29, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (134, '大龙虾', 3, 29, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (135, '鲍鱼', 3, 29, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (136, '海参', 3, 29, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (137, '鱿鱼', 3, 29, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (138, '三文鱼', 3, 29, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (139, '扇贝', 3, 29, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (140, '干贝', 3, 30, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (141, '海苔', 3, 30, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (142, '虾仁', 3, 30, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (143, '海鱼干', 3, 30, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (144, '水蜜桃茶', 3, 33, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (145, '白桃乌龙茶', 3, 33, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (146, '柠檬片', 3, 33, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (147, '白茶', 3, 31, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (148, '红茶', 3, 31, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (149, '绿茶', 3, 31, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (150, '铁观音', 3, 31, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (151, '胖大海', 3, 31, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (152, '碧螺春', 3, 31, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (153, '速冲奶茶', 3, 32, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (154, '珍珠奶茶', 3, 32, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (155, '袋装奶茶', 3, 32, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (156, '英式奶茶', 3, 32, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (157, '黄瓜', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (158, '芋头', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (159, '玉米', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (160, '南瓜', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (161, '荸荠', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (162, '山药', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (163, '秋葵', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (164, '红薯/紫薯', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (165, '土豆', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (166, '水蜜桃', 3, 35, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (167, '西瓜', 3, 35, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (168, '苹果', 3, 35, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (169, '凤梨', 3, 35, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (170, '草莓', 3, 35, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (171, '葡萄', 3, 35, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (172, '杨梅', 3, 35, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (173, '牛排', 3, 36, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (174, '鸡肉', 3, 36, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (175, '猪肉', 3, 36, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (176, '羊肉', 3, 36, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (177, '蹄子', 3, 36, NULL, NULL, NULL, NULL);
INSERT INTO `category` VALUES (178, '糖醋排骨', 3, 36, NULL, NULL, NULL, NULL);

表数据

二、根据一级分类查询子分类语句

SELECT
    f.id AS id,
    f.`name` AS `name`,
    f.type AS type,
    f.father_id AS fatherId,
    c.id AS subId,
    c.NAME AS subName,
    c.type AS subType,
    c.father_id AS subFatherId 
FROM
    category f
    LEFT JOIN category c ON f.id = c.father_id 
WHERE
    f.father_id =1

View Code

三、Mapper实现

1、新增vo对象 CategoryVO （目录：com/imooc/pojo/vo/CategoryVO.java）

package com.imooc.pojo.vo;

import java.util.List;

/**
 * 二级分类 VO  （view）
 */
public class CategoryVO {

    private Integer id;
    private String name;
    private String type;
    private Integer fatherId;

    //三级分类vo list
    private List<SubCategoryVO> subCatList;


    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public Integer getFatherId() {
        return fatherId;
    }

    public void setFatherId(Integer fatherId) {
        this.fatherId = fatherId;
    }

    public List<SubCategoryVO> getSubCatList() {
        return subCatList;
    }

    public void setSubCatList(List<SubCategoryVO> subCatList) {
        this.subCatList = subCatList;
    }


}

View Code

2、新增vo对象 SubCategoryVO.java（目录：com/imooc/pojo/vo/SubCategoryVO.java）

package com.imooc.pojo.vo;

import java.util.List;

/**
 * 二级分类 VO  （view）
 */
public class CategoryVO {

    private Integer id;
    private String name;
    private String type;
    private Integer fatherId;

    //三级分类vo list
    private List<SubCategoryVO> subCatList;


    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public Integer getFatherId() {
        return fatherId;
    }

    public void setFatherId(Integer fatherId) {
        this.fatherId = fatherId;
    }

    public List<SubCategoryVO> getSubCatList() {
        return subCatList;
    }

    public void setSubCatList(List<SubCategoryVO> subCatList) {
        this.subCatList = subCatList;
    }


}

View Code

3、新增定制接口 CategoryMapperCustom （目录：com/imooc/mapper/CategoryMapperCustom.java）

package com.imooc.mapper;


import com.imooc.pojo.vo.CategoryVO;

import java.util.List;

public interface CategoryMapperCustom  {

    public List<CategoryVO> getSubCatList(Integer rootCatId);
}

View Code

4、新增 CategoryMapperCustom.xml （目录：src/main/resources/mapper/CategoryMapperCustom.xml）

(1)自定义mapper，注意在xml中mapper的namespace一定要跟mapper接口名相同
(2)resultMap的type参数对应的vo类，如果有构造函数，一定要加上无参构造函数

（3）mapper.xml 中拼接SQL时

## mapper.xml 中拼接SQL时
- 单个情况判断用 <if></if>
- 多个情况判断用  <choose>
            <when></when>
            <when></when>
            <otherwise> </otherwise>
            </choose>
- 拼接字符串时用 ${} 不用 #{}
- 单引号要转义 &quot;

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.imooc.mapper.CategoryMapperCustom" >
  <resultMap id="myCategoryVO" type="com.imooc.pojo.vo.CategoryVO">
      <id column="id" property="id"/>
      <result column="name" property="name"/>
      <result column="type" property="type"/>
      <result column="fatherId" property="fatherId"/>

      <!--
         collection 标签：用于定义关联的list集合类型的封装规则
         property：对应三级分类的list属性名
         ofType：集合的类型，三级分类的vo
      -->
      <collection property="subCatList" ofType="com.imooc.pojo.vo.SubCategoryVO">
          <id column="subId" property="subId"/>
          <result column="subName" property="subName"/>
          <result column="subType" property="subType"/>
          <result column="subFatherId" property="subFatherId"/>
      </collection>

  </resultMap>

  <select id="getSubCatList"  resultMap="myCategoryVO" parameterType="int">

     SELECT
        f.id AS id,
        f.`name` AS `name`,
        f.type AS type,
        f.father_id AS fatherId,
        c.id AS subId,
        c.NAME AS subName,
        c.type AS subType,
        c.father_id AS subFatherId
    FROM
        category f
    LEFT JOIN
        category c
    ON
        f.id = c.father_id
    WHERE
        f.father_id = #{rootCatId}


  </select>

</mapper>

View Code

四、service接口

（1）接口方法定义

package com.imooc.service;

import com.imooc.pojo.Carousel;
import com.imooc.pojo.Category;
import com.imooc.pojo.vo.CategoryVO;

import java.util.List;

public interface CategoryService {
    /**
     *查询所有一级分类
     * @return
     */
    public List<Category> queryAllRootLeverCat();

    /**根据一级分类查询
     *
     * @param rootCatId
     * @return
     */
    public List<CategoryVO> getSubCatList(Integer rootCatId);

}

View Code

（2）接口实现

package com.imooc.service.impl;

import com.imooc.mapper.CarouselMapper;
import com.imooc.mapper.CategoryMapper;
import com.imooc.mapper.CategoryMapperCustom;
import com.imooc.pojo.Carousel;
import com.imooc.pojo.Category;
import com.imooc.pojo.vo.CategoryVO;
import com.imooc.service.CarouselService;
import com.imooc.service.CategoryService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import tk.mybatis.mapper.entity.Example;

import java.util.List;

@Service
public class CategoryServiceImpl implements CategoryService {

    @Autowired
    private CategoryMapper categoryMapper;
    @Autowired
    private CategoryMapperCustom categoryMapperCustom;

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public List<CategoryVO> getSubCatList(Integer rootCatId) {
        return categoryMapperCustom.getSubCatList(rootCatId);
    }

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public List<Category> queryAllRootLeverCat() {
        Example example = new Example(Carousel.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andEqualTo("type",1);
        List<Category> resList= categoryMapper.selectByExample(example);
        return resList;
    }


}

View Code

五、api接口实现（目录com/imooc/controller/IndexController.java）

前端：

axios.get(serverUrl + '/index/subCat/' + rootCatId, {})

使用@PathVariable注解获取URL中的参数，@getMapping（）中要以路径形式 使用{ }注明值的位置

package com.imooc.controller;

import com.imooc.enums.YesOrNo;
import com.imooc.pojo.Carousel;
import com.imooc.pojo.Category;
import com.imooc.pojo.vo.CategoryVO;
import com.imooc.service.CarouselService;
import com.imooc.service.CategoryService;
import com.imooc.utils.IMOOCJSONResult;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import springfox.documentation.annotations.ApiIgnore;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.util.List;

@Api(value = "首页",tags = "首页展示的相关接口")
@RestController
@RequestMapping("index")
public class IndexController {

    @Autowired
     private CarouselService carouselService;
    @Autowired
    private CategoryService categoryService;

    @ApiOperation(value="获取首页轮播图列表",notes = "获取首页轮播图列表",httpMethod = "GET")
    @GetMapping("/carouse")
    public IMOOCJSONResult carouse() {
        List<Carousel> list= carouselService.queryAll(YesOrNo.YES.type);
        return IMOOCJSONResult.ok(list);
    }

    @ApiOperation(value="获取商品分类(一级分类)",notes = "获取商品分类(一级分类)",httpMethod = "GET")
    @GetMapping("/cats")
    public IMOOCJSONResult cats() {
        List<Category> list= categoryService.queryAllRootLeverCat();
        return IMOOCJSONResult.ok(list);
    }

    @ApiOperation(value="获取商品子分类",notes = "获取商品子分类",httpMethod = "GET")
    @GetMapping("/subCat/{rootCatId}")
    public IMOOCJSONResult subCat(
            @ApiParam(name="rootCatId",value = "一级分类id",required = true)
            @PathVariable Integer rootCatId) {
        if(rootCatId ==null){
            return IMOOCJSONResult.errorMsg("分类不存在");
        }
        List<CategoryVO> list= categoryService.getSubCatList(rootCatId);
        return IMOOCJSONResult.ok(list);
    }
}

View Code