MyBatis實(shí)現(xiàn)遞歸查詢的方法詳解

更新時(shí)間：2022年08月12日 10:50:49 作者：june-YiDai

在項(xiàng)目開(kāi)發(fā)過(guò)程中，往往會(huì)遇到多級(jí)菜單、分類等多層級(jí)結(jié)構(gòu)數(shù)據(jù)的查詢。本文就來(lái)為大家講講MyBatis實(shí)現(xiàn)遞歸查詢的方法，感興趣的可以動(dòng)手嘗試一下

業(yè)務(wù)場(chǎng)景

在項(xiàng)目開(kāi)發(fā)過(guò)程中，往往會(huì)遇到多級(jí)菜單、分類等多層級(jí)結(jié)構(gòu)數(shù)據(jù)的查詢。

for example：

請(qǐng)看上圖，這是一個(gè)電商項(xiàng)目中常見(jiàn)的多級(jí)類目功能，如圖所示，共分為一、二、三，共三級(jí)類目，每一個(gè)一級(jí)類目有各自的二級(jí)目錄，每個(gè)二級(jí)目錄有自己的三級(jí)目錄

再看這個(gè)例子，下圖是一個(gè)多級(jí)菜單的功能，和上面的例子類似

在這種場(chǎng)景下，通常我們要用遞歸進(jìn)行處理。下面博主以電商項(xiàng)目的多級(jí)類目功能，通過(guò)MyBatis進(jìn)行遞歸查詢功能說(shuō)明

開(kāi)發(fā)環(huán)境

名稱	版本
IntelliJ IDEA	2021.3.2
Spring Boot	2.6.4
mybatis-spring-boot-starter	2.2.2

數(shù)據(jù)庫(kù)

表結(jié)構(gòu)

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	數(shù)據(jù)表中每條數(shù)據(jù)的唯一標(biāo)識(shí)符
name	類目名稱
picture	類目的預(yù)覽圖id（由于業(yè)務(wù)需要，這個(gè)地方保存的另一張表的文件的id，可根據(jù)需要直接存儲(chǔ)圖片地址），由于上級(jí)父分類沒(méi)有預(yù)覽圖，所以可以為空
superior_category	上級(jí)類目的id，不能為空，如果是頂級(jí)類目，那么他的上級(jí)類目id就為0
sort_number	排序號(hào)，用于在同一級(jí)的目錄下進(jìn)行排序
create_time	數(shù)據(jù)的創(chuàng)建時(shí)間，無(wú)需關(guān)注，值是插入數(shù)據(jù)時(shí)自動(dòng)通過(guò)當(dāng)前時(shí)間戳填充
update_time	數(shù)據(jù)的更新時(shí)間，無(wú)需關(guān)注，值是在當(dāng)這條記錄被更新時(shí)自動(dòng)以當(dāng)前時(shí)間戳進(jìn)行更新

約束解釋：

類型	用途
主鍵約束(id)	(顯而易見(jiàn)，無(wú)需贅述)
聯(lián)合唯一約束(UNIQUE KEY (superior_category, name))	在同一級(jí)類目下面，限制不能有重復(fù)的類目名稱
聯(lián)合唯一約束(UNIQUE KEY (superior_category, sort_number))	在同一級(jí)類目下，限制排序號(hào)不能重復(fù)

