前言

商品评价

在这里插入图片描述

代码编写

service层

/**

 * 根据商品id查询商品的评价等级数量
 * @param itemId
 */
public CommentLevelCountsVO queryCommentCounts(String itemId);

impl

@Transactional(propagation = Propagation.SUPPORTS)

@Override
public CommentLevelCountsVO queryCommentCounts(String itemId) { 
    Integer goodCounts = getCommentCounts(itemId, CommentLevel.GOOD.type);
    Integer normalCounts = getCommentCounts(itemId, CommentLevel.NORMAL.type);
    Integer badCounts = getCommentCounts(itemId, CommentLevel.BAD.type);
    Integer totalCounts = goodCounts + normalCounts + badCounts;
    CommentLevelCountsVO countsVO = new CommentLevelCountsVO();
    countsVO.setTotalCounts(totalCounts);
    countsVO.setGoodCounts(goodCounts);
    countsVO.setNormalCounts(normalCounts);
    countsVO.setBadCounts(badCounts);
    return countsVO;
}

@Transactional(propagation = Propagation.SUPPORTS)

Integer getCommentCounts(String itemId, Integer level) { 
    ItemsComments condition = new ItemsComments();
    condition.setItemId(itemId);
    if (level != null) { 
        condition.setCommentLevel(level);
    }
    return itemsCommentsMapper.selectCount(condition);
}

pojo

/**
- 用于展示商品评价数量的vo
  */
  public class CommentLevelCountsVO {
  
  public Integer totalCounts;
  public Integer goodCounts;
  public Integer normalCounts;
  public Integer badCounts;
  
  public Integer getTotalCounts() {
```
 return totalCounts;
```
  }
  
  public void setTotalCounts(Integer totalCounts) {
```
 this.totalCounts = totalCounts;
```
  }
  
  public Integer getGoodCounts() {
```
 return goodCounts;
```
  }
  
  public void setGoodCounts(Integer goodCounts) {
```
 this.goodCounts = goodCounts;
```
  }
  
  public Integer getNormalCounts() {
```
 return normalCounts;
```
  }
  
  public void setNormalCounts(Integer normalCounts) {
```
 this.normalCounts = normalCounts;
```
  }
  
  public Integer getBadCounts() {
```
 return badCounts;
```
  }
  
  public void setBadCounts(Integer badCounts) {
```
 this.badCounts = badCounts;
```
  }
  }
枚举

/**
- @Desc: 商品评价等级枚举
  */
  public enum CommentLevel {
  GOOD(1, “好评”),
  NORMAL(2, “中评”),
  BAD(3, “差评”);
  
  public final Integer type;
  public final String value;
  
  CommentLevel(Integer type, String value) {
```
 this.type = type;
 this.value = value;
```
  }
  }
/**
- 用于展示商品评价数量的vo
  */
  public class CommentLevelCountsVO {
  
  public Integer totalCounts;
  public Integer goodCounts;
  public Integer normalCounts;
  public Integer badCounts;
  
  public Integer getTotalCounts() {
```
 return totalCounts;
```
  }
  
  public void setTotalCounts(Integer totalCounts) {
```
 this.totalCounts = totalCounts;
```
  }
  
  public Integer getGoodCounts() {
```
 return goodCounts;
```
  }
  
  public void setGoodCounts(Integer goodCounts) {
```
 this.goodCounts = goodCounts;
```
  }
  
  public Integer getNormalCounts() {
```
 return normalCounts;
```
  }
  
  public void setNormalCounts(Integer normalCounts) {
```
 this.normalCounts = normalCounts;
```
  }
  
  public Integer getBadCounts() {
```
 return badCounts;
```
  }
  
  public void setBadCounts(Integer badCounts) {
```
 this.badCounts = badCounts;
```
  }
  }

controller

@ApiOperation(value = “查询商品评论”, notes = “查询商品评论”, httpMethod = “GET”)

