SpringBoot +MyBatis 三表关联嵌套查询

深藏阁楼爱情的钟 2023-06-25 03:21 31阅读 0赞

# SpringBoot +MyBatis 两表关联嵌套查询 #

废话不多说，代码走起来！

# 效果图 #

{
        "respCode": "200",
        "respMessage": "查询成功",
        "data": [
            {
                "firstLevelId": 1,
                "categoryName": "手机",
                "categorySecondLevelVO": [
                    {
                        "secondLevelId": 1,
                        "categoryName": "手机",
                        "categoryThirdLevelVO": [
                            {
                                "thirdLevelId": 1,
                                "categoryName": "手机"
                            }
                        ]
                    },
                    {
                        "secondLevelId": 2,
                        "categoryName": "手机配件",
                        "categoryThirdLevelVO": [
                            {
                                "thirdLevelId": 2,
                                "categoryName": "移动电源"
                            },
                            {
                                "thirdLevelId": 3,
                                "categoryName": "蓝牙耳机"
                            },
                            {
                                "thirdLevelId": 4,
                                "categoryName": "手机耳机"
                            }
                        ]
                    },
                    {
                        "secondLevelId": 3,
                        "categoryName": "运营商",
                        "categoryThirdLevelVO": [
                            {
                                "thirdLevelId": 5,
                                "categoryName": "上网卡"
                            },
                            {
                                "thirdLevelId": 6,
                                "categoryName": "装宽带"
                            },
                            {
                                "thirdLevelId": 7,
                                "categoryName": "办号卡"
                            },
                            {
                                "thirdLevelId": 8,
                                "categoryName": "合约机"
                            },
                            {
                                "thirdLevelId": 9,
                                "categoryName": "运营商周边"
                            }
                        ]
                    },
                    {
                        "secondLevelId": 4,
                        "categoryName": "手机服务",
                        "categoryThirdLevelVO": [
                            {
                                "thirdLevelId": 10,
                                "categoryName": "保障服务"
                            },
                            {
                                "thirdLevelId": 11,
                                "categoryName": "电池换新"
                            },
                            {
                                "thirdLevelId": 12,
                                "categoryName": "手机故障"
                            },
                            {
                                "thirdLevelId": 13,
                                "categoryName": "屏幕换新"
                            }
                        ]
                    },
                    {
                        "secondLevelId": 5,
                        "categoryName": "对讲机",
                        "categoryThirdLevelVO": [
                            {
                                "thirdLevelId": 14,
                                "categoryName": "模拟对讲机"
                            },
                            {
                                "thirdLevelId": 15,
                                "categoryName": "数字对讲机"
                            },
                            {
                                "thirdLevelId": 16,
                                "categoryName": "对讲机配件"
                            },
                            {
                                "thirdLevelId": 17,
                                "categoryName": "公网对讲机"
                            }
                        ]
                    }
                ]
            },
            {
                "firstLevelId": 2,
                "categoryName": "电脑",
                "categorySecondLevelVO": []
            }
        ]
    }

# **数据库表** #

category\_first\_level 一级分类表

category\_second\_level 二级分类表

category\_third\_level 三级分类表

