[大数据]MySQL+Java实现父子级联查询

概述

在做后台网站(平台/系统)业务开发时，经常遇到层级概念。比如我最近在全权负责（开发+测试+产品）的一款数据产品就有分类的层级概念，层级有3层；另外产品有数据集、图表、看板、组合看板、数据推送等功能点（概念），这些功能点名称都有层级的概念。

举个例子：创建一个一级分类（最顶级）数据集。背景知识：数据集其实就是多段SQL，SQL里面可以有删表后建表的语句（drop then create table），那我可以在这个SQL里面创建一个最基础的表（table），只不过SQL的最后一个子句必须得是查询字句（数据集概念体现点）。然后我可以再创建一个二级分类的数据集，然后这个数据集的SQL可以使用一级分类数据集SQL里面的表（table），查询这个table，用来做图表。三级分类类推。

上图中，以/形式拼接返回多级分类名称，并给出层级的实现，参考附录。

分类表设计：

create table category(
    category_id      bigint auto_increment   primary key,
    category_name    varchar(100)            not null,
    type             int(1)    			     not null comment '1:数据集 2：图表 3：看板 4:组合看板',
    isactive         tinyint(1) default 1    not null comment '逻辑删除',
    parent_id        bigint                  null comment '父级id'
);

数据准备：

INSERT INTO category (category_id, category_name, type, isactive, parent_id) VALUES (869, '图表分类A', 2, 1, 898);
INSERT INTO category (category_id, category_name, type, isactive, parent_id) VALUES (882, '图表分类B', 2, 1, 869);
INSERT INTO category (category_id, category_name, type, isactive, parent_id) VALUES (888, '图表分类1', 2, 1, 898);
INSERT INTO category (category_id, category_name, type, isactive, parent_id) VALUES (898, '图表分类', 2, 1, null);

图表的表设计：

create table widget (
    widget_id         bigint auto_increment primary key,
    widget_name       varchar(100) 			not null comment '图表名称',
    category_id       bigint                not null comment '分类id',
    isactive          tinyint(1) default 1  not null comment '逻辑删除字段'
);

问题

如何选择一级分类时，查询下面的二级以及三级分类呢？具体来说，查询条件里面指定图表的一级分类ID，如何查询其下的二级和三级分类的图表？即所谓的MySQL级联（父子）查询。

实现

在构思实现方案前先Google一下，发现级联查询有两种情况：自下向上和自上向下。

自下向上

即：给定子级查询父级。每个子级肯定只有一个父级，实现起来相对容易些。这里直接给出实现SQL：

SELECT category_id
FROM (
         SELECT @r           AS _id,
                (
                    SELECT @r := parent_id
                    FROM category
                    WHERE category_id = _id
                )            AS parent_id,
                @l := @l + 1 AS lvl
         FROM (SELECT @r := 893, @l := 0) vars,
              category h
         WHERE @r <> 0
     ) T1
         JOIN category T2 ON T1._id = T2.category_id;

自上向下

即：给定父级查询全部子级。由于父级含有若干个子级，每个子级又有若干个子级，即形成一颗树的概念。

MySQL实现

通过SQL不好实现。可以通过定义函数的方式，不建议使用函数。

Java实现

实体类定义：

@Data
public class DashboardCategory {
    private Long categoryId;
    private String categoryName;
    private Integer type;
    private Boolean isactive;
    private Long parentId;
    /**
     * 非DB字段,表示第几级
     */
    private Integer level;
}

CategoryServiceImpl.java实现类，由于最多只有3级，故而可以两层for循环嵌套实现，参考下面的附录，业务代码在保存分类时有个数限制。因此for循环嵌套情况下，性能绝对不是问题：

/**
 * 根据分类ID查询子级分类ID
 *
 * @param categoryId 分类ID
 * @return 列表形式
 */
