设计图如图:
直接上代码
数据库中的格式:
JAVA实体类:
@Data
public class SysAreaZoningDO {
private Long districtId;
private Long parentId;
private String districtName;
private List<SysAreaZoningDO> children;
}
MapperSQL语句:
<select id="getAllArea" resultType="cn.iocoder.saas.module.bank.dal.dataobject.syszoning.SysAreaZoningDO">
WITH Provinces AS (
SELECT *
FROM sys_zoning
WHERE parent_id = 0
),
Cities AS (
SELECT *
FROM sys_zoning
WHERE parent_id IN (SELECT id FROM Provinces)
),
Districts AS (
SELECT *
FROM sys_zoning
WHERE parent_id IN (SELECT id FROM Cities)
)
SELECT
p.id AS districtId,
p.parent_id AS parentId,
p.name AS districtName,
NULL AS order_city,
NULL AS order_area,
'province' AS level
FROM Provinces p
UNION ALL
SELECT
c.id AS districtId,
c.parent_id AS parentId,
c.name AS districtName,
c.order_city AS order_city,
NULL AS order_area,
'city' AS level
FROM Cities c
UNION ALL
SELECT
d.id AS districtId,
d.parent_id AS parentId,
d.name AS districtName,
c.order_city AS order_city,
d.order_area AS order_area,
'district' AS level
FROM Districts d
JOIN Cities c ON d.parent_id = c.id
ORDER BY order_city DESC,
order_area ASC;
</select>
Service层实现方法:
@Override
public List<SysAreaZoningDO> getAllArea() {
// 获取扁平化的所有区域数据
List<SysAreaZoningDO> allAreas = userAddressMapper.getAllArea();
Map<Long, SysAreaZoningDO> areaMap = new HashMap<>();
List<SysAreaZoningDO> provinces = new ArrayList<>();
for (SysAreaZoningDO area : allAreas) {
areaMap.put(area.getDistrictId(), area);
if (area.getParentId() == 0) {
provinces.add(area);
}
}
for (SysAreaZoningDO area : allAreas) {
if (area.getParentId() != 0) {
SysAreaZoningDO parent = areaMap.get(area.getParentId());
if (parent != null) {
if (parent.getChildren() == null) {
parent.setChildren(new ArrayList<>());
}
parent.getChildren().add(area);
}
}
}
return provinces;
}
返回格式: