千峰商城-springboot项目搭建-62-分类商品推荐数据库实现

最新推荐文章于 2022-08-19 11:31:47 发布

临易i

最新推荐文章于 2022-08-19 11:31:47 发布

阅读量379

点赞数

本文链接：https://blog.csdn.net/LYly_B/article/details/125878842

版权

按照商品的分类(一级分类)推销销量最高的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);
        }
    }

临易i

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
千峰商城-springboot项目搭建-62-分类商品推荐数据库实现

按照商品的分类(一级分类)推销销量最高的6个商品。一、流程分析加载分类商品推荐有两种实现方案：方案一：当加载首页时不加载分类的推荐商品，监听进度条滚动事件，当进度条触底(滚动指定的距离)，就触发分类推荐商品的加载，每次只加载一个分类的商品。方案二：一次性加载所有分类的推荐商品，整体进行初始化。(1)方案一：首页 ——————一级分类的ID——...
复制链接

扫一扫