@GetMapping("/comments")
public ZCWJSONResult comments(
        @ApiParam(name = "itemId", value = "商品id", required = true)
        @RequestParam String itemId,
        @ApiParam(name = "level", value = "评价等级", required = false)
        @RequestParam Integer level,
        @ApiParam(name = "page", value = "查询下一页的第几页", required = false)
        @RequestParam Integer page,
        @ApiParam(name = "pageSize", value = "分页的每一页显示的条数", required = false)
        @RequestParam Integer pageSize) { 
    if (StringUtils.isBlank(itemId)) { 
        return ZCWJSONResult.errorMsg(null);
    }
    if (page == null) { 
        page = 1;
    }
    if (pageSize == null) { 
        pageSize = COMMON_PAGE_SIZE;
    }
    PagedGridResult grid = itemService.queryPagedComments(itemId,
                                                            level,
                                                            page,
                                                            pageSize);
    return ZCWJSONResult.ok(grid);
}

商品评价的sql语句

SELECT

ic.comment_level AS commentLevel,
ic.content AS content,
ic.sepc_name AS specName,
ic.created_time AS createdTime,
u.face AS userFace,
u.nickname AS nickname

FROM

items_comments ic
LEFT JOIN users u ON ic.user_id = u.id

WHERE

ic.item_id = 'cake-1001' 
AND ic.comment_level = 1

在这里插入图片描述

编写自定义Mapper进行数据查询

<?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“ >

<select id="searchItems" parameterType="Map" resultType="com.imooc.pojo.vo.SearchItemsVO">
    SELECT
        i.id as itemId,
        i.item_name as itemName,
        i.sell_counts as sellCounts,
        ii.url as imgUrl,
        tempSpec.price_discount as price
    FROM
        items i
    LEFT JOIN
        items_img ii
    on
        i.id = ii.item_id
    LEFT JOIN
        (SELECT item_id,MIN(price_discount) as price_discount from items_spec GROUP BY item_id) tempSpec
    on
        i.id = tempSpec.item_id
    WHERE
        ii.is_main = 1
        <if test=" paramsMap.keywords != null and paramsMap.keywords != '' ">
            AND i.item_name like '%${paramsMap.keywords}%'
        </if>
        order by
        <choose>
            <when test=" paramsMap.sort == "c" ">
                i.sell_counts desc
            </when>
            <when test=" paramsMap.sort == "p" ">
                tempSpec.price_discount asc
            </when>
            <otherwise>
                i.item_name asc
            </otherwise>
        </choose>
</select>
<!-- k: 默认，代表默认排序，根据name-->
<!-- c: 根据销量排序-->
<!-- p: 根据价格排序-->

    <select id="searchItemsByThirdCat" parameterType="Map" resultType="com.imooc.pojo.vo.SearchItemsVO">
        SELECT
            i.id as itemId,
            i.item_name as itemName,
            i.sell_counts as sellCounts,
            ii.url as imgUrl,
            tempSpec.price_discount as price
        FROM
          items i
        LEFT JOIN
          items_img ii
        on
          i.id = ii.item_id
        LEFT JOIN
          (SELECT item_id,MIN(price_discount) as price_discount from items_spec GROUP BY item_id) tempSpec
        on
          i.id = tempSpec.item_id
        WHERE
          ii.is_main = 1
          and
          i.cat_id = #{paramsMap.catId}
        order by
        <choose>
            <when test=" paramsMap.sort == "c" ">
                i.sell_counts desc
            </when>
            <when test=" paramsMap.sort == "p" ">
                tempSpec.price_discount asc
            </when>
            <otherwise>
                i.item_name asc
            </otherwise>
        </choose>
    </select>
    <select id="queryItemsBySpecIds" parameterType="List" resultType="com.imooc.pojo.vo.ShopcartVO">
        SELECT
            t_items.id as itemId,
            t_items.item_name as itemName,
            t_items_img.url as itemImgUrl,
            t_items_spec.id as specId,
            t_items_spec.`name` as specName,
            t_items_spec.price_discount as priceDiscount,
            t_items_spec.price_normal as priceNormal
        FROM
            items_spec t_items_spec
        LEFT JOIN
            items t_items
        ON
            t_items.id = t_items_spec.item_id
        LEFT JOIN
            items_img t_items_img
        on
            t_items_img.item_id = t_items.id
        WHERE
            t_items_img.is_main = 1
        AND
            t_items_spec.id IN
            <foreach collection="paramsList" index="index" item="specId" open="(" separator="," close=")">
              #{specId}
            </foreach>
    </select>
    <update id="decreaseItemSpecStock">
        update
            items_spec
        set
            stock = stock - #{pendingCounts}
        where
            id = #{specId}
        and
            stock >= #{pendingCounts}
    </update>
