MyBatis实现递归查询的方法详解

目录

业务场景

开发环境

数据库

关键代码

实体类代码

MyBatis的mapper接口代码

mapper映射文件代码

查询测试

业务场景

在项目开发过程中,往往会遇到多级菜单、分类等多层级结构数据的查询。

for example:

请看上图,这是一个电商项目中常见的多级类目功能,如图所示,共分为一、二、三,共三级类目,每一个一级类目有各自的二级目录,每个二级目录有自己的三级目录

再看这个例子,下图是一个多级菜单的功能,和上面的例子类似

在这种场景下,通常我们要用递归进行处理。下面博主以电商项目的多级类目功能,通过MyBatis进行递归查询功能说明

开发环境 名称版本
IntelliJ IDEA2021.3.2
Spring Boot2.6.4
mybatis-spring-boot-starter2.2.2
数据库

表结构

CREATE TABLE `shopping_commodity_category` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL COMMENT 'category name', `picture` varchar(255) DEFAULT NULL COMMENT 'category picture id', `superior_category` int NOT NULL DEFAULT 0 COMMENT 'superior category id', `sort_number` int NOT NULL COMMENT 'sort number', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time of this record', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time of this record', PRIMARY KEY (`id`), UNIQUE KEY (`superior_category`, `name`), UNIQUE KEY (`superior_category`, `sort_number`) );

字段解释:

字段名含义
id数据表中每条数据的唯一标识符
name类目名称
picture类目的预览图id(由于业务需要,这个地方保存的另一张表的文件的id,可根据需要直接存储图片地址),由于上级父分类没有预览图,所以可以为空
superior_category上级类目的id,不能为空,如果是顶级类目,那么他的上级类目id就为0
sort_number排序号,用于在同一级的目录下进行排序
create_time数据的创建时间,无需关注,值是插入数据时自动通过当前时间戳填充
update_time数据的更新时间,无需关注,值是在当这条记录被更新时自动以当前时间戳进行更新

约束解释:

类型用途
主键约束(id)(显而易见,无需赘述)
联合唯一约束(UNIQUE KEY (superior_category, name))在同一级类目下面,限制不能有重复的类目名称
联合唯一约束(UNIQUE KEY (superior_category, sort_number))在同一级类目下,限制排序号不能重复

由于博主这个地方业务需要,用到了另一张表的数据,为了说明问题,博主将另一张表的结构也贴出来参考

CREATE TABLE `storage_multimedia_file` ( `id` int NOT NULL AUTO_INCREMENT, `absolute_path` text NOT NULL COMMENT 'file absolute path', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time of this record', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time of this record', PRIMARY KEY (`id`) );

这个表很简单,就一个主要的字段,存储的这个文件的绝对路径,方便后续通过IO流读取

插入测试数据

shopping_commodity_category表 INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (1, '手机通讯', null, 0, 1, '2022-08-11 17:27:15', '2022-08-11 17:27:15'); INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (2, '手机配件', null, 1, 1, '2022-08-11 17:29:59', '2022-08-11 17:29:59'); INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (3, '手机耳机', '2', 2, 1, '2022-08-11 17:29:59', '2022-08-11 17:29:59'); INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (4, '蓝牙耳机', '3', 2, 2, '2022-08-11 17:31:26', '2022-08-11 17:31:26'); INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (5, '手机壳/保护壳', '4', 2, 3, '2022-08-11 17:32:51', '2022-08-11 17:32:51'); INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (6, '手机贴膜', '5', 2, 4, '2022-08-11 17:33:44', '2022-08-11 17:33:44'); INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (7, '运营商', null, 1, 2, '2022-08-11 17:34:44', '2022-08-11 17:34:44'); INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (8, '办号卡', '6', 7, 1, '2022-08-11 17:36:25', '2022-08-11 17:36:25'); INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (9, '家用电器', null, 0, 2, '2022-08-11 17:36:54', '2022-08-11 17:36:54'); INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (10, '生活电器', null, 9, 1, '2022-08-11 17:37:46', '2022-08-11 17:37:46'); INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (11, '吸尘器', '7', 10, 1, '2022-08-11 17:38:55', '2022-08-11 17:38:55'); INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (12, '厨房小电', null, 9, 2, '2022-08-11 17:40:04', '2022-08-11 17:40:04'); INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (13, '电饭煲', '8', 12, 1, '2022-08-11 17:41:17', '2022-08-11 17:41:17');

storage_multimedia_file表

