千峰商城-springboot项目搭建-62-分类商品推荐数据库实现
按照商品的分类（一级分类）推销销量最高的6个商品。
一、流程分析
加载分类商品推荐有两种实现方案：
方案一：当加载首页时不加载分类的推荐商品，监听进度条滚动事件，当进度条触底（滚动指定的距离），就触发分类推荐商品的加载，每次只加载一个分类的商品。
方案二：一次性加载所有分类的推荐商品，整体进行初始化。
（1）方案一：
首页 ——————一级分类的ID——————> 分类推荐商品
index.html <————某个分类下的推荐商品———— 接口
1.获取一级分类id
2.查询当前一级分类下销量最高的6个商品
3.返回查询到的6个商品。
（2）方案二：
首页 ————————ajax————————> 分类推荐商品
index.html <————所有分类下的推荐商品———— 接口
1.分别获取所有一级分类下的销量最高的6个商品
2.返回查询到的所有分类下的6个商品
二、数据库实现SQL
1.数据准备
2.查询sql
#子查询： #查询所有的一级分类 SELECT * FROM category WHERE category_level=1; #查询每个分类项下销量前6的商品 SELECT * FROM product WHERE root_category_id=2 ORDER BY sold_num DESC LIMIT 0,6; #查询每个商品的图片 SELECT * FROM product_img WHERE item_id=1; #连接查询： SELECT * FROM category c INNER JOIN product p ON p.root_category_id=c.category_id WHERE category_level=1 ORDER BY p.sold_num DESC LIMIT 0,6;
三、数据库实现DAO及映射配置
1.实体类：
@Data @AllArgsConstructor @NoArgsConstructor @ToString public class CategoryVO { private Integer categoryId; private String categoryName; private Integer categoryLevel; private Integer parentId; private String categoryIcon; private String categorySlogan; private String categoryPic; private String categoryBgColor; //实现首页分类商品推荐 private List<ProductVO> products; //实现首页类别显示 private List<CategoryVO> categories; }
2.在mapper接口中定义查询方法
@Repository public interface CategoryMapper extends GeneralDAO<Category> { //1.使用连接查询实现分类查询 public List<CategoryVO> selectAllCategories(); //2.子查询，就是根据parentId查询子分类 public List<CategoryVO> selectAllCategories2(int parentId); //查询一级类别 public List<CategoryVO> selectFirstLevelCategories(); }
ProductMapper ：
@Repository public interface ProductMapper extends GeneralDAO<Product> { public List<ProductVO> selectRecommendProducts(); //查询指定一级类别下销量最高的6个商品 public List<ProductVO> selectTop6ByCategory(int cid); }
3.映射配置：
在查询商品的同时关联查询商品的图片
<?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.qfedu.fmmall.dao.ProductMapper"> <resultMap id="BaseResultMap" type="com.qfedu.fmmall.entity.Product"> <id column="product_id" jdbcType="VARCHAR" property="productId" /> <result column="product_name" jdbcType="VARCHAR" property="productName" /> <result column="category_id" jdbcType="INTEGER" property="categoryId" /> <result column="root_category_id" jdbcType="INTEGER" property="rootCategoryId" /> <result column="sold_num" jdbcType="INTEGER" property="soldNum" /> <result column="product_status" jdbcType="INTEGER" property="productStatus" /> <result column="create_time" jdbcType="TIMESTAMP" property="createTime" /> <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" /> <result column="content" jdbcType="LONGVARCHAR" property="content" /> </resultMap> <resultMap id="ProductVOMap" type="com.qfedu.fmmall.entity.ProductVO"> <id column="product_id" jdbcType="VARCHAR" property="productId" /> <result column="product_name" jdbcType="VARCHAR" property="productName" /> <result column="category_id" jdbcType="INTEGER" property="categoryId" /> <result column="root_category_id" jdbcType="INTEGER" property="rootCategoryId" /> <result column="sold_num" jdbcType="INTEGER" property="soldNum" /> <result column="product_status" jdbcType="INTEGER" property="productStatus" /> <result column="create_time" jdbcType="TIMESTAMP" property="createTime" /> <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" /> <result column="content" jdbcType="LONGVARCHAR" property="content" /> <collection property="imgs" select="com.qfedu.fmmall.dao.ProductImgMapper.selectProductImgByProductId" column="product_id"/> </resultMap> <select id="selectRecommendProducts" resultMap="ProductVOMap"> SELECT product_id, product_name, category_id, root_category_id, sold_num, product_status, content, create_time, update_time FROM product ORDER BY create_time DESC LIMIT 0,3; </select> <select id="selectTop6ByCategory" resultMap="ProductVOMap"> SELECT product_id, product_name, category_id, root_category_id, sold_num, product_status, content, create_time, update_time FROM product WHERE root_category_id=#{cid} ORDER BY sold_num DESC LIMIT 0,6; </select> </mapper>
在查询一级类别时，关联查询一级类别下销量最高的6个商品。
<?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.qfedu.fmmall.dao.CategoryMapper"> <resultMap id="BaseResultMap" type="com.qfedu.fmmall.entity.Category"> <!-- WARNING - @mbg.generated --> <id column="category_id" jdbcType="INTEGER" property="categoryId" /> <result column="category_name" jdbcType="VARCHAR" property="categoryName" /> <result column="category_level" jdbcType="INTEGER" property="categoryLevel" /> <result column="parent_id" jdbcType="INTEGER" property="parentId" /> <result column="category_icon" jdbcType="VARCHAR" property="categoryIcon" /> <result column="category_slogan" jdbcType="VARCHAR" property="categorySlogan" /> <result column="category_pic" jdbcType="VARCHAR" property="categoryPic" /> <result column="category_bg_color" jdbcType="VARCHAR" property="categoryBgColor" /> </resultMap> <resultMap id="categoryVOMap" type="com.qfedu.fmmall.entity.CategoryVO"> <!-- WARNING - @mbg.generated --> <id column="category_id1" jdbcType="INTEGER" property="categoryId" /> <result column="category_name1" jdbcType="VARCHAR" property="categoryName" /> <result column="category_level1" jdbcType="INTEGER" property="categoryLevel" /> <result column="parent_id1" jdbcType="INTEGER" property="parentId" /> <result column="category_icon1" jdbcType="VARCHAR" property="categoryIcon" /> <result column="category_slogan1" jdbcType="VARCHAR" property="categorySlogan" /> <result column="category_pic1" jdbcType="VARCHAR" property="categoryPic" /> <result column="category_bg_color1" jdbcType="VARCHAR" property="categoryBgColor" /> <collection property="categories" ofType="com.qfedu.fmmall.entity.CategoryVO" javaType="list"> <id column="category_id2" jdbcType="INTEGER" property="categoryId" /> <result column="category_name2" jdbcType="VARCHAR" property="categoryName" /> <result column="category_level2" jdbcType="INTEGER" property="categoryLevel" /> <result column="parent_id2" jdbcType="INTEGER" property="parentId" /> <collection property="categories" ofType="com.qfedu.fmmall.entity.CategoryVO" javaType="list"> <id column="category_id3" jdbcType="INTEGER" property="categoryId" /> <result column="category_name3" jdbcType="VARCHAR" property="categoryName" /> <result column="category_level3" jdbcType="INTEGER" property="categoryLevel" /> <result column="parent_id3" jdbcType="INTEGER" property="parentId" /> </collection> </collection> </resultMap> <select id="selectAllCategories" resultMap="categoryVOMap"> SELECT c1.category_id 'category_id1', c1.category_name 'category_name1', c1.category_level 'category_level1', c1.parent_id 'parent_id1', c1.category_icon 'category_icon1', c1.category_slogan 'category_slogan1', c1.category_pic 'category_pic1', c1.category_bg_color 'category_bg_color1', c2.category_id 'category_id2', c2.category_name 'category_name2', c2.category_level 'category_level2', c2.parent_id 'parent_id2', c3.category_id 'category_id3', c3.category_name 'category_name3', c3.category_level 'category_level3', c3.parent_id 'parent_id3' FROM category c1 INNER JOIN category c2 ON c2.parent_id=c1.category_id LEFT JOIN category c3 ON c3.parent_id=c2.category_id WHERE c1.category_level=1 </select> <resultMap id="categoryVOMap2" type="com.qfedu.fmmall.entity.CategoryVO"> <!-- WARNING - @mbg.generated --> <id column="category_id" jdbcType="INTEGER" property="categoryId" /> <result column="category_name" jdbcType="VARCHAR" property="categoryName" /> <result column="category_level" jdbcType="INTEGER" property="categoryLevel" /> <result column="parent_id" jdbcType="INTEGER" property="parentId" /> <result column="category_icon" jdbcType="VARCHAR" property="categoryIcon" /> <result column="category_slogan" jdbcType="VARCHAR" property="categorySlogan" /> <result column="category_pic" jdbcType="VARCHAR" property="categoryPic" /> <result column="category_bg_color" jdbcType="VARCHAR" property="categoryBgColor" /> <collection property="categories" column="category_id" select="com.qfedu.fmmall.dao.CategoryMapper.selectAllCategories2"/> </resultMap> <!-- 根据父级分类的id查询子级分类--> <select id="selectAllCategories2" resultMap="categoryVOMap2"> select category_id, category_name, category_level, parent_id, category_icon, category_slogan, category_pic, category_bg_color from category where parent_id=#{parentId} </select> <resultMap id="categoryVOMap3" type="com.qfedu.fmmall.entity.CategoryVO"> <!-- WARNING - @mbg.generated --> <id column="category_id" jdbcType="INTEGER" property="categoryId" /> <result column="category_name" jdbcType="VARCHAR" property="categoryName" /> <result column="category_level" jdbcType="INTEGER" property="categoryLevel" /> <result column="parent_id" jdbcType="INTEGER" property="parentId" /> <result column="category_icon" jdbcType="VARCHAR" property="categoryIcon" /> <result column="category_slogan" jdbcType="VARCHAR" property="categorySlogan" /> <result column="category_pic" jdbcType="VARCHAR" property="categoryPic" /> <result column="category_bg_color" jdbcType="VARCHAR" property="categoryBgColor" /> <collection property="products" select="com.qfedu.fmmall.dao.ProductMapper.selectTop6ByCategory" column="category_id"/> </resultMap> <select id="selectFirstLevelCategories" resultMap="categoryVOMap3"> select category_id, category_name, category_level, parent_id, category_icon, category_slogan, category_pic, category_bg_color FROM category WHERE category_level=1; </select> </mapper>
单元测试：
@Test public void testSelectFirstLevelCategory(){ List<CategoryVO> categoryVOS = categoryMapper.selectFirstLevelCategories(); for (CategoryVO categoryVO:categoryVOS){ System.out.println(categoryVO); } }
