尚庭公寓----------分页查询

根据条件分页查询公寓列表

进行分页配置

package com.nie.lease.common.mybatisplus;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
@MapperScan("com.nie.lease.web.*.mapper")
@MapperScan("com.nie.lease.web.*.mapper")
public class MybatisPlusConfiguration {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }


}

controller层

package com.nie.lease.web.admin.controller.apartment;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.nie.lease.common.result.Result;
import com.nie.lease.model.entity.ApartmentInfo;
import com.nie.lease.model.enums.ReleaseStatus;
import com.nie.lease.web.admin.service.ApartmentInfoService;
import com.nie.lease.web.admin.vo.apartment.ApartmentDetailVo;
import com.nie.lease.web.admin.vo.apartment.ApartmentItemVo;
import com.nie.lease.web.admin.vo.apartment.ApartmentQueryVo;
import com.nie.lease.web.admin.vo.apartment.ApartmentSubmitVo;
import com.baomidou.mybatisplus.core.metadata.IPage;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import org.checkerframework.checker.units.qual.A;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;

@Tag(name = "公寓信息管理")
@RestController
@RequestMapping("/admin/apartment")
public class ApartmentController {

    @Operation(summary = "根据条件分页查询公寓列表")
    @GetMapping("pageItem")
    public Result<IPage<ApartmentItemVo>> pageItem(@RequestParam long current, @RequestParam long size, ApartmentQueryVo queryVo) {
        Page<ApartmentItemVo> page = new Page<>(current, size);
        IPage<ApartmentItemVo> result=apartmentInfoService.pageItem(page, queryVo);
        return Result.ok(result);
    }

service层

package com.nie.lease.web.admin.service;

import com.nie.lease.model.entity.ApartmentInfo;
import com.nie.lease.web.admin.vo.apartment.ApartmentDetailVo;
import com.nie.lease.web.admin.vo.apartment.ApartmentItemVo;
import com.nie.lease.web.admin.vo.apartment.ApartmentQueryVo;
import com.nie.lease.web.admin.vo.apartment.ApartmentSubmitVo;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.IService;

/**
* @author liubo
* @description 针对表【apartment_info(公寓信息表)】的数据库操作Service
* @createDate 2023-07-24 15:48:00
*/
public interface ApartmentInfoService extends IService<ApartmentInfo> {

    void saveOrUpdateApartment(ApartmentSubmitVo apartmentSubmitVo);

    IPage<ApartmentItemVo> pageItem(Page<ApartmentItemVo> page, ApartmentQueryVo queryVo);
}

service实现类

package com.nie.lease.web.admin.service.impl;

import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.nie.lease.model.entity.*;
import com.nie.lease.model.enums.ItemType;
import com.nie.lease.web.admin.mapper.ApartmentInfoMapper;
import com.nie.lease.web.admin.service.*;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.nie.lease.web.admin.vo.apartment.ApartmentItemVo;
import com.nie.lease.web.admin.vo.apartment.ApartmentQueryVo;
import com.nie.lease.web.admin.vo.apartment.ApartmentSubmitVo;
import com.nie.lease.web.admin.vo.graph.GraphVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * @author liubo
 * @description 针对表【apartment_info(公寓信息表)】的数据库操作Service实现
 * @createDate 2023-07-24 15:48:00
 */
@Service
public class ApartmentInfoServiceImpl extends ServiceImpl<ApartmentInfoMapper, ApartmentInfo>
        implements ApartmentInfoService {

    @Autowired
    private ApartmentInfoMapper apartmentInfoMapper;

    @Override
    public IPage<ApartmentItemVo> pageItem(Page<ApartmentItemVo> page, ApartmentQueryVo queryVo) {
        return apartmentInfoMapper.pageItem(page, queryVo);
    }
}

mapper接口

package com.nie.lease.web.admin.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.nie.lease.model.entity.ApartmentInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.nie.lease.web.admin.vo.apartment.ApartmentItemVo;
import com.nie.lease.web.admin.vo.apartment.ApartmentQueryVo;

/**
* @author liubo
* @description 针对表【apartment_info(公寓信息表)】的数据库操作Mapper
* @createDate 2023-07-24 15:48:00
*/
public interface ApartmentInfoMapper extends BaseMapper<ApartmentInfo> {

    IPage<ApartmentItemVo> pageItem(Page<ApartmentItemVo> page, ApartmentQueryVo queryVo);
}

mapper.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.nie.lease.web.admin.mapper.ApartmentInfoMapper">

    <select id="pageItem" resultType="com.nie.lease.web.admin.vo.apartment.ApartmentItemVo">
        select ai.id,
               ai.name,
               ai.introduction,
               ai.district_id,
               ai.district_name,
               ai.city_id,
               ai.city_name,
               ai.province_id,
               ai.province_name,
               ai.address_detail,
               ai.latitude,
               ai.longitude,
               ai.phone,
               ai.is_release,
               ifnull(tc.cnt,0) total_room_count,
               tc.cnt - ifnull(cc.cnt,0) free_room_count
        from (select id,
                     name,
                     introduction,
                     district_id,
                     district_name,
                     city_id,
                     city_name,
                     province_id,
                     province_name,
                     address_detail,
                     latitude,
                     longitude,
                     phone,
                     is_release
              from apartment_info
        <where>
            is_deleted=0
            <if test="queryVo.provinceId !=null">
                and province_id=#{queryVo.provinceId}
            </if>
            <if test="queryVo.cityId !=null">
                and city_id=#{queryVo.cityId}
            </if>
            <if test="queryVo.districtId !=null">
                and district_id=#{queryVo.districtId}
            </if>
        </where>
              ) ai
                 left join
             (select apartment_id,
                     COUNT(*) cnt
              from room_info
              where is_deleted = 0
                and is_release = 1
              group by apartment_id) tc
             on ai.id = tc.apartment_id

                 left join
             (select apartment_id,
                     COUNT(*) cnt
              from lease_agreement
              where is_deleted = 0
                and status in (2, 5)
              group by apartment_id) cc
             on ai.id = cc.apartment_id

    </select>
</mapper>