商品评论分页查询 - SpringBoot 整合 mybatis-pagehelper

一、SpringBoot 整合 mybatis-pagehelper

  1.引入分页插件依赖

    

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

  2.配置 yml

     api目录路径：src/main/resources/application.yml

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

 3.使用分页插件

   

/**
 * page: 第几页
 * pageSize: 每页显示条数
 */
PageHelper.startPage(page, pageSize);

 

4.分页数据封装到PagedGridResult.java传给前端

  

PageInfo<?> pageList = new PageInfo<>(list);
PagedGridResult grid = new PagedGridResult();
grid.setPage(page);
grid.setRows(list);
grid.setTotal(pageList.getPages());
grid.setRecords(pageList.getTotal());

 

二、查询商品评论-分页

   1、数据表

        

create table `foodie-shop-dev`.items_comments
(
    id            varchar(64)  not null comment 'id主键'
        primary key,
    user_id       varchar(64)  null comment '用户id 用户名须脱敏',
    item_id       varchar(64)  not null comment '商品id',
    item_name     varchar(64)  null comment '商品名称',
    item_spec_id  varchar(64)  null comment '商品规格id 可为空',
    sepc_name     varchar(32)  null comment '规格名称 可为空',
    comment_level int          not null comment '评价等级 1：好评 2：中评 3：差评',
    content       varchar(128) not null comment '评价内容',
    created_time  datetime     null comment '创建时间',
    updated_time  datetime     null comment '更新时间'
)
    comment '商品评价表 ' charset = utf8mb4;

 

create table `foodie-shop-dev`.users
(
    id           varchar(64)   not null comment '主键id 用户id'
        primary key,
    username     varchar(32)   not null comment '用户名 用户名',
    password     varchar(64)   not null comment '密码 密码',
    nickname     varchar(32)   null comment '昵称 昵称',
    realname     varchar(128)  null comment '真实姓名',
    face         varchar(1024) not null comment '头像',
    mobile       varchar(32)   null comment '手机号 手机号',
    email        varchar(32)   null comment '邮箱地址 邮箱地址',
    sex          int           null comment '性别 性别 1:男  0:女  2:保密',
    birthday     date          null comment '生日 生日',
    created_time datetime      not null comment '创建时间 创建时间',
    updated_time datetime      not null comment '更新时间 更新时间'
)
    comment '用户表 ' charset = utf8mb4;

 

   2、自定义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

 

   3、Mapper子模块实现

       （1）xml文件定义

             路径：\src\main\resources\mapper\ItemsMapperCustom.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.imooc.mapper.ItemsMapperCustom" >
  <select id="queryItemComments" parameterType="Map" resultType="com.imooc.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.leve !='' ">
          AND ic.comment_level = #{paramsMap.level}
        </if>
  </select>
</mapper>

 

        （2）VO定义

            路径： com/imooc/pojo/vo/ItemCommentVO.java

      

package com.imooc.pojo.vo;

import java.util.Date;

/**
 * 用于展示商品评价的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;
    }
}

 

         (3)接口定义

          路径：com/imooc/mapper/ItemsMapperCustom.java

package com.imooc.mapper;

import com.imooc.pojo.vo.ItemCommentVO;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface ItemsMapperCustom  {

    public List<ItemCommentVO> queryItemComments (@Param("paramsMap") Map<String, Object> map);

}

 

  4、service子模块实现

         

      （1）分页通用 PagedGridResult.java 引入

         路径：  com/imooc/utils/PagedGridResult.java

package com.imooc.utils;

import java.util.List;

/**
 * 
 * @Title: PagedGridResult.java
 * @Package com.imooc.utils
 * @Description: 用来返回分页Grid的数据格式
 * Copyright: Copyright (c) 2019
 */
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;
    }
}

 

       （2）接口定义

            路径：com/imooc/service/ItemService.java

           方法：queryPagedComments

    

package com.imooc.service;


import com.imooc.pojo.Items;
import com.imooc.pojo.ItemsImg;
import com.imooc.pojo.ItemsParam;
import com.imooc.pojo.ItemsSpec;
import com.imooc.pojo.vo.CommentLevelCountsVO;
import com.imooc.pojo.vo.ItemCommentVO;
import com.imooc.utils.PagedGridResult;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface ItemService {

    /**
     * 根据商品ID查询详情
     * @param itemId
     * @return
     */
    public Items queryItemById(String itemId);

    /**
     * 根据商品ID查询图片列表
     * @param itemId
     * @return
     */
    public List<ItemsImg> queryItemImgList(String itemId);

    /**
     * 根据商品ID查询商品规格列表
     * @param itemId
     * @return
     */
    public List<ItemsSpec> queryItemSpecList(String itemId);

    /**
     * 根据商品ID查询商品参数
     * @param itemId
     * @return
     */
    public ItemsParam queryItemParam(String itemId);

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

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

}

 

       （3）接口实现

          com/imooc/service/impl/ItemServiceImpl.java

        

