一、提出疑问-为什么要从数据库中把省市区信息返回给前端
1、本项目是一个商城项目-在进行计算运费价格的时候,需要知道用户所填地址信息,是否在包邮地区,并且根据距离、地区、包裹件数、重量等计算邮费,所以需要通过一个city_id来唯一区分城市id;
2、在全国范围内,存在同名的区名情况下,我不能根据简单的名称查询区查询城市的id,那么只能通过省份名称、市级名称、县级名称来唯一确定城市的id
3、因为地方名称的更改-数据库中的信息-可能和前端静态信息不匹配等,所以需要从数据中查询省市-区信息。
第一版本代码-
1、通过LambdaQueryWrapper 查询
2、通过封装响应类
3、通过递归查询
控制器代码
@Operation(summary = "获取地址导航")
@RequestMapping(value = "/getAddressHead", method = RequestMethod.GET)
public List<CityInfoResponse> getAddressHead() {
return systemCityService.getCityInfo();
}
Service层代码
@Override
public List<CityInfoResponse> getCityInfo() {
LambdaQueryWrapper<SystemCity> lqwProvince=new LambdaQueryWrapper<>();
lqwProvince.eq(SystemCity::getLevel,0);
//获取所有城市省级城市信息
List<SystemCity> systemCities0 = dao.selectList(lqwProvince);
List<CityInfoResponse> cityInfoResponses = new ArrayList<>();
for (SystemCity systemCity0 : systemCities0) {
CityInfoResponse cityInfoResponse = new CityInfoResponse();
BeanUtils.copyProperties(systemCity0,cityInfoResponse);
//根据当前省级城市id,获取下辖的市信息
Integer cityId0 = systemCity0.getCityId();
List<CityInfoResponse> citys = getCityId(cityId0);
cityInfoResponse.setCityInfoResponses(citys);
cityInfoResponses.add(cityInfoResponse);
}
return cityInfoResponses;
}
/**
* @Author luozhao
* @Description 根据当前城市id,查询下级城市信息集合
* @Date 11:32 2023/3/25
* @param cityId
**/
List<CityInfoResponse> getCityId(Integer cityId){
LambdaQueryWrapper<SystemCity> lqw=new LambdaQueryWrapper<>();
lqw.eq(SystemCity::getParentId,cityId);
List<SystemCity> systemCities1 = dao.selectList(lqw);
List<CityInfoResponse> cityInfoResponses = new ArrayList<>();
//第一次市级城市遍历
for (SystemCity systemCity : systemCities1) {
CityInfoResponse cityInfoResponse = new CityInfoResponse();
if (systemCity.getLevel()!=2){
Integer cityId1 = systemCity.getCityId();
List<CityInfoResponse> citys2 = getCityId(cityId1);
cityInfoResponse.setCityInfoResponses(citys2);
}
BeanUtils.copyProperties(systemCity,cityInfoResponse);
cityInfoResponses.add(cityInfoResponse);
}
return cityInfoResponses;
}
数据库基本结构-
第二版代码-去掉 拷贝对象,-直接返回响应类封装集合、
@Override
public List<CityInfoResponse> getCityInfo1() {
//获取所有城市省级城市信息
List<CityInfoResponse> cityInfoResponses = dao.queryCityInfo(0,null);
for (CityInfoResponse response : cityInfoResponses) {
//根据当前省级城市id,获取下辖的市信息
Integer cityId0 = response.getCityId();
List<CityInfoResponse> citys = getCityId1(cityId0);
response.setCityInfoResponses(citys);
}
return cityInfoResponses;
}
/**
* @Author luozhao
* @Description 根据当前城市id,查询下级城市信息集合
* @Date 11:32 2023/3/25
* @param cityId
**/
List<CityInfoResponse> getCityId1(Integer cityId){
List<CityInfoResponse> cityInfoResponses = dao.queryCityInfo(null,cityId);
//第一次市级城市遍历
for (CityInfoResponse cityInfoResponse : cityInfoResponses) {
if (cityInfoResponse.getLevel()!=2){
//当前市级城市id
Integer cityId1 = cityInfoResponse.getCityId();
List<CityInfoResponse> citys2 = getCityId1(cityId1);
cityInfoResponse.setCityInfoResponses(citys2);
}
}
return cityInfoResponses;
}
package com.fangzhou.service.vo.response;
import io.swagger.v3.oas.annotations.media.Schema;
import io.swagger.v3.oas.annotations.tags.Tag;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.util.List;
/**
* @Classname CityInfoResponse
* @Description TODO
* @Version 1.0.0
* @Date 2023/3/25 11:05
* @Created by luozhao
*/
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@Tag(name="CityInfoResponse", description="")
public class CityInfoResponse {
@Schema(title = "城市id")
private Integer cityId;
@Schema(title = "名称")
private String name;
@Schema(title = "上级城市id")
private Integer parentId;
@Schema(title = "城市等级",description = "0 、为省级、直辖市、特别行政区等; 1为市、0为县或者区等")
private Integer level;
private List<CityInfoResponse> cityInfoResponses;
}
<select id="queryCityInfo" resultType="com.fangzhou.service.vo.response.CityInfoResponse">
select city_id,
`name`,
parent_id,
`level`
from mh_system_city where 1=1
<if test="parentId != null">
AND parent_id=#{parentId}
</if>
<if test="level != null">
AND level=#{level}
</if>
</select>
第三版本代码-通过redis存储,因为省-市-区很少变动
@Override
public List<CityInfoResponse> getCityInfo1() {
boolean cityInfo = redisCache.exists(RedisConstants.MH_SYSTEM_CITY_INFO);
if (cityInfo) return redisCache.get(RedisConstants.MH_SYSTEM_CITY_INFO);
//获取所有城市省级城市信息
List<CityInfoResponse> cityInfoResponses = dao.queryCityInfo(0,null);
for (CityInfoResponse response : cityInfoResponses) {
//根据当前省级城市id,获取下辖的市信息
Integer cityId0 = response.getCityId();
List<CityInfoResponse> citys = getCityId1(cityId0);
response.setCityInfoResponses(citys);
}
redisCache.set(RedisConstants.MH_SYSTEM_CITY_INFO,cityInfoResponses);
return cityInfoResponses;
}
第一版本 响应时间-平均1.5秒左右
第二版本响应时间-0.5秒左右
第三版本响应时间-0.01秒左右