mybatis如何实现查询一级目录下的二级，三级菜单的两种方法

本文链接：https://blog.youkuaiyun.com/qq_61935738/article/details/129718347

数据库如图

新开一个实习类categoryVo在原来ctegory的基础上加上一个数组

方法一：连接查询

<resultMap id="categoryVoMap" type="com.qfedu.fmmall.entity.CategoryVo">
    <!--
      WARNING - @mbg.generated
    -->
    <id column="category_id1" jdbcType="INTEGER" property="categoryId" />
    <result column="category_name1" jdbcType="VARCHAR" property="categoryName" />
    <result column="category_level1" jdbcType="INTEGER" property="categoryLevel" />
    <result column="parent_id1" jdbcType="INTEGER" property="parentId" />
    <result column="category_icon1" jdbcType="VARCHAR" property="categoryIcon" />
    <result column="category_slogan1" jdbcType="VARCHAR" property="categorySlogan" />
    <result column="category_pic1" jdbcType="VARCHAR" property="categoryPic" />
    <result column="category_bg_color1" jdbcType="VARCHAR" property="categoryBgColor" />
    <collection property="categories" ofType="com.qfedu.fmmall.entity.CategoryVo">
      <id column="category_id2" jdbcType="INTEGER" property="categoryId" />
      <result column="category_name2" jdbcType="VARCHAR" property="categoryName" />
      <result column="category_level2" jdbcType="INTEGER" property="categoryLevel" />
      <result column="parent_id2" jdbcType="INTEGER" property="parentId" />
      <collection property="categories" ofType="com.qfedu.fmmall.entity.CategoryVo">
        <id column="category_id3" jdbcType="INTEGER" property="categoryId" />
        <result column="category_name3" jdbcType="VARCHAR" property="categoryName" />
        <result column="category_level3" jdbcType="INTEGER" property="categoryLevel" />
        <result column="parent_id3" jdbcType="INTEGER" property="parentId" />
      </collection>
    </collection>
  </resultMap>

  <select id="selectAllCategories" resultMap="categoryVoMap">
    select c1.category_id       'category_id1',
           c1.category_name     'category_name1',
           c1.category_level    'category_level1',
           c1.parent_id         'parent_id1',
           c1.category_icon     'category_icon1',
           c1.category_slogan   'category_slogan1',
           c1.category_pic      'category_pic1',
           c1.category_bg_color 'category_bg_color1',
           c2.category_id       'category_id2',
           c2.category_name     'category_name2',
           c2.category_level    'category_level2',
           c2.parent_id         'parent_id2',
           c3.category_id       'category_id3',
           c3.category_name     'category_name3',
           c3.category_level    'category_level3',
           c3.parent_id         'parent_id3'
    from category c1
           inner join category c2
                      on c2.parent_id = c1.category_id
           left join category c3
                     on c3.parent_id = c2.category_id
    where c1.category_level = 1
  </select>

方法二：子查询

定义mapper包下的接口

代码如下

<resultMap id="categoryVoMap2" type="com.qfedu.fmmall.entity.CategoryVo">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="category_id" jdbcType="INTEGER" property="categoryId" />
        <result column="category_name" jdbcType="VARCHAR" property="categoryName" />
        <result column="category_level" jdbcType="INTEGER" property="categoryLevel" />
        <result column="parent_id" jdbcType="INTEGER" property="parentId" />
        <result column="category_icon" jdbcType="VARCHAR" property="categoryIcon" />
        <result column="category_slogan" jdbcType="VARCHAR" property="categorySlogan" />
        <result column="category_pic" jdbcType="VARCHAR" property="categoryPic" />
        <result column="category_bg_color" jdbcType="VARCHAR" property="categoryBgColor" />
        <collection property="categories" column="category_id" select="selectAllCategories2"></collection>
        <!--拿到每个一级分类category_id查询它的下一级信息，相当于递归调用-->
    </resultMap>

    <!--根据父级分类id查询子集分类-->
    <select id="selectAllCategories2" resultMap="categoryVoMap2" >
        select
            category_id,
            category_name,
            category_level,
            parent_id,
            category_icon,
            category_slogan,
            category_pic,
            category_bg_color
        from category where parent_id = #{parendId}
    </select>