</mapper>
public interface ItemsMapperCustom { 
    public List<ItemCommentVO> queryItemComments(@Param("paramsMap") Map<String, Object> map);
    public List<SearchItemsVO> searchItems(@Param("paramsMap") Map<String, Object> map);
    public List<SearchItemsVO> searchItemsByThirdCat(@Param("paramsMap") Map<String, Object> map);
    public List<ShopcartVO> queryItemsBySpecIds(@Param("paramsList") List specIdsList);
    public int decreaseItemSpecStock(@Param("specId") String specId,
                                     @Param("pendingCounts") int pendingCounts);
}
/**
 * 用于展示商品评价的VO
 */
public class ItemCommentVO { 
    private Integer commentLevel;
    private String content;
    private String specName;
    private Date createdTime;
    private String userFace;
    private String nickname;
    public Integer getCommentLevel() { 
        return commentLevel;
    }
    public void setCommentLevel(Integer commentLevel) { 
        this.commentLevel = commentLevel;
    }
    public String getContent() { 
        return content;
    }
    public void setContent(String content) { 
        this.content = content;
    }
    public String getSpecName() { 
        return specName;
    }
    public void setSpecName(String specName) { 
        this.specName = specName;
    }
    public Date getCreatedTime() { 
        return createdTime;
    }
    public void setCreatedTime(Date createdTime) { 
        this.createdTime = createdTime;
    }
    public String getUserFace() { 
        return userFace;
    }
    public void setUserFace(String userFace) { 
        this.userFace = userFace;
    }
    public String getNickname() { 
        return nickname;
    }
    public void setNickname(String nickname) { 
        this.nickname = nickname;
    }
}

service

/**

 * 根据商品id查询商品的评价（分页）
 * @param itemId
 * @param level
 * @return
 */
public PagedGridResult queryPagedComments(String itemId, Integer level,
                                          Integer page, Integer pageSize);

@Transactional(propagation = Propagation.SUPPORTS)

@Override
public PagedGridResult queryPagedComments(String itemId,
                                              Integer level,
                                              Integer page,
                                              Integer pageSize) { 
    Map<String, Object> map = new HashMap<>();
    map.put("itemId", itemId);
    map.put("level", level);
    // mybatis-pagehelper
    /**
     * page: 第几页
     * pageSize: 每页显示条数
     */
    PageHelper.startPage(page, pageSize);
    List<ItemCommentVO> list = itemsMapperCustom.queryItemComments(map);
    for (ItemCommentVO vo : list) { 
        vo.setNickname(DesensitizationUtil.commonDisplay(vo.getNickname()));
    }
    return setterPagedGrid(list, page);
}

分页对象

public class PagedGridResult {

private int page;            // 当前页数
private int total;            // 总页数    
private long records;        // 总记录数
private List<?> rows;        // 每行显示的内容
public int getPage() { 
    return page;
}
public void setPage(int page) { 
    this.page = page;
}
public int getTotal() { 
    return total;
}
public void setTotal(int total) { 
    this.total = total;
}
public long getRecords() { 
    return records;
}
public void setRecords(long records) { 
    this.records = records;
}
public List<?> getRows() { 
    return rows;
}
public void setRows(List<?> rows) { 
    this.rows = rows;
}