SpringBoot | 构建客户树及其关联关系的设计思路和实践Demo

关注：CodingTechWork

引言

  在企业级应用中，客户关系管理（CRM）是核心功能之一。客户树是一种用于表示客户之间层级关系的结构，例如企业客户与子公司、经销商与下级经销商等。本文将详细介绍如何设计客户树及其关联关系，通过三张表来实现：客户表、客户树表和客户关联关系表。这种设计可以清晰地分离客户的基本信息、树的结构信息以及客户之间的层级关系。

一、客户树及其关联关系的定义

客户树是一种层级结构，用于表示客户之间的上下级关系。每个客户可以有多个子客户，而每个子客户又可以有自己的子客户，形成一个树状结构。客户树通常用于以下场景：

• 企业与子公司：表示企业集团的层级结构。
• 经销商与下级经销商：表示销售渠道的层级关系。
• 客户与联系人：表示客户内部的组织架构。

客户树的特点包括：

• 层级性：每个客户都有一个层级，顶级客户为第1层，其子客户为第2层，依此类推。
• 递归性：客户树的结构是递归的，每个子客户可以有自己的子客户。
• 关联性：客户之间通过父子关系关联。

二、设计方案

2.1 数据库设计

为了实现客户树及其关联关系，我们需要设计三张表：customer 表（客户表）、customer_tree 表（客户树表）和 customer_relationship 表（客户关联关系表）。

2.1.1 客户表（customer）

字段名	数据类型	描述
id	INT	客户唯一标识（主键）
name	VARCHAR(100)	客户名称
code	VARCHAR(50)	客户编码
created_at	DATETIME	创建时间
updated_at	DATETIME	更新时间

2.1.2 客户树表（customer_tree）

字段名	数据类型	描述
id	INT	树唯一标识（主键）
tree_name	VARCHAR(100)	树名称
root_id	INT	根节点ID（外键，指向customer表）
max_level	INT	树的最大层级
created_at	DATETIME	创建时间
updated_at	DATETIME	更新时间

2.1.3 客户关联关系表（customer_relationship）

字段名	数据类型	描述
id	INT	关系唯一标识（主键）
tree_id	INT	所属树ID（外键，指向customer_tree表）
customer_id	INT	客户ID（外键，指向customer表）
parent_id	INT	父节点ID（外键，指向customer表）
level	INT	当前层级
created_at	DATETIME	创建时间
updated_at	DATETIME	更新时间

2.2 功能设计

1. 查询客户树：通过递归查询或存储过程，获取完整的客户树结构。
2. 新增客户：支持添加顶级客户或子客户。
3. 删除客户：删除客户时，需要考虑其子客户是否需要同时删除。
4. 更新客户信息：更新客户的基本信息或层级关系。
5. 查询客户层级：查询某个客户的层级关系。

三、SQL实现

3.1 创建表

创建客户表

CREATE TABLE `customer` (`id` INT AUTO_INCREMENT PRIMARY KEY,`name` VARCHAR(100) NOT NULL,`code` VARCHAR(50) NOT NULL,`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

创建客户树表

CREATE TABLE `customer_tree` (`id` INT AUTO_INCREMENT PRIMARY KEY,`tree_name` VARCHAR(100) NOT NULL,`root_id` INT,`max_level` INT DEFAULT 1,`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY (`root_id`) REFERENCES `customer` (`id`)
);

创建客户关联关系表

CREATE TABLE `customer_relationship` (`id` INT AUTO_INCREMENT PRIMARY KEY,`tree_id` INT,`customer_id` INT,`parent_id` INT,`level` INT DEFAULT 1,`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY (`tree_id`) REFERENCES `customer_tree` (`id`),FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`),FOREIGN KEY (`parent_id`) REFERENCES `customer` (`id`)
);

3.2 插入测试数据

插入客户数据

