商城分类多级菜单实现

最新推荐文章于 2023-01-03 15:45:49 发布

臆想的一只猫

最新推荐文章于 2023-01-03 15:45:49 发布

阅读量373

点赞数 1

分类专栏： SpringBoot 文章标签： Mybatis 商品多级分类 SQL

本文链接：https://blog.csdn.net/weixin_42047611/article/details/120243632

版权

SpringBoot 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

本文介绍了如何使用MyBatis进行多级分类查询，包括连接查询和子查询两种方法。首先展示了连接查询的实现，通过三层join获取所有分类信息，并在ResultMap中定义了嵌套的集合映射。然后，提出了子查询的方式，利用ResultMap的引用和自定义查询解决无限层级分类问题。这种方法更加灵活，不受分类层级限制。

摘要由CSDN通过智能技术生成

实体类封装

在这里插入图片描述

查询方法

连接查询

select
    c1.id as 'id1',
    c1.category_name as 'category_name1',
    c1.category_level as 'category_level1',
    c1.parent_id as 'parent_id1',
    c1.category_icon as 'category_icon1',
    c1.category_slogan as 'category_slogan1',
    c1.category_pic as 'category_pic1',
    c1.category_bg_color as 'category_bg_color1',
    c2.id as 'id2',
    c2.category_name as 'category_name2',
    c2.category_level as 'category_level2',
    c2.parent_id as 'parent_id2',
    c3.id as 'id3',
    c3.category_name as 'category_name3',
    c3.category_level as 'category_level3',
    c3.parent_id as 'parent_id3'
from category c1
         inner join category c2 on c1.id = c2.parent_id
         left join category c3 on c2.id = c3.parent_id
where c1.category_level = 1;

MyBatis实现

    <resultMap id="CategoryMap" type="com.monologue.entity.Category">
        <id column="id1" property="id"/>
        <result column="category_name1" property="categoryName"/>
        <result column="category_level1" property="categoryLevel"/>
        <result column="parent_id1" property="parentId"/>
        <result column="category_icon1" property="categoryIcon"/>
        <result column="category_slogan1" property="categorySlogan"/>
        <result column="category_pic1" property="categoryPic"/>
        <result column="category_bg_color1" property="categoryBgColor"/>
        <collection property="categories" ofType="com.monologue.entity.Category">
            <id column="id2" property="id"/>
            <result column="category_name2" property="categoryName"/>
            <result column="category_level2" property="categoryLevel"/>
            <result column="parent_id2" property="parentId"/>
            <collection property="categories" ofType="com.monologue.entity.Category">
                <id column="id3" property="id"/>
                <result column="category_name3" property="categoryName"/>
                <result column="category_level3" property="categoryLevel"/>
                <result column="parent_id3" property="parentId"/>
            </collection>
        </collection>
    </resultMap>

    <select id="queryCategoryAll" resultMap="CategoryMap">
        select
            c1.id as 'id1',
            c1.category_name as 'category_name1',
            c1.category_level as 'category_level1',
            c1.parent_id as 'parent_id1',
            c1.category_icon as 'category_icon1',
            c1.category_slogan as 'category_slogan1',
            c1.category_pic as 'category_pic1',
            c1.category_bg_color as 'category_bg_color1',
            c2.id as 'id2',
            c2.category_name as 'category_name2',
            c2.category_level as 'category_level2',
            c2.parent_id as 'parent_id2',
            c3.id as 'id3',
            c3.category_name as 'category_name3',
            c3.category_level as 'category_level3',
            c3.parent_id as 'parent_id3'
        from category c1
                 inner join category c2 on c1.id = c2.parent_id
                 left join category c3 on c2.id = c3.parent_id
        where c1.category_level = #{categoryLevel};
    </select>

测试

在这里插入图片描述

这种方法有一个弊端，必须明确知道有几级分类，所以下面介绍另一种方法

子查询

    <resultMap id="CategoryMap_" type="com.monologue.entity.Category">
        <id column="id" property="id"/>
        <result column="category_name" property="categoryName"/>
        <result column="category_level" property="categoryLevel"/>
        <result column="parent_id" property="parentId"/>
        <result column="category_icon" property="categoryIcon"/>
        <result column="category_slogan" property="categorySlogan"/>
        <result column="category_pic" property="categoryPic"/>
        <result column="category_bg_color" property="categoryBgColor"/>
        <collection property="categories" column="id" select="com.monologue.mapper.CategoryMapper.queryCategoryAll2"/>
    </resultMap>

    <select id="queryCategoryAll2" resultMap="CategoryMap_">
        select
        <include refid="selectFields"/>
        from `category`
        where parent_id = #{parentId}
    </select>