功能:查询每个一级分类的最新6条商品数据
一、数据库表结构
create table `foodie-shop-dev`.category ( id int auto_increment comment ‘主键‘ primary key, name varchar(32) not null comment ‘分类名称‘, type int not null comment ‘分类类型‘, father_id int not null comment ‘父id‘, logo varchar(64) null comment ‘图标‘, slogan varchar(64) null comment ‘口号‘, cat_image varchar(64) null comment ‘分类图‘, bg_color varchar(32) null comment ‘背景颜色‘ ) comment ‘商品分类 ‘ charset = utf8mb4;
create table `foodie-shop-dev`.items ( id varchar(64) not null comment ‘商品主键id‘ primary key, item_name varchar(32) not null comment ‘商品名称 商品名称‘, cat_id int not null comment ‘分类外键id 分类id‘, root_cat_id int not null comment ‘一级分类外键id‘, sell_counts int not null comment ‘累计销售 累计销售‘, on_off_status int not null comment ‘上下架状态 上下架状态,1:上架 2:下架‘, content text not null comment ‘商品内容 商品内容‘, created_time datetime not null comment ‘创建时间‘, updated_time datetime not null comment ‘更新时间‘ ) comment ‘商品表‘ charset = utf8mb4;
create table `foodie-shop-dev`.items_img ( id varchar(64) not null comment ‘图片主键‘ primary key, item_id varchar(64) not null comment ‘商品外键id 商品外键id‘, url varchar(128) not null comment ‘图片地址 图片地址‘, sort int not null comment ‘顺序 图片顺序,从小到大‘, is_main int not null comment ‘是否主图 是否主图,1:是,0:否‘, created_time datetime not null comment ‘创建时间‘, updated_time datetime not null comment ‘更新时间‘ ) comment ‘商品图片 ‘ charset = utf8mb4;
二、自定义Sql查询语句
SELECT f.id as rootCatId, f.`name` as rootCatName, f.slogan as slogan, f.cat_image as catImage, f.bg_color as bgColor, i.id as itemId, i.item_name as itemName, ii.url as itemUrl, i.created_time as createdTime FROM category f LEFT JOIN items i ON f.id = i.root_cat_id LEFT JOIN items_img ii ON i.id = ii.item_id WHERE f.type = 1 AND i.root_cat_id = 7 AND ii.is_main = 1 ORDER BY i.created_time DESC LIMIT 0,6
三、Mapper层 xml文件和接口
1、接口定义(定义sql方法名称getSixNewItemsLazy,用在xml)
package com.imooc.mapper; import com.imooc.pojo.vo.CategoryVO; import com.imooc.pojo.vo.NewItemsVO; import org.apache.ibatis.annotations.Param; import java.util.List; import java.util.Map; public interface CategoryMapperCustom { public List<CategoryVO> getSubCatList(Integer rootCatId); public List<NewItemsVO> getSixNewItemsLazy(@Param("paramsMap") Map<String,Object> map); }
2、xml定义
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.imooc.mapper.CategoryMapperCustom" > <resultMap id="myCategoryVO" type="com.imooc.pojo.vo.CategoryVO"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="type" property="type"/> <result column="fatherId" property="fatherId"/> <!-- collection 标签:用于定义关联的list集合类型的封装规则 property:对应三级分类的list属性名 ofType:集合的类型,三级分类的vo --> <collection property="subCatList" ofType="com.imooc.pojo.vo.SubCategoryVO"> <id column="subId" property="subId"/> <result column="subName" property="subName"/> <result column="subType" property="subType"/> <result column="subFatherId" property="subFatherId"/> </collection> </resultMap> <select id="getSubCatList" resultMap="myCategoryVO" parameterType="int"> SELECT f.id AS id, f.`name` AS `name`, f.type AS type, f.father_id AS fatherId, c.id AS subId, c.NAME AS subName, c.type AS subType, c.father_id AS subFatherId FROM category f LEFT JOIN category c ON f.id = c.father_id WHERE f.father_id = #{rootCatId} </select> <resultMap id="myNewItemsVO" type="com.imooc.pojo.vo.NewItemsVO"> <id column="rootCatId" property="rootCatId"/> <result column="rootCatName" property="rootCatName"/> <result column="slogan" property="slogan"/> <result column="cat_image" property="cat_image"/> <result column="bgColor" property="bgColor"/> <collection property="simpleItemList" ofType="com.imooc.pojo.vo.SimpleItemVO"> <id column="itemId" property="itemId"/> <result column="itemName" property="itemName"/> <result column="itemUrl" property="itemUrl"/> </collection> </resultMap> <select id="getSixNewItemsLazy" resultMap="myNewItemsVO" parameterType="Map"> SELECT f.id as rootCatId, f.`name` as rootCatName, f.slogan as slogan, f.cat_image as catImage, f.bg_color as bgColor, i.id as itemId, i.item_name as itemName, ii.url as itemUrl, i.created_time as createdTime FROM category f LEFT JOIN items i ON f.id = i.root_cat_id LEFT JOIN items_img ii ON i.id = ii.item_id WHERE f.type = 1 AND i.root_cat_id = #{paramsMap.rootCatId} AND ii.is_main = 1 ORDER BY i.created_time DESC LIMIT 0,6 </select> </mapper>
3.VO对象定义(目录:com/imooc/pojo/vo/NewItemsVO.java和com/imooc/pojo/vo/SimpleItemVO.java)
package com.imooc.pojo.vo; import java.util.List; /** * 最新商品VO */ public class NewItemsVO { private Integer rootCatId; private String rootCatName; private String slogan; private String catImage; private String bgColor; private List<SimpleItemVO> simpleItemList; public Integer getRootCatId() { return rootCatId; } public void setRootCatId(Integer rootCatId) { this.rootCatId = rootCatId; } public String getRootCatName() { return rootCatName; } public void setRootCatName(String rootCatName) { this.rootCatName = rootCatName; } public String getSlogan() { return slogan; } public void setSlogan(String slogan) { this.slogan = slogan; } public String getCatImage() { return catImage; } public void setCatImage(String catImage) { this.catImage = catImage; } public String getBgColor() { return bgColor; } public void setBgColor(String bgColor) { this.bgColor = bgColor; } public List<SimpleItemVO> getSimpleItemList() { return simpleItemList; } public void setSimpleItemList(List<SimpleItemVO> simpleItemList) { this.simpleItemList = simpleItemList; } }
package com.imooc.pojo.vo; /** * 6个最新商品的简单数据类型 */ public class SimpleItemVO { private String itemId; private String itemName; private String itemUrl; public String getItemId() { return itemId; } public void setItemId(String itemId) { this.itemId = itemId; } public String getItemName() { return itemName; } public void setItemName(String itemName) { this.itemName = itemName; } public String getItemUrl() { return itemUrl; } public void setItemUrl(String itemUrl) { this.itemUrl = itemUrl; } }
四、service层实现
1、接口定义(目录:com/imooc/service/CategoryService.java)
package com.imooc.service; import com.imooc.pojo.Carousel; import com.imooc.pojo.Category; import com.imooc.pojo.vo.CategoryVO; import com.imooc.pojo.vo.NewItemsVO; import java.util.List; import java.util.Map; public interface CategoryService { /** *查询所有一级分类 * @return */ public List<Category> queryAllRootLeverCat(); /**根据一级分类查询 * * @param rootCatId * @return */ public List<CategoryVO> getSubCatList(Integer rootCatId); /** * 查询一级分类的前6个商品 * @param rootCatId * @return */ public List<NewItemsVO> getSixNewItemsLazy(Integer rootCatId); }
2、接口实现(目录:com.imooc.service.impl.CategoryServiceImpl)
package com.imooc.service.impl; import com.imooc.mapper.CarouselMapper; import com.imooc.mapper.CategoryMapper; import com.imooc.mapper.CategoryMapperCustom; import com.imooc.pojo.Carousel; import com.imooc.pojo.Category; import com.imooc.pojo.vo.CategoryVO; import com.imooc.pojo.vo.NewItemsVO; import com.imooc.service.CarouselService; import com.imooc.service.CategoryService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Propagation; import org.springframework.transaction.annotation.Transactional; import tk.mybatis.mapper.entity.Example; import java.util.HashMap; import java.util.List; import java.util.Map; @Service public class CategoryServiceImpl implements CategoryService { @Autowired private CategoryMapper categoryMapper; @Autowired private CategoryMapperCustom categoryMapperCustom; @Transactional(propagation = Propagation.SUPPORTS) @Override public List<CategoryVO> getSubCatList(Integer rootCatId) { return categoryMapperCustom.getSubCatList(rootCatId); } @Transactional(propagation = Propagation.SUPPORTS) @Override public List<Category> queryAllRootLeverCat() { Example example = new Example(Carousel.class); Example.Criteria criteria = example.createCriteria(); criteria.andEqualTo("type",1); List<Category> resList= categoryMapper.selectByExample(example); return resList; } @Transactional(propagation = Propagation.SUPPORTS) @Override public List<NewItemsVO> getSixNewItemsLazy(Integer rootCatId) { Map<String,Object> map=new HashMap<>(); map.put("rootCatId",rootCatId); return categoryMapperCustom.getSixNewItemsLazy(map); } }
五、API层实现
方法:sixNewItems
目录:com.imooc.controller.IndexController
package com.imooc.controller; import com.imooc.enums.YesOrNo; import com.imooc.pojo.Carousel; import com.imooc.pojo.Category; import com.imooc.pojo.vo.CategoryVO; import com.imooc.pojo.vo.NewItemsVO; import com.imooc.service.CarouselService; import com.imooc.service.CategoryService; import com.imooc.utils.IMOOCJSONResult; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import io.swagger.annotations.ApiParam; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import springfox.documentation.annotations.ApiIgnore; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpSession; import java.util.List; @Api(value = "首页",tags = "首页展示的相关接口") @RestController @RequestMapping("index") public class IndexController { @Autowired private CarouselService carouselService; @Autowired private CategoryService categoryService; @ApiOperation(value="获取首页轮播图列表",notes = "获取首页轮播图列表",httpMethod = "GET") @GetMapping("/carouse") public IMOOCJSONResult carouse() { List<Carousel> list= carouselService.queryAll(YesOrNo.YES.type); return IMOOCJSONResult.ok(list); } @ApiOperation(value="获取商品分类(一级分类)",notes = "获取商品分类(一级分类)",httpMethod = "GET") @GetMapping("/cats") public IMOOCJSONResult cats() { List<Category> list= categoryService.queryAllRootLeverCat(); return IMOOCJSONResult.ok(list); } @ApiOperation(value="获取商品子分类",notes = "获取商品子分类",httpMethod = "GET") @GetMapping("/subCat/{rootCatId}") public IMOOCJSONResult subCat( @ApiParam(name="rootCatId",value = "一级分类id",required = true) @PathVariable Integer rootCatId) { if(rootCatId ==null){ return IMOOCJSONResult.errorMsg("分类不存在"); } List<CategoryVO> list= categoryService.getSubCatList(rootCatId); return IMOOCJSONResult.ok(list); } @ApiOperation(value="查询每个一级分类的最新6条商品数据",notes = "查询每个一级分类的最新6条商品数据",httpMethod = "GET") @GetMapping("/sixNewItems/{rootCatId}") public IMOOCJSONResult sixNewItems( @ApiParam(name="rootCatId",value = "一级分类id",required = true) @PathVariable Integer rootCatId) { if(rootCatId ==null){ return IMOOCJSONResult.errorMsg("分类不存在"); } List<NewItemsVO> list= categoryService.getSixNewItemsLazy(rootCatId); return IMOOCJSONResult.ok(list); } }