最终效果

数据库表设计

编写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  # 商品评价类型：好、中、差

分析创建VO

创建分类ItemCommentVO

package com.shiers.pojo.vo;
import java.util.Date;
/**
 * 用于展示商品评价的VO
 *
 * @author shierS
 * @date 2021/5/31
 */
public class ItemCommentVO {
    private Integer commentLevel;
    private String content;
    private String specName;
    private Date createdTime;
    private String userFace;
    private String nickname;
    //省略get、set方法
}

编写mapper

if判断是否根据评论等级查询

<?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.shiers.mapper.ItemsMapperCustom">
  <select id="queryItemComments" parameterType="Map" resultType="com.shiers.pojo.vo.ItemCommentVO">
    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 = #{paramsMap.itemId}
    <if test="paramsMap.level != null and paramsMap.level != '' ">
      AND ic.comment_level = #{paramsMap.level}
    </if>
  </select>
</mapper>

编写初步service

@Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public List<ItemCommentVO>  queryPagedComments(String itemId,Integer level) {
        Map<String, Object> map = new HashMap<>();
        map.put("itemId", itemId);
        map.put("level", level);
        List<ItemCommentVO> list = itemsMapperCustom.queryItemComments(map);
        return list;
    }

这样我们已经可以实现获取商品评论信息了
接下来为了更好的体验需要实现分页功能

导入分页pagehelper依赖

<!--pagehelper-->
<dependency>
  <groupId>com.github.pagehelper</groupId>
  <artifactId>pagehelper-spring-boot-starter</artifactId>
  <version>1.2.5</version>
</dependency>

在application.yml添加分页配置

# 分页插件配置
pagehelper:
  helperDialect: mysql
  supportMethodsArguments: true

使用分页完成service

@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);
    /**
         * page:第几页
         * pageSize:每页显示条数
         */
    PageHelper.startPage(page, pageSize);
    List<ItemCommentVO> list = itemsMapperCustom.queryItemComments(map);
    return setterPagedGrid(list,page);
}
/**
 *  🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥
 *  PageInfo是大佬已经写好的分页数据封装
 *  然而我们又自己写了一个PagedGridResult封装
 *  主要是因为在我们项目的前端，可能分页有多个不同的组件来完成，每一个组件需求的参数（名称、类型）
 *      可能会不同，所以我们多了一步自定义的封装，使得能够对应前端，在不同的需求下，只需要添加相
 *      应的封装类型即可
 *  🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥
 */
private PagedGridResult setterPagedGrid(List<?> list,Integer page){
    PageInfo<?> pageList = new PageInfo<>(list);
    PagedGridResult grid = new PagedGridResult();
    grid.setPage(page);
    grid.setRows(list);
    grid.setTotal(pageList.getPages());
    grid.setRecords(pageList.getTotal());
    return grid;
}

🔥添加util类PagedGridResult.java🔥

package com.shiers.utils;
import java.util.List;
/**
 * PagedGridResult.java
 * 用来显示分页返回的Grid数据
 *
 * @author shierS
 * @date 2021/5/31
 */
public class PagedGridResult {
    private int page;           //当前页数
    private int total;          //总页数
    private long records;       //总记录数
    private List<?> rows;       //每行显示的内容
    //省略get、set方法
}

编写Controller

@ApiOperation(value = "查询商品评论", notes = "查询商品评论", httpMethod = "GET")
    @GetMapping("/comments")
    public MyJSONResult 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) {
        //除了itemID其他都是非必传参数
        if (StringUtils.isBlank(itemId)) {
            return MyJSONResult.errorMsg(null);
        }
        if (page == null) {
            page = 1;  //没有page可默认为1
        }
        if (pageSize == null) {
            pageSize = 10;  //默认为10条
        }
        PagedGridResult grid = itemService.queryPagedComments(itemId, level, page, pageSize);
        return MyJSONResult.ok(grid);
    }

pagehelper实现简析

PageHelper首先将前端传递的参数保存到page这个对象中，接着将page的副本存放入ThreadLoacl中，这样可以保证分页的时候，参数互不影响，接着利用了mybatis提供的拦截器，取得ThreadLocal的值，重新拼装分页SQL，完成分页。

通过最终功能实现
我们在控制台打印的数据库查询语句
该商品下有23条评论

当查询第一页时

==> Preparing: 
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 = ? 
LIMIT ?    # 🔥SQL后面拼接了LIMIT，限制了返回条数
==> Parameters: 
cake-1001(String), 10(Integer)

当查询第二页时

==> Preparing: 
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 = ?    
LIMIT ?, ?  # 🔥SQL后面拼接了LIMIT，实现分页
==> Parameters: 
cake-1001(String), 10(Integer), 10(Integer)