public List<Long> getChildIds(Long categoryId) {
    List<DashboardCategory> categoryList = categoryMapper.getCategoryListByParentId(categoryId);
    if (CollectionUtils.isEmpty(categoryList)) {
        return Lists.newArrayList(categoryId);
    }
    List<Long> result = Lists.newArrayList(categoryId);
    for (DashboardCategory it : categoryList) {
        result.add(it.getCategoryId());
        List<DashboardCategory> sonCategoryList = categoryMapper.getCategoryListByParentId(it.getCategoryId());
        for (DashboardCategory item : sonCategoryList) {
            result.add(item.getCategoryId());
        }
    }
    return result;
}

CategoryMapper.java接口定义：

List<DashboardCategory> getCategoryListByParentId(Long parentId);

CategoryMapper.xmlmapper定义：

<select id="getCategoryListByParentId" resultType="com.xy.cloudiview.common.model.DashboardCategory">
    SELECT category_id categoryId, parent_id parentId FROM category
    WHERE isactive = 1 AND parent_id = #{parentId}
</select>

附录

保存分类

@Value("${category.level.one:15}")
private Integer levelOne;
@Value("${category.level.two:10}")
private Integer levelTwo;
@Value("${category.level.three:10}")
private Integer levelThree;

public String saveCategory(JSONObject jsonObject) {
    try {
        DashboardCategory dashboardCategory = new DashboardCategory();
        Long levelOneId = jsonObject.getLong("levelOneId");
        Long levelTwoId = jsonObject.getLong("levelTwoId");
        Integer type = jsonObject.getInteger("type");
        if (levelOneId == null && levelTwoId != null) {
            return JSONObject.toJSONString(ServiceUtil.returnError("非法情况：一级为空，二级不为空"));
        }
        // 一级分类ID为空,则新建一级分类,先判断一级分类个数
        if (null == levelOneId) {
            int categoryCount = categoryMapper.selectFirstLevelCategoryCount(type);
            if (categoryCount >= levelOne) {
                return JSONObject.toJSONString(ServiceUtil.returnError(String.format("一级分类不得超过%d个！", levelOne)));
            }
            // 分类名重复校验
            List<DashboardCategory> list = categoryMapper.getCategoryListByCondition(jsonObject);
            if (CollectionUtils.isNotEmpty(list)) {
                return JSONObject.toJSONString(ServiceUtil.returnError("一级分类名不能重复"));
            }
            // 注意加上else
        } else if (null == levelTwoId) {
            // 一级分类ID不为空,二级分类ID为空,则新建二级分类,先判断所选一级分类下已有二级分类个数
            int categoryCount = categoryMapper.selectCategoryCountByParentId(levelOneId, type);
            if (categoryCount >= levelTwo) {
                return JSONObject.toJSONString(ServiceUtil.returnError(String.format("二级分类不得超过%d个！", levelTwo)));
            }
            List<DashboardCategory> list = categoryMapper.getCategoryListByCondition(jsonObject);
            if (CollectionUtils.isNotEmpty(list)) {
                return JSONObject.toJSONString(ServiceUtil.returnError("二级分类名不能重复"));
            }
            dashboardCategory.setParentId(levelOneId);
        }

        // 一级二级分类ID都不为空,则新建一个三级分类,父类ID,为二级分类ID
        if (null != levelOneId && null != levelTwoId) {
            int categoryCount = categoryMapper.selectCategoryCountByParentId(levelTwoId, type);
            if (categoryCount >= levelThree) {
                return JSONObject.toJSONString(ServiceUtil.returnError(String.format("三级分类不得超过%d个！", levelThree)));
            }
            List<DashboardCategory> list = categoryMapper.getCategoryListByCondition(jsonObject);
            if (CollectionUtils.isNotEmpty(list)) {
                return JSONObject.toJSONString(ServiceUtil.returnError("三级分类名不能重复"));
            }
            dashboardCategory.setParentId(levelTwoId);
        }
        dashboardCategory.setUserId(jsonObject.getString("userId"));
        dashboardCategory.setCategoryName(jsonObject.getString("categoryName"));
        dashboardCategory.setUpdateUserName(jsonObject.getString("updateUserName"));
        dashboardCategory.setType(type);
        int num = categoryMapper.insertSelective(dashboardCategory);
        if (num > 0) {
            return JSONObject.toJSONString(ServiceUtil.returnSuccess());
        } else {
            return JSONObject.toJSONString(ServiceUtil.returnError("添加分类失败！"));
        }
    } catch (Exception e) {
        logger.error("saveCategory error:{}", e.toString());
        return JSONObject.toJSONString(ServiceUtil.returnError(e.getMessage()));
    }
}

