根据一级标题查询书本的信息
根据一级标题查询二级标题的信息
根据二级标题查询书本的信息
根据二级标题查询一级标题的信息
分页查询
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>