知行合一

博客园 首页 新随笔 联系 订阅 管理
  371 随笔 :: 25 文章 :: 4 评论 :: 15万 阅读
商品评论分页查询 - 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;
商品评价表 items_cooments
复制代码

 

复制代码
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;
用户表 users
复制代码

 

   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
View Code
复制代码

 

   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>
View Code
复制代码

 

        （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;
    }
}
ItemCommentVO
复制代码

 

         (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);

}
ItemsMapperCustom
复制代码

 

  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;
    }
}
PagedGridResult
复制代码

 

       （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 );

}
View Code
复制代码

 

       （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;

    }



}
ItemServiceImpl
复制代码

 

  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);
    }
}
View Code
复制代码

 

 

PageHelper.startPage 静态方法调用

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

posted on   callbin  阅读(199)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
阅读排行：
· winform 绘制太阳，地球，月球 运作规律
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· AI与.NET技术实操系列（五）：向量存储与相似性搜索在 .NET 中的实现
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
点击右上角即可分享
微信分享提示
AI IDE Trae