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：看板',
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一下，发现级联查询有两种情况：自下向上和自上向下。
自下向上
MySQL实现方案一
即：给定子级查询父级。每个子级肯定只有一个父级，实现起来相对容易些。这里直接给出实现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, -- 替换为#{childId}
category h
WHERE @r <> 0
) T1
JOIN category T2 ON T1._id = T2.category_id;
MySQL实现方案二
WITH RECURSIVE ... AS语法，MySQL 8.x 新增特性，递归查询，即临时表。注意一定得使用MySQL 8以上版本，否则会执行报错：
[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'recursive t(category_id, parentId) as ( select category_id, parent_id ' at line 1
这里直接给出SQL：
with recursive t(category_id, parentId) as (
select category_id, parent_id
from category
where category_id = 893 -- 替换为#{childId}
union all
select category.category_id, category.parent_id
from category
join t on category.category_id = t.parentId)
select category.category_id, category.parent_id
from category
join t on category.category_id = t.category_id;
可参考
- MySQL系列之MySQL8.0新特性
- https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/with.html
Java实现
很简单，代码略。
自上向下
即：给定父级查询全部子级。由于父级含有若干个子级，每个子级又有若干个子级，即形成一颗树的概念。可通过MySQL或Java代码来实现：
MySQL实现方案一
select category_id
from (
select t1.category_id,
t1.parent_id,
if(find_in_set(parent_id, @parentId) > 0, @parentId := concat(@parentId, ',', category_id), 0) as ischild
from (
select category_id, parent_id
from category t
where isactive = 1
) t1,
(select @parentId := 899) t2 -- 替换为#{parentId}
) t3
where ischild != 0;
MySQL实现方案二
使用WITH RECURSIVE ... AS语法，需要使用MySQL 8以上版本，直接给出查询SQL：
WITH RECURSIVE category_paths AS
(SELECT category_id,
parent_id,
category_name,
1 level -- 层级
FROM category
WHERE category.parent_id IS NULL
and category_id = 88 -- 替换为#{parentId}
UNION ALL
SELECT e.category_id,
e.parent_id,
e.category_name,
level + 1
FROM category e
INNER JOIN category_paths ep ON ep.category_id = e.parent_id)
SELECT category_id,
parent_id,
level,
category_name
FROM category_paths
ORDER BY level;
查询结果：
Java实现
实体类定义：
@Data
public class Category {
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<Category> categoryList = categoryMapper.getCategoryListByParentId(categoryId);
if (CollectionUtils.isEmpty(categoryList)) {
return Lists.newArrayList(categoryId);
}
List<Long> result = Lists.newArrayList(categoryId);
for (Category it : categoryList) {
result.add(it.getCategoryId());
List<Category> sonCategoryList = categoryMapper.getCategoryListByParentId(it.getCategoryId());
for (Category item : sonCategoryList) {
result.add(item.getCategoryId());
}
}
return result;
}
CategoryMapper.java接口定义：
List<Category> getCategoryListByParentId(Long parentId);
CategoryMapper.xmlmapper定义：
<select id="getCategoryListByParentId" resultType="com.johnny.common.model.Category">
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 {
Category category = new Category();
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<Category> 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<Category> list = categoryMapper.getCategoryListByCondition(jsonObject);
if (CollectionUtils.isNotEmpty(list)) {
return JSONObject.toJSONString(ServiceUtil.returnError("二级分类名不能重复"));
}
category.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<Category> list = categoryMapper.getCategoryListByCondition(jsonObject);
if (CollectionUtils.isNotEmpty(list)) {
return JSONObject.toJSONString(ServiceUtil.returnError("三级分类名不能重复"));
}
category.setParentId(levelTwoId);
}
category.setUserId(jsonObject.getString("userId"));
category.setCategoryName(jsonObject.getString("categoryName"));
category.setUpdateUserName(jsonObject.getString("updateUserName"));
category.setType(type);
int num = categoryMapper.insertSelective(category);
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<Category> 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<Category> 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) {
Category 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 {
Category 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为三级
Category 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(Category item) {
if (item.getParentId() == null) {
return false;
}
Category 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;
}
