SpringBoot实战：多表联查

1. 保存和更新公寓信息

请求数据的结构 

@Schema(description = "公寓信息")
@Data
public class ApartmentSubmitVo extends ApartmentInfo {@Schema(description="公寓配套id")private List<Long> facilityInfoIds;@Schema(description="公寓标签id")private List<Long> labelIds;@Schema(description="公寓杂费值id")private List<Long> feeValueIds;@Schema(description="公寓图片id")private List<GraphVo> graphVoList;}

Controller层

    @Operation(summary = "保存或更新公寓信息")@PostMapping("saveOrUpdate")public Result saveOrUpdate(@RequestBody ApartmentSubmitVo apartmentSubmitVo) {apartmentInfoService.saveOrUpdateApartment(apartmentSubmitVo);return Result.ok();}

Service层

 void saveOrUpdateApartment(ApartmentSubmitVo apartmentSubmitVo);

/*** 保存或更新公寓信息** @param apartmentSubmitVo 公寓信息提交对象*                          若传入公寓ID为空，则执行插入操作；若不为空，则执行更新操作*                          更新操作时会先删除原有配套、杂费、标签和图片信息，再插入新的信息*/@Overridepublic void saveOrUpdateApartment(ApartmentSubmitVo apartmentSubmitVo) {//保存或修改公寓信息：//1.判断该参数id是否为空，为空：插入的新数据 不为空：修改数据boolean isUpdate = apartmentSubmitVo.getId() != null;//2.调用父类保存或修改方法，将公寓基本信息进行保存super.saveOrUpdate(apartmentSubmitVo);if (isUpdate) {//修改数据：直接将所有原数据删除后重新插入//1.删除配套LambdaQueryWrapper<ApartmentFacility> facilityLambdaQueryWrapper = new LambdaQueryWrapper<>();facilityLambdaQueryWrapper.eq(ApartmentFacility::getApartmentId, apartmentSubmitVo.getId());facilityService.remove(facilityLambdaQueryWrapper);//2.删除杂费LambdaQueryWrapper<ApartmentFeeValue> feeValueLambdaQueryWrapper = new LambdaQueryWrapper<>();feeValueLambdaQueryWrapper.eq(ApartmentFeeValue::getFeeValueId, apartmentSubmitVo.getId());feeValueService.remove(feeValueLambdaQueryWrapper);//3.删除标签LambdaQueryWrapper<ApartmentLabel> labelLambdaQueryWrapper = new LambdaQueryWrapper<>();labelLambdaQueryWrapper.eq(ApartmentLabel::getLabelId, apartmentSubmitVo.getId());labelService.remove(labelLambdaQueryWrapper);//4.删除图片LambdaQueryWrapper<GraphInfo> graphInfoLambdaQueryWrapper = new LambdaQueryWrapper<>();graphInfoLambdaQueryWrapper.eq(GraphInfo::getId, apartmentSubmitVo.getId());graphInfoService.remove(graphInfoLambdaQueryWrapper);}//1.插入配套List<Long> facilityInfoIdsList = apartmentSubmitVo.getFacilityInfoIds();if (!CollectionUtils.isEmpty(facilityInfoIdsList)) {ArrayList<ApartmentFacility> facilityArrayList = new ArrayList<>();for (Long facilityId : facilityInfoIdsList) {ApartmentFacility apartmentFacility = ApartmentFacility.builder().build();apartmentFacility.setFacilityId(facilityId);apartmentFacility.setApartmentId(apartmentSubmitVo.getId());facilityArrayList.add(apartmentFacility);}facilityService.saveBatch(facilityArrayList);}//2.插入杂费List<Long> feeValueIds = apartmentSubmitVo.getFeeValueIds();if (!CollectionUtils.isEmpty(feeValueIds)) {ArrayList<ApartmentFeeValue> apartmentFeeValueList = new ArrayList<>();for (Long feeValueId : feeValueIds) {ApartmentFeeValue apartmentFeeValue = ApartmentFeeValue.builder().build();apartmentFeeValue.setApartmentId(apartmentSubmitVo.getId());apartmentFeeValue.setFeeValueId(feeValueId);apartmentFeeValueList.add(apartmentFeeValue);}feeValueService.saveBatch(apartmentFeeValueList);}//3.插入标签List<Long> labelIds = apartmentSubmitVo.getLabelIds();if (!CollectionUtils.isEmpty(labelIds)) {ArrayList<ApartmentLabel> apartmentLabelArrayList = new ArrayList<>();for (Long labelId : labelIds) {ApartmentLabel apartmentLabel = ApartmentLabel.builder().build();apartmentLabel.setApartmentId(apartmentSubmitVo.getId());apartmentLabel.setLabelId(labelId);apartmentLabelArrayList.add(apartmentLabel);}labelService.saveBatch(apartmentLabelArrayList);}//4.插入图片List<GraphVo> graphVoList = apartmentSubmitVo.getGraphVoList();if (!CollectionUtils.isEmpty(graphVoList)) {ArrayList<GraphInfo> graphInfos = new ArrayList<>();for (GraphVo graphVo : graphVoList) {GraphInfo graphInfo = new GraphInfo();graphInfo.setItemType(ItemType.APARTMENT);graphInfo.setItemId(apartmentSubmitVo.getId());graphInfo.setName(graphVo.getName());graphInfo.setUrl(graphVo.getUrl());graphInfos.add(graphInfo);}graphInfoService.saveBatch(graphInfos);}}

2. 根据条件分页查询

请求数据结构

@Data
@Schema(description = "公寓查询实体")
public class ApartmentQueryVo {@Schema(description = "省份id")private Long provinceId;@Schema(description = "城市id")private Long cityId;@Schema(description = "区域id")private Long districtId;
}

 响应数据结构

@Data
@Schema(description = "后台管理系统公寓列表实体")
public class ApartmentItemVo extends ApartmentInfo {@Schema(description = "房间总数")private Long totalRoomCount;@Schema(description = "空闲房间数")private Long freeRoomCount;}

Controller层

    @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层

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

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

Mapper层

IPage<ApartmentItemVo> pageApartmentItemByQuery(IPage<ApartmentItemVo> page, ApartmentQueryVo queryVo);

多表联查大sql

    <select id="pageItem" resultType="com.atguigu.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,ifnull(tc.cnt, 0) - ifnull(cc.cnt, 0) free_room_countfrom (select id,name,introduction,district_id,district_name,city_id,city_name,province_id,province_name,address_detail,latitude,longitude,phone,is_releasefrom 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>) aileft join(select apartment_id,count(*) cntfrom room_infowhere is_deleted = 0and is_release = 1group by apartment_id) tcon ai.id = tc.apartment_idleft join(select apartment_id,count(*) cntfrom lease_agreementwhere is_deleted = 0and status in (2, 5)group by apartment_id) ccon ai.id = cc.apartment_id</select>

knife4j调整传递参数

默认情况下Knife4j为该接口生成的接口文档如下图所示，其中的queryVo参数不方便调试 

可在application.yml文件中增加如下配置，将queryVo做打平处理

springdoc:default-flat-param-object: true

 

 3. 根据ID获取公寓详细信息

响应数据

@Schema(description = "公寓信息")
@Data
public class ApartmentDetailVo extends ApartmentInfo {@Schema(description = "图片列表")private List<GraphVo> graphVoList;@Schema(description = "标签列表")private List<LabelInfo> labelInfoList;@Schema(description = "配套列表")private List<FacilityInfo> facilityInfoList;@Schema(description = "杂费列表")private List<FeeValueVo> feeValueVoList;
}

 Controller层

    @Operation(summary = "根据ID获取公寓详细信息")@GetMapping("getDetailById")public Result<ApartmentDetailVo> getDetailById(@RequestParam Long id) {ApartmentDetailVo apartmentDetailVo = apartmentInfoService.getDetailById(id);return Result.ok(apartmentDetailVo);}

Service层

ApartmentDetailVo getDetailById(Long id);

    @Overridepublic ApartmentDetailVo getDetailById(Long id) {//1.查询公寓基本信息ApartmentInfo apartmentInfo = apartmentInfoMapper.selectById(id);//2.查询图片列表List<GraphVo> graphVoList = graphInfoMapper.selectGraphVoList(id, ItemType.APARTMENT);//3.查询标签列表List<LabelInfo> labelInfoList = labelInfoMapper.selectLabelInfoList(id); //通过公寓id查询出所有标签id，在通过标签id查出所有标签信息//4.查询配套列表List<FacilityInfo> facilityInfoList = facilityInfoMapper.selectFacilityInfoList(id); //通过公寓id查询出所有配套id，在通过配套id查出所有配套信息//5.查询杂费列表List<FeeValueVo> feeValueList = feeValueMapper.selectFeeValueList(id); //通过公寓id查询出所有杂费值id，在通过杂费值id查出所有杂费以及杂费名称id，再通过杂费名称id查出杂费名//6.组装结果ApartmentDetailVo apartmentDetailVo = new ApartmentDetailVo();BeanUtils.copyProperties(apartmentInfo, apartmentDetailVo);apartmentDetailVo.setGraphVoList(graphVoList);apartmentDetailVo.setLabelInfoList(labelInfoList);apartmentDetailVo.setFacilityInfoList(facilityInfoList);apartmentDetailVo.setFeeValueVoList(feeValueList);return apartmentDetailVo;}

Mapper。。。

4. 根据ID删除公寓信息

    @Operation(summary = "根据id删除公寓信息")@DeleteMapping("removeById")public Result removeById(@RequestParam Long id) {apartmentInfoService.removeApartmentById(id);return Result.ok();}