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