INSERT INTO `customer` (`name`, `code`) VALUES
('顶级客户A', 'A'),
('子客户A1', 'A1'),
('子客户A2', 'A2'),
('子客户A1-1', 'A1-1'),
('顶级客户B', 'B'),
('子客户B1', 'B1');

插入客户树数据

INSERT INTO `customer_tree` (`tree_name`, `root_id`, `max_level`) VALUES
('客户树A', 1, 3),
('客户树B', 5, 2);

插入客户关联关系数据

INSERT INTO `customer_relationship` (`tree_id`, `customer_id`, `parent_id`, `level`) VALUES
(1, 1, NULL, 1),  -- 顶级客户A
(1, 2, 1, 2),     -- 子客户A1
(1, 3, 1, 2),     -- 子客户A2
(1, 4, 2, 3),     -- 子客户A1-1
(2, 5, NULL, 1),  -- 顶级客户B
(2, 6, 5, 2);     -- 子客户B1

3.3 查询客户树

使用递归查询（MySQL 8.0+）：

WITH RECURSIVE CustomerTree AS (SELECT cr.id, cr.customer_id, cr.parent_id, cr.levelFROM customer_relationship crWHERE cr.parent_id IS NULLUNION ALLSELECT cr.id, cr.customer_id, cr.parent_id, cr.levelFROM customer_relationship crJOIN CustomerTree cte ON cr.parent_id = cte.customer_id
)
SELECT * FROM CustomerTree ORDER BY level, id;

3.4 删除客户

删除客户及其所有子客户：

-- 删除客户及其所有子客户
WITH RECURSIVE CustomerTree AS (SELECT id FROM customer_relationship WHERE customer_id = ?UNION ALLSELECT cr.id FROM customer_relationship cr JOIN CustomerTree cte ON cr.parent_id = cte.customer_id
)
DELETE FROM customer_relationship WHERE id IN (SELECT id FROM CustomerTree);

四、Java代码实现

4.1 项目依赖

使用Spring Boot和MyBatis：

<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.1.4</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency>
</dependencies>

4.2 实体类

客户实体类

package com.example.demo.model;import java.util.Date;public class Customer {private Integer id;private String name;private String code;private Date createdAt;private Date updatedAt;// Getters and Setterspublic Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getCode() {return code;}public void setCode(String code) {this.code = code;}public Date getCreatedAt() {return createdAt;}public void setCreatedAt(Date createdAt) {this.createdAt = createdAt;}public Date getUpdatedAt() {return updatedAt;}public void setUpdatedAt(Date updatedAt) {this.updatedAt = updatedAt;}
}

客户树实体类

package com.example.demo.model;import java.util.Date;public class CustomerTree {private Integer id;private String treeName;private Integer rootId;private Integer maxLevel;private Date createdAt;private Date updatedAt;// Getters and Setterspublic Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getTreeName() {return treeName;}public void setTreeName(String treeName) {this.treeName = treeName;}public Integer getRootId() {return rootId;}public void setRootId(Integer rootId) {this.rootId = rootId;}public Integer getMaxLevel() {return maxLevel;}public void setMaxLevel(Integer maxLevel) {this.maxLevel = maxLevel;}public Date getCreatedAt() {return createdAt;}public void setCreatedAt(Date createdAt) {this.createdAt = createdAt;}public Date getUpdatedAt() {return updatedAt;}public void setUpdatedAt(Date updatedAt) {this.updatedAt = updatedAt;}
}

客户关联关系实体类

package com.example.demo.model;import java.util.Date;public class CustomerRelationship {private Integer id;private Integer treeId;private Integer customerId;private Integer parentId;private Integer level;private Date createdAt;private Date updatedAt;// Getters and Setterspublic Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public Integer getTreeId() {return treeId;}public void setTreeId(Integer treeId) {this.treeId = treeId;}public Integer getCustomerId() {return customerId;}public void setCustomerId(Integer customerId) {this.customerId = customerId;}public Integer getParentId() {return parentId;}public void setParentId(Integer parentId) {this.parentId = parentId;}public Integer getLevel() {return level;}public void setLevel(Integer level) {this.level = level;}public Date getCreatedAt() {return createdAt;}public void setCreatedAt(Date createdAt) {this.createdAt = createdAt;}public Date getUpdatedAt() {return updatedAt;}public void setUpdatedAt(Date updatedAt) {this.updatedAt = updatedAt;}
}

