当当网Day2-Day3

最新推荐文章于 2024-09-12 18:58:25 发布

水自流.

最新推荐文章于 2024-09-12 18:58:25 发布

阅读量169

点赞数

分类专栏：笔记文章标签： linq c#

本文链接：https://blog.csdn.net/Z2424858916/article/details/125195831

版权

笔记专栏收录该内容

5 篇文章 0 订阅

订阅专栏

该博客介绍了如何在Spring Boot中实现根据一级和二级分类进行图书查询的分页功能。涉及了Mapper接口的定义，如根据编辑推荐、热销推荐等条件查询图书，以及查询一级和二级分类的总条数。同时，展示了Controller层的实现，包括分页查询的方法和JSP页面的展示逻辑。此外，还展示了如何通过JSP遍历分类和子分类并生成链接。

摘要由CSDN通过智能技术生成

根据一级标题查询书本的信息

根据一级标题查询二级标题的信息

根据二级标题查询书本的信息

根据二级标题查询一级标题的信息

分页查询

Mapper

    //编辑推荐 根据销量查询前二少
    public List<Book> selectBookByEdit();
    //热销推荐 根据销量查询前三多
    public List<Book> selectBookByHotBuy();
    //最新上架 根据日期查询前三新
    public List<Book> selectBookByNewDate();
    //新书热卖 根据日期降序销量降序取前二
    public List<Book> selectBookByNewDateAndHotBuy();
    //根据id查询
    public Book selectBookById(Integer id);
    //查询一级类别分页展示数据
    List<Book> selectBookByPageLevelOne(@Param("categoryId") Integer categoryId, @Param("pageIndex") Integer pageIndex, @Param("pageSize") Integer pageSize);
    //查询二级类别 分页展示数据
    List<Book> selectBookByPageLevelTwo(@Param("categoryId") Integer categoryId, @Param("pageIndex") Integer pageIndex, @Param("pageSize") Integer pageSize);
    //查询一级类别总条数
    Integer selectBookByLevelOne(@Param("categoryId") Integer categoryId);
    //查询二级类别总条数
    Integer selectBookByLevelTwo(@Param("categoryId")Integer categoryId);


    public interface CategoryMapper {
    //查询所有类别
    public List<Category> selectAllCategory();
    //根据一级分类id查询二级分类
    public Category selectCategoryById(Integer id);
    //查询一级分类的图书
    public List<Book> selectBookByLevelOne(Integer id);
    //根据二级分类id查询一级分类
    public Integer selectLevelOneByTwo(Integer id);
    //根据二级分类查询图书
    public List<Book> selectBookByLevelTwo(Integer id);
    //查询二级分类的图书个数
    public List<Category> selectLevelTwoNum(Integer id);

}

