• 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'
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    数据准备：

    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);

    • 1
    • 2
    • 3
    • 4

    图表的表设计：

    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 '逻辑删除字段'
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    问题

    如何选择一级分类时，查询下面的二级以及三级分类呢？具体来说，查询条件里面指定图表的一级分类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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    自上向下

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

    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;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    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;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    CategoryMapper.java接口定义：

    List<DashboardCategory> getCategoryListByParentId(Long parentId);

    • 1

    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>

    • 1
    • 2
    • 3
    • 4

    附录

    保存分类

    @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()));
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68

    查询分类

    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;
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68

    二元组定义

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

    • 1
    • 2
    • 3
    • 4
    • 5

    参考

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

  • 相关阅读:
    IDEA报错：Plugin ‘org.springframework.boot:spring-boot-maven-plugin:‘ not found
    将CString里的连续字符串压缩为一个
    echarts插件使用初级的记录
    计算机毕业设计ssm餐饮管理系统uto0o系统+程序+源码+lw+远程部署
    【完美云曦篇】新预告，云曦遭魔改被抓，石昊首秀九天劫光，反杀战王
    RabbitMQ：消息队列的卓越之选
    跨行新手学习并备考阿里云云计算专业认证ACP大概需要多长时间呢？
    django 内置 JSON 字段 使用场景
    【一知半解】synchronied
    第一篇章：JVM与Java体系结构
  • 原文地址：https://blog.csdn.net/lonelymanontheway/article/details/126319052