业务场景
开发环境
数据库
关键代码
实体类代码
MyBatis的mapper接口代码
mapper映射文件代码
查询测试
业务场景在项目开发过程中,往往会遇到多级菜单、分类等多层级结构数据的查询。
for example:
请看上图,这是一个电商项目中常见的多级类目功能,如图所示,共分为一、二、三,共三级类目,每一个一级类目有各自的二级目录,每个二级目录有自己的三级目录
再看这个例子,下图是一个多级菜单的功能,和上面的例子类似
在这种场景下,通常我们要用递归进行处理。下面博主以电商项目的多级类目功能,通过MyBatis进行递归查询功能说明
开发环境IntelliJ IDEA | 2021.3.2 |
Spring Boot | 2.6.4 |
mybatis-spring-boot-starter | 2.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)其它相关文章!