1.初始化数据库数据
DROP TABLE IF EXISTS `t_domain`;
CREATE TABLE `t_domain` (
`id` varchar(36) NOT NULL,
`domain_name` varchar(20) NOT NULL COMMENT '区划名称',
`parent_domain_id` varchar(36) DEFAULT NULL COMMENT '父区划id',
`domain_type` tinyint(2) NOT NULL DEFAULT '1' COMMENT '区划类型 1销售区划 2运营区划',
`data_status` tinyint(2) NOT NULL DEFAULT '1' COMMENT '1:正常，2：冻结，3：删除',
`create_time` datetime DEFAULT NULL,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='区划表';
-- ----------------------------
-- Records of t_domain
-- ----------------------------
INSERT INTO `t_domain` VALUES ('0', '全国', null, '1', '1', null, '-03-15 11:28:16');
INSERT INTO `t_domain` VALUES ('2042bbee-9688-4741-bd9e-6579f186531a', '湘潭', 'dd7460f7-019e-45b9-ab16-44f2bc12a132', '2', '1', '-03-06 00:24:30', '-03-06 14:24:29');
INSERT INTO `t_domain` VALUES ('544897dc-bb5b-4f22-8483-19e89175f00e', '祁东1hao', 'd5d63419-3e81-406c-8993-0a4f5f943fc8', '1', '1', '-03-05 01:35:37', '-03-05 15:40:05');
INSERT INTO `t_domain` VALUES ('67bf668a-35cc-4051-90a0-5f4a78251d59', '长沙', 'a8d20afd-6759-49d4-ac0b-c71214b7546b', '1', '1', '-03-05 00:36:39', '-03-05 14:36:39');
INSERT INTO `t_domain` VALUES ('68639fd5-1deb-4975-bc9f-c516739b4ebd', '山东大区', '0', '1', '1', '-03-05 00:15:04', '-03-05 14:15:04');
INSERT INTO `t_domain` VALUES ('6ab58032-2df5-4edc-a4e5-8f8b6d45ead8', '祁东2hao', 'd5d63419-3e81-406c-8993-0a4f5f943fc8', '1', '1', '-03-05 00:42:23', '-03-05 15:40:09');
INSERT INTO `t_domain` VALUES ('a69dbe97-f5ea-4e7c-af34-fd182d413215', '祁东type2', 'd5d63419-3e81-406c-8993-0a4f5f943fc8', '2', '1', '-03-05 02:54:15', '-03-05 16:54:15');
INSERT INTO `t_domain` VALUES ('a8d20afd-6759-49d4-ac0b-c71214b7546b', '湖南大区', '0', '1', '1', '-03-05 00:35:18', '-03-05 14:35:18');
INSERT INTO `t_domain` VALUES ('c64f09e5-9bb8-4099-b27c-14c8176cfd82', '湘潭', 'a8d20afd-6759-49d4-ac0b-c71214b7546b', '2', '1', '-03-05 02:55:38', '-03-05 16:55:38');
INSERT INTO `t_domain` VALUES ('d5d63419-3e81-406c-8993-0a4f5f943fc8', '衡阳', 'a8d20afd-6759-49d4-ac0b-c71214b7546b', '1', '1', '-03-05 00:37:08', '-03-05 14:37:08');
INSERT INTO `t_domain` VALUES ('dd7460f7-019e-45b9-ab16-44f2bc12a132', '湖南大区', '0', '2', '1', '-03-06 00:23:06', '-03-06 14:23:05');
代码新增数据:
// 分与权限的区划添加,包括销售区划和运营区划@Testpublic void test01(){Example example = new Example(TDomain.class);int totalCount = domainMapper.selectCountByExample(example);if(totalCount == 0){ // 如果表里没数据,加个全国进去TDomain bean = new TDomain();bean.setId("0");bean.setDomainName("全国");bean.setCreateTime(new Date());domainMapper.insertSelective(bean);}// 如果是从全国添加大区,传0,否则就是取当前操作节点的id,如在大区上添加市,传大区的id,String parentId = "a8d20afd-6759-49d4-ac0b-c71214b7546b";// 区划类型 1销售区划 2运营区划int domain_type = 1;TDomain bean = new TDomain();bean.setId(UUID.randomUUID().toString());bean.setDomainName("株洲");bean.setParentDomainId(parentId);bean.setDomainType(domain_type);bean.setCreateTime(new Date());int i = domainMapper.insertSelective(bean);}
2.实体类,省略set,get
@Table(name = "t_domain")public class TDomain {@Idprivate String id;private String domainName;private String parentDomainId;private Integer domainType;@JSONField(serialize = false)private Integer dataStatus;@JSONField(serialize = false)private Date createTime;@JSONField(serialize = false)private Date updateTime;private List<TDomain> childList = new ArrayList<>();
3.mapper代码
public interface DomainMapper extends Mapper<TDomain> {@Select("SELECT * FROM t_domain WHERE id = #{id} and data_status = 1")List<TDomain> getNode(@Param("id") String id);@Select("SELECT * FROM t_domain WHERE parent_domain_id = #{parentDomainId} and domain_type = #{domainType} and data_status = 1")List<TDomain> getNodeList(@Param("parentDomainId") String parentDomainId, @Param("domainType") int domainType);
4.方法代码及测试结果
@Testpublic void test666(){TDomain quhua = recursiveTree("0",1);logger.info(IJsonUtils.toJson(quhua));}public TDomain recursiveTree(String id,int domainType) { // id为数据的主键IDTDomain node;List<TDomain> datas = domainMapper.getNode(id);if (datas.isEmpty()){return null;}else{node = datas.get(0);}List<TDomain> childTreeNodes = domainMapper.getNodeList(id,domainType);for(TDomain child : childTreeNodes){TDomain n = recursiveTree(child.getId(),domainType);node.getChildList().add(n);}return node;}
[
{
"childList": [],
"domainName": "山东大区",
"domainType": 1,
"id": "68639fd5-1deb-4975-bc9f-c516739b4ebd",
"parentDomainId": "0"
},
{
"childList": [
{
"childList": [],
"domainName": "长沙",
"domainType": 1,
"id": "67bf668a-35cc-4051-90a0-5f4a78251d59",
"parentDomainId": "a8d20afd-6759-49d4-ac0b-c71214b7546b"
},
{
"childList": [
{
"childList": [],
"domainName": "祁东1hao",
"domainType": 1,
"id": "544897dc-bb5b-4f22-8483-19e89175f00e",
"parentDomainId": "d5d63419-3e81-406c-8993-0a4f5f943fc8"
},
{
"childList": [],
"domainName": "祁东2hao",
"domainType": 1,
"id": "6ab58032-2df5-4edc-a4e5-8f8b6d45ead8",
"parentDomainId": "d5d63419-3e81-406c-8993-0a4f5f943fc8"
}
],
"domainName": "衡阳",
"domainType": 1,
"id": "d5d63419-3e81-406c-8993-0a4f5f943fc8",
"parentDomainId": "a8d20afd-6759-49d4-ac0b-c71214b7546b"
}
],
"domainName": "湖南大区",
"domainType": 1,
"id": "a8d20afd-6759-49d4-ac0b-c71214b7546b",
"parentDomainId": "0"
}
]
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
使用mybatis自带递归查询功能实现:
1.mapper.xml
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/mybatis-3-mapper.dtd"><!--suppress SqlDialectInspection --><mapper namespace="com.wd.dao.privilege.DomainMapper"><resultMap id="SaleResult" type="com.wd.domain.po.TDomain"><id column="id" property="id"/><result column="domain_name" property="domainName" /><result column="parent_domain_id" property="parentDomainId" /><result column="domain_type" property="domainType" /><result column="data_status" property="dataStatus" /><result column="create_time" property="createTime" /><result column="update_time" property="updateTime" /><collection column="id" property="childList"ofType="com.wd.domain.po.TDomain"select="listForSale"></collection></resultMap><resultMap id="OperationResult" type="com.wd.domain.po.TDomain"><id column="id" property="id"/><result column="domain_name" property="domainName" /><result column="parent_domain_id" property="parentDomainId" /><result column="domain_type" property="domainType" /><result column="data_status" property="dataStatus" /><result column="create_time" property="createTime" /><result column="update_time" property="updateTime" /><collection column="id" property="childList"ofType="com.wd.domain.po.TDomain"select="listForOperation"></collection></resultMap><!--注意,递归查询只能跟一个条件--><!-- 递归查询所有,传最外层节点的parent_id值,数据库里的值,不能是null,比如定义parent_id=0表示最外层--><!--销售区划树查询--><select id="listForSale" resultMap="SaleResult">select * from t_domain where parent_domain_id = #{parentDomainId} and domain_type = 1;</select><!--运营区划树查询--><select id="listForOperation" resultMap="OperationResult">select * from t_domain where parent_domain_id = #{parentDomainId} and domain_type = 2;</select></mapper>
2.mapper代码
/*** 递归查询销售区划树** @param parentDomainId 传大区的parentDomainId,0哦,不能是null* @return*/List<TDomain> listForSale(@Param("parentDomainId") String parentDomainId);/*** 递归查询运营区划树** @param parentDomainId 传大区的parentDomainId,0哦,不能是null* @return*/List<TDomain> listForOperation(@Param("parentDomainId") String parentDomainId);
3.测试代码
// 销售区划树查询@Testpublic void test02(){String parentDomainId = "0";List<TDomain> domains = domainMapper.listForSale(parentDomainId);String json = IJsonUtils.toJson(domains);logger.info(json);}// 运营区划树查询@Testpublic void test03(){String parentDomainId = "0";List<TDomain> domains = domainMapper.listForOperation(parentDomainId);String json = IJsonUtils.toJson(domains);logger.info(json);}
如果觉得《递归查询树形结构数据》对你有帮助，请点赞、收藏，并留下你的观点哦！
