商品推荐和分类商品推荐

商品推荐：

数据库实现：

简单的商品推荐算法：

 

 在product新增List<ProductImg> imgs 用于存储商品的图片

package com.qfedu.fmmall.entity;

import javax.persistence.Column;
import javax.persistence.Id;
import java.util.Date;
import java.util.List;

public class ProductVO {

    @Override
    public String toString() {
        return "ProductVO{" +
                "productId='" + productId + '\'' +
                ", productName='" + productName + '\'' +
                ", categoryId=" + categoryId +
                ", rootCategoryId=" + rootCategoryId +
                ", soldNum=" + soldNum +
                ", productStatus=" + productStatus +
                ", createTime=" + createTime +
                ", updateTime=" + updateTime +
                ", imgs=" + imgs +
                ", content='" + content + '\'' +
                '}';
    }

    /**
     * 商品主键id
     */
    @Id
    @Column(name = "product_id")
    private String productId;

    /**
     * 商品名称 商品名称
     */
    @Column(name = "product_name")
    private String productName;

    /**
     * 分类外键id 分类id
     */
    @Column(name = "category_id")
    private Integer categoryId;

    /**
     * 一级分类外键id 一级分类id，用于优化查询
     */
    @Column(name = "root_category_id")
    private Integer rootCategoryId;

    /**
     * 销量 累计销售
     */
    @Column(name = "sold_num")
    private Integer soldNum;

    /**
     * 默认是1，表示正常状态, -1表示删除, 0下架 默认是1，表示正常状态, -1表示删除, 0下架
     */
    @Column(name = "product_status")
    private Integer productStatus;

    /**
     * 创建时间
     */
    @Column(name = "create_time")
    private Date createTime;

    /**
     * 更新时间
     */
    @Column(name = "update_time")
    private Date updateTime;

    private List<ProductImg> imgs;

    public List<ProductImg> getImgs() {
        return imgs;
    }

    public void setImgs(List<ProductImg> imgs) {
        this.imgs = imgs;
    }

    /**
     * 商品内容 商品内容
     */
    private String content;

    /**
     * 获取商品主键id
     *
     * @return product_id - 商品主键id
     */
    public String getProductId() {
        return productId;
    }

    /**
     * 设置商品主键id
     *
     * @param productId 商品主键id
     */
    public void setProductId(String productId) {
        this.productId = productId;
    }

    /**
     * 获取商品名称 商品名称
     *
     * @return product_name - 商品名称 商品名称
     */
    public String getProductName() {
        return productName;
    }

    /**
     * 设置商品名称 商品名称
     *
     * @param productName 商品名称 商品名称
     */
    public void setProductName(String productName) {
        this.productName = productName;
    }

    /**
     * 获取分类外键id 分类id
     *
     * @return category_id - 分类外键id 分类id
     */
    public Integer getCategoryId() {
        return categoryId;
    }

    /**
     * 设置分类外键id 分类id
     *
     * @param categoryId 分类外键id 分类id
     */
    public void setCategoryId(Integer categoryId) {
        this.categoryId = categoryId;
    }

    /**
     * 获取一级分类外键id 一级分类id，用于优化查询
     *
     * @return root_category_id - 一级分类外键id 一级分类id，用于优化查询
     */
    public Integer getRootCategoryId() {
        return rootCategoryId;
    }

    /**
     * 设置一级分类外键id 一级分类id，用于优化查询
     *
     * @param rootCategoryId 一级分类外键id 一级分类id，用于优化查询
     */
    public void setRootCategoryId(Integer rootCategoryId) {
        this.rootCategoryId = rootCategoryId;
    }

    /**
     * 获取销量 累计销售
     *
     * @return sold_num - 销量 累计销售
     */
    public Integer getSoldNum() {
        return soldNum;
    }

    /**
     * 设置销量 累计销售
     *
     * @param soldNum 销量 累计销售
     */
    public void setSoldNum(Integer soldNum) {
        this.soldNum = soldNum;
    }