SQL/Mapper

    Book：
    <select id="selectBookByPageLevelOne" resultMap="bookResultMap">
        select * from d_book
        where cid in
        (select category_id from d_category where parent_id=#{categoryId})
        limit #{pageIndex},#{pageSize}
    </select>
    <select id="selectBookByPageLevelTwo" resultMap="bookResultMap">
        select * from d_book
        where cid=#{categoryId}
        limit #{pageIndex},#{pageSize}
    </select>
    <select id="selectBookByLevelOne" resultType="java.lang.Integer">
        select count(*) from d_book
        where cid in
        (select category_id from d_category where parent_id=#{categoryId})
    </select>
    <select id="selectBookByLevelTwo" resultType="java.lang.Integer">
        select count(*) from d_book where cid=#{categoryId}
    </select>

    Category：
    <select id="selectAllCategory" resultMap="categoryResultMap">
        select father.*,child.category_id cid,child.category_name cname
        from d_category father inner join d_category child
        on child.parent_id = father.category_id
    </select>
    <select id="selectCategoryById" resultMap="categoryResultMap">
        select father.*,child.category_id cid,child.category_name cname
        from d_category father inner join d_category child
        on child.parent_id = father.category_id where father.category_id = #{id}
    </select>
    <select id="selectBookByLevelOne"             
        resultMap="com.baizhi.mapper.BookMapper.bookResultMap">
        select * from d_book where cid in
        (select child.category_id from d_category father INNER JOIN d_category child
        on child.parent_id = father.category_id where father.category_id = #{id})
    </select>
    <select id="selectLevelTwoNum" resultMap="categoryResultMap">
        select c.category_id,c.category_name,count(book_id) book_count from d_book b         
        RIGHT JOIN d_category c ON b.cid = c.category_id
        where c.parent_id=#{id} GROUP BY c.category_id
    </select>
    <select id="selectBookByLevelTwo" 
     resultMap="com.baizhi.mapper.BookMapper.bookResultMap">
        select * from d_book where cid=#{id}
    </select>
    <select id="selectLevelOneByTwo" resultType="java.lang.Integer">
        select father.category_id from d_category father left join d_category child on 
        father.category_id = child.parent_id where child.category_id = #{id}
    </select>

Controller

@RequestMapping("/showMain")
    public String showAll(Model model, HttpSession session) {
        User user = (User) session.getAttribute("user");
        if (user!=null){
            List<Book> editBooks = bookService.selectBookByEdit();
            List<Book> hotBooks = bookService.selectBookByHotBuy();
            List<Book> newBooks = bookService.selectBookByNewDate();
            List<Book> newHotBooks = bookService.selectBookByNewDateAndHotBuy();
            model.addAttribute("editBooks", editBooks);
            model.addAttribute("hotBooks", hotBooks);
            model.addAttribute("newBooks", newBooks);
            model.addAttribute("newHotBooks", newHotBooks);
            //类别
            List<Category> categories = categoryService.selectAllCategory();
            model.addAttribute("categories", categories);
            return "/main/main";
        }
        else {
            return "user/login_form";
        }
    }
//分页Controller实现
    @RequestMapping("selectBookByOne")
    private String selectBookByOne(Integer categoryId,Integer pageIndex,Integer pageSize,Model model) {
        //查询一级标题下的二级标题
        List<Category> categories = categoryService.selectLevelTwoNum(categoryId);
        model.addAttribute("categories", categories);

        Category category = categoryService.selectCategoryById(categoryId);
        model.addAttribute("category", category);

        List<Book> books = bookService.selectBookByPageLevelOne(categoryId, pageIndex, pageSize);
        model.addAttribute("books",books);

        //书本总数
        Integer integer = bookService.selectBookByLevelOne(categoryId);
        //页数
        int totalPage = 0;
        if (integer%pageSize==0){
            totalPage = integer/pageSize;
        }else {
            totalPage = integer/pageSize+1;
        }
        //当前页
        model.addAttribute("method","selectBookByOne.do");
        model.addAttribute("pageIndex",pageIndex);
        //categoryId
        model.addAttribute("c",categoryId);
        model.addAttribute("integer",integer);
        model.addAttribute("totalPage",totalPage);
        return "main/book_list";
    }

    @RequestMapping("selectBookByTwo")
    private String selectBookByTwo(Integer categoryId,Integer pageIndex,Integer pageSize,Model model) {

        Integer i = categoryService.selectLevelOneByTwo(categoryId);
        //查询分类
        Category category = categoryService.selectCategoryById(i);
        model.addAttribute("category", category);
        //查询二级分类的图书个数
        List<Category> categories = categoryService.selectLevelTwoNum(i);
        model.addAttribute("categories", categories);
        //查询二级分类的图书总个数
        Integer sum = 0;
        for (Category c : categories) {
            sum += c.getBookCount();
        }
        model.addAttribute("sum", sum);
        //二级标题下的所有图书
        List<Book> books = bookService.selectBookByPageLevelTwo(categoryId, pageIndex, pageSize);
        model.addAttribute("books",books);
        System.out.println(pageIndex);
        //书本总数
        Integer integer = bookService.selectBookByLevelTwo(categoryId);
        //页数
        int totalPage = 0;
        if (integer%pageSize==0){
            totalPage = integer/pageSize;
        }else {
            totalPage = integer/pageSize+1;
        }
        System.out.println(totalPage);
        model.addAttribute("method","selectBookByTwo.do");
        //当前页
        model.addAttribute("pageIndex",pageIndex);
        //categoryId
        model.addAttribute("c",categoryId);
        model.addAttribute("integer",integer);
        model.addAttribute("totalPage",totalPage);
        return "main/book_list";
    }

JSP

<c:forEach items="${categories}" var="category">
    [<a href='${pageContext.request.contextPath}/book/selectBookByOne.do?        
     categoryId=${category.id}&pageIndex=1&pageSize=5'>${category.name}</a>]
<c:forEach items="${category.child}" var="child">
    <a href='${pageContext.request.contextPath}/book/selectBookByTwo.do?categoryId=${child.id}&pageIndex=1&pageSize=5'>${child.name}</a>
</c:forEach>

<c:if test="${pageIndex>1}">
	<div class='list_r_title_text3a'>
			<a name=link_page_next href="${pageContext.request.contextPath}/book/${method}?categoryId=${c}&pageIndex=${pageIndex-1}&pageSize=5">
		<img src='../images/page_up.gif' /> </a>
	</div>
</c:if>