[尚庭公寓]14-找房模块
地区信息
对于找房模块，地区信息共需三个接口，分别是**查询省份列表**、**根据省份ID查询城市列表**、**根据城市ID查询区县列表**，具体实现如下
package com.atguigu.lease.web.app.controller.region;@Tag(name = \"地区信息\")@RestController@RequestMapping(\"/app/region\")public class RegionController { @Autowired private ProvinceInfoService provinceInfoService; @Autowired private CityInfoService cityInfoService; @Autowired private DistrictInfoService districtInfoService; @Operation(summary=\"查询省份信息列表\") @GetMapping(\"province/list\") public Result<List> listProvince(){ List list = provinceInfoService.list(); return Result.ok(list); } @Operation(summary=\"根据省份id查询城市信息列表\") @GetMapping(\"city/listByProvinceId\") public Result<List> listCityInfoByProvinceId(@RequestParam Long id){ LambdaQueryWrapper queryWrapper = new LambdaQueryWrapper(); queryWrapper.eq(CityInfo::getProvinceId,id); List list = cityInfoService.list(queryWrapper); return Result.ok(list); } @GetMapping(\"district/listByCityId\") @Operation(summary=\"根据城市id查询区县信息\") public Result<List> listDistrictInfoByCityId(@RequestParam Long id){ LambdaQueryWrapper queryWrapper = new LambdaQueryWrapper(); queryWrapper.eq(DistrictInfo::getCityId,id); List list = districtInfoService.list(queryWrapper); return Result.ok(list); }}支付方式
package com.atguigu.lease.web.app.controller.payment;@Tag(name = \"支付方式接口\")@RestController@RequestMapping(\"/app/payment\")public class PaymentTypeController { @Autowired private PaymentTypeService paymentTypeService; @Operation(summary = \"获取全部支付方式列表\") @GetMapping(\"list\") public Result<List> list() { List list = paymentTypeService.list(); return Result.ok(list); }}
房间信息
根据条件分页查询房间列表
package com.atguigu.lease.web.app.controller.room;@Tag(name = \"房间信息\")@RestController@RequestMapping(\"/app/room\")public class RoomController { @Autowired RoomInfoService roomInfoService; @Operation(summary = \"分页查询房间列表\") @GetMapping(\"pageItem\") public Result<IPage> pageItem(@RequestParam long current, @RequestParam long size, RoomQueryVo queryVo) { IPage page = new Page(current, size); IPage list = roomInfoService.pageItem(page, queryVo); return Result.ok(); }}package com.atguigu.lease.web.app.service.impl;/** * @author liubo * @description 针对表【room_info(房间信息表)】的数据库操作Service实现 * @createDate 2023-07-26 11:12:39 */@Service@Slf4jpublic class RoomInfoServiceImpl extends ServiceImpl implements RoomInfoService { @Autowired private RoomInfoMapper roomInfoMapper; @Override public IPage pageItem(IPage page, RoomQueryVo queryVo) { return roomInfoMapper.pageRoomItemByQuery(page, queryVo); }} select ri.id as room_id, ri.room_number, ri.rent, 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 from room_info ri left join apartment_info ai on ri.apartment_id = ai.id and ai.is_deleted = 0 ri.is_deleted = 0 and ri.is_release = 1 and ai.province_id = #{queryVo.provinceId} and ai.city_id = #{queryVo.cityId} and ai.district_id = #{queryVo.districtId} and ri.rent >= #{queryVo.minRent} and ri.rent <= #{queryVo.maxRent} order by ri.rent desc ${queryVo.orderType} select id, name, url from graph_info where is_deleted = 0 and item_type = 2 and item_id = #{id}; select id, type, name from label_info where is_deleted = 0 and type = 2 and id in ( select label_id from room_label where room_id = #{id} and is_deleted = 1 )
知识点
xml文件``的转义
由于xml文件中的``是特殊符号，需要转义处理。
Mybatis-Plus分页插件注意事项
- 使用Mybatis-Plus的分页插件进行分页查询时，如果结果需要使用``进行映射，只能使用**[嵌套查询（Nested Select for Collection）](https://mybatis.org/mybatis-3/sqlmap-xml.html#nested-select-for-collection)**，而不能使用**[嵌套结果映射（Nested Results for Collection）](https://mybatis.org/mybatis-3/sqlmap-xml.html#nested-results-for-collection)**。
- **嵌套查询**和**嵌套结果映射**是Collection映射的两种方式，下面通过一个案例进行介绍
- 例如有`room_info`和`graph_info`两张表，其关系为一对多，如下
- 现需要查询房间列表及其图片信息，期望返回的结果如下
[ { \"id\": 1, \"number\": 201, \"rent\": 2000, \"graphList\": [ {\"id\": 1,\"url\": \"http://\",\"roomId\": 1 }, {\"id\": 2,\"url\": \"http://\",\"roomId\": 1 } ] }, { \"id\": 2, \"number\": 202, \"rent\": 3000, \"graphList\": [ {\"id\": 3,\"url\": \"http://\",\"roomId\": 2 }, {\"id\": 4,\"url\": \"http://\",\"roomId\": 2 } ] } ]- 为得到上述结果，可使用以下两种方式
嵌套结果映射 select ri.id room_id, ri.number, ri.rent, gi.id graph_id, gi.url, gi.room_id from room_info ri left join graph_info gi on ri.id=gi.room_id 这种方式的执行原理如下图所示
嵌套查询 select id, number, rent from room_info select id, url, room_id from graph_info where room_id = #{id} 这种方法使用两个独立的查询语句来获取一对多关系的数据。首先，Mybatis会执行主查询来获取`room_info`列表，然后对于每个`room_info`，Mybatis都会执行一次子查询来获取其对应的`graph_info`。
- 若现在使用MybatisPlus的分页插件进行分页查询，假如查询的内容是第**1**页，每页**2**条记录，则上述两种方式的查询结果分别是
- 显然**嵌套结果映射**的分页逻辑是存在问题的(数据条数少)
根据ID查询房间详细信息
package com.atguigu.lease.web.app.controller.room;@Tag(name = \"房间信息\")@RestController@RequestMapping(\"/app/room\")public class RoomController { @Autowired RoomInfoService roomInfoService; @Operation(summary = \"根据id获取房间的详细信息\") @GetMapping(\"getDetailById\") public Result getDetailById(@RequestParam Long id) { RoomDetailVo roomInfo = roomInfoService.getDetailById(id); return Result.ok(roomInfo); }}package com.atguigu.lease.web.app.service.impl;/** * @author liubo * @description 针对表【room_info(房间信息表)】的数据库操作Service实现 * @createDate 2023-07-26 11:12:39 */@Service@Slf4jpublic class RoomInfoServiceImpl extends ServiceImpl implements RoomInfoService { @Autowired private RoomInfoMapper roomInfoMapper; @Autowired GraphInfoMapper graphInfoMapper; @Autowired LeaseTermMapper leaseTermMapper; @Autowired FacilityInfoMapper facilityInfoMapper; @Autowired LabelInfoMapper labelInfoMapper; @Autowired PaymentTypeMapper paymentTypeMapper; @Autowired AttrValueMapper attrValueMapper; @Autowired FeeValueMapper feeValueMapper; @Autowired ApartmentInfoService apartmentInfoService; @Override public RoomDetailVo getDetailById(Long id) { RoomInfo roomInfo = roomInfoMapper.selectById(id); if (roomInfo == null) { return null; } //2.查询图片 List graphVoList = graphInfoMapper.selectListByItemTypeAndId(ItemType.ROOM, id); //3.查询租期 List leaseTermList = leaseTermMapper.selectListByRoomId(id); //4.查询配套 List facilityInfoList = facilityInfoMapper.selectListByRoomId(id); //5.查询标签 List labelInfoList = labelInfoMapper.selectListByRoomId(id); //6.查询支付方式 List paymentTypeList = paymentTypeMapper.selectListByRoomId(id); //7.查询基本属性 List attrValueVoList = attrValueMapper.selectListByRoomId(id); //8.查询杂费信息 List feeValueVoList = feeValueMapper.selectListByApartmentId(roomInfo.getApartmentId()); //9.查询公寓信息 ApartmentItemVo apartmentItemVo = apartmentInfoService.selectApartmentItemVoById(roomInfo.getApartmentId()); RoomDetailVo roomDetailVo = new RoomDetailVo(); BeanUtils.copyProperties(roomInfo, roomDetailVo); roomDetailVo.setGraphVoList(graphVoList); roomDetailVo.setLeaseTermList(leaseTermList); roomDetailVo.setFacilityInfoList(facilityInfoList); roomDetailVo.setLabelInfoList(labelInfoList); roomDetailVo.setPaymentTypeList(paymentTypeList); roomDetailVo.setAttrValueVoList(attrValueVoList); roomDetailVo.setFeeValueVoList(feeValueVoList); roomDetailVo.setApartmentItemVo(apartmentItemVo); return roomDetailVo; }} select id, name, url from graph_info where item_type = #{room} and item_id = #{id} and is_deleted = 0 select * from lease_term where is_deleted = 0 and id in (select lease_term_id from room_lease_term where room_id = #{id} and is_deleted = 0) select * from facility_info where id in ( select facility_id from room_facility where room_id = #{id} and is_deleted = 0 ) and is_deleted = 0 select * from label_info where is_deleted = 0 and id in ( select label_id from room_label where room_id = #{id} and is_deleted = 0 ) select * from payment_type where id in ( select payment_type_id from room_payment_type where room_id = #{id} and is_deleted = 0 ) and is_deleted = 0 select av.id, av.name, ak.id as attr_key_id, ak.name as attr_key_name from attr_value av left join attr_key ak on av.attr_key_id = ak.id and ak.is_deleted = 0 where av.id in ( select attr_value_id from room_attr_value where room_id = #{id} and is_deleted = 0 ) and av.is_deleted = 0 select * from fee_value fv where fv.id in ( select fee_value_id from apartment_fee_value where apartment_id = #{apartmentId} and is_deleted = 0 ) and fv.is_deleted = 0 package com.atguigu.lease.web.app.service.impl;/** * @author liubo * @description 针对表【apartment_info(公寓信息表)】的数据库操作Service实现 * @createDate 2023-07-26 11:12:39 */@Servicepublic class ApartmentInfoServiceImpl extends ServiceImpl implements ApartmentInfoService { @Autowired private ApartmentInfoMapper apartmentInfoMapper; @Autowired private LabelInfoMapper labelInfoMapper; @Autowired private GraphInfoMapper graphInfoMapper; @Autowired private RoomInfoMapper roomInfoMapper; @Override public ApartmentItemVo selectApartmentItemVoById(Long apartmentId) { ApartmentInfo apartmentInfo = apartmentInfoMapper.selectById(apartmentId); List labelInfoList = labelInfoMapper.selectListByApartmentId(apartmentId); List graphVoList = graphInfoMapper.selectListByItemTypeAndId(ItemType.APARTMENT, apartmentId); BigDecimal minRent = roomInfoMapper.selectMinRentByApartmentId(apartmentId); ApartmentItemVo apartmentItemVo = new ApartmentItemVo(); BeanUtils.copyProperties(apartmentInfo, apartmentItemVo); apartmentItemVo.setLabelInfoList(labelInfoList); apartmentItemVo.setGraphVoList(graphVoList); apartmentItemVo.setMinRent(minRent); return apartmentItemVo; }}
根据公寓ID分页查询房间列表
package com.atguigu.lease.web.app.controller.room;@Tag(name = \"房间信息\")@RestController@RequestMapping(\"/app/room\")public class RoomController { @Autowired RoomInfoService roomInfoService; @Operation(summary = \"根据公寓id分页查询房间列表\") @GetMapping(\"pageItemByApartmentId\") public Result<IPage> pageItemByApartmentId(@RequestParam long current, @RequestParam long size, @RequestParam Long id) { IPage page = new Page(current, size); IPage result =roomInfoService.pageItemByApartmentId(page, id); return Result.ok(result); }}package com.atguigu.lease.web.app.service.impl;/** * @author liubo * @description 针对表【room_info(房间信息表)】的数据库操作Service实现 * @createDate 2023-07-26 11:12:39 */@Service@Slf4jpublic class RoomInfoServiceImpl extends ServiceImpl implements RoomInfoService { @Autowired private RoomInfoMapper roomInfoMapper; @Override public IPage pageItemByApartmentId(IPage page, Long id) { IPage res = roomInfoMapper.pageItemByApartmentId(page,id); return res; }} select id, name, url from graph_info where is_deleted = 0 and item_type = 2 and item_id = #{id}; select id, type, name from label_info where is_deleted = 0 and type = 2 and id in ( select label_id from room_label where room_id = #{id} and is_deleted = 1 ) select min(rent) from room_info where apartment_id = #{id} and is_deleted = 0 and is_release = 1 select ri.id as room_id, ri.room_number, ri.rent, 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 from room_info ri left join apartment_info ai on ri.apartment_id = ai.id and ai.is_deleted = 0 where ri.is_deleted = 0 and ri.is_release = 1
公寓信息
公寓信息只需一个接口，即**根据ID查询公寓详细信息**，具体实现如下
package com.atguigu.lease.web.app.controller.apartment;@RestController@Tag(name = \"公寓信息\")@RequestMapping(\"/app/apartment\")public class ApartmentController { @Autowired ApartmentInfoService apartmentInfoService; @Operation(summary = \"根据id获取公寓信息\") @GetMapping(\"getDetailById\") public Result getDetailById(@RequestParam Long id) { ApartmentDetailVo vo = apartmentInfoService.selectApartmentDetailById(id); return Result.ok(vo); }}package com.atguigu.lease.web.app.service.impl;/** * @author liubo * @description 针对表【apartment_info(公寓信息表)】的数据库操作Service实现 * @createDate 2023-07-26 11:12:39 */@Servicepublic class ApartmentInfoServiceImpl extends ServiceImpl implements ApartmentInfoService { @Autowired private ApartmentInfoMapper apartmentInfoMapper; @Autowired private LabelInfoMapper labelInfoMapper; @Autowired private GraphInfoMapper graphInfoMapper; @Autowired private RoomInfoMapper roomInfoMapper; @Autowired private FacilityInfoMapper facilityInfoMapper; @Override public ApartmentDetailVo selectApartmentDetailById(Long id) { ApartmentInfo apartmentInfo = apartmentInfoMapper.selectById(id); List graphVoList = graphInfoMapper.selectListByItemTypeAndId(ItemType.APARTMENT, id); List labelInfoList = labelInfoMapper.selectListByApartmentId(id); BigDecimal minRent = roomInfoMapper.selectMinRentByApartmentId(id); List facilityInfoList = facilityInfoMapper.selectListFacilityInfoByApartmentId(id); ApartmentDetailVo apartmentDetailVo = new ApartmentDetailVo(); BeanUtils.copyProperties(apartmentInfo, apartmentDetailVo); apartmentDetailVo.setGraphVoList(graphVoList); apartmentDetailVo.setLabelInfoList(labelInfoList); apartmentDetailVo.setMinRent(minRent); apartmentDetailVo.setFacilityInfoList(facilityInfoList); return apartmentDetailVo; }} select * from facility_info where id in ( select facility_id from apartment_facility where apartment_id = #{id} and is_deleted = 0 ) and is_deleted = 0 