4.3 Mapper接口

客户Mapper

package com.example.demo.mapper;import com.example.demo.model.Customer;
import org.apache.ibatis.annotations.*;import java.util.List;@Mapper
public interface CustomerMapper {@Select("SELECT * FROM customer")List<Customer> getAllCustomers();@Insert("INSERT INTO customer (name, code) VALUES (#{name}, #{code})")void insertCustomer(Customer customer);@Delete("DELETE FROM customer WHERE id = #{id}")void deleteCustomerById(Integer id);@Update("UPDATE customer SET name = #{name}, code = #{code} WHERE id = #{id}")void updateCustomer(Customer customer);
}

客户树Mapper

package com.example.demo.mapper;import com.example.demo.model.CustomerTree;
import org.apache.ibatis.annotations.*;import java.util.List;@Mapper
public interface CustomerTreeMapper {@Select("SELECT * FROM customer_tree")List<CustomerTree> getAllCustomerTrees();@Insert("INSERT INTO customer_tree (tree_name, root_id, max_level) VALUES (#{treeName}, #{rootId}, #{maxLevel})")void insertCustomerTree(CustomerTree customerTree);@Delete("DELETE FROM customer_tree WHERE id = #{id}")void deleteCustomerTreeById(Integer id);@Update("UPDATE customer_tree SET tree_name = #{treeName}, root_id = #{rootId}, max_level = #{maxLevel} WHERE id = #{id}")void updateCustomerTree(CustomerTree customerTree);
}

客户关联关系Mapper

package com.example.demo.mapper;import com.example.demo.model.CustomerRelationship;
import org.apache.ibatis.annotations.*;import java.util.List;@Mapper
public interface CustomerRelationshipMapper {@Select("SELECT * FROM customer_relationship")List<CustomerRelationship> getAllCustomerRelationships();@Insert("INSERT INTO customer_relationship (tree_id, customer_id, parent_id, level) VALUES (#{treeId}, #{customerId}, #{parentId}, #{level})")void insertCustomerRelationship(CustomerRelationship customerRelationship);@Delete("DELETE FROM customer_relationship WHERE id = #{id}")void deleteCustomerRelationshipById(Integer id);@Update("UPDATE customer_relationship SET tree_id = #{treeId}, customer_id = #{customerId}, parent_id = #{parentId}, level = #{level} WHERE id = #{id}")void updateCustomerRelationship(CustomerRelationship customerRelationship);
}

4.4 服务层

客户服务类

package com.example.demo.service;import com.example.demo.mapper.CustomerMapper;
import com.example.demo.model.Customer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;@Service
public class CustomerService {@Autowiredprivate CustomerMapper customerMapper;public List<Customer> getAllCustomers() {return customerMapper.getAllCustomers();}public void addCustomer(Customer customer) {customerMapper.insertCustomer(customer);}public void deleteCustomer(Integer id) {customerMapper.deleteCustomerById(id);}public void updateCustomer(Customer customer) {customerMapper.updateCustomer(customer);}
}

客户树服务类

package com.example.demo.service;import com.example.demo.mapper.CustomerTreeMapper;
import com.example.demo.model.CustomerTree;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;@Service
public class CustomerTreeService {@Autowiredprivate CustomerTreeMapper customerTreeMapper;public List<CustomerTree> getAllCustomerTrees() {return customerTreeMapper.getAllCustomerTrees();}public void addCustomerTree(CustomerTree customerTree) {customerTreeMapper.insertCustomerTree(customerTree);}public void deleteCustomerTree(Integer id) {customerTreeMapper.deleteCustomerTreeById(id);}public void updateCustomerTree(CustomerTree customerTree) {customerTreeMapper.updateCustomerTree(customerTree);}
}

