Mybatis中实现递归查询

Qfoom

已于 2022-12-16 14:34:29 修改

阅读量2.6k

点赞数

分类专栏： mysql # mybatis-plus 文章标签： mybatis java

于 2021-07-22 17:40:08 首次发布

本文链接：https://blog.csdn.net/Qfoom/article/details/119003817

版权

mysql 同时被 2 个专栏收录

7 篇文章

订阅专栏

mybatis-plus

1 篇文章

订阅专栏

本文介绍了如何在Mybatis中利用递归查询机制，通过`parentid`字段查询出商品分类树结构，包括一个示例及SQL语句。展示了如何定义 resultMap 和 select 映射，以及在Mapper接口中的方法实现，适用于数据结构中存在层级关系的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

https://blog.csdn.net/janet796/article/details/79500349

在Mybatis中实现递归查询，父类嵌套子类

swagger测试结果：

{
  "status": 0,
  "data": {
    "pageNum": 1,
    "pageSize": 11,
    "size": 2,
    "startRow": 1,
    "endRow": 2,
    "total": 2,
    "pages": 1,
    "list": [
      {
        "goodscateid": 1,
        "name": "食品类",
        "parentid": 0,
        "description": "11111111111111111",
        "displayorder": 1,
        "commissionrate": null,
        "enabled": 1,
        "catelist": [
          {
            "goodscateid": 2,
            "name": "生鲜类",
            "parentid": 1,
            "description": "222222222222222",
            "displayorder": 2,
            "commissionrate": null,
            "enabled": 1,
            "catelist": [
              {
                "goodscateid": 3,
                "name": "海鲜类",
                "parentid": 2,
                "description": "3333333",
                "displayorder": 3,
                "commissionrate": null,
                "enabled": 1,
                "catelist": []
              }
            ]
          }
        ]
      },
      {
        "goodscateid": 4,
        "name": "服装类",
        "parentid": 0,
        "description": "服装生成商",
        "displayorder": 2,
        "commissionrate": null,
        "enabled": 1,
        "catelist": []
      }
    ],
    "prePage": 0,
    "nextPage": 0,
    "isFirstPage": true,
    "isLastPage": true,
    "hasPreviousPage": false,
    "hasNextPage": false,
    "navigatePages": 8,
    "navigatepageNums": [
      1
    ],
    "navigateFirstPage": 1,
    "navigateLastPage": 1,
    "firstPage": 1,
    "lastPage": 1
  }
}

其中 parentid为父类goodscateid，默认无父类则parentid=0

实现如下：

    <resultMap id="getSelf" type="net.chunxiao.vo.GoodsCategoryVo">
        <id column="goodscateid" property="goodscateid"></id>
        <result column="name" property="name"></result>
        <collection property="catelist" select="getCategory" column="goodscateid"></collection>
        <!--查到的cid作为下次的pid-->
    </resultMap>
 
    <select id="getCategory" resultMap="getSelf">
        select * from goods_category where status=0 AND parentid=#{pid}
        ORDER BY displayorder,goodscateid
    </select>

mapper中方法为：

List<GoodsCategoryVo> getCategory(Integer pid);

pid为从parentid为几级的开始查询，如从根部开始即传pid=0

VO属性如下：

public class GoodsCategoryVo {
    private Integer goodscateid;
    private String name;
    private Integer parentid;
    private String description;
    private Integer displayorder;
    private Double commissionrate;
    private Integer enabled;
    private List<GoodsCategoryVo> catelist;
……
}

第二种:先用parent_id找到所有的子部门id ,在拿子部门id当做父级id一层层向下遍历.通过函数方式

    /**
     * 递归根据父节点下的一级节点遍历出全部节点
     * @param total  初始为空
     * @param stringList
     * @return 存放所有部门id 的集合
     */
    public Set<String> fibonacci(Set<String> total, Set<String> stringList) {// 5个
        total.addAll(stringList);
        for (String s : stringList) {
            Set<String> departmentIds = depotDao.getDepartmentIds(s);
            if (departmentIds.size() == 0){
                continue;
            }else {
                total.addAll(departmentIds);
                //        自己调用自己;      fibonacci(total,departmentIds);
                fibonacci(total,departmentIds);
            }
        }
        return  total;
    }

SQL:

    <select id="getDepartmentIds" resultType="java.lang.String">

        SELECT department_id from  NameTable
where  parent_id =  #{pid}
GROUP BY department_id


    </select>