INSERT INTO trembling_bird.storage_multimedia_file (id, absolute_path, create_time, update_time) VALUES (1, 'D:\\files\\trembling-bird\\commodity-previews\\1.webp', '2022-08-11 16:18:49', '2022-08-11 16:18:49'); INSERT INTO trembling_bird.storage_multimedia_file (id, absolute_path, create_time, update_time) VALUES (2, 'D:\\files\\trembling-bird\\category-preview\\1.webp', '2022-08-11 17:30:12', '2022-08-11 17:30:12'); INSERT INTO trembling_bird.storage_multimedia_file (id, absolute_path, create_time, update_time) VALUES (3, 'D:\\files\\trembling-bird\\category-preview\\2.webp', '2022-08-11 17:31:07', '2022-08-11 17:31:07'); INSERT INTO trembling_bird.storage_multimedia_file (id, absolute_path, create_time, update_time) VALUES (4, 'D:\\files\\trembling-bird\\category-preview\\3.webp', '2022-08-11 17:32:07', '2022-08-11 17:32:07'); INSERT INTO trembling_bird.storage_multimedia_file (id, absolute_path, create_time, update_time) VALUES (5, 'D:\\files\\trembling-bird\\category-preview\\4.webp', '2022-08-11 17:33:36', '2022-08-11 17:33:36'); INSERT INTO trembling_bird.storage_multimedia_file (id, absolute_path, create_time, update_time) VALUES (6, 'D:\\files\\trembling-bird\\category-preview\\5.webp', '2022-08-11 17:35:48', '2022-08-11 17:35:48'); INSERT INTO trembling_bird.storage_multimedia_file (id, absolute_path, create_time, update_time) VALUES (7, 'D:\\files\\trembling-bird\\category-preview\\6.webp', '2022-08-11 17:38:22', '2022-08-11 17:38:22'); INSERT INTO trembling_bird.storage_multimedia_file (id, absolute_path, create_time, update_time) VALUES (8, 'D:\\files\\trembling-bird\\category-preview\\7.webp', '2022-08-11 17:40:38', '2022-08-11 17:40:38'); 关键代码 实体类代码

实体类的公共父类(因为数据库中每个表都有共同的字段,如id,create_time,update_time,所以将这些共有的字段抽取出来放到公共的父类,让其他实体类继承此父类,就有了这些公共字段,降低代码冗余)

package com.fenzhimedia.commons.pojo; import lombok.Data; import java.io.Serializable; import java.time.LocalDateTime; /** * @author Yi Dai 484201132@qq.com * @since 2022/3/21 13:55 */ @Data public abstract class BasePojo implements Serializable { /** * the unique identification of this record in the database table */ protected int id; /** * creation time of this record */ protected LocalDateTime createTime; /** * update time of this record */ protected LocalDateTime updateTime; }

描述类目的实体类

package com.fenzhimedia.commons.shopping.pojo; import com.fenzhimedia.commons.pojo.BasePojo; import com.fenzhimedia.commons.storage.pojo.MultimediaFile; import lombok.Data; import lombok.EqualsAndHashCode; import java.util.List; /** * @author Yi Dai 484201132@qq.com * @since 2022/4/23 10:43 */ @Data @EqualsAndHashCode(callSuper = true) public class CommodityCategory extends BasePojo { /** * category name */ private String name; /** * entity class encapsulating picture information */ private MultimediaFile picture; /** * used to specify the order of categories, * which is only valid under the same level category */ private Integer sortNumber; /** * sub commodity category */ private List<CommodityCategory> subCommodityCategories; }

描述文件的实体类

package com.fenzhimedia.commons.storage.pojo; import com.fenzhimedia.commons.pojo.BasePojo; import lombok.Data; import lombok.EqualsAndHashCode; /** * @author Yi Dai 484201132@qq.com * @since 2022/8/11 16:03 */ @Data @EqualsAndHashCode(callSuper = true) public class MultimediaFile extends BasePojo { private String absolutePath; } MyBatis的mapper接口代码 package com.fenzhimedia.shopping.mapper; import com.fenzhimedia.commons.shopping.pojo.CommodityCategory; import java.util.List; /** * @author Yi Dai 484201132@qq.com * @since 2022/8/11 16:46 */ public interface CommodityCategoryMapper { List<CommodityCategory> queryCommodityCategories(int superiorCategoryId); } mapper映射文件代码 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.fenzhimedia.shopping.mapper.CommodityCategoryMapper"> <select id="queryCommodityCategories" resultMap="commodityCategoryMap"> select `shopping_commodity_category`.`id` as `shopping_commodity_category_id`, `shopping_commodity_category`.`name` as `shopping_commodity_category_name`, `shopping_commodity_category`.`picture` as `shopping_commodity_category_picture`, `shopping_commodity_category`.`superior_category` as `shopping_commodity_category_superior_category`, `storage_multimedia_file`.`id` as `storage_multimedia_file_id`, `storage_multimedia_file`.`absolute_path` as `storage_multimedia_file_absolute_path` from `shopping_commodity_category` left join `storage_multimedia_file` on `shopping_commodity_category`.`picture` = `storage_multimedia_file`.`id` where `superior_category` = #{categoryId} order by `shopping_commodity_category`.`sort_number` </select> <resultMap id="commodityCategoryMap" type="commodityCategory"> <id property="id" column="shopping_commodity_category_id"/> <result property="name" column="shopping_commodity_category_name"/> <association property="picture" javaType="multimediaFile"> <id property="id" column="storage_multimedia_file_id"/> <result property="absolutePath" column="storage_multimedia_file_absolute_path"/> </association> <collection property="subCommodityCategories" ofType="commodityCategory" column="shopping_commodity_category_id" select="queryCommodityCategories"/> </resultMap> </mapper>

