1 表
1.1 表结构
CREATE TABLE region (
`id` bigint(20) NOT NULL COMMENT "主键id",
`region_id` VARCHAR(20) DEFAULT NULL UNIQUE COMMENT "区域id",
`region_code` VARCHAR(50) DEFAULT NULL UNIQUE COMMENT "区域编码",
`region_name` VARCHAR(50) DEFAULT NULL UNIQUE COMMENT "区域名称",
`parent_id` VARCHAR(50) DEFAULT NULL COMMENT "父节点id",
`created_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建数据时间',
`updated_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新数据时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT "地区信息";
1.2 测试数据
-- 区域id规则:国家:IDA0001,省:IDBB0001,市:IDC0001,区:IDD0001
-- 区域编码规则:国家:CODEA0001,省:CODEB0001,市:CODEC0001,区:CODED0001
INSERT INTO region VALUES(1, "IDA0001", "CODEA0001", "中国", "0000", "2021-03-26 10:10:10", "2021-03-26 10:10:10");
INSERT INTO region VALUES(2, "IDB0001", "CODEB0001", "安徽省", "IDA0001", "2021-03-26 10:10:10", "2021-03-26 10:10:10");
INSERT INTO region VALUES(3, "IDB0002", "CODEB0002", "黑龙江省", "IDA0001", "2021-03-26 10:10:10", "2021-03-26 10:10:10");
INSERT INTO region VALUES(4, "IDB0003", "CODEB0003", "广东省", "IDA0001", "2021-03-26 10:10:10", "2021-03-26 10:10:10");
INSERT INTO region VALUES(5, "IDC0001", "CODEC0001", "合肥市", "IDB0001", "2021-03-26 10:10:10", "2021-03-26 10:10:10");
INSERT INTO region VALUES(6, "IDC0002", "CODEC0002", "淮北市", "IDB0001", "2021-03-26 10:10:10", "2021-03-26 10:10:10");
INSERT INTO region VALUES(7, "IDC0003", "CODEC0003", "哈尔滨市", "IDB0002", "2021-03-26 10:10:10", "2021-03-26 10:10:10");
INSERT INTO region VALUES(8, "IDC0004", "CODEC0004", "鹤岗市", "IDB0002", "2021-03-26 10:10:10", "2021-03-26 10:10:10");
INSERT INTO region VALUES(9, "IDC0005", "CODEC0005", "广州市", "IDB0003", "2021-03-26 10:10:10", "2021-03-26 10:10:10");
INSERT INTO region VALUES(10, "IDC0006", "CODEC0006", "深圳市", "IDB0003", "2021-03-26 10:10:10", "2021-03-26 10:10:10");
INSERT INTO region VALUES(11, "IDD0001", "CODED0001", "龙华区", "IDC0006", "2021-03-26 10:10:10", "2021-03-26 10:10:10");
INSERT INTO region VALUES(12, "IDD0002", "CODED0002", "南山区", "IDC0006", "2021-03-26 10:10:10", "2021-03-26 10:10:10");
INSERT INTO region VALUES(13, "IDD0003", "CODED0003", "天河区", "IDC0005", "2021-03-26 10:10:10", "2021-03-26 10:10:10");
2 实体
2.1 入参
package com.hardsoft.monkeyrun.modules.user.vo;
import java.util.List;
/**
* 区域信息入参.
*
* @author xindaqi
* @since 2021/3/26 16:56
*/
public class RegionInputMultiVO {
private List<String> regionIdList;
public void setRegionIdList(List<String> regionIdList) {
this.regionIdList = regionIdList;
}
public List<String> getRegionIdList() {
return regionIdList;
}
@Override
public String toString() {
return "RegionInputMultiVO{" +
"regionIdList=" + regionIdList +
'}';
}
}
2.2 出参
package com.hardsoft.monkeyrun.modules.user.vo;
import java.util.ArrayList;
import java.util.List;
/**
* 区域信息.
*
* @author xindaqi
* @since 2021/3/26 9:58
*/
public class RegionTreeOutputVO {
/**
* 区域id
*/
private String regionId;
/**
* 区域code
*/
private String regionCode;
/**
* 区域名称
*/
private String regionName;
/**
* 父id
*/
private String parentId;
/**
* 子节点
*/
private List<RegionTreeOutputVO> regionTreeOutputVOList = new ArrayList<>();
public void setRegionId(String regionId) {
this.regionId = regionId;
}
public String getRegionId() {
return regionId;
}
public void setRegionCode(String regionCode) {
this.regionCode = regionCode;
}
public String getRegionCode() {
return regionCode;
}
public void setRegionName(String regionName) {
this.regionName = regionName;
}
public String getRegionName() {
return regionName;
}
public void setParentId(String parentId) {
this.parentId = parentId;
}
public String getParentId() {
return parentId;
}
public void setRegionTreeOutputVOList(List<RegionTreeOutputVO> regionTreeOutputVOList) {
this.regionTreeOutputVOList = regionTreeOutputVOList;
}
public List<RegionTreeOutputVO> getRegionTreeOutputVOList() {
return regionTreeOutputVOList;
}
@Override
public String toString() {
return "RegionTreeVO{" +
"regionId='" + regionId + '\'' +
", regionCode='" + regionCode + '\'' +
", regionName='" + regionName + '\'' +
", parentId='" + parentId + '\'' +
", regionTreeVOList=" + regionTreeOutputVOList +
'}';
}
}
3 生成树结构
3.1 生成单根树结构
通过递归查询
package com.hardsoft.monkeyrun.common.utils;
import com.hardsoft.monkeyrun.modules.user.dao.RegionDAO;
import com.hardsoft.monkeyrun.modules.user.dto.RegionOutputDTO;
import com.hardsoft.monkeyrun.modules.user.vo.RegionInputMultiVO;
import com.hardsoft.monkeyrun.modules.user.vo.RegionTreeOutputVO;
import org.aspectj.lang.JoinPoint;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import java.util.List;
import java.util.stream.Stream;
/**
* 数据处理.
*
* @author xindaqi
* @since 2021/2/20 15:40
*/
@Component
public class DataProcessUtil {
private static final Logger logger = LoggerFactory.getLogger(DataProcessUtil.class);
/**
* 生成单根树结构
*
* @param regionDAO 数据查询Mapper
* @param regionId 区域id
* @return 单根树
*/
public RegionTreeOutputVO recursiveTree(RegionDAO regionDAO, String regionId) {
/**
* 查询:父节点
*/
RegionTreeOutputVO fatherNode = regionDAO.queryRegionById(regionId);
/**
* 查询:子节点
*/
List<RegionTreeOutputVO> childNodeList = regionDAO.queryRegionByParentId(regionId);
for(RegionTreeOutputVO child : childNodeList) {
RegionTreeOutputVO childNode = recursiveTree(regionDAO, child.getRegionId());
fatherNode.getRegionTreeOutputVOList().add(childNode);
}
logger.info("区域树:{}", fatherNode);
return fatherNode;
}
}
3.2 生成多根树结构
package com.hardsoft.monkeyrun.common.utils;
import com.hardsoft.monkeyrun.modules.user.dao.RegionDAO;
import com.hardsoft.monkeyrun.modules.user.dto.RegionOutputDTO;
import com.hardsoft.monkeyrun.modules.user.vo.RegionInputMultiVO;
import com.hardsoft.monkeyrun.modules.user.vo.RegionTreeOutputVO;
import org.aspectj.lang.JoinPoint;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.stereotype.Component;
import java.util.List;
/**
* 数据处理.
*
* @author xindaqi
* @since 2021/2/20 15:40
*/
@Component
public class DataProcessUtil {
private static final Logger logger = LoggerFactory.getLogger(DataProcessUtil.class);
/**
* 生成单根树结构
*
* @param regionDAO 数据查询Mapper
* @param regionId 区域id
* @return 单根树
*/
public RegionTreeOutputVO recursiveTree(RegionDAO regionDAO, String regionId) {
/**
* 查询:父节点
*/
RegionTreeOutputVO fatherNode = regionDAO.queryRegionById(regionId);
/**
* 查询:子节点
*/
List<RegionTreeOutputVO> childNodeList = regionDAO.queryRegionByParentId(regionId);
for(RegionTreeOutputVO child : childNodeList) {
RegionTreeOutputVO childNode = recursiveTree(regionDAO, child.getRegionId());
fatherNode.getRegionTreeOutputVOList().add(childNode);
}
logger.info("区域树:{}", fatherNode);
return fatherNode;
}
/**
* 生成多根树结构
*
* @param regionDAO
* @param regionInputMultiVO
* @return 多根数
*/
public List<RegionTreeOutputVO> recursiveTreeList(RegionDAO regionDAO, RegionInputMultiVO regionInputMultiVO) {
List<RegionTreeOutputVO> regionTreeOutputVOS = regionDAO.queryRegionByRegionIdList(regionInputMultiVO);
for(RegionTreeOutputVO regionTreeOutputVO : regionTreeOutputVOS) {
RegionTreeOutputVO child = recursiveTree(regionDAO, regionTreeOutputVO.getRegionId());
regionTreeOutputVO.getRegionTreeOutputVOList().add(child);
}
return regionTreeOutputVOS;
}
}
4 数据库操作
4.1 Mapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hardsoft.monkeyrun.modules.user.dao.RegionDAO">
<select id="queryRegionById" resultType="com.hardsoft.monkeyrun.modules.user.vo.RegionTreeOutputVO">
SELECT
region_id regionId,
region_code regionCode,
region_name regionName,
parent_id parentId
FROM
region
WHERE
region_id = #{regionId};
</select>
<select id="queryRegionByParentId" resultType="com.hardsoft.monkeyrun.modules.user.vo.RegionTreeOutputVO">
SELECT
region_id regionId,
region_code regionCode,
region_name regionName,
parent_id parentId
FROM
region
WHERE
parent_id = #{regionId};
</select>
<select id="queryRegionByRegionIdList" parameterType="com.hardsoft.monkeyrun.modules.user.vo.RegionInputMultiVO" resultType="com.hardsoft.monkeyrun.modules.user.vo.RegionTreeOutputVO">
SELECT
region_id regionId,
region_code regionCode,
region_name regionName,
parent_id parentId
FROM
region
WHERE
<if test="regionIdList != null and regionIdList.size()>0">
region_id in
<foreach item="item" index="index" collection="regionIdList" open="(" separator="," close=")">
#{item}
</foreach>
</if>
</select>
</mapper>
4.2 DAO
package com.hardsoft.monkeyrun.modules.user.dao;
import com.hardsoft.monkeyrun.modules.user.dto.RegionOutputDTO;
import com.hardsoft.monkeyrun.modules.user.vo.RegionInputMultiVO;
import com.hardsoft.monkeyrun.modules.user.vo.RegionTreeOutputVO;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* 区域信息查询.
*
* @author xindaqi
* @since 2021/3/26 11:24
*/
public interface RegionDAO {
/**
* 根据id查询区域信息
*
* @param regionId
* @return 区域信息列表
*/
RegionTreeOutputVO queryRegionById(@Param("regionId") String regionId);
/**
* 根据regionId列表,查询区域信息
*
* @param regionInputMultiVO 区域id列表
* @return 区域信息列表
*/
List<RegionTreeOutputVO> queryRegionByRegionIdList(RegionInputMultiVO regionInputMultiVO);
/**
* 根据parentId查询区域信息
*
* @param regionId 区域id
* @return 区域信息列表
*/
List<RegionTreeOutputVO> queryRegionByParentId(@Param("regionId") String regionId);
}
4.3 Service
4.3.1 Interface
package com.hardsoft.monkeyrun.modules.user.service;
import com.hardsoft.monkeyrun.modules.user.vo.*;
import java.util.List;
/**
* @description 用户服务
* @author xindaqi
* @since 2021-03-17 23:04:58
*/
public interface IUserService {
/**
* 创建区域树
*
* @param regionId 省id
* @return 区域树
*/
default RegionTreeOutputVO buildRegionTreeByRegionId(String regionId) {
return null;
}
/**
* 创建多级根节点区域树
*
* @param regionInputMultiVO 区域入参
* @return 多级根节点区域树
*/
default List<RegionTreeOutputVO> buildMultiRegionTreeByRegionIdList(RegionInputMultiVO regionInputMultiVO) {
return null;
}
}
4.3.2 Implements
package com.hardsoft.monkeyrun.modules.user.service.impl;
import com.hardsoft.monkeyrun.common.enums.BizExceptionCodeEnums;
import com.hardsoft.monkeyrun.common.utils.DataProcessUtil;
import com.hardsoft.monkeyrun.modules.user.dao.RegionDAO;
import com.hardsoft.monkeyrun.modules.user.dao.UserDAO;
import com.hardsoft.monkeyrun.modules.user.dto.UserInfoOutputDTO;
import com.hardsoft.monkeyrun.modules.user.dto.UserInputDTO;
import com.hardsoft.monkeyrun.modules.user.dto.UserLoginOutputDTO;
import com.hardsoft.monkeyrun.modules.user.vo.*;
import com.hardsoft.monkeyrun.modules.user.service.IUserService;
import com.hardsoft.monkeyrun.common.exception.BizException;
import com.hardsoft.monkeyrun.common.constrant.DigitalConstant;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;
/**
* @description 用户模块实现
* @author xindaqi
* @since 2021-03-17 23:10:57
*/
@Service
public class UserServiceImpl implements IUserService {
private static final Logger logger = LoggerFactory.getLogger(UserServiceImpl.class);
@Resource
DataProcessUtil dataProcessUtil;
@Resource
RegionDAO regionDAO;
@Override
public RegionTreeOutputVO buildRegionTreeByRegionId(String regionId) {
return dataProcessUtil.recursiveTree(regionDAO, regionId);
}
@Override
public List<RegionTreeOutputVO> buildMultiRegionTreeByRegionIdList(RegionInputMultiVO regionInputMultiVO) {
return dataProcessUtil.recursiveTreeList(regionDAO, regionInputMultiVO);
}
}
4.4 Controller
package com.hardsoft.monkeyrun.api.facade.user;
import com.hardsoft.monkeyrun.common.constrant.DigitalConstant;
import com.hardsoft.monkeyrun.common.response.Response;
import com.hardsoft.monkeyrun.modules.user.service.IUserService;
import com.hardsoft.monkeyrun.modules.user.vo.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.util.List;
/**
* 用户接口.
*
* @author xindaqi
* @since 2021/3/18 17:58
*/
@RestController
@RequestMapping("/v1/user")
public class UserFacade {
private static final Logger logger = LoggerFactory.getLogger(UserFacade.class);
@Resource
IUserService userService;
@PostMapping("/region/tree/list")
public Response<List<RegionTreeOutputVO>> regionTreeOutputVOResponse(@RequestBody RegionInputMultiVO regionInputMultiVO) {
logger.info("regionId入参 : {}", regionInputMultiVO);
return Response.success(userService.buildMultiRegionTreeByRegionIdList(regionInputMultiVO));
}
}
5 结果
5.1 Postman请求样例
Postman构造请求,如图5.1所示。
5.1 单根节点树结构
- 入参
{
"regionIdList":["IDB0003"]
}
- 结果
{
"code": "200",
"msg": "成功",
"status": true,
"data": {
"regionId": "IDB0003",
"regionCode": "CODEB0003",
"regionName": "广东省",
"parentId": "IDA0001",
"regionTreeOutputVOList": [
{
"regionId": "IDC0005",
"regionCode": "CODEC0005",
"regionName": "广州市",
"parentId": "IDB0003",
"regionTreeOutputVOList": [
{
"regionId": "IDD0003",
"regionCode": "CODED0003",
"regionName": "天河区",
"parentId": "IDC0005",
"regionTreeOutputVOList": []
}
]
},
{
"regionId": "IDC0006",
"regionCode": "CODEC0006",
"regionName": "深圳市",
"parentId": "IDB0003",
"regionTreeOutputVOList": [
{
"regionId": "IDD0001",
"regionCode": "CODED0001",
"regionName": "龙华区",
"parentId": "IDC0006",
"regionTreeOutputVOList": []
},
{
"regionId": "IDD0002",
"regionCode": "CODED0002",
"regionName": "南山区",
"parentId": "IDC0006",
"regionTreeOutputVOList": []
}
]
}
]
}
}
5.2 多根节点树结构
- 入参
{
"regionIdList":["IDB0003","IDB0002"]
}
- 结果
{
"code": "200",
"msg": "成功",
"status": true,
"data": [
{
"regionId": "IDB0002",
"regionCode": "CODEB0002",
"regionName": "黑龙江省",
"parentId": "IDA0001",
"regionTreeOutputVOList": [
{
"regionId": "IDB0002",
"regionCode": "CODEB0002",
"regionName": "黑龙江省",
"parentId": "IDA0001",
"regionTreeOutputVOList": [
{
"regionId": "IDC0003",
"regionCode": "CODEC0003",
"regionName": "哈尔滨市",
"parentId": "IDB0002",
"regionTreeOutputVOList": []
},
{
"regionId": "IDC0004",
"regionCode": "CODEC0004",
"regionName": "鹤岗市",
"parentId": "IDB0002",
"regionTreeOutputVOList": []
}
]
}
]
},
{
"regionId": "IDB0003",
"regionCode": "CODEB0003",
"regionName": "广东省",
"parentId": "IDA0001",
"regionTreeOutputVOList": [
{
"regionId": "IDB0003",
"regionCode": "CODEB0003",
"regionName": "广东省",
"parentId": "IDA0001",
"regionTreeOutputVOList": [
{
"regionId": "IDC0005",
"regionCode": "CODEC0005",
"regionName": "广州市",
"parentId": "IDB0003",
"regionTreeOutputVOList": [
{
"regionId": "IDD0003",
"regionCode": "CODED0003",
"regionName": "天河区",
"parentId": "IDC0005",
"regionTreeOutputVOList": []
}
]
},
{
"regionId": "IDC0006",
"regionCode": "CODEC0006",
"regionName": "深圳市",
"parentId": "IDB0003",
"regionTreeOutputVOList": [
{
"regionId": "IDD0001",
"regionCode": "CODED0001",
"regionName": "龙华区",
"parentId": "IDC0006",
"regionTreeOutputVOList": []
},
{
"regionId": "IDD0002",
"regionCode": "CODED0002",
"regionName": "南山区",
"parentId": "IDC0006",
"regionTreeOutputVOList": []
}
]
}
]
}
]
}
]
}
【参考文献】
[1]https://www.jb51.net/article/120571.htm
[2]https://blog.csdn.net/qq_42765276/article/details/87933560