使用路径枚举法(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. 最佳实践建议

添加校验：确保路径格式正确，防止出现无效路径
事务处理：移动分类操作应该放在事务中
缓存：对频繁访问的分类树进行缓存
异步处理：对于大规模分类树的移动可以考虑异步处理
定期校验：定期运行校验脚本确保路径数据一致性

这种实现方式特别适合分类结构相对稳定、查询频繁但更新较少的场景。

Java

用路径枚举法(Path Enumeration)实现分类CRUD

MySQL WHERE IN 子句详解

MyBatis XML 中判断对象属性存在的封装方法

Java String工具类

文章