Springboot+MyBatis分页查询案例

Springboot+MyBatis分页查询案例

controller

  /**
     * 查询统计信息
     * 按照监控点名称、单位、板块、所属部位等条件查询
     */
    @GetMapping("/findInfo")
    @ApiOperation(value = "查询统计信息列表")
    public BaseResponse<PageBean<TotalCameraInfoDto>> findInfo(@Param("pageNo") int pageNo,@Param("pageSize") int pageSize, @Param("relatedPropertyIdDto") RelatedPropertyIdDto relatedPropertyIdDto) {
        BaseResponse<PageBean<TotalCameraInfoDto>> response = cameraInfoService.findInfo(pageNo,pageSize,relatedPropertyIdDto);
        return response;
    }

service

 /**
     * 按照条件查询
     */
    BaseResponse<PageBean<TotalCameraInfoDto>> findInfo(int pageNo,int pageSize,@Param("relatedPropertyIdDto") RelatedPropertyIdDto relatedPropertyIdDto);

serviceImpl

 /**
     * 按照条件查询
     */
    @Override
    public BaseResponse<PageBean<TotalCameraInfoDto>> findInfo(int pageNo, int pageSize, RelatedPropertyIdDto relatedPropertyIdDto) {
        BaseResponse<PageBean<TotalCameraInfoDto>> response = new BaseResponse();
        //创建pageBean对象
        PageBean<TotalCameraInfoDto> pageBean = new PageBean<TotalCameraInfoDto>();
        //设置参数
        pageBean.setPageNo(pageNo);
        pageBean.setPageSize(pageSize);
        try {
            //调用dao查询总记录数
            int totalCount = cameraInfoMapper.getFindInfoCount(relatedPropertyIdDto);
            pageBean.setTotalCount(totalCount);
            //调用dao查询List集合
            int start = (pageNo - 1) * pageSize;
            List<TotalCameraInfoDto> lists = cameraInfoMapper.findInfo(pageNo, pageSize, relatedPropertyIdDto);
            for (TotalCameraInfoDto t : lists) {
                t.setRegionIndexCode(this.getRegionNameById(t.getRegionIndexCode()));
                //在线状态
                if("1".equals(t.getOnlineState())){
                    t.setOnlineState("在线");
                }else{
                    t.setOnlineState("离线");
                }

            }
            pageBean.setList(lists);
            //计算总页码
            int totalPage = (totalCount % pageSize) == 0 ? totalCount / pageSize : (totalCount / pageSize + 1);
            pageBean.setTotalPage(totalPage);

            if (CollectionUtils.isNotEmpty(lists)) {
                response.setCode("0");
                response.setMsg("查询统计信息成功");
                response.setData(pageBean);
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            response.setCode(ErrorCodeEnum.FIND_STATIC_INFO_ERROR.getCode());
            response.setMsg(ErrorCodeEnum.FIND_STATIC_INFO_ERROR.getMsg());
        }
        return response;
    }
}

mapper

 <!--按照条件查询-->
    <select id="findInfo" resultType="com.hikvision.aidp.dto.TotalCameraInfoDto">
        select region_index_code,
        (select pi.property_name from property_info pi where pi.property_id = rci.unit_id) as unit_name,
        (select pi.property_name from property_info pi where pi.property_id = rci.plate_id) as plate_name,
        (select pi.property_name from property_info pi where pi.property_id = rci.subordinate_part_id) as subordinate_part_name,
        camera_name,
        ip,
        online_state,
        offline_duration,
        not_recorded_duration,
        state_change_num,
        blur_duration
        from related_camera_info rci
        left join camera_info ci
        on rci.camera_index_code = ci.camera_index_code
        <where>
            <if test="relatedPropertyIdDto.cameraName != null and relatedPropertyIdDto.cameraName !=''">
                camera_name like '%${relatedPropertyIdDto.cameraName}%'

            </if>
            <if test="relatedPropertyIdDto.plateNames != null">
                and
                <foreach collection="relatedPropertyIdDto.plateNames" item="item" separator="or" open="(" close=")">
                    plate_id = #{item}
                </foreach>
            </if>
            <if test="relatedPropertyIdDto.subordinatePartNames!=null">
                and
                <foreach collection="relatedPropertyIdDto.subordinatePartNames" item="item" separator="or" open="("
                         close=")">
                    subordinate_part_id = #{item}
                </foreach>
            </if>
            <if test="relatedPropertyIdDto.unitNames!=null">
                and
                <foreach collection="relatedPropertyIdDto.unitNames" item="item" separator="or" open="(" close=")">
                    unit_id = #{item}
                </foreach>
            </if>
        </where>
        offset (#{pageNo}-1) * #{pageSize}
        limit #{pageSize}
    </select>

 <!--按照条件查询条数-->
    <select id="getFindInfoCount" parameterType="com.hikvision.aidp.dto.TotalCameraInfoDto" resultType="int">
        select count(*) from related_camera_info rci
        left join camera_info ci
        on rci.camera_index_code = ci.camera_index_code
        <where>
            <if test="relatedPropertyIdDto.cameraName != null and relatedPropertyIdDto.cameraName !=''">
                camera_name like '%${relatedPropertyIdDto.cameraName}%'
            </if>
            <if test="relatedPropertyIdDto.plateNames != null">
                and
                <foreach collection="relatedPropertyIdDto.plateNames" item="item" separator="or" open="(" close=")">
                    plate_id = #{item}
                </foreach>
            </if>
            <if test="relatedPropertyIdDto.subordinatePartNames!=null">
                and
                <foreach collection="relatedPropertyIdDto.subordinatePartNames" item="item" separator="or" open="("
                         close=")">
                    subordinate_part_id = #{item}
                </foreach>
            </if>
            <if test="relatedPropertyIdDto.unitNames!=null">
                and
                <foreach collection="relatedPropertyIdDto.unitNames" item="item" separator="or" open="(" close=")">
                    unit_id = #{item}
                </foreach>
            </if>
        </where>
    </select>