错误的分页写法及修改

最新推荐文章于 2024-09-16 20:08:29 发布

28岁青春痘老男孩

最新推荐文章于 2024-09-16 20:08:29 发布

阅读量129

点赞数

分类专栏：笔记 BUG 文章标签： java sql

本文链接：https://blog.csdn.net/stan_ko/article/details/130942859

版权

笔记同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

BUG

2 篇文章 0 订阅

订阅专栏

一.错误的写法：

api:

@GetMapping("/groupList")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "current", value = "页码", required = true, dataType = "Long", dataTypeClass = Long.class),
            @ApiImplicitParam(name = "pageSize", value = "每页大小", required = true, dataType = "Long", dataTypeClass = Long.class),
            @ApiImplicitParam(name = "id", value = "店铺ID", required = true, dataType = "Long", dataTypeClass = Long.class)
    })
    @ApiOperation(value = "小组列表", tags = "9.11.27")
    public BaseResult<PageList<ResLineGroupDTO>> groupList (
            @RequestParam("current") Long current, @RequestParam("pageSize") Long pageSize,
            @RequestParam("id")Long shopId){

        return BaseResult.success(businessShopService.groupList(current,pageSize,shopId));
    }

service:

    /**
     * 根据店铺ID获取对应的小组列表
     *
     * @param shopId 店铺ID
     */
    PageList<ResLineGroupDTO> groupList (Long current,Long pageSize,Long shopId);

实现的方法：

@Override
    public PageList<ResLineGroupDTO> groupList(Long current,Long pageSize,Long shopId) {

        PageList<ResLineGroupDTO> page = new PageList<>();
        page.setPage(current);
        page.setPageSize(pageSize);

        Integer pageStart = (current.intValue() - 1) * pageSize.intValue();

        List<ResLineGroupDTO> resultList = new ArrayList<ResLineGroupDTO>();

        //查询店铺的信息
        BusinessLineShopRelevance shop = this.getBaseMapper().selectById(shopId);

        if(Objects.isNull(shop)){
            throw new BizException("该店铺不存在");
        }
        if(shop.getPrivateSphereFlag()==1 || shop.getType()==2){
            //私域 拿的是 clickhouse private_sphere_group表中的数据
            resultList = biGuiderBoardMapper.getPrivateSphereGuiderGroupList(pageStart,pageSize,shop.getBusinessUnitId());
            //查询私域小组组长
            List<ResShoppingGuideDTO> leaderList = biGuiderBoardMapper.getPrivateSphereGuiderLeaderList();
            if(!leaderList.isEmpty()&&leaderList.size()>0){
                Map<Long, ResShoppingGuideDTO> leaderMap = leaderList.stream().collect(Collectors.toMap(ResShoppingGuideDTO::getGroupId, ResShoppingGuideDTO -> ResShoppingGuideDTO, (k1, k2) -> k2));
                //装配负责人信息
                if(!leaderMap.isEmpty()){
                    for(ResLineGroupDTO dto:resultList){
                        if(leaderMap.containsKey(dto.getId())){
                            ResShoppingGuideDTO dto2 = leaderMap.get(dto.getId());
                            ResPrincipalDTO dto1 = new ResPrincipalDTO(dto2.getId(),dto2.getGuiderEmployeeName());
                            List<ResPrincipalDTO> dtos = new ArrayList<>();
                            dtos.add(dto1);
                            dto.setPrincipalList(dtos);
                        }
                    }
                }
            }

        }else {
            //非私域
            resultList= businessLineGroupMapper.getGroupDetail(pageStart,pageSize,shopId);
            //查询负责人
            List<BusinessPrincipalList> principallist =businessPrincipalListMapper.getGroupPricipalListByShopId(shopId);

            if(!principallist.isEmpty()&&principallist.size()>0){
                Map<Long, List<BusinessPrincipalList>> principalmap = principallist.stream().collect(Collectors.groupingBy(BusinessPrincipalList::getBusinessId));
                if(!principalmap.isEmpty()){
                    for(ResLineGroupDTO dto:resultList){

                        if(principalmap.containsKey(dto.getId())){
                            List<ResPrincipalDTO> dtos = new ArrayList<>();
                            List<BusinessPrincipalList> principalLists = principalmap.get(dto.getId());

                            principalLists.forEach(it->{
                                ResPrincipalDTO dto1 = new ResPrincipalDTO();

                                dto1.setId(it.getEmployeeId());
                                dto1.setName(it.getName());

                                dtos.add(dto1);

                            });


                            dto.setPrincipalList(dtos);
                        }



                    }
                }
            }

        }
        for(ResLineGroupDTO dto:resultList){
            dto.setShopName(shop.getName());
        }

        page.setData(resultList);
        page.setTotal((long)resultList.size());

        return page;
    }

mapper接口：

List<ResLineGroupDTO> getGroupDetail(Integer pageStart,Long pageSize,Long shopId);

xml:

<select id="getGroupDetail" resultType="com.huimei.bi.service.business_line.pojo.dto.response.ResLineGroupDTO">
        select gro.*, ifnull(gui.guider_count,0) as guider_count from hmcdata.bi_business_line_group gro
        left join (select group_id , count(1) as guider_count from hmcdata.bi_business_shopping_guide)gui
        on gro.id=gui.group_id
        <if test="shopId != null and shopId != ''">
            WHERE gro.shop_id=#{shopId}
        </if>
        LIMIT #{pageStart},#{pageSize}
    </select>

二.错误点

上面的写法查询出来的总数永远=pageSize，与符合查询条件的总数不符，使用mybatis-plus框架的方法：mapper方法中需要传入一个分页的对象 Ipage ，mapper方法返回也是一个分页对象Page，就能自动实现分页（具体原因、底层逻辑待后续研究）。不使用自定义的sql，就可以直接用BaseMapper自带的selectPage方法就可以。

三.修改后的代码

xml

 <select id="getGroupDetail" resultType="com.huimei.bi.service.business_line.pojo.dto.response.ResLineGroupDTO">
        select gro.*, ifnull(gui.guider_count,0) as guider_count from hmcdata.bi_business_line_group gro
        left join (select group_id , count(1) as guider_count from hmcdata.bi_business_shopping_guide)gui
        on gro.id=gui.group_id
        <if test="shopId != null and shopId != ''">
            WHERE gro.shop_id=#{shopId}
        </if>
    </select>

mapper:

Page<ResLineGroupDTO> getGroupDetail(IPage<ResLineGroupDTO> page, Long pageSize, Long shopId);

业务实现方法：

@Override
    public PageList<ResLineGroupDTO> groupList(Long current,Long pageSize,Long shopId) {

        Page<ResLineGroupDTO> page = new Page<>(current, pageSize);

        Page<ResLineGroupDTO> privateSphereGuiderGroupList=new Page<>();


        /*Integer pageStart = (current.intValue() - 1) * pageSize.intValue();*/

        List<ResLineGroupDTO> resultList = new ArrayList<ResLineGroupDTO>();

        //查询店铺的信息
        BusinessLineShopRelevance shop = this.getBaseMapper().selectById(shopId);

        if(Objects.isNull(shop)){
            throw new BizException("该店铺不存在");
        }
        if(shop.getPrivateSphereFlag()==1 || shop.getType()==2){
            //私域 拿的是 clickhouse private_sphere_group表中的数据
            privateSphereGuiderGroupList = biGuiderBoardMapper.getPrivateSphereGuiderGroupList(page, pageSize, shop.getBusinessUnitId());
            resultList =privateSphereGuiderGroupList.getRecords();
            //查询私域小组组长
            List<ResShoppingGuideDTO> leaderList = biGuiderBoardMapper.getPrivateSphereGuiderLeaderList();
            if(!leaderList.isEmpty()&&leaderList.size()>0){
                Map<Long, ResShoppingGuideDTO> leaderMap = leaderList.stream().collect(Collectors.toMap(ResShoppingGuideDTO::getGroupId, ResShoppingGuideDTO -> ResShoppingGuideDTO, (k1, k2) -> k2));
                //装配负责人信息
                if(!leaderMap.isEmpty()){
                    for(ResLineGroupDTO dto:resultList){
                        if(leaderMap.containsKey(dto.getId())){
                            ResShoppingGuideDTO dto2 = leaderMap.get(dto.getId());
                            ResPrincipalDTO dto1 = new ResPrincipalDTO(dto2.getId(),dto2.getGuiderEmployeeName());
                            List<ResPrincipalDTO> dtos = new ArrayList<>();
                            dtos.add(dto1);
                            dto.setPrincipalList(dtos);
                        }
                    }
                }
            }

        }else {
            //非私域
            privateSphereGuiderGroupList = businessLineGroupMapper.getGroupDetail(page, pageSize, shopId);
            resultList=privateSphereGuiderGroupList.getRecords();
            //查询负责人
            List<BusinessPrincipalList> principallist =businessPrincipalListMapper.getGroupPricipalListByShopId(shopId);

            if(!principallist.isEmpty()&&principallist.size()>0){
                Map<Long, List<BusinessPrincipalList>> principalmap = principallist.stream().collect(Collectors.groupingBy(BusinessPrincipalList::getBusinessId));
                if(!principalmap.isEmpty()){
                    for(ResLineGroupDTO dto:resultList){

                        if(principalmap.containsKey(dto.getId())){
                            List<ResPrincipalDTO> dtos = new ArrayList<>();
                            List<BusinessPrincipalList> principalLists = principalmap.get(dto.getId());

                            principalLists.forEach(it->{
                                ResPrincipalDTO dto1 = new ResPrincipalDTO();

                                dto1.setId(it.getEmployeeId());
                                dto1.setName(it.getName());

                                dtos.add(dto1);

                            });


                            dto.setPrincipalList(dtos);
                        }



                    }
                }
            }

        }
        for(ResLineGroupDTO dto:resultList){
            dto.setShopName(shop.getName());
        }

        privateSphereGuiderGroupList.setRecords(resultList);

        return new PageList<>(privateSphereGuiderGroupList);

    }