代码解释:

可以看到,queryCommodityCategories是一个类目表和文件表的左连接查询,然后分别起了别名,接收一个int型的参数,为上级类目的id,当然也就是0,然后声明一个resultMap ,将字段映射起来,都是基本操作,唯一值得注意的就是

subCommodityCategories作为一个集合,它有通过select直接调用了queryCommodityCategories这个查询,而参数正是由column属性传递过去的,参数的值就是当前类目的id(shopping_commodity_category_id)(有点绕),其实这样就递归查询起来了

那么有的小伙伴可能会疑问,既然是已知是0,为何不直接写道xml中,而是大费周章的,在接口上声明一个参数,然后传递进来?其实这是因为后面的递归查询的时候需要传入上级分类的id,一旦写死了,就只能查询上级分类id为0的,很显然,达不到想要的效果

既然如此,那么就需要业务代码中来传递上级类目id这个参数,很显然我们直接在代码中写死不是那么的优雅。博主这里为了更灵活,我想要实现一个如果前台传递了上级分类id,那么就帮他查询指定上级类目的子级类目,如果没有传递,那么就查询所有的类目及其子类目。所以博主是这么处理的:

@GetMapping("/queryCommodityCategories") public ResponseBody queryCommodityCategories(@RequestParam(required = false, defaultValue = "0") int superiorCategoryId) { return commodityCategoryService.queryCommodityCategories(superiorCategoryId); } 查询测试

返回结果

statusCode、message是通用返回实体类的结构,无需关注,查询的数据在data中

{ "statusCode": 200, "message": null, "data": [ { "id": 1, "createTime": null, "updateTime": null, "name": "手机通讯", "picture": null, "sortNumber": null, "subCommodityCategories": [ { "id": 2, "createTime": null, "updateTime": null, "name": "手机配件", "picture": null, "sortNumber": null, "subCommodityCategories": [ { "id": 3, "createTime": null, "updateTime": null, "name": "手机耳机", "picture": { "id": 2, "createTime": null, "updateTime": null, "absolutePath": "D:\\files\\trembling-bird\\category-preview\\1.webp" }, "sortNumber": null, "subCommodityCategories": [ ] }, { "id": 4, "createTime": null, "updateTime": null, "name": "蓝牙耳机", "picture": { "id": 3, "createTime": null, "updateTime": null, "absolutePath": "D:\\files\\trembling-bird\\category-preview\\2.webp" }, "sortNumber": null, "subCommodityCategories": [ ] }, { "id": 5, "createTime": null, "updateTime": null, "name": "手机壳/保护壳", "picture": { "id": 4, "createTime": null, "updateTime": null, "absolutePath": "D:\\files\\trembling-bird\\category-preview\\3.webp" }, "sortNumber": null, "subCommodityCategories": [ ] }, { "id": 6, "createTime": null, "updateTime": null, "name": "手机贴膜", "picture": { "id": 5, "createTime": null, "updateTime": null, "absolutePath": "D:\\files\\trembling-bird\\category-preview\\4.webp" }, "sortNumber": null, "subCommodityCategories": [ ] } ] }, { "id": 7, "createTime": null, "updateTime": null, "name": "运营商", "picture": null, "sortNumber": null, "subCommodityCategories": [ { "id": 8, "createTime": null, "updateTime": null, "name": "办号卡", "picture": { "id": 6, "createTime": null, "updateTime": null, "absolutePath": "D:\\files\\trembling-bird\\category-preview\\5.webp" }, "sortNumber": null, "subCommodityCategories": [ ] } ] } ] }, { "id": 9, "createTime": null, "updateTime": null, "name": "家用电器", "picture": null, "sortNumber": null, "subCommodityCategories": [ { "id": 10, "createTime": null, "updateTime": null, "name": "生活电器", "picture": null, "sortNumber": null, "subCommodityCategories": [ { "id": 11, "createTime": null, "updateTime": null, "name": "吸尘器", "picture": { "id": 7, "createTime": null, "updateTime": null, "absolutePath": "D:\\files\\trembling-bird\\category-preview\\6.webp" }, "sortNumber": null, "subCommodityCategories": [ ] } ] }, { "id": 12, "createTime": null, "updateTime": null, "name": "厨房小电", "picture": null, "sortNumber": null, "subCommodityCategories": [ { "id": 13, "createTime": null, "updateTime": null, "name": "电饭煲", "picture": { "id": 8, "createTime": null, "updateTime": null, "absolutePath": "D:\\files\\trembling-bird\\category-preview\\7.webp" }, "sortNumber": null, "subCommodityCategories": [ ] } ] } ] } ] }

以上就是MyBatis实现递归查询的方法详解的详细内容,更多关于MyBatis递归查询的资料请关注易知道(ezd.cc)其它相关文章!

推荐阅读