1. Ask questions – why should the province and city information be returned to the front end from the database
1. This project is a mall project – when calculating the shipping price, you need to know the address information filled in by the user, whether it is in the free shipping area, and calculate the postage based on the distance, area, number of packages, weight, etc., so you need to pass a city_id To uniquely distinguish the city id;
2. In the whole country, if there are district names with the same name, I can’t query the city’s id based on a simple name query, so I can only uniquely determine the city’s id through the province name, city-level name, and county-level name
3. Because the change of the place name – the information in the database – may not match the front-end static information, etc., it is necessary to query the province, city and district information from the data.
First version code –
1. Query through LambdaQueryWrapper
2. By encapsulating the response class
3. Through recursive query
Controller code
@Operation(summary = "Get address navigation")
@RequestMapping(value = "/getAddressHead", method = RequestMethod.GET)
public List<CityInfoResponse> getAddressHead() {<!-- -->
return systemCityService.getCityInfo();
}
Service layer code
@Override
public List<CityInfoResponse> getCityInfo() {<!-- -->
LambdaQueryWrapper<SystemCity> lqwProvince=new LambdaQueryWrapper<>();
lqwProvince.eq(SystemCity::getLevel,0);
//Get information about all cities and provincial cities
List<SystemCity> systemCities0 = dao.selectList(lqwProvince);
List<CityInfoResponse> cityInfoResponses = new ArrayList<>();
for (SystemCity systemCity0 : systemCities0) {<!-- -->
CityInfoResponse cityInfoResponse = new CityInfoResponse();
BeanUtils.copyProperties(systemCity0, cityInfoResponse);
//According to the current provincial city id, get the information of the city under its jurisdiction
Integer cityId0 = systemCity0.getCityId();
List<CityInfoResponse> cities = getCityId(cityId0);
cityInfoResponse.setCityInfoResponses(citys);
cityInfoResponses. add(cityInfoResponse);
}
return cityInfoResponses;
}
/**
* @Author luozhao
* @Description According to the current city id, query the lower-level city information collection
* @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<>();
//The first city-level city traversal
for (SystemCity systemCity : systemCities1) {<!-- -->
CityInfoResponse cityInfoResponse = new CityInfoResponse();
if (systemCity.getLevel()!=2){<!-- -->
Integer cityId1 = systemCity.getCityId();
List<CityInfoResponse> cities2 = getCityId(cityId1);
cityInfoResponse.setCityInfoResponses(citys2);
}
BeanUtils. copyProperties(systemCity, cityInfoResponse);
cityInfoResponses. add(cityInfoResponse);
}
return cityInfoResponses;
}
Basic database structure-
The second version of the code – remove the copy object, – directly return the response class package collection,
@Override
public List<CityInfoResponse> getCityInfo1() {<!-- -->
//Get information about all cities and provincial cities
List<CityInfoResponse> cityInfoResponses = dao.queryCityInfo(0,null);
for (CityInfoResponse response : cityInfoResponses) {<!-- -->
//According to the current provincial city id, get the information of the city under its jurisdiction
Integer cityId0 = response. getCityId();
List<CityInfoResponse> cities = getCityId1(cityId0);
response.setCityInfoResponses(citys);
}
return cityInfoResponses;
}
/**
* @Author luozhao
* @Description According to the current city id, query the lower-level city information collection
* @Date 11:32 2023/3/25
* @param cityId
**/
List<CityInfoResponse> getCityId1(Integer cityId){<!-- -->
List<CityInfoResponse> cityInfoResponses = dao.queryCityInfo(null,cityId);
//The first city-level city traversal
for (CityInfoResponse cityInfoResponse : cityInfoResponses) {<!-- -->
if (cityInfoResponse. getLevel()!=2){<!-- -->
//Current city-level city id
Integer cityId1 = cityInfoResponse.getCityId();
List<CityInfoResponse> cities2 = 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 = "city id")
private Integer cityId;
@Schema(title = "Name")
private String name;
@Schema(title = "superior city id")
private Integer parentId;
@Schema(title = "city level", description = "0, for provinces, municipalities, special administrative regions, etc.; 1 for cities, 0 for counties or districts, etc.")
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>
The third version code-stored by redis, because the province-city-district rarely changes
@Override
public List<CityInfoResponse> getCityInfo1() {<!-- -->
boolean cityInfo = redisCache.exists(RedisConstants.MH_SYSTEM_CITY_INFO);
if (cityInfo) return redisCache.get(RedisConstants.MH_SYSTEM_CITY_INFO);
//Get information about all cities and provincial cities
List<CityInfoResponse> cityInfoResponses = dao.queryCityInfo(0,null);
for (CityInfoResponse response : cityInfoResponses) {<!-- -->
//According to the current provincial city id, get the information of the city under its jurisdiction
Integer cityId0 = response. getCityId();
List<CityInfoResponse> cities = getCityId1(cityId0);
response.setCityInfoResponses(citys);
}
redisCache.set(RedisConstants.MH_SYSTEM_CITY_INFO, cityInfoResponses);
return cityInfoResponses;
}
First version Response time – about 1.5 seconds on average
Response time of the second version – about 0.5 seconds
Response time of the third version – about 0.01 seconds