客户关联关系服务类

package com.example.demo.service;import com.example.demo.mapper.CustomerRelationshipMapper;
import com.example.demo.model.CustomerRelationship;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;@Service
public class CustomerRelationshipService {@Autowiredprivate CustomerRelationshipMapper customerRelationshipMapper;public List<CustomerRelationship> getAllCustomerRelationships() {return customerRelationshipMapper.getAllCustomerRelationships();}public void addCustomerRelationship(CustomerRelationship customerRelationship) {customerRelationshipMapper.insertCustomerRelationship(customerRelationship);}public void deleteCustomerRelationship(Integer id) {customerRelationshipMapper.deleteCustomerRelationshipById(id);}public void updateCustomerRelationship(CustomerRelationship customerRelationship) {customerRelationshipMapper.updateCustomerRelationship(customerRelationship);}
}

4.5 控制器

客户控制器

package com.example.demo.controller;import com.example.demo.model.Customer;
import com.example.demo.service.CustomerService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;import java.util.List;@RestController
@RequestMapping("/customers")
public class CustomerController {@Autowiredprivate CustomerService customerService;@GetMappingpublic List<Customer> getAllCustomers() {return customerService.getAllCustomers();}@PostMappingpublic void addCustomer(@RequestBody Customer customer) {customerService.addCustomer(customer);}@DeleteMapping("/{id}")public void deleteCustomer(@PathVariable Integer id) {customerService.deleteCustomer(id);}@PutMappingpublic void updateCustomer(@RequestBody Customer customer) {customerService.updateCustomer(customer);}
}

客户树控制器

package com.example.demo.controller;import com.example.demo.model.CustomerTree;
import com.example.demo.service.CustomerTreeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;import java.util.List;@RestController
@RequestMapping("/customer-trees")
public class CustomerTreeController {@Autowiredprivate CustomerTreeService customerTreeService;@GetMappingpublic List<CustomerTree> getAllCustomerTrees() {return customerTreeService.getAllCustomerTrees();}@PostMappingpublic void addCustomerTree(@RequestBody CustomerTree customerTree) {customerTreeService.addCustomerTree(customerTree);}@DeleteMapping("/{id}")public void deleteCustomerTree(@PathVariable Integer id) {customerTreeService.deleteCustomerTree(id);}@PutMappingpublic void updateCustomerTree(@RequestBody CustomerTree customerTree) {customerTreeService.updateCustomerTree(customerTree);}
}

客户关联关系控制器

package com.example.demo.controller;import com.example.demo.model.CustomerRelationship;
import com.example.demo.service.CustomerRelationshipService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;import java.util.List;@RestController
@RequestMapping("/customer-relationships")
public class CustomerRelationshipController {@Autowiredprivate CustomerRelationshipService customerRelationshipService;@GetMappingpublic List<CustomerRelationship> getAllCustomerRelationships() {return customerRelationshipService.getAllCustomerRelationships();}@PostMappingpublic void addCustomerRelationship(@RequestBody CustomerRelationship customerRelationship) {customerRelationshipService.addCustomerRelationship(customerRelationship);}@DeleteMapping("/{id}")public void deleteCustomerRelationship(@PathVariable Integer id) {customerRelationshipService.deleteCustomerRelationship(id);}@PutMappingpublic void updateCustomerRelationship(@RequestBody CustomerRelationship customerRelationship) {customerRelationshipService.updateCustomerRelationship(customerRelationship);}
}

五、总结

  本文通过设计客户表、客户树表和客户关联关系表，展示了如何构建和管理客户树及其关联关系。这种设计可以满足企业级应用中对客户关系管理的需求，同时提供了灵活的查询和操作功能。希望本文能够为需要实现类似功能的开发者提供参考。如果对本文有任何疑问或建议，欢迎在评论区留言。