通过递归查询省-市-区信息给前端-接口返回速度从1.5秒,优化到0.01s；从LambdaQueryWrapper查询---＞sql编写查询-＞redis缓存

一、提出疑问-为什么要从数据库中把省市区信息返回给前端

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秒左右