springBoot中实现分页查询

小咪咪 2023-09-29 19:06 14阅读 0赞

## 分页查询功能 ##

### 1.创建实体类 ###

1.根据需求创建请求实体类

/**
     *分页查询
     */
    @Data
    public class InquirySheetQueryPageVo {
        
        @ApiModelProperty(notes = "询价单号")
        private Integer requestForQuotationId;//询价单号
        @ApiModelProperty(notes = "法人id")
        private Integer legalPersonId;//法人id
        @ApiModelProperty(notes = "询价日期")
        private Date createTime;
        @ApiModelProperty(notes = "创建开始日期")
        private Date createStartTime;
        @ApiModelProperty(notes = "创建结束日期")
        private Date createEndTime;
        @ApiModelProperty(notes = "询价开始日期")
        @NotNull(message = "询价开始日期为空")
        private Date startTime;
        @ApiModelProperty(notes = "询价结束日期")
        @NotNull(message = "询价结束日期为空")
        private Date endTime;
        @ApiModelProperty(notes = "供应商编码")
        private String vendor;
        @ApiModelProperty(notes = "询价状态")
        private Integer inquiryStatus;
        @ApiModelProperty(notes = "询价类型")
        private Integer type;
    
        private Integer pageIndex;
        private Integer pageSize;
    }

创建要响应的实体类

/**
     * 分页查询返回类型
     */
    @Data
    public class InquirySheetQueryPageFanVo {
        
        private Integer totalPage;//总页数
        private Integer totalNumber;//总条数
        private List<InquirySheetQueryPageFansVo> dataList;//数据
        
    }

2.controller层代码

@RestController
    @RequestMapping("/inquiryList")
    @Api(value="SwaggerController" ,description="询价清单模块")
    @CrossOrigin//支持跨域
    public class InquirySheetController {
        
    //    注入
        @Autowired
        private InquirySheetService inquirySheetService;
        //    分页查询
        @PostMapping("/search")
        @ApiOperation("分页查询")
        public ResultVo queryPage(@RequestBody InquirySheetQueryPageVo inquirySheetQueryPageVo) {
        
            System.out.println(inquirySheetQueryPageVo);
    
            List<InquirySheetQueryPageFansVo> lists=inquirySheetService.queryPage(inquirySheetQueryPageVo);
    
             //        使用pageInfo中的方法,内部封装了我们所有的数据
            PageInfo<InquirySheetQueryPageFansVo> pageInfo=new PageInfo<>(lists);
    
            InquirySheetQueryPageFanVo inlist=new InquirySheetQueryPageFanVo();
    
            inlist.setTotalNumber(pageInfo.getPageSize());
            inlist.setTotalPage(pageInfo.getPages());
            inlist.setDataList(lists);
    
            return ResultPlsVo.success(inlist);
        }
        }

2.service层代码–接口实现类

@Service
    public class InquirySheetServiceImpl implements InquirySheetService {
        
        @Autowired
        private InquirySheetDao inquirySheetDao;
    
        //    首页的分页查询
        @Override
        public List<InquirySheetQueryPageFansVo> queryPage(InquirySheetQueryPageVo inquirySheetQueryPageVo) {
        
            //使用分页插件功能、Mapper接口方式的调用，推荐这种使用方式。
            Integer pageSize =inquirySheetQueryPageVo.getPageSize();//每页显示条数
            Integer pageIndex=inquirySheetQueryPageVo.getPageIndex();//当前页
    //        参数1：当前页，参数2：每页条数
    //        注意：在查询之前使用
            PageHelper.startPage(pageIndex,pageSize);
            List<InquirySheetQueryPageFansVo> lists=inquirySheetDao.queryPage(inquirySheetQueryPageVo);
            return lists;
        }
        }

3.dao层代码

@Mapper
    public interface InquirySheetDao {
        
    //    首页分页查询
    
        List<InquirySheetQueryPageFansVo> queryPage(InquirySheetQueryPageVo inquirySheetQueryPageVo);
        }

4.xml中的sql语句

<?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.xyh.inquiry.dao.InquirySheetDao">
    
     
        <select id="queryPage" resultType="com.xyh.inquiry.domain.InquirySheet.InquirySheetQueryPageFansVo">
            select xt.id,xt.requestForQuotationId,xt.createTime,xt.startTime,
            xt.endTime,xt.inquiryStatus,zd.name inquiryStatusName,xt.creatorName,le.legalPlantName legalPersonName from legalplant le
            LEFT JOIN xjttable xt on le.id=xt.legalPersonId
            LEFT JOIN legal_supp ls on le.id=ls.legalPersonId
            LEFT JOIN supplier su on su.id=ls.gongyingId
            LEFT JOIN zidian zd on zd.id=xt.inquiryStatus
            <where>
    
                <if test="legalPersonId !=null">
                    and xt.legalPersonId=#{legalPersonId}
                </if>
                <if test="requestForQuotationId !=null">
                    and xt.requestForQuotationId=#{requestForQuotationId}
                </if>
    
    
                <if test="createStartTime !=null">
                    and xt.createStartTime >=#{createStartTime}
                </if>
                <if test="createEndTime !=null">
                    and xt.createEndTime <=#{createEndTime}
                </if>
    
    
                <if test="startTime !=null">
                    and xt.startTime >=#{startTime}
                </if>
                <if test="endTime !=null">
                    and xt.endTime <=#{endTime}
                </if>
    
                <if test="vendor !=null and vendor !=''">
                    and su.supplierCode=#{vendor}
                </if>
    
                <if test="type !=null">
                    and xt.type <=#{type}
                </if>
    
                <if test="inquiryStatus !=null">
                    and xt.inquiryStatus <=#{inquiryStatus}
                </if>
    
            </where>
            GROUP BY xt.requestForQuotationId
            ORDER BY xt.createTime
        </select>
        </mapper>