由于博主這個(gè)地方業(yè)務(wù)需要，用到了另一張表的數(shù)據(jù)，為了說(shuō)明問(wèn)題，博主將另一張表的結(jié)構(gòu)也貼出來(lái)參考

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`)
);

這個(gè)表很簡(jiǎn)單，就一個(gè)主要的字段，存儲(chǔ)的這個(gè)文件的絕對(duì)路徑，方便后續(xù)通過(guò)IO流讀取

插入測(cè)試數(shù)據(jù)

shopping_commodity_category表
INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (1, '手機(jī)通訊', 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, '手機(jī)配件', 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, '手機(jī)耳機(jī)', '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, '藍(lán)牙耳機(jī)', '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, '手機(jī)殼/保護(hù)殼', '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, '手機(jī)貼膜', '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, '運(yùn)營(yíng)商', 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, '辦號(hào)卡', '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.jpg', '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.jpg', '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.png', '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.jpg', '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.png', '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.jpg', '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.jpg', '2022-08-11 17:40:38', '2022-08-11 17:40:38');

關(guān)鍵代碼

實(shí)體類代碼

實(shí)體類的公共父類（因?yàn)閿?shù)據(jù)庫(kù)中每個(gè)表都有共同的字段，如id，create_time,update_time,所以將這些共有的字段抽取出來(lái)放到公共的父類，讓其他實(shí)體類繼承此父類，就有了這些公共字段，降低代碼冗余）

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

描述類目的實(shí)體類

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

描述文件的實(shí)體類

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是一個(gè)類目表和文件表的左連接查詢，然后分別起了別名，接收一個(gè)int型的參數(shù)，為上級(jí)類目的id，當(dāng)然也就是0，然后聲明一個(gè)resultMap ，將字段映射起來(lái)，都是基本操作，唯一值得注意的就是

subCommodityCategories作為一個(gè)集合，它有通過(guò)select直接調(diào)用了queryCommodityCategories這個(gè)查詢，而參數(shù)正是由column屬性傳遞過(guò)去的，參數(shù)的值就是當(dāng)前類目的id(shopping_commodity_category_id)（有點(diǎn)繞），其實(shí)這樣就遞歸查詢起來(lái)了

那么有的小伙伴可能會(huì)疑問(wèn)，既然是已知是0，為何不直接寫(xiě)道xml中，而是大費(fèi)周章的，在接口上聲明一個(gè)參數(shù)，然后傳遞進(jìn)來(lái)？其實(shí)這是因?yàn)楹竺娴倪f歸查詢的時(shí)候需要傳入上級(jí)分類的id，一旦寫(xiě)死了，就只能查詢上級(jí)分類id為0的，很顯然，達(dá)不到想要的效果

既然如此，那么就需要業(yè)務(wù)代碼中來(lái)傳遞上級(jí)類目id這個(gè)參數(shù)，很顯然我們直接在代碼中寫(xiě)死不是那么的優(yōu)雅。博主這里為了更靈活，我想要實(shí)現(xiàn)一個(gè)如果前臺(tái)傳遞了上級(jí)分類id，那么就幫他查詢指定上級(jí)類目的子級(jí)類目，如果沒(méi)有傳遞，那么就查詢所有的類目及其子類目。所以博主是這么處理的：

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

查詢測(cè)試

返回結(jié)果

statusCode、message是通用返回實(shí)體類的結(jié)構(gòu)，無(wú)需關(guān)注，查詢的數(shù)據(jù)在data中

{
    "statusCode": 200, 
    "message": null, 
    "data": [
        {
            "id": 1, 
            "createTime": null, 
            "updateTime": null, 
            "name": "手機(jī)通訊", 
            "picture": null, 
            "sortNumber": null, 
            "subCommodityCategories": [
                {
                    "id": 2, 
                    "createTime": null, 
                    "updateTime": null, 
                    "name": "手機(jī)配件", 
                    "picture": null, 
                    "sortNumber": null, 
                    "subCommodityCategories": [
                        {
                            "id": 3, 
                            "createTime": null, 
                            "updateTime": null, 
                            "name": "手機(jī)耳機(jī)", 
                            "picture": {
                                "id": 2, 
                                "createTime": null, 
                                "updateTime": null, 
                                "absolutePath": "D:\\files\\trembling-bird\\category-preview\\1.jpg"
                            }, 
                            "sortNumber": null, 
                            "subCommodityCategories": [ ]
                        }, 
                        {
                            "id": 4, 
                            "createTime": null, 
                            "updateTime": null, 
                            "name": "藍(lán)牙耳機(jī)", 
                            "picture": {
                                "id": 3, 
                                "createTime": null, 
                                "updateTime": null, 
                                "absolutePath": "D:\\files\\trembling-bird\\category-preview\\2.jpg"
                            }, 
                            "sortNumber": null, 
                            "subCommodityCategories": [ ]
                        }, 
                        {
                            "id": 5, 
                            "createTime": null, 
                            "updateTime": null, 
                            "name": "手機(jī)殼/保護(hù)殼", 
                            "picture": {
                                "id": 4, 
                                "createTime": null, 
                                "updateTime": null, 
                                "absolutePath": "D:\\files\\trembling-bird\\category-preview\\3.png"
                            }, 
                            "sortNumber": null, 
                            "subCommodityCategories": [ ]
                        }, 
                        {
                            "id": 6, 
                            "createTime": null, 
                            "updateTime": null, 
                            "name": "手機(jī)貼膜", 
                            "picture": {
                                "id": 5, 
                                "createTime": null, 
                                "updateTime": null, 
                                "absolutePath": "D:\\files\\trembling-bird\\category-preview\\4.jpg"
                            }, 
                            "sortNumber": null, 
                            "subCommodityCategories": [ ]
                        }
                    ]
                }, 
                {
                    "id": 7, 
                    "createTime": null, 
                    "updateTime": null, 
                    "name": "運(yùn)營(yíng)商", 
                    "picture": null, 
                    "sortNumber": null, 
                    "subCommodityCategories": [
                        {
                            "id": 8, 
                            "createTime": null, 
                            "updateTime": null, 
                            "name": "辦號(hào)卡", 
                            "picture": {
                                "id": 6, 
                                "createTime": null, 
                                "updateTime": null, 
                                "absolutePath": "D:\\files\\trembling-bird\\category-preview\\5.png"
                            }, 
                            "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.jpg"
                            }, 
                            "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.jpg"
                            }, 
                            "sortNumber": null, 
                            "subCommodityCategories": [ ]
                        }
                    ]
                }
            ]
        }
    ]
}

以上就是MyBatis實(shí)現(xiàn)遞歸查詢的方法詳解的詳細(xì)內(nèi)容，更多關(guān)于MyBatis遞歸查詢的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: