使用路径枚举法(Path Enumeration)实现分类CRUD
路径枚举法通过在分类表中添加一个path字段来存储从根分类到当前分类的完整路径，格式如1/2/3，其中数字代表分类ID。
1. 数据库表设计
CREATE TABLE category (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
path VARCHAR(255) NOT NULL COMMENT '存储从根到当前节点的路径，如1/2/3',
level INT NOT NULL COMMENT '分类层级(从1开始)',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_path (path),
INDEX idx_level (level)
);
2. 实体类设计
public class Category {
private Integer id;
private String name;
private String path;
private Integer level;
private LocalDateTime createdAt;
private LocalDateTime updatedAt;
// getters and setters
}
3. Mapper接口
@Mapper
public interface CategoryMapper {
// 创建分类
int insert(Category category);
// 根据ID查询
Category selectById(Integer id);
// 更新分类基本信息
int update(Category category);
// 更新路径(用于移动分类)
int updatePath(@Param("id") Integer id, @Param("newPath") String newPath);
// 删除分类
int delete(Integer id);
// 查询子分类
List<Category> selectChildren(String parentPath);
// 查询所有后代分类
List<Category> selectDescendants(String ancestorPath);
// 查询完整子树(包括自身)
List<Category> selectSubtree(String path);
}
4. Mapper XML实现
<!-- 插入分类 -->
<insert id="insert" useGeneratedKeys="true" keyProperty="id">
INSERT INTO category(name, path, level)
VALUES(#{name}, #{path}, #{level})
</insert>
<!-- 根据ID查询 -->
<select id="selectById" resultType="Category">
SELECT * FROM category WHERE id = #{id}
</select>
<!-- 更新基本信息 -->
<update id="update">
UPDATE category SET
name = #{name},
updated_at = NOW()
WHERE id = #{id}
</update>
<!-- 更新路径 -->
<update id="updatePath">
UPDATE category SET
path = #{newPath},
level = LENGTH(#{newPath}) - LENGTH(REPLACE(#{newPath}, '/', '')) + 1,
updated_at = NOW()
WHERE id = #{id}
</update>
<!-- 删除分类 -->
<delete id="delete">
DELETE FROM category WHERE id = #{id}
</delete>
<!-- 查询直接子分类 -->
<select id="selectChildren" resultType="Category">
SELECT * FROM category
WHERE path LIKE CONCAT(#{parentPath}, '/%')
AND level = LENGTH(#{parentPath}) - LENGTH(REPLACE(#{parentPath}, '/', '')) + 2
</select>
<!-- 查询所有后代分类 -->
<select id="selectDescendants" resultType="Category">
SELECT * FROM category
WHERE path LIKE CONCAT(#{ancestorPath}, '/%')
</select>
<!-- 查询完整子树(包括自身) -->
<select id="selectSubtree" resultType="Category">
SELECT * FROM category
WHERE path = #{path} OR path LIKE CONCAT(#{path}, '/%')
</select>
5. Service层实现
@Service
public class CategoryService {
@Autowired
private CategoryMapper categoryMapper;
/**
* 创建分类
* @param parentId 父分类ID，为null时创建根分类
* @param name 分类名称
*/
public Category createCategory(Integer parentId, String name) {
Category category = new Category();
category.setName(name);
if (parentId == null) {
// 创建根分类
category.setPath("");
category.setLevel(1);
} else {
Category parent = categoryMapper.selectById(parentId);
if (parent == null) {
throw new IllegalArgumentException("父分类不存在");
}
category.setPath(parent.getPath() + (parent.getPath().isEmpty() ? "" : "/") + parentId);
category.setLevel(parent.getLevel() + 1);
}
categoryMapper.insert(category);
// 更新path包含自己的ID
String newPath = category.getPath() + (category.getPath().isEmpty() ? "" : "/") + category.getId();
categoryMapper.updatePath(category.getId(), newPath);
category.setPath(newPath);
return category;
}
/**
* 移动分类到新的父分类下
* @param categoryId 要移动的分类ID
* @param newParentId 新的父分类ID，null表示移动到根
*/
public void moveCategory(Integer categoryId, Integer newParentId) {
Category category = categoryMapper.selectById(categoryId);
if (category == null) {
throw new IllegalArgumentException("分类不存在");
}
String newPath;
if (newParentId == null) {
newPath = "";
} else {
Category newParent = categoryMapper.selectById(newParentId);
if (newParent == null) {
throw new IllegalArgumentException("新的父分类不存在");
}
newPath = newParent.getPath() + (newParent.getPath().isEmpty() ? "" : "/") + newParentId;
}
// 获取旧路径前缀用于更新子分类
String oldPathPrefix = category.getPath();
// 更新当前分类路径
String currentNewPath = newPath + (newPath.isEmpty() ? "" : "/") + categoryId;
categoryMapper.updatePath(categoryId, currentNewPath);
// 更新所有子分类路径
List<Category> descendants = categoryMapper.selectDescendants(oldPathPrefix);
for (Category desc : descendants) {
String newDescPath = currentNewPath + desc.getPath().substring(oldPathPrefix.length());
categoryMapper.updatePath(desc.getId(), newDescPath);
}
}
/**
* 删除分类及其所有子分类
* @param categoryId 要删除的分类ID
*/
public void deleteCategory(Integer categoryId) {
Category category = categoryMapper.selectById(categoryId);
if (category == null) {
throw new IllegalArgumentException("分类不存在");
}
// 删除所有子分类
List<Category> descendants = categoryMapper.selectDescendants(category.getPath());
for (Category desc : descendants) {
categoryMapper.delete(desc.getId());
}
// 删除当前分类
categoryMapper.delete(categoryId);
}
/**
* 获取分类树
* @param parentId 父分类ID，null表示获取所有根分类
*/
public List<Category> getCategoryTree(Integer parentId) {
if (parentId == null) {
return categoryMapper.selectChildren("");
}
Category parent = categoryMapper.selectById(parentId);
if (parent == null) {
throw new IllegalArgumentException("父分类不存在");
}
return categoryMapper.selectChildren(parent.getPath() + (parent.getPath().isEmpty() ? "" : "/") + parentId);
}
/**
* 获取完整子树(包括自身)
*/
public List<Category> getSubtree(Integer categoryId) {
Category category = categoryMapper.selectById(categoryId);
if (category == null) {
throw new IllegalArgumentException("分类不存在");
}
return categoryMapper.selectSubtree(category.getPath());
}
}
6. Controller层实现
@RestController
@RequestMapping("/api/categories")
public class CategoryController {
@Autowired
private CategoryService categoryService;
@PostMapping
public ResponseEntity<Category> createCategory(
@RequestParam(required = false) Integer parentId,
@RequestParam String name) {
Category category = categoryService.createCategory(parentId, name);
return ResponseEntity.ok(category);
}
@PutMapping("/{id}/move")
public ResponseEntity<Void> moveCategory(
@PathVariable Integer id,
@RequestParam(required = false) Integer newParentId) {
categoryService.moveCategory(id, newParentId);
return ResponseEntity.ok().build();
}
@DeleteMapping("/{id}")
public ResponseEntity<Void> deleteCategory(@PathVariable Integer id) {
categoryService.deleteCategory(id);
return ResponseEntity.noContent().build();
}
@GetMapping
public ResponseEntity<List<Category>> getCategoryTree(
@RequestParam(required = false) Integer parentId) {
List<Category> categories = categoryService.getCategoryTree(parentId);
return ResponseEntity.ok(categories);
}
@GetMapping("/{id}/subtree")
public ResponseEntity<List<Category>> getSubtree(@PathVariable Integer id) {
List<Category> categories = categoryService.getSubtree(id);
return ResponseEntity.ok(categories);
}
}
7. 查询分类下的所有文章
在之前的表结构基础上，我们可以轻松查询一个分类及其所有子分类下的文章：
public interface ArticleMapper {
/**
* 查询分类及其子分类下的所有文章
*/
List<Article> selectByCategoryAndDescendants(String categoryPath);
}
<select id="selectByCategoryAndDescendants" resultType="Article">
SELECT a.* FROM article a
JOIN category c ON a.category_id = c.id
WHERE c.path = #{categoryPath} OR c.path LIKE CONCAT(#{categoryPath}, '/%')
ORDER BY a.created_at DESC
</select>
8. 路径枚举法的优缺点
优点：
- 查询效率高，特别是查询子树时
- 实现相对简单
- 可以轻松计算分类层级
缺点：
- 移动分类时需要更新所有子分类的路径
- 路径长度有限制(VARCHAR长度)
- 依赖应用层维护路径的正确性
9. 最佳实践建议
- 添加校验：确保路径格式正确，防止出现无效路径
- 事务处理：移动分类操作应该放在事务中
- 缓存：对频繁访问的分类树进行缓存
- 异步处理：对于大规模分类树的移动可以考虑异步处理
- 定期校验：定期运行校验脚本确保路径数据一致性
这种实现方式特别适合分类结构相对稳定、查询频繁但更新较少的场景。