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为空，则执行插入操作；若不为空，则执行更新操作
* 更新操作时会先删除原有配套、杂费、标签和图片信息，再插入新的信息
*/
@Override
public 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); @Override
public 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_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>
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); @Override
public 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();
}原文地址：https://blog.csdn.net/2301_79526467/article/details/140253737
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！