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();
}