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

快来打我* 2022-06-04 01:15 208阅读 0赞

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

### 实体类 ###

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