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

场景:

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>

<!--根据父类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());
            }

        }
    }