    /**
     * 获取默认是1，表示正常状态, -1表示删除, 0下架 默认是1，表示正常状态, -1表示删除, 0下架
     *
     * @return product_status - 默认是1，表示正常状态, -1表示删除, 0下架 默认是1，表示正常状态, -1表示删除, 0下架
     */
    public Integer getProductStatus() {
        return productStatus;
    }

    /**
     * 设置默认是1，表示正常状态, -1表示删除, 0下架 默认是1，表示正常状态, -1表示删除, 0下架
     *
     * @param productStatus 默认是1，表示正常状态, -1表示删除, 0下架 默认是1，表示正常状态, -1表示删除, 0下架
     */
    public void setProductStatus(Integer productStatus) {
        this.productStatus = productStatus;
    }

    /**
     * 获取创建时间
     *
     * @return create_time - 创建时间
     */
    public Date getCreateTime() {
        return createTime;
    }

    /**
     * 设置创建时间
     *
     * @param createTime 创建时间
     */
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    /**
     * 获取更新时间
     *
     * @return update_time - 更新时间
     */
    public Date getUpdateTime() {
        return updateTime;
    }

    /**
     * 设置更新时间
     *
     * @param updateTime 更新时间
     */
    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    /**
     * 获取商品内容 商品内容
     *
     * @return content - 商品内容 商品内容
     */
    public String getContent() {
        return content;
    }

    /**
     * 设置商品内容 商品内容
     *
     * @param content 商品内容 商品内容
     */
    public void setContent(String content) {
        this.content = content;
    }
}

在ProductMapper文件中加入方法:

package com.qfedu.fmmall.service;

import com.qfedu.fmmall.vo.R;

/**
 * @Description:
 * @Author : Jerry
 * @create : 2022-06-25 18:40
 */
public interface ProductService {

    public R listRecommendProducts();

}

productMapper.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.qfedu.fmmall.dao.ProductMapper" >
  <resultMap id="BaseResultMap" type="com.qfedu.fmmall.entity.Product" >
    <id column="product_id" property="productId" jdbcType="VARCHAR" />
    <result column="product_name" property="productName" jdbcType="VARCHAR" />
    <result column="category_id" property="categoryId" jdbcType="INTEGER" />
    <result column="root_category_id" property="rootCategoryId" jdbcType="INTEGER" />
    <result column="sold_num" property="soldNum" jdbcType="INTEGER" />
    <result column="product_status" property="productStatus" jdbcType="INTEGER" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
    <result column="content" property="content" jdbcType="LONGVARCHAR" />
  </resultMap>

  <resultMap id="ProductVOMap" type="com.qfedu.fmmall.entity.ProductVO" >
    <id column="product_id" property="productId" jdbcType="VARCHAR" />
    <result column="product_name" property="productName" jdbcType="VARCHAR" />
    <result column="category_id" property="categoryId" jdbcType="INTEGER" />
    <result column="root_category_id" property="rootCategoryId" jdbcType="INTEGER" />
    <result column="sold_num" property="soldNum" jdbcType="INTEGER" />
    <result column="product_status" property="productStatus" jdbcType="INTEGER" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
    <result column="content" property="content" jdbcType="LONGVARCHAR" />
    <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>
</mapper>

productImgMapper接口:（根据商品id查询当前商品的图片信息）

package com.qfedu.fmmall.dao;

import com.qfedu.fmmall.entity.ProductImg;
import com.qfedu.fmmall.general.GeneralDAO;

import java.util.List;

public interface ProductImgMapper extends GeneralDAO<ProductImg> {

    //根据商品id查询当前商品的图片信息
    public List<ProductImg> selectProductImgByProductId(int productId);

}