package com.imooc.service.impl;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.imooc.enums.CommentLevel;
import com.imooc.mapper.*;
import com.imooc.pojo.*;
import com.imooc.pojo.vo.CommentLevelCountsVO;
import com.imooc.pojo.vo.ItemCommentVO;
import com.imooc.service.ItemService;
import com.imooc.utils.PagedGridResult;
import io.swagger.annotations.ApiParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import tk.mybatis.mapper.entity.Example;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ItemServiceImpl implements ItemService {
    @Autowired
    ItemsMapper itemsMapper;

    @Autowired
    ItemsImgMapper itemsImgMapper;

    @Autowired
    ItemsSpecMapper itemsSpecMapper;

    @Autowired
    ItemsParamMapper itemsParamMapper;

    @Autowired
    ItemsCommentsMapper itemsCommentsCommentsMapper;

    @Autowired
    ItemsMapperCustom itemsMapperCustom;

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public Items queryItemById(String itemId) {
        return itemsMapper.selectByPrimaryKey(itemId);
    }

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public List<ItemsImg> queryItemImgList(String itemId) {
        Example itemsImgExp = new Example(ItemsImg.class);
        Example.Criteria criteria =itemsImgExp.createCriteria();
        criteria.andEqualTo("itemId",itemId);
        return itemsImgMapper.selectByExample(itemsImgExp);
    }

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public List<ItemsSpec> queryItemSpecList(String itemId) {
        Example itemsSpecExp = new Example(ItemsSpec.class);
        Example.Criteria criteria =itemsSpecExp.createCriteria();
        criteria.andEqualTo("itemId",itemId);
        return itemsSpecMapper.selectByExample(itemsSpecExp);
    }

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public ItemsParam queryItemParam(String itemId) {
        Example itemsParamExp = new Example(ItemsParam.class);
        Example.Criteria criteria =itemsParamExp.createCriteria();
        criteria.andEqualTo("itemId",itemId);
        return itemsParamMapper.selectOneByExample(itemsParamExp);
    }



    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public CommentLevelCountsVO queryItemCommentCounts(String itemId) {

       //Integer totalCounts=getCommentCounts(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 commentLevelCountsVO=new CommentLevelCountsVO();
        commentLevelCountsVO.setTotalCounts(totalCounts);
        commentLevelCountsVO.setGoodCounts(goodCounts);
        commentLevelCountsVO.setNormalCounts(normalCounts);
        commentLevelCountsVO.setBadCounts(badCounts);
        return commentLevelCountsVO;
    }

    @Transactional(propagation = Propagation.SUPPORTS)
     Integer getCommentCounts(String itemId,Integer level){

        ItemsComments confdition =new ItemsComments();
        confdition.setItemId(itemId);
        if (level != null) {
            confdition.setCommentLevel(level);
        }

      return   itemsCommentsCommentsMapper.selectCount(confdition);

    }
    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public PagedGridResult queryPagedComments(String itemId,
                                                  Integer level,
                                                  Integer page,
                                                  Integer pageSzie) {
        Map<String,Object> map =new HashMap<>();
        map.put("itemId",itemId);
        map.put("level",level);
        /**
         * page:第几页
         * pageSize:每页显示多少条
         */
        PageHelper.startPage(page,pageSzie);
        List<ItemCommentVO> list=itemsMapperCustom.queryItemComments(map);
        return setterPagedGrid(list,page);

    }

    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;

    }



}

 

  5、api子模块实现

   

        评论分页实现

     

package com.imooc.controller;

import com.imooc.enums.YesOrNo;
import com.imooc.pojo.*;
import com.imooc.pojo.vo.CategoryVO;
import com.imooc.pojo.vo.CommentLevelCountsVO;
import com.imooc.pojo.vo.ItemInfoVO;
import com.imooc.pojo.vo.NewItemsVO;
import com.imooc.service.CarouselService;
import com.imooc.service.CategoryService;
import com.imooc.service.ItemService;
import com.imooc.utils.IMOOCJSONResult;
import com.imooc.utils.PagedGridResult;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@Api(value = "商品接口",tags = "商品信息展示的相关接口")
@RestController
@RequestMapping("item")
public class ItemController {

    @Autowired
     private ItemService itemService;

    @ApiOperation(value="查询商品详情",notes = "查询商品详情",httpMethod = "GET")
    @GetMapping("/info/{itemId}")
    public IMOOCJSONResult info(
            @ApiParam(name = "itemId",value = "商品ID",required = true)
            @PathVariable() String itemId) {
         if (StringUtils.isBlank(itemId)) {

             return IMOOCJSONResult.errorMsg("");

         }


        Items item = itemService.queryItemById(itemId);
        List<ItemsImg> itemImgList=itemService.queryItemImgList(itemId);
        List<ItemsSpec> itemSpecList=itemService.queryItemSpecList(itemId);
        ItemsParam itemParam=itemService.queryItemParam(itemId);
        ItemInfoVO itemInfoVO=new ItemInfoVO();
        itemInfoVO.setItem(item);
        itemInfoVO.setItemImgList(itemImgList);
        itemInfoVO.setItemSpecList(itemSpecList);
        itemInfoVO.setItemParam(itemParam);

        return IMOOCJSONResult.ok(itemInfoVO);
    }


    @ApiOperation(value="查询商品评价分页",notes = "查询商品评价分页",httpMethod = "GET")
    @GetMapping("/comments")
    public IMOOCJSONResult 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 IMOOCJSONResult.errorMsg("");
        }
        if(page==null) {
            page=1;
        }
        if(pageSize==null) {
            pageSize=10;
        }


        PagedGridResult grid = itemService.queryPagedComments(itemId,level,page,pageSize);
        return IMOOCJSONResult.ok(grid);
    }

    @ApiOperation(value="查询商品评价等级",notes = "查询商品评价等级",httpMethod = "GET")
    @GetMapping("/commentLevel")
    public IMOOCJSONResult commentLevel(
            @ApiParam(name = "itemId",value = "商品ID",required = true)
            @RequestParam() String itemId) {
        if (StringUtils.isBlank(itemId)) {

            return IMOOCJSONResult.errorMsg("");

        }
        CommentLevelCountsVO countsVO = itemService.queryItemCommentCounts(itemId);
        return IMOOCJSONResult.ok(countsVO);
    }
}

 

 

PageHelper.startPage 静态方法调用

Ref: https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/en/HowToUse.md