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

以你之姓@ 2022-06-04 01:15 489阅读 0赞

## 场景: ##

#### 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递归查找其下的所有子类别**

<resultMap id="categoryMap1" type="Category">
            <id property="id" column="id"></id>
            <result property="cname" column="cname"></result>
            <result property="count" column="count"></result>
            <collection property="categoryList" ofType="Category" javaType="java.util.List" column="id" select="getById"/>
        </resultMap>
    
        
        <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());
                }
    
            }
        }

![这里写图片描述][SouthEast]

[SouthEast]: /images/20220604/3b538c801d3246fd87b5707792ad08ba.png