查询分类

categoryMapper.getCategoryById(id);，根据主键，即category_id查询，省略代码。

public String getCategoryList(JSONObject jsonObject) {
    try {
    	// 分页
        PageHelper.startPage(jsonObject.getInteger("pageNo"), jsonObject.getInteger("pageSize"));
        // 代码省略
        List<DashboardCategory> list = categoryMapper.getCategoryList(jsonObject);
        list.forEach(x -> {
            x.setCategoryName(this.getParentCategoryById(x.getCategoryId()).getT2());
            if (x.getParentId() == null) {
                x.setLevel(1);
            } else if (this.isLevelTwo(x)) {
                x.setLevel(2);
            } else {
                x.setLevel(3);
            }
        });
        PageInfo<DashboardCategory> pageInfo = new PageInfo<>(list);
        return JSONObject.toJSONString(ServiceUtil.returnSuccessData(pageInfo));
    } catch (Exception e) {
        logger.error("getCategoryList error:{}", e.toString());
        return JSONObject.toJSONString(ServiceUtil.returnError(e.getMessage()));
    }
}

/**
 * tuple.t1 为分类ID
 * tuple.t2 为分类名称
 */
public Tuple<String, String> getParentCategoryById(Long categoryId) {
    DashboardCategory result = categoryMapper.getCategoryById(categoryId);
    Long parentId = result.getParentId();
    Tuple<String, String> tuple = new Tuple<>();
    // 当父级ID为空时,此时为一级分类
    if (null == parentId) {
        tuple.setT1(Collections.singletonList(categoryId).toString());
        tuple.setT2(result.getCategoryName());
        return tuple;
    } else {
        DashboardCategory parentResult = categoryMapper.getCategoryById(parentId);
        // 表明parentResult是一级分类,result为二级分类
        if (null == parentResult.getParentId()) {
            tuple.setT1(Arrays.asList(parentResult.getCategoryId(), categoryId).toString());
            tuple.setT2(parentResult.getCategoryName().concat("/").concat(result.getCategoryName()));
            return tuple;
        } else {
            // 用parentResult的parentId当做categoryId去查询信息,lastResult为一级,parentResult为二级,result为三级
            DashboardCategory lastResult = categoryMapper.getCategoryById(parentResult.getParentId());
            tuple.setT1(Arrays.asList(lastResult.getCategoryId(), parentResult.getCategoryId(), categoryId).toString());
            tuple.setT2(lastResult.getCategoryName().concat("/").concat(parentResult.getCategoryName()).concat("/").concat(result.getCategoryName()));
            return tuple;
        }
    }
}

/**
 * 判断是否是二级
 */
private boolean isLevelTwo(DashboardCategory item) {
    if (item.getParentId() == null) {
        return false;
    }
    DashboardCategory po = categoryMapper.getCategoryById(item.getParentId());
    if (po == null) {
        return false;
    } else {
        return po.getParentId() == null;
    }
}

二元组定义

@Data
public class Tuple<T1, T2> {
    private T1 t1;
    private T2 t2;
}

参考

MySQL递归查询，实现上下级联查，父子级查询
你在用递归查询 Mysql 的树形结构吗