mybatis+mysql查询类别下的所有子类别(递归)

﹏ヽ暗。殇╰゛Y 2022-03-06 08:52 1139阅读 0赞

转自：[https://blog.csdn.net/menglinjie/article/details/78676117][https_blog.csdn.net_menglinjie_article_details_78676117]

场景:  
1. 查询所有类别(大类别+子类别+子类别下的所有类别..);  
2. 查询某个类别下的所有子类别;  
3. 递归查询;  
亲测可用,亲手总结!

实体类(省略get set方法)  
public class Category implements Serializable \{

private int id;  
    private String cname;  
    private int count;//类别数量  
    private List<Category> categoryList;//子类类别集合

// private List<Book> bookList;//类别下的图书集合  
\}  
  
dao层  
当id=0时查找所有类别,id不为0时查找id下的自类别

public interface CategoryDao \{

//查看所有类别（id=0）（或指定类别下所有子类别）  
    public List<Category> getAll(@Param("categoryId") int categoryId);  
  
mapper  
先根据id查找到大类别,然后根据查找到的类别id递归查找其下的所有子类别

<select id="getById" resultMap="categoryMap1" parameterType="int">  
        SELECT \*  
        FROM dcategory  
        WHERE parent\_id = \#\{id\}  
    </select>

<select id="getAll" resultMap="categoryMap1" parameterType="int">  
        SELECT \*  
        FROM dcategory  
        WHERE 1 = 1  
        <choose>  
            <when test="categoryId ==0">  
                AND dcategory.parent\_id IS NULL  
            </when>  
            <otherwise>  
                AND id = \#\{categoryId\}  
            </otherwise>  
        </choose>  
    </select>  
  
测试  
@Test  
    public void testGetAll() \{  
        List<Category> categories = categoryDao.getAll(1);  
        System.out.println("测试获取所有类别========》" + categories);

for (Category c : categories  
                ) \{  
            System.out.println(c.getCname() + "=========" + c.getCount());  
            List<Category> childs = c.getCategoryList();  
            for (Category c1 : childs  
                    ) \{  
                System.out.println(c1.getCname());  
            \}

\}  
    \}

\---------------------   
作者：孟林洁   
来源：CSDN   
原文：https://blog.csdn.net/menglinjie/article/details/78676117   
版权声明：本文为博主原创文章，转载请附上博文链接！

1.java+js递归循环树实现无限下拉菜单：[https://blog.csdn.net/lianzhang861/article/details/83783796][https_blog.csdn.net_lianzhang861_article_details_83783796]

2.Mybatis递归查询并自动装填所有子节点：[https://blog.csdn.net/lianzhang861/article/details/86243532][https_blog.csdn.net_lianzhang861_article_details_86243532]

3. mybatis+mysql查询类别下的所有子类别(递归)：[https://blog.csdn.net/menglinjie/article/details/78676117][https_blog.csdn.net_menglinjie_article_details_78676117]

4.Mybatis自查询递归查找子菜单：[https://blog.csdn.net/frankcheng5143/article/details/72723958][https_blog.csdn.net_frankcheng5143_article_details_72723958]

5. Mybatis实现部门递归查询： [https://blog.csdn.net/qq\_37342374/article/details/79643291][https_blog.csdn.net_qq_37342374_article_details_79643291]

6. Mybatis之高级映射collection （递归查出树形数据之查询部门及部门下所有人员 单节点）：[https://blog.csdn.net/FANTASY522272820/article/details/70053449][https_blog.csdn.net_FANTASY522272820_article_details_70053449]

7.MyBatis 关联关系查询 自关联 一对多查询指定父节点的所有子节点：[https://www.2cto.com/kf/201704/625280.html][https_www.2cto.com_kf_201704_625280.html]

8. mybatis递归查询所有子节点： [https://blog.csdn.net/QQ\_1094428625/article/details/82767340][https_blog.csdn.net_QQ_1094428625_article_details_82767340]

9. mysql -- 递归查询所有子节点：[https://blog.csdn.net/bob\_baobao/article/details/78104504][https_blog.csdn.net_bob_baobao_article_details_78104504]

10. mysql查询所有子节点，后代： [https://blog.csdn.net/xiaofanren1111/article/details/79711774][https_blog.csdn.net_xiaofanren1111_article_details_79711774]

11. Mybatis中实现递归查询： [https://blog.csdn.net/janet796/article/details/79500349][https_blog.csdn.net_janet796_article_details_79500349]

12.[查询树节点以及所有子节点][Link 1]：[https://sshitaime.iteye.com/blog/2019796][Link 1]

13. mysql树节点【所有子节点列表 and 查询所有父节点列表】：[https://blog.csdn.net/u014033756/article/details/70567746][https_blog.csdn.net_u014033756_article_details_70567746]

14.mybatis+mysql递归查询：[https://blog.csdn.net/make\_a\_difference/article/details/51778233][https_blog.csdn.net_make_a_difference_article_details_51778233]

15.mysql中树状表的所有子节点递归查询和所有父节点递归查询方法： [https://blog.csdn.net/abc286229825/article/details/78905673][https_blog.csdn.net_abc286229825_article_details_78905673]

16.MyBatis中动态SQL语句完成多条件查询：[https://blog.csdn.net/qq\_21454973/article/details/78096832][https_blog.csdn.net_qq_21454973_article_details_78096832]

17.mybatis如何实现递归查询：[https://bbs.csdn.net/topics/392208379?page=1][https_bbs.csdn.net_topics_392208379_page_1]

18.Mybatis之高级映射collection ：[https://blog.csdn.net/u014774073/article/details/78119954][https_blog.csdn.net_u014774073_article_details_78119954]

递归SQL：

WITH RECURSIVE r AS (  
    SELECT \* FROM m\_am WHERE m.attri\_id = 346  
       union  ALL  
    SELECT m1.\* FROM m\_am1, r WHERE m1.attri\_pid = r.attri\_id  
    )  
    SELECT r.attri\_id FROM r ORDER BY attri\_id;  
          
          
        WITH RECURSIVE r AS (  
        SELECT \* FROM ml m where m.ml\_id = 1  
           union all   
             SELECT m1.\* from ml m1 , r where m1.ml\_pid = r.ml\_id  
          
        )  
        SELECT r.\*    from r ORDER BY ml\_id;

<select id="findByModelIds" parameterType="java.util.List" resultMap="BaseResultMap">
       WITH RECURSIVE r AS (
      SELECT * FROM model_attribute m
      where m.model_id in
      <foreach item="list" collection="list" open="(" separator="," close=")">
        #{list}
      </foreach>
      union  ALL
      SELECT m1.* FROM model_attribute m1, r WHERE m1.attri_pid = r.attri_id
      )
      SELECT r.* FROM r ORDER BY attri_id
    </select>

[https_blog.csdn.net_menglinjie_article_details_78676117]: https://blog.csdn.net/menglinjie/article/details/78676117
[https_blog.csdn.net_lianzhang861_article_details_83783796]: https://blog.csdn.net/lianzhang861/article/details/83783796
[https_blog.csdn.net_lianzhang861_article_details_86243532]: https://blog.csdn.net/lianzhang861/article/details/86243532
[https_blog.csdn.net_frankcheng5143_article_details_72723958]: https://blog.csdn.net/frankcheng5143/article/details/72723958
[https_blog.csdn.net_qq_37342374_article_details_79643291]: https://blog.csdn.net/qq_37342374/article/details/79643291
[https_blog.csdn.net_FANTASY522272820_article_details_70053449]: https://blog.csdn.net/FANTASY522272820/article/details/70053449
[https_www.2cto.com_kf_201704_625280.html]: https://www.2cto.com/kf/201704/625280.html
[https_blog.csdn.net_QQ_1094428625_article_details_82767340]: https://blog.csdn.net/QQ_1094428625/article/details/82767340
[https_blog.csdn.net_bob_baobao_article_details_78104504]: https://blog.csdn.net/bob_baobao/article/details/78104504
[https_blog.csdn.net_xiaofanren1111_article_details_79711774]: https://blog.csdn.net/xiaofanren1111/article/details/79711774
[https_blog.csdn.net_janet796_article_details_79500349]: https://blog.csdn.net/janet796/article/details/79500349
[Link 1]: https://sshitaime.iteye.com/blog/2019796
[https_blog.csdn.net_u014033756_article_details_70567746]: https://blog.csdn.net/u014033756/article/details/70567746
[https_blog.csdn.net_make_a_difference_article_details_51778233]: https://blog.csdn.net/make_a_difference/article/details/51778233
[https_blog.csdn.net_abc286229825_article_details_78905673]: https://blog.csdn.net/abc286229825/article/details/78905673
[https_blog.csdn.net_qq_21454973_article_details_78096832]: https://blog.csdn.net/qq_21454973/article/details/78096832
[https_bbs.csdn.net_topics_392208379_page_1]: https://bbs.csdn.net/topics/392208379?page=1
[https_blog.csdn.net_u014774073_article_details_78119954]: https://blog.csdn.net/u014774073/article/details/78119954