DROP TABLE IF EXISTS `category_first_level`;
    CREATE TABLE `category_first_level` (
      `first_level_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '一级类目ID',
      `category_name` varchar(64) DEFAULT NULL COMMENT '类目名字',
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
      PRIMARY KEY (`first_level_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='一级类目表';
    
    -- ----------------------------
    -- Records of category_first_level
    -- ----------------------------
    
    -- ----------------------------
    -- Table structure for category_second_level
    -- ----------------------------
    DROP TABLE IF EXISTS `category_second_level`;
    CREATE TABLE `category_second_level` (
      `second_level_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '二级类目ID',
      `parent_id` int(10) unsigned NOT NULL COMMENT '一级类目ID',
      `tree` varchar(64) DEFAULT NULL COMMENT '总标识',
      `category_name` varchar(64) DEFAULT NULL COMMENT '分类名字',
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
      PRIMARY KEY (`second_level_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='二级类目表';
    
    -- ----------------------------
    -- Records of category_second_level
    -- ----------------------------
    
    -- ----------------------------
    -- Table structure for category_third_level
    -- ----------------------------
    DROP TABLE IF EXISTS `category_third_level`;
    CREATE TABLE `category_third_level` (
      `third_level_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '三级类目ID',
      `first_level_id` int(10) DEFAULT NULL COMMENT '一级类目ID',
      `parent_id` int(10) unsigned NOT NULL COMMENT '二级类目ID',
      `tree` varchar(64) DEFAULT NULL COMMENT '总标识',
      `category_name` varchar(64) DEFAULT NULL COMMENT '分类名字',
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
      PRIMARY KEY (`third_level_id`),
      KEY `id` (`first_level_id`,`parent_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='三级类目表';

# 封装的CategoryFirstLevelsVO #

@Data
    public class CategoryFirstLevelsVO { 
        /** * 一级类目ID **/
        private Integer firstLevelId;
        /** * 一级类目名称 **/
        private String categoryName;
        /** * 二级类目集合 **/
        private List<CategorySecondLevelVO> categorySecondLevelVO;
    }

# 封装的CategorySecondLevelVO #

@Data
    public class CategorySecondLevelVO { 
        /** * 二级类目ID **/
        private Integer secondLevelId;
        /** * 二级类目名称 **/
        private String categoryName;
        /** * 三级类目集合 **/
        private List<CategoryThirdLevelVO> categoryThirdLevelVO;
    }

# 封装的CategoryThirdLevelVO #

@Data
    public class CategoryThirdLevelVO { 
        /** * 三级类目ID **/
        private Integer thirdLevelId;
        /** * 三级类目名字 **/
        private String categoryName;
    }

# Mapper接口 #

/** * @Description 嵌套查询一二三级分类 * @Retuen * @Param **/
        List<CategoryFirstLevelsVO> getAllLevel();

# Mapper.xml 文件 #

<resultMap id="AllLevel" type="com.dy.mallConfig.pojo.vo.CategoryFirstLevelsVO">
            <id column="first_level_id" property="firstLevelId" jdbcType="BIGINT"/>
            <result column="fcategory_name" property="categoryName" jdbcType="VARCHAR"/>
            <collection property="categorySecondLevelVO" ofType="com.dy.mallConfig.pojo.vo.CategorySecondLevelVO">
                <id column="second_level_id" property="secondLevelId" jdbcType="BIGINT"/>
                <result column="scategory_name" property="categoryName" jdbcType="VARCHAR"/>
                <collection property="categoryThirdLevelVO" ofType="com.dy.mallConfig.pojo.vo.CategoryThirdLevelVO">
                    <id column="third_level_id" property="thirdLevelId" jdbcType="BIGINT"/>
                    <result column="tcategory_name" property="categoryName" jdbcType="VARCHAR"/>
                </collection>
            </collection>
        </resultMap>
        <select id="getAllLevel" resultMap="AllLevel">
            SELECT
            	`first`.first_level_id,
            	`first`.category_name fcategory_name,
            	`second`.second_level_id,
            	`second`.category_name scategory_name,
            	third.third_level_id,
             	third.category_name  tcategory_name
            FROM
            	category_first_level `first`
            LEFT JOIN category_second_level `second` ON `second`.parent_id = `first`.first_level_id
             LEFT JOIN category_third_level third ON third.parent_id = `second`.second_level_id
        </select>

# Service接口 #

/** * @Description 嵌套查询一二三级分类 * @Retuen * @Param **/
        OutputObject getAllLevel();

# ServiceImpl实现类 #

/** * @Description 嵌套查询一二三级分类 * @Retuen * @Param **/
        @Override
        public OutputObject getAllLevel() { 
            try{ 
                List<CategoryFirstLevelsVO> list = categoryFirstLevelMapper.getAllLevel();
                return new OutputObject(ReturnCode.SUCCESS,"查询成功",list);
            }catch (Exception e){ 
                return new OutputObject(ReturnCode.FAIL,"查询失败",e.getMessage());
            }
        }

# Controller控制器 #

@RequestMapping("getAllLevel")
        @ResponseBody
        public OutputObject getAllLevel(){ 
            LOGGER.info("com.dy.mallConfig.controller.CategoryFirstLevelController.getAllLevel.嵌套查询一二三级分类");
            return categoryFirstLevelService.getAllLevel();
        }

# 大功告成！！！ #