productImgMapper.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.qfedu.fmmall.dao.ProductImgMapper" >
  <resultMap id="BaseResultMap" type="com.qfedu.fmmall.entity.ProductImg" >
    <id column="id" property="id" jdbcType="VARCHAR" />
    <result column="item_id" property="itemId" jdbcType="VARCHAR" />
    <result column="url" property="url" jdbcType="VARCHAR" />
    <result column="sort" property="sort" jdbcType="INTEGER" />
    <result column="is_main" property="isMain" jdbcType="INTEGER" />
    <result column="created_time" property="createdTime" jdbcType="TIMESTAMP" />
    <result column="updated_time" property="updatedTime" jdbcType="TIMESTAMP" />
  </resultMap>

  <select id="selectProductImgByProductId" resultMap="BaseResultMap">
    select id,
      item_id,
      url,
      sort,
      is_main,
      created_time,
      updated_time
    from product_img
    where item_id = #{productId}
  </select>
</mapper>

测试：

@Test
    public void testRecommand(){
        List<ProductVO> productVOS = productMapper.selectRecommendProducts();
        for(ProductVO p:productVOS){
            System.out.println(p);
        }
    }

 测试结果：

ProductVO{productId='3', productName='咪咪虾条', categoryId=15, rootCategoryId=2, soldNum=199, productStatus=1, createTime=Mon Apr 26 15:58:09 CST 2021, updateTime=Sun Apr 25 15:58:11 CST 2021, imgs=[ProductImg{id='5', itemId='3', url='mmxt_1.png', sort=1, isMain=1, createdTime=Sun Apr 25 15:59:28 CST 2021, updatedTime=Sun Apr 25 15:59:31 CST 2021}, ProductImg{id='6', itemId='3', url='mmxt_2.png', sort=2, isMain=0, createdTime=Sun Apr 25 15:59:51 CST 2021, updatedTime=Sun Apr 25 15:59:54 CST 2021}], content='<img src="static/pimgs/mmxt_desc.jpg"/>'}
ProductVO{productId='2', productName='旺旺华夫饼', categoryId=22, rootCategoryId=1, soldNum=202, productStatus=1, createTime=Mon Apr 26 15:27:22 CST 2021, updateTime=Sun Apr 25 15:27:25 CST 2021, imgs=[ProductImg{id='3', itemId='2', url='hfb_1.png', sort=1, isMain=1, createdTime=Sat Apr 24 15:30:09 CST 2021, updatedTime=Sun Apr 25 15:30:19 CST 2021}, ProductImg{id='4', itemId='2', url='hfb_2.png', sort=2, isMain=0, createdTime=Sun Apr 25 15:30:45 CST 2021, updatedTime=Sun Apr 25 15:30:48 CST 2021}], content='旺旺华夫饼商品内容'}
ProductVO{productId='1', productName='奥利奥小饼干', categoryId=46, rootCategoryId=2, soldNum=196, productStatus=1, createTime=Mon Apr 26 15:20:51 CST 2021, updateTime=Fri Apr 23 15:20:58 CST 2021, imgs=[ProductImg{id='1', itemId='1', url='ala_1.png', sort=1, isMain=1, createdTime=Thu Apr 22 15:25:08 CST 2021, updatedTime=Thu Apr 22 15:25:15 CST 2021}, ProductImg{id='2', itemId='1', url='ala_2.png', sort=2, isMain=0, createdTime=Thu Apr 22 15:25:39 CST 2021, updatedTime=Thu Apr 22 15:25:44 CST 2021}], content='<label style="color:red;font-size:18px">1.全新包装</label><br/><img src="static/pimgs/ala_desc1.png"/><br/><label style="color:green;font-size:18px">2.全新口味</label><br/><img src="static/pimgs/ala_desc2.png"/><label style="color:blue;font-size:18px">3.全新体验</label><br/><img src="static/pimgs/ala_desc3.png"/>'}

分类商品推荐：

 流程分析：

 

 

 

 

 封装实体类：

package com.qfedu.fmmall.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.List;

/**
 * 首页商品类别推荐
 */

@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<CategoryVO> categories;

    //实现首页分类商品推荐
    private List<ProductVO> products;

}

在mapper接口中定义查询方法：

categoryMapper接口：

package com.qfedu.fmmall.dao;

import com.qfedu.fmmall.entity.Category;
import com.qfedu.fmmall.entity.CategoryVO;
import com.qfedu.fmmall.general.GeneralDAO;
import org.springframework.stereotype.Repository;

import java.util.List;
@Repository
public interface CategoryMapper extends GeneralDAO<Category> {
    //1.使用连接查询实现分类查询：3级查询
    public List<CategoryVO> selectAllCategories();

    //2.子查询：根据parentId查询子分类
    public List<CategoryVO> selectAllCategories2(int parentId);

    //3.查询一级类别
    public List<CategoryVO> selectFirstLevelCategories();
}

categoryMapper.xml文件：

<resultMap id="categoryVOMap3" type="com.qfedu.fmmall.entity.CategoryVO">
  <result 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>

productMapper接口：（查询指定一级类别下销量最高的 6个商品）

package com.qfedu.fmmall.dao;

import com.qfedu.fmmall.entity.Product;
import com.qfedu.fmmall.entity.ProductVO;
import com.qfedu.fmmall.general.GeneralDAO;
import org.springframework.stereotype.Repository;

import java.util.List;
@Repository
public interface ProductMapper extends GeneralDAO<Product> {

    public List<ProductVO> selectRecommendProducts();

    /**
     * 查询指定一级类别下销量最高的 6个商品
     * @param cid
     * @return
     */
    public List<ProductVO> selectTop6ByCategory(int cid);

}

productMapper.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.qfedu.fmmall.dao.ProductMapper" >
  <resultMap id="BaseResultMap" type="com.qfedu.fmmall.entity.Product" >
    <id column="product_id" property="productId" jdbcType="VARCHAR" />
    <result column="product_name" property="productName" jdbcType="VARCHAR" />
    <result column="category_id" property="categoryId" jdbcType="INTEGER" />
    <result column="root_category_id" property="rootCategoryId" jdbcType="INTEGER" />
    <result column="sold_num" property="soldNum" jdbcType="INTEGER" />
    <result column="product_status" property="productStatus" jdbcType="INTEGER" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
    <result column="content" property="content" jdbcType="LONGVARCHAR" />
  </resultMap>

  <resultMap id="ProductVOMap" type="com.qfedu.fmmall.entity.ProductVO" >
    <id column="product_id" property="productId" jdbcType="VARCHAR" />
    <result column="product_name" property="productName" jdbcType="VARCHAR" />
    <result column="category_id" property="categoryId" jdbcType="INTEGER" />
    <result column="root_category_id" property="rootCategoryId" jdbcType="INTEGER" />
    <result column="sold_num" property="soldNum" jdbcType="INTEGER" />
    <result column="product_status" property="productStatus" jdbcType="INTEGER" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
    <result column="content" property="content" jdbcType="LONGVARCHAR" />
    <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>

测试：

@Test
    public void testSelectFirstLevelCategory(){
        //查询一级分类
        List<CategoryVO> categoryVOS = categoryMapper.selectFirstLevelCategories();
        for(CategoryVO c:categoryVOS){
            System.out.println(c);
        }
    }

测试结果：

 总结：

    商品推荐算法：

  查询最新上架的商品并且根据商品id查询商品图片；

  用到了product表和productImg表，在productMapper映射文件中连接查询productImg中的方法；

     分类商品推荐：

  根据一级分类查询出一级商品；再根据指定的一级类别下销量最高的6个商品；

用到了product和category表，进行映射关联；在product中加入selectTop6ByCategory方法，再在product映射文件中设置selectFirstLevelCategories方法，并子查询selectTop6ByCategory方法，从而只需查询selectFirstLevelCategories方法可以查出一级类别下销量最高的6个商品。

仅供参考.....