mybatis+mysql查询大类别下的所有图书(递归)

场景:查询某个大类别下所有类别(及类别下子类别..)下的所有图书(递归查询)[分页\排序]

实体类

public class Category implements Serializable {

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

    private List<Book> bookList;//类别下的图书集合


    public int getCount() {
        return count;
    }

    public void setCount(int count) {
        this.count = count;
    }

    public List<Book> getBookList() {
        return bookList;
    }

    public void setBookList(List<Book> bookList) {
        this.bookList = bookList;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    public List<Category> getCategoryList() {
        return categoryList;
    }

    public void setCategoryList(List<Category> categoryList) {
        this.categoryList = categoryList;
    }

    @Override
    public String toString() {
        return "Category{" +
                "id=" + id +
                ", cname='" + cname + '\'' +
                ", count=" + count +
                ", categoryList=" + categoryList +
                ", bookList=" + bookList +
                '}';
    }
}

dao

//根据类别查找图书1：按上架时间排序 2：按销量排序（热度） 3价格排序
    //ispa 0:无父类别 1：有父类别
    public List<Category> books(@Param("categoryId") int categoryId,
                                @Param("isPa") int isPa,
                                @Param("orderBy") int orderBy,
                                @Param("startPos") int startPos);

    //查询类别下图书数量
    public int count(@Param("categoryId") int categoryId,
                     @Param("isPa") int isPa);

mapper

<!--查询类别下的图书-->
    <resultMap id="categoryMap3" type="Category">
        <collection property="bookList" ofType="Book">
            <id property="bookid" column="b_id"></id>
            <result property="bname" column="bname"></result>
            <result property="author" column="author"></result>
            <result property="publisher" column="publisher"></result>
            <result property="publishTime" column="publish_time"></result>
            <result property="brief" column="brief"></result>
            <result property="price" column="price"></result>
            <result property="ddprice" column="ddprice"></result>
            <result property="img" column="img"></result>
        </collection>
    </resultMap>

    <select id="books" resultMap="categoryMap3">
        SELECT *
        FROM dcategory c, dbook b
        WHERE c.id = b.category_id
        <if test="isPa==0">
            AND c.parent_id = #{categoryId}
        </if>
        <if test="isPa==1">
            AND c.id = #{categoryId}
        </if>
        <if test="orderBy==1">
            ORDER BY b.publish_time DESC
        </if>
        <if test="orderBy==2">
            ORDER BY b.sales DESC
        </if>
        <if test="orderBy==3">
            ORDER BY b.ddprice DESC
        </if>
        LIMIT #{startPos},4
    </select>

    <select id="count" resultType="int">
        SELECT count(*)
        FROM dcategory c, dbook b
        WHERE c.id = b.category_id
        <if test="isPa==0">
            AND c.parent_id = #{categoryId}
        </if>
        <if test="isPa==1">
            AND c.id = #{categoryId}
        </if>
    </select>

分页工具类在博客列表

service

@Override
 public Page<Category> getBooks(int categoryId,
                                   int isPa,
                                   int orderBy,
                                   int pageNow) {
        int totalCount = categoryDao.count(categoryId, isPa);
        Page<Category> categoryPage = new Page<Category>(totalCount, pageNow);
        int startPos = categoryPage.getStartPos();
        List<Category> categoryList = categoryDao.books(categoryId, isPa, orderBy, startPos);
        categoryPage.setItems(categoryList);
